CODEWITHSUNDEEP
jsonpostgresql
Naty Bizz
Naty Bizz

Reputation: 2342

Postgres - Merge two arrays into a JSON

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

Upvotes: 3

Views: 994

Answers (1)

Schwern
Schwern

Reputation: 165606

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

Upvotes: 4

Related Questions