如何在PostgreSQL的ARRAY_AGG行中关联查询其他表？

现有以下数据表：

team表 #

player表 #

coach表 #

skill表 #

当前使用以下PostgreSQL查询获取指定球队的所有球员，同时返回球队自身及教练信息：

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'

该查询运行正常，但现在需要为ARRAY_AGG中的每个球员关联对应的main_skill信息，请问如何实现？

注：此SQL由后端根据GraphQL请求字段动态生成。

当前查询结果 #

期望查询结果 #

方法1：在子查询中关联skill表构造球员完整信息 #

修改生成players数组的子查询，将player表与skill表关联，用ROW()函数构造包含main_skill的复合结构后再聚合：

SELECT
  "team".*,
  ( SELECT coach FROM "coach" WHERE "coach"."id" = "team".coach_id ) AS coach,
  (
    SELECT ARRAY_AGG(
      ROW(
        p.id,
        p.name,
        p.team_id,
        p.main_skill_id,
        s AS main_skill
      )
    )
    FROM "player" p
    LEFT JOIN "skill" s ON p.main_skill_id = s.id
    WHERE p.team_id = "team".id
  ) AS players
FROM
  "team"
WHERE
  "team"."id" = '123'

如果球员可能没有主技能，用LEFT JOIN可以保证即使main_skill_id为空，球员信息仍会被保留。

方法2：使用JOIN+GROUP BY实现聚合 #

如果动态生成SQL时更倾向于关联写法，可以先关联所有表，再按球队分组聚合：

SELECT
  t.*,
  c AS coach,
  ARRAY_AGG(
    ROW(p.id, p.name, p.team_id, p.main_skill_id, s AS main_skill)
  ) AS players
FROM "team" t
LEFT JOIN "coach" c ON t.coach_id = c.id
LEFT JOIN "player" p ON t.id = p.team_id
LEFT JOIN "skill" s ON p.main_skill_id = s.id
WHERE t.id = '123'
GROUP BY t.id, t.name, t.coach_id, c;

这种写法需要确保分组字段包含球队的所有列以及教练对象，保证分组逻辑正确。

内容的提问来源于stack exchange，提问作者Fred Hors