Postgres多对多关系（3表及以上）的JSON聚合查询实现

数据库Schema #

CREATE TABLE first 
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE second 
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE third 
(
    id SERIAL PRIMARY KEY
);

CREATE TABLE first_second 
(
    first_id INTEGER REFERENCES first(id),
    second_id INTEGER REFERENCES second(id),
    PRIMARY KEY (first_id, second_id)
);

CREATE TABLE second_third 
(
    second_id INTEGER REFERENCES second(id),
    third_id INTEGER REFERENCES third(id),
    PRIMARY KEY (second_id, third_id)
);

目标JSON输出格式 #

[
  {
    "id": 1,
    "seconds": [
      {
        "id": 2,
        "thirds": [
          {
            "id": 3
          }
        ]
      }
    ]
  }
]

现有两表关联聚合代码 #

SELECT 
    JSON_AGG(firsts) AS firsts 
FROM 
    (SELECT 
         f."id", 
         JSON_AGG(JSON_BUILD_OBJECT('id', s."id")) AS seconds 
     FROM 
         "first" f 
     INNER JOIN 
         "first_second" fs ON fs."first_id" = f."id"
     INNER JOIN 
         "second" s ON s."id" = fs."second_id" 
     GROUP BY 
         f."id") AS firsts

三表关联聚合查询实现 #

要实现三层嵌套的JSON结构，需要从最内层的third表开始聚合，再逐层向外关联聚合。以下是具体实现代码：

SELECT JSON_AGG(first_obj) AS result
FROM (
    SELECT 
        f.id,
        JSON_AGG(second_obj) AS seconds
    FROM first f
    JOIN first_second fs ON fs.first_id = f.id
    JOIN (
        SELECT 
            s.id,
            COALESCE(JSON_AGG(JSON_BUILD_OBJECT('id', t.id)), '[]'::JSON) AS thirds
        FROM second s
        LEFT JOIN second_third st ON st.second_id = s.id
        LEFT JOIN third t ON t.id = st.third_id
        GROUP BY s.id
    ) AS second_obj ON second_obj.id = fs.second_id
    GROUP BY f.id
) AS first_obj;

代码说明 #

1. 最内层子查询：先对second表和关联的third表进行聚合，生成每个second对应的thirds数组，用COALESCE确保没有关联third时返回空数组而非null。
2. 中间层关联：将第一步得到的包含thirds的second数据，与first表通过中间表first_second关联，再聚合生成每个first对应的seconds数组。
3. 最外层聚合：将所有first对象聚合为最终的JSON数组。

如果需要保留没有关联second的first记录，可以把JOIN改成LEFT JOIN，同时用COALESCE处理seconds的空值情况：

SELECT JSON_AGG(first_obj) AS result
FROM (
    SELECT 
        f.id,
        COALESCE(JSON_AGG(second_obj) FILTER (WHERE second_obj.id IS NOT NULL), '[]'::JSON) AS seconds
    FROM first f
    LEFT JOIN first_second fs ON fs.first_id = f.id
    LEFT JOIN (
        SELECT 
            s.id,
            COALESCE(JSON_AGG(JSON_BUILD_OBJECT('id', t.id)), '[]'::JSON) AS thirds
        FROM second s
        LEFT JOIN second_third st ON st.second_id = s.id
        LEFT JOIN third t ON t.id = st.third_id
        GROUP BY s.id
    ) AS second_obj ON second_obj.id = fs.second_id
    GROUP BY f.id
) AS first_obj;

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