How to query other tables in ARRAY_AGG rows?

Question

I have the below tables:

• team

  col_name	type
  id	string
  name	string
  coach_id	string

• player

  col_name	type
  id	string
  name	string
  team_id	string
  main_skill_id	string

• coach

  col_name	type
  id	string
  name	string

• skill

  col_name	type
  id	string
  name	string

I'm using the below query in PostgreSQL to retrieve all the team's players, along with the team itself and the coach:

SELECT
  "team".*,
( SELECT coach FROM "coach" WHERE "coach"."id" = "team".coach_id ) AS coach,
( SELECT ARRAY_AGG ( player ) FROM "player" WHERE "player".team_id = "team"."id" ) AS players,
FROM
    "team"
WHERE
    "team"."id" = '123'

This is working amazing!

But now I need to query the main_skill of the player for those players in ARRAY_AGG.

How to do that?

This is an SQL query I'm generating in my backend based on possibly asked graphql fields.

The actual result is:

id	name	coach_id	coach	players
1	The good ones	1	{1,"Bob"}	{"(1,"John",1,1)","(2,"Tom",1,2)"}

The result I need is:

id	name	coach_id	coach	players
1	The good ones	1	{1,"Bob"}	{"(1,"John",1,1,{main_skill:{1,"MainSkill1Name"}})","(2,"Tom",1,2,{main_skill:{2,"MainSkill2Name"}})"}

Answer 1

If you really need a json object inside players array column you can use this query:

with player_data as (
  select player.id as player_id,
       player.name as player_name,
       player.team_id as player_team_id,
       player.main_skill_id as player_skill_id,
       json_build_object('main_skill', array[skill.id, skill.name]) as skill_json
  from player
  join skill on player.main_skill_id = skill.id)
select team.*,
       (select coach from coach where coach.id = team.coach_id) as coach,
       (select ARRAY_AGG(player_data) from player_data where player_data.player_team_id = team.id) as players
from team
where team.id = '1';

Details:

• The additional query builds a json object using json_build_object() function, and returns all the necessary in a player_data table:

  select player.id as player_id,
       player.name as player_name,
       player.team_id as player_team_id,
       player.main_skill_id as player_skill_id,
       json_build_object('main_skill', array[skill.id, skill.name]) as skill_json
  from player
  join skill on player.main_skill_id = skill.id

• Then player_data results are aggregated into one array in the outer query.

See the demo.

Answer 2

Just create a matrix for your required fields (all of player and all of skill tables) like this:

(select array_agg(array[p.id, p.name, p.team_id, p.main_skill_id, s.id, s.name]) 
from player p inner join
skill s on p.main_skill_id = s.id
where p.team_id = team.id)

The complete query would be like

SELECT
  team.*,
( SELECT coach FROM coach WHERE coach.id = team.coach_id ) AS coach,
( SELECT ARRAY_AGG ( array[p.id,p."name",p.team_id,p.main_skill_id,s.id,s."name"]  ) 
  FROM player p inner join skill s on  p.main_skill_id = s.id 
  WHERE p.team_id = team.id ) AS players
FROM
    team
WHERE
    team.id = '1';

Working sample here