如何在PostgreSQL中用json_agg合并多组一对多关联结果？

我明白你现在的需求：主表ft_references和多个表通过中间表实现一对多关联，要把每个关联的结果都以JSON数组形式返回，同时合并多组关联的查询结果。

首先得提醒你一个容易踩的坑：别直接把所有关联表都JOIN进来再聚合，那样会产生笛卡尔积。比如你有一个主记录对应3个分类和2个作者，直接JOIN会生成3×2=6条记录，最后聚合出来的分类数组会重复每条分类3次，作者数组重复每条作者2次，数据完全不对。

下面给你两种靠谱的实现方式，都能避免笛卡尔积问题：

方法一：用子查询逐个聚合 #

这种方式最直观，每个关联字段单独用子查询计算聚合结果，彼此独立不干扰：

SELECT
  ft.id,
  -- 聚合分类数据
  (
    SELECT json_agg(genres)
    FROM genre_references gr
    INNER JOIN genres_catalog genres ON gr.genre_id = genres.id
    WHERE gr.reference_id = ft.id
  ) AS genres,
  -- 聚合作者数据（假设你有authors相关的中间表和目录表）
  (
    SELECT json_agg(authors)
    FROM author_references ar
    INNER JOIN authors_catalog authors ON ar.author_id = authors.id
    WHERE ar.reference_id = ft.id
  ) AS authors,
  -- 聚合标签数据（同理可扩展其他关联）
  (
    SELECT json_agg(tags)
    FROM tag_references tr
    INNER JOIN tags_catalog tags ON tr.tag_id = tags.id
    WHERE tr.reference_id = ft.id
  ) AS tags
FROM ft_references ft
WHERE ft.id = 2;

如果某个关联没有数据，对应的字段会返回NULL，要是想返回空数组[]，可以用COALESCE包裹子查询，比如：
COALESCE((SELECT json_agg(...) ...), '[]'::json) AS genres

方法二：使用LATERAL JOIN聚合 #

这种方式更灵活，适合关联逻辑复杂的场景，用LEFT JOIN LATERAL还能保证主记录始终被返回，即使某些关联没有数据：

SELECT
  ft.id,
  COALESCE(g.genres, '[]'::json) AS genres,
  COALESCE(a.authors, '[]'::json) AS authors,
  COALESCE(t.tags, '[]'::json) AS tags
FROM ft_references ft
LEFT JOIN LATERAL (
  SELECT json_agg(genres) AS genres
  FROM genre_references gr
  INNER JOIN genres_catalog genres ON gr.genre_id = genres.id
  WHERE gr.reference_id = ft.id
) g ON true
LEFT JOIN LATERAL (
  SELECT json_agg(authors) AS authors
  FROM author_references ar
  INNER JOIN authors_catalog authors ON ar.author_id = authors.id
  WHERE ar.reference_id = ft.id
) a ON true
LEFT JOIN LATERAL (
  SELECT json_agg(tags) AS tags
  FROM tag_references tr
  INNER JOIN tags_catalog tags ON tr.tag_id = tags.id
  WHERE tr.reference_id = ft.id
) t ON true
WHERE ft.id = 2;

额外优化提示 #

• 如果不需要返回关联表的所有字段，可以用json_build_object自定义JSON结构，减少数据量：
  json_agg(json_build_object('id', genres.id, 'name', genres.name))
• 确保中间表的reference_id字段、关联表的id字段都创建了索引，这样聚合查询的性能会提升很多。

内容的提问来源于stack exchange，提问作者Webdev Tory