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' }
]
}
]
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;