Transform JSON map to an array in Postgres via update statement

Question

I have a postgresql table called datasource with jsonb column called data. It has the following structure:

{  
   "key1":{"param1" : "value1", "param2" : "value2"},
   "key2":{"param2_1" : "value2_1", "param2_2" : "value2_2"},
   "key3":{"param3_1" : "value3_1", "param3_2" : "value3_2"}  
}

Is there any way to write some UPDATE script to transform given JSON to the following:

[  
   {"key": "key1", "param1" : "value1", "param2" : "value2"},
   {"key": "key2", "param2_1" : "value2_1", "param2_2" : "value2_2"},
   {"key": "key3", "param3_1" : "value3_1", "param3_2" : "value3_2"}  
]

GMB · Accepted Answer · 2020-09-08 21:54:48Z

5

You can unnest the object to rows in a lateral join, then aggregate back into an array:

select d.*, x.*
from datasource d
cross join lateral (
    select jsonb_agg(jsonb_build_object('key', j.k) || j.v) new_data
    from jsonb_each(d.data) as j(k, v)
) x

Demo on DB Fiddle - with jsonb_pretty() enabled:

If you wanted an update statement:

update datasource d
set data = (
    select jsonb_agg(jsonb_build_object('key', j.k) || j.v)
    from jsonb_each(d.data) as j(k, v)
)

edited Sep 8, 2020 at 21:54

answered Sep 8, 2020 at 21:48

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transform JSON map to an array in Postgres via update statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related