0

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"]
Improve this question
1
  • please, add your query. Commented Apr 21, 2017 at 23:46

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.