4

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?

Improve this question
0

1 Answer 1

Reset to default
6

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
Improve this answer
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. Commented Jan 3, 2021 at 7:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.