Search code examples
sqlpostgresqljsonb

Postgresql, jsonb with multiple keys is returning a single row for each key

Here's what my situation is. I have rows that have a json column, and what I've been trying to do is get all the values for all the keys in that json in just one row.

let's say if I have row with the json value:

{"key1": "a", "key2": "b"}

Now, is it possible to extract the values as such: ["a", "b"]?

I attempted this so far:

select ---- some sum() fields ----,
b.match_data::json -> jsonb_object_keys(b.match_data) as "Course"
from --- tables ---
join -- tables ---
where -- condition ---
group by -- sum() fields ----, b.match_data

The problem with this is that for json with multiple keys, it is returning multiple rows.

Solution

  • S-Man's answer gave me a direction to use aggregators, and after a few trial and errors I got my answer

    (select string_agg((select value from jsonb_array_elements_text(value)), ',')
from jsonb_each(b.match_data)) "Course"

    It collects and displays values as a, b,... in one single row.