I have a table with a json as a field. The structure of a json is completly random but it never has inner objects inside.
| data |
|---|
| {"test": "baa-1496", "test2": "baa-1431", "test3": "baa-1497"} |
| {"test": "baa-1452", "test4": "baa-1597"} |
i want to turn this table into this
| key | value |
|---|---|
| test | "baa-1496", "baa-1452" |
| test2 | "baa-1431" |
| test3 | "baa-1497" |
| test4 | "baa-1597" |
where each value from each json is spliced into different row and values with same keys united in one row as array
json probably isn't the best option for this, but we need it for other purposes
does anybody know, how to achive this by any chance? I can't see any useful functions in postgres to make this work.
Having
create table t1 (data jsonb);
Just expand the top-level JSON object into a type record set of key/value pairs, then separate the fields of record by a lateral join then aggregate corresponding values to their keys, like:
select j.key, string_agg(j.value, ', ') as value
from t1 t, lateral jsonb_each_text(t.data) j
group by j.key
order by j.key;