I have a postgres query that returns a result set simplified as follows, with an id column and two json columns.

my_id     |  col_a       |  col_b
(integer) |  (json)      |  (json)
 5001        ["a", "b"]     <NULL>
 5001        <NULL>         ["c", "d"]

My question is: what is the best way in postgres to aggregate this result? e.g.:

my_id     |  col_a       |  col_b
(integer) |  (json)      |  (json)
 5001        ["a", "b"]     ["c", "d"]
  • please, add your query.
    – McNets
    Apr 21 '17 at 23:46

Unless I don't know your actual query, you can JOIN rows where col_a is not null with rows where col_b is not null.

create table test(my_id int, col_a json, col_b json);
insert into test values
(5001, '["a", "b"]', null),
(5001, null, '["c", "d"]');
    select t1.my_id, t1.col_a, t2.col_b 
    from test t1
    inner join (select my_id, col_b
                from test
                where col_b is not null) t2
    on t1.my_id = t2.my_id
    where t1.col_a is not null;
my_id | col_a      | col_b     
----: | :--------- | :---------
 5001 | ["a", "b"] | ["c", "d"]

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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