Combine two JSON objects in PostgreSQL

json postgresql postgresql-9.4

Travis · Oct 29, 2015 · Viewed 10.8k times · Source

I have two JSON rows in a PostgreSQL 9.4 table:

      the_column      
----------------------
 {"evens": [2, 4, 6]}
 {"odds": [1, 3, 5]}

I want to combine all of the rows into one JSON object. (It should work for any number of rows.)

Desired output:

{"evens": [2, 4, 6], "odds": [1, 3, 5]}

Answer

Use json_agg() to get an array:

SELECT json_agg(source_column) AS the_column    
FROM   tbl;

Or json_each() in a LATERAL join and json_object_agg() to assemble elements:

SELECT json_object_agg(key, value) AS the_column
FROM   tbl, json_each(data);