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

Question

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?

Answer 1

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

• jayarjo: It's crucial for me to have it the other way around - array of objects, not object of arrays. In real world scenario these ids are uuids and in the end I need to be able to do a partial match queries for autocomplete input on them.