0

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"}})"}
Fred Hors
  • 3,258
  • 3
  • 25
  • 71

2 Answers2

1

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

Pepe N O
  • 1,678
  • 1
  • 7
  • 11
  • Can you please re-read my question that I think is complete now? – Fred Hors May 12 '23 at 16:04
  • Solution completed according to your question edits. – Pepe N O May 12 '23 at 16:39
  • Is there a way to avoid the `inner join`? – Fred Hors May 12 '23 at 16:50
  • No because it establish a relationship between the two tables, however if the relationship between them is 1:1, is redundant have two tables when all fields can be in one (a reason for having two tables with a rel 1:1 would be a table that contains a large number of columns and you want to split it into two tables for performance reasons). – Pepe N O May 12 '23 at 16:59
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.

Albina
  • 1,901
  • 3
  • 7
  • 19