PostgreSQL多对多关联查询：同时关联演员与制片商表问题

问题根源 #

同时关联两个多对多中间表（movies_actors和movies_studios）时，会产生笛卡尔积：假设某部电影有2个演员、3个制片商，JOIN后会生成6条重复的电影记录。直接用GROUP BY movies.id要么会出现聚合结果重复（演员/制片商多次出现在数组里），要么因非聚合列未包含在GROUP BY中触发PostgreSQL的语法限制（除非movies.id是主键，但重复数据问题依然存在）。

推荐解决方案 #

方案1：子查询预聚合后关联 #

先分别对演员、制片商按电影ID聚合生成JSON数组，再和主表关联，从根源避免笛卡尔积。

SELECT
  m.*,
  -- 处理无演员的情况，返回空数组
  COALESCE(a.actors, '[]'::JSON) AS actors,
  -- 处理无制片商的情况，返回空数组
  COALESCE(s.studios, '[]'::JSON) AS studios
FROM movies m
-- 关联预聚合的演员数据
LEFT JOIN (
  SELECT
    ma.movie_id,
    JSON_AGG(
      JSON_BUILD_OBJECT(
        'id', a.id,
        'name', a.name,
        'gender', a.gender -- 根据actors表实际字段调整
      )
    ) AS actors
  FROM movies_actors ma
  JOIN actors a ON ma.actor_id = a.id
  GROUP BY ma.movie_id
) a ON m.id = a.movie_id
-- 关联预聚合的制片商数据
LEFT JOIN (
  SELECT
    ms.movie_id,
    JSON_AGG(
      JSON_BUILD_OBJECT(
        'id', s.id,
        'name', s.name,
        'location', s.location -- 根据studios表实际字段调整
      )
    ) AS studios
  FROM movies_studios ms
  JOIN studios s ON ms.studio_id = s.id
  GROUP BY ms.movie_id
) s ON m.id = s.movie_id;

方案2：使用LATERAL JOIN #

通过LATERAL JOIN为每一部电影单独查询对应的演员和制片商列表，同样避免笛卡尔积，逻辑更直观。

SELECT
  m.*,
  COALESCE(a.actors, '[]'::JSON) AS actors,
  COALESCE(s.studios, '[]'::JSON) AS studios
FROM movies m
-- 为每个电影查询演员列表
LEFT JOIN LATERAL (
  SELECT JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', a.id,
      'name', a.name,
      'gender', a.gender
    )
  ) AS actors
  FROM movies_actors ma
  JOIN actors a ON ma.actor_id = a.id
  WHERE ma.movie_id = m.id
) a ON true
-- 为每个电影查询制片商列表
LEFT JOIN LATERAL (
  SELECT JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', s.id,
      'name', s.name,
      'location', s.location
    )
  ) AS studios
  FROM movies_studios ms
  JOIN studios s ON ms.studio_id = s.id
  WHERE ms.movie_id = m.id
) s ON true;

不推荐的临时方案（仅用于小数据量） #

如果数据量很小，也可以通过JSON_AGG(DISTINCT ...)去重，但性能较差，不适合大数据场景：

SELECT
  m.id,
  m.title,
  m.release_year, -- 列出movies表所有需要的字段
  JSON_AGG(DISTINCT JSON_BUILD_OBJECT('id', a.id, 'name', a.name)) AS actors,
  JSON_AGG(DISTINCT JSON_BUILD_OBJECT('id', s.id, 'name', s.name)) AS studios
FROM movies m
LEFT JOIN movies_actors ma ON m.id = ma.movie_id
LEFT JOIN actors a ON ma.actor_id = a.id
LEFT JOIN movies_studios ms ON m.id = ms.movie_id
LEFT JOIN studios s ON ms.studio_id = s.id
GROUP BY m.id, m.title, m.release_year; -- 必须包含所有非聚合字段

内容的提问来源于stack exchange，提问作者Nicola Gaioni