up vote 2 down vote favorite

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

share|improve this question
    
Please post what you have tried so far. – Rohit Gupta Oct 29 '15 at 1:00
    
@RohitGupta - Please don't edit posts to provide worthless punctuation. Only contribute edits that meaningfully improve posts. – Brad Larson♦ Oct 29 '15 at 22:36
up vote 1 down vote accepted

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);
share|improve this answer
1  
(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
up vote 0 down vote

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.