Postgres, aggregate result to a single row using values from a column

Question

In Postgres,

I am trying to aggregate results from a table that looks like this:

id    restaurant_id       title          category
-------------------------------------------------
1          2               pizza          main
2          2               pasta          main
3          2               coke           drink
4          2               beer           drink
5          2               fries          side
6          3               chips          side

I would like to return this type of result.

main                               drink                      side
--------------------------------------------------------------------------------------
[{id:1, title: pizza},...]        [{id:3, title: coke},...]   [{id:5, title: fries},...]

I need the result to be only one row as this is part of a bigger query. I have been trying quite a few things in the last couple of days with my limited Postgres knowledge but haven't managed to get anything close to what I need.

Some help would be greatly appreciated.

Kevin Amiranoff · Accepted Answer · 2020-03-20 18:11:38Z

You can use conditional aggregation and json functions:

select
    jsonb_agg(jsonb_build_object('id', id, 'title', title)) 
        filter(where category = 'main') main,
    jsonb_agg(jsonb_build_object('id', id, 'title', title)) 
        filter(where category = 'drink') drink,
    jsonb_agg(jsonb_build_object('id', id, 'title', title)) 
        filter(where category = 'side') side
from mytable

Demo on DB Fiddle:

main                                                           | drink                                                        | side                                                          
:------------------------------------------------------------- | :----------------------------------------------------------- | :-------------------------------------------------------------
[{"id": "1", "title": "pizza"}, {"id": "2", "title": "pasta"}] | [{"id": "3", "title": "coke"}, {"id": "4", "title": "beer"}] | [{"id": "5", "title": "fries"}, {"id": "6", "title": "chips"}]

Collectives™ on Stack Overflow

Postgres, aggregate result to a single row using values from a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related