编写SQL查询生成嵌套JSON：关联Collections、Groups与Items表

针对你的需求，我们可以通过PostgreSQL的JSON函数结合数组匹配操作，实现将数组ID替换为关联表完整信息的嵌套JSON结果。

假设表结构 #

先明确三张表的基础结构（如果你的表字段有差异，只需对应调整字段名即可）：

CREATE TABLE Collections (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    group_ids INT[] -- 存储关联Groups的ID数组
);

CREATE TABLE Groups (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    items_ids INT[] -- 存储关联Items的ID数组
);

CREATE TABLE Items (
    id INT PRIMARY KEY,
    content TEXT,
    create_time TIMESTAMP
);

核心查询SQL #

SELECT json_agg(
    json_build_object(
        'id', c.id,
        'name', c.name,
        'groups', COALESCE(
            (
                SELECT json_agg(
                    json_build_object(
                        'id', g.id,
                        'title', g.title,
                        'items', COALESCE(
                            (
                                SELECT json_agg(i)
                                FROM Items i
                                WHERE i.id = ANY(g.items_ids)
                            ), '[]'::json
                        )
                    )
                )
                FROM Groups g
                WHERE g.id = ANY(c.group_ids)
            ), '[]'::json
        )
    )
) AS collections_json
FROM Collections c;

关键逻辑说明 #

1. 数组匹配：使用ANY()操作符匹配数组中的所有ID，比如g.id = ANY(c.group_ids)会找出当前Collection关联的所有Group记录。
2. JSON构建与聚合：
   • json_build_object()：将表字段组装成指定结构的JSON对象。
   • json_agg()：将多条记录聚合为JSON数组，实现嵌套层级。
3. 空值处理：COALESCE()用于处理空数组场景，当某个Collection没有关联Group，或某个Group没有关联Item时，对应字段会返回空数组[]，避免出现null值破坏JSON结构。

示例输出结构 #

最终生成的JSON会是如下嵌套格式：

[
  {
    "id": 1,
    "name": "我的收藏集",
    "groups": [
      {
        "id": 101,
        "title": "学习分组",
        "items": [
          {"id": 201, "content": "SQL入门笔记", "create_time": "2024-01-01T00:00:00"},
          {"id": 202, "content": "PostgreSQL进阶指南", "create_time": "2024-01-02T00:00:00"}
        ]
      }
    ]
  }
]

内容的提问来源于stack exchange，提问作者Neifer Reverón