Question 230077 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to turn random jsonb in postgres into kv table

ы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.

Tags:

2

Answers


  1. 0

    Use a combination of jsonb_each() and jsonb_agg() to achieve this:

    select key, jsonb_agg(value) as value
  from some_table
       cross join lateral jsonb_each_text(data) as e(key, value)
 group by key;

    If the objective is a text[] array, then use array_agg():

    select key, array_agg(value) as value
  from some_table
       cross join lateral jsonb_each_text(data) as e(key, value)
 group by key;

    If you just want a comma-separated string for value, then use string_agg():

    select key, string_agg(value, ',') as value
  from some_table
       cross join lateral jsonb_each_text(data) as e(key, value)
 group by key;

    Working fiddle

    Login or Signup to reply.
  2. 0

    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;

    Working example here and the docs

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search