postgresql: grouping with json columns, eliminating nulls

Question

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

• McNets: please, add your query.

Answer 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