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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.