PostgreSQL - How to return array of objects with nested array using joins

Question

I know basic PostgreSQL usage, but I'm looking to return data formatted in a specific way. The current query below lists all team names with their division name. I basically need to list all teams UNDER their respective division. I believe I need some sort of SELECT DISTINCT call but I am absolutely lost on what to correct terms to search for. Can anyone point me in the right direction? Is it best to do it in the Postgres query or should it be handled on the server (after the current response is returned)?

Minimal examples below, Teams and Divisions tables are basically set up as id and name

Pivot table

id | team_id | season_id | division_id
--------------------------------------
1  |    3    |     1     |      3   
2  |    4    |     1     |      3   
3  |    5    |     1     |      3   
4  |    6    |     1     |      3

Query to list all teams and their division name

SELECT t.name AS team_name, d.name AS division_name FROM team_season_division tsd
JOIN teams t ON t.id = tsd.team_id
JOIN divisions d ON d.id = tsd.division_id
WHERE tsd.season_id = 1;

Current response

[
    {
        team_name: 'synthesize onlines',
        division_name: 'A1',
    },
    {
        team_name: 'array arrays',
        division_name: 'A1',
    },
    {
        team_name: 'quantify matrixs',
        division_name: 'B1',
    }
]

Example response desired

[
    {
        division_name: 'A1',
        teams_in_division: [ 
            { name: 'synthesize onlines' }, 
            { name: 'array arrays' },
            { name: 'mobile microchips' } 
        ]
    },
    {
        division_name: 'B1',
        teams_in_divisions: [ 
            { name: 'quantify matrixs' }, 
            { name: 'matrix matrixs' }, 
            { name: 'hack hacks' }, 
            { name: 'bluetooth generates' }, 
            { name: 'override protocols' } 
        ]
    }
]

how do you get such a result containing JSON values from this query? How do you define those columns within the table's definition ? What's the relation between the pivot table and the query? — Barbaros Özhan
– Barbaros Özhan, Commented May 16, 2020 at 22:57

GMB · Accepted Answer · 2020-05-17 00:00:43Z

You can use aggregation. I would suggest putting the team list in an array of json(b) objects, using jsonb_agg() and jsonb_build_object():

select 
    d.name as division_name 
    jsonb_agg(jsonb_build_object('name', t.name)) as teams_in_division 
from team_season_division tsd
inner join teams t on t.id = tsd.team_id
inner join divisions d on d.id = tsd.division_id
where tsd.season_id = 1
group by d.division_id, d.name;

If you want the results as a single json array, you can add another level of aggregation:

select jsonb_agg(
    jsonb_build_object(
        'division_name', division_name,
        'teams_in_division', teams_in_division 
    )
) res
from (
    select 
        d.name as division_name 
        jsonb_agg(jsonb_build_object('name', t.name)) as teams_in_division 
    from team_season_division tsd
    inner join teams t on t.id = tsd.team_id
    inner join divisions d on d.id = tsd.division_id
    where tsd.season_id = 1
    group by d.division_id, d.name
) t;

Collectives™ on Stack Overflow

PostgreSQL - How to return array of objects with nested array using joins

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