CODEWITHSUNDEEP
sqlarraysjsonpostgresqlsql-update
avarabyeu
avarabyeu

Reputation: 736

Transform JSON map to an array in Postgres via update statement

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"}  
]

Upvotes: 0

Views: 252

Answers (1)

GMB
GMB

Reputation: 222582

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:

enter image description here

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)
)

Upvotes: 5

Related Questions