CODEWITHSUNDEEP
jsonpostgresqlsql-update
Jack Brooks
Jack Brooks

Reputation: 1

update each value in json array

I have a table that currently looks like this:

id tags
1 {"key1" : "val1", "key2" : "val2" }

I want it to look like this:

id tags
1 {"key1" : ["val1"], "key2" : ["val2"] }

I'm not sure how to write a PSQL query that will transform each value in the json array.

Upvotes: 0

Views: 36

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

-- simple way 
select jsonb_object_agg(t.a, jsonb_build_array(t.b)) from jsonb_each_text('{"key1" : "val1", "key2" : "val2" }'::jsonb) as t(a, b)


-- for multiple json rows
with t1(id, jsondata) as 
(
    select 1, '{"key1" : "val1", "key2" : "val2" }'::jsonb
    union all 
    select 2, '{"key1" : "val3", "key2" : "val4" }'::jsonb
) 
select t1.id, jsonb_object_agg(t2.a, jsonb_build_array(t2.b)) from t1 
cross join jsonb_each_text(t1.jsondata) t2(a, b)
group by t1.id

Upvotes: 0

Related Questions