15

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

Braiam
  • 1
  • 11
  • 47
  • 78
Travis
  • 1,998
  • 1
  • 21
  • 36

2 Answers2

24

Use json_agg() to build 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 object with unnested key/value pairs (your desired output):

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

db<>fiddle here

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 3
    (For clarity, note that in his answer, `data` is a json column in the table `tbl`. - If it's a jsonb column, remember to use `jsonb_each`.) – Travis Oct 29 '15 at 15:39
19

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)
Suhaib Janjua
  • 3,538
  • 16
  • 59
  • 73
Arthur Nascimento
  • 941
  • 1
  • 8
  • 3