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]}
Use json_agg() to build an array of objects:
SELECT json_agg(the_column) AS result
FROM tbl;
Or json_each() in a LATERAL join and json_object_agg() to build an object with unnested key/value pairs (your desired output):
SELECT json_object_agg(key, value) AS the_column
FROM tbl, json_each(the_column);
data is a json column in the table tbl. - If it's a jsonb column, remember to use jsonb_each.)FYI, if someone's using jsonb in >= 9.5 and they only care about top-level elements being merged without duplicate keys, then it's as easy as using the || operator:
select '{"evens": [2, 4, 6]}'::jsonb || '{"odds": [1, 3, 5]}'::jsonb;
?column?
-----------------------------------------
{"odds": [1, 3, 5], "evens": [2, 4, 6]}
(1 row)
