I have two text[] columns like this:
Col1:
{itema, itemb, itemc}
Col2
{valuea, valueb, valuec}
How can I merge those two arrays into a json? like this:
{"itema":"valuea", "itemb":"valueb", "itemc":"valuec"}
Add a comment
|
1 Answer
Use unnest to turn the arrays into rows.
select unnest(col1) as col1, unnest(col2) as col2
from test;
col1 | col2
-------+--------
itema | valuea
itemb | valueb
itemc | valuec
Then use that in a subquery to jsonb_object_agg to aggregate the rows together as key/value pairs.
select jsonb_object_agg(col1, col2)
from (
select unnest(col1) as col1, unnest(col2) as col2
from test
) t;
jsonb_object_agg
----------------------------------------------------------------
{ "itema" : "valuea", "itemb" : "valueb", "itemc" : "valuec" }
