2

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?

0
2

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
1
  • 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.
    – jayarjo
    Jan 3 '21 at 7:10

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.