Question 167860 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to query other tables in ARRAY_AGG rows?

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"}})"}
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search