How to aggregate only unique and not null values from column into a JSON array?

Suppose I have a table like this:

| id | linked_id | another_id |
|----|-----------|------------|
| 1  | 1         | 1          |
| 1  | 2         | null       |
| 1  | 3         | 1          |

I would like to aggregate results by id - collect linked_ids and another_ids into a JSON objects. So in the end what I would like to get is this:

id: 1
linked_ids: [{ id: 1 }, { id: 2 }, { id: 3 }]
another_ids: [{ id: 1 }]

Notice how in the case of another_id only unique ids were aggregated and null got ignored. I'm able to achieve this with regular arrays by using:

array_agg(distinct another_id) filter (where another_id is not null).

However I need to get JSON array of objects in the end, not regular one. Any ideas?

You can use jsonb_agg() instead:

select id, 
       jsonb_agg(linked_id) as linked_ids, 
       jsonb_agg(distinct another_id) filter (where another_id is not null) as other_ids
from the_table
group by id;

If you want a single JSON value for each row (=group) you can use to_jsonb()

select to_jsonb(r)
from (
  select id, 
         jsonb_agg(linked_id) as linked_ids, 
         jsonb_agg(distinct another_id) filter (where another_id is not null) as other_ids
  from the_table
  group by id
) r

Or using jsonb_build_object()

select jsonb_build_object(
         'id', id, 
         'linked_ids', jsonb_agg(linked_id),
         'other_ids', jsonb_agg(distinct another_id) filter (where another_id is not null)
       )
from the_table
group by id

StackExchange Database Administrators Q#282451, answer score: 3