PostgreSQL 17：指定厨师ID的嵌套JSON全食谱查询需求

先确认表结构（基于你的描述补全，可根据实际调整） #

假设你的表结构如下（如果字段名有差异，替换成你实际的字段即可）：

CREATE TABLE chefs (
    chef_id INT PRIMARY KEY,
    name VARCHAR(100),
    ancestor_chef_id INT -- 祖先ID字段
);

CREATE TABLE recipes (
    recipe_id INT PRIMARY KEY,
    chef_id INT REFERENCES chefs(chef_id),
    name VARCHAR(100),
    ancestor_recipe_id INT -- 祖先ID字段
);

CREATE TABLE ingredients (
    ingredient_id INT PRIMARY KEY,
    recipe_id INT REFERENCES recipes(recipe_id),
    name VARCHAR(100),
    quantity VARCHAR(50),
    ancestor_ingredient_id INT -- 祖先ID字段
);

CREATE TABLE sausages (
    sausage_id INT PRIMARY KEY,
    recipe_id INT REFERENCES recipes(recipe_id),
    type VARCHAR(100),
    flavor VARCHAR(100),
    ancestor_sausage_id INT -- 祖先ID字段
);

正确的SQL查询语句 #

用嵌套子查询+json_agg+coalesce实现层级JSON，同时保证空列表返回[]：

SELECT
    json_build_object(
        'chef_id', c.chef_id,
        'chef_name', c.name,
        'recipes', coalesce(
            (
                SELECT json_agg(
                    json_build_object(
                        'recipe_id', r.recipe_id,
                        'recipe_name', r.name,
                        'ingredients', coalesce(
                            (
                                SELECT json_agg(
                                    json_build_object(
                                        'ingredient_id', i.ingredient_id,
                                        'name', i.name,
                                        'quantity', i.quantity,
                                        'ancestor_ingredient_id', i.ancestor_ingredient_id
                                    )
                                ) FROM ingredients i
                                WHERE i.recipe_id = r.recipe_id
                            ), '[]'::json
                        ),
                        'sausages', coalesce(
                            (
                                SELECT json_agg(
                                    json_build_object(
                                        'sausage_id', s.sausage_id,
                                        'type', s.type,
                                        'flavor', s.flavor,
                                        'ancestor_sausage_id', s.ancestor_sausage_id
                                    )
                                ) FROM sausages s
                                WHERE s.recipe_id = r.recipe_id
                            ), '[]'::json
                        ),
                        'ancestor_recipe_id', r.ancestor_recipe_id
                    )
                ) FROM recipes r
                WHERE r.chef_id = c.chef_id
            ), '[]'::json
        ),
        'ancestor_chef_id', c.ancestor_chef_id
    ) AS chef_recipe_json
FROM chefs c
WHERE c.chef_id = 1; -- 替换成你要查询的厨师ID

核心要点解释 #

• coalesce(..., '[]'::json)：解决空列表问题，当子查询没有匹配到数据时，把默认的NULL替换成JSON空数组[]。
• json_agg：把多行记录打包成JSON数组，是PostgreSQL处理集合转JSON的关键函数。
• 嵌套子查询：从最底层的食材/香肠开始聚合，再到食谱，最后到厨师，逐层构建层级结构，确保JSON的嵌套关系正确。

示例测试结果 #

如果插入以下测试数据：

INSERT INTO chefs VALUES (1, '张三', NULL);
INSERT INTO recipes VALUES (101, 1, '香肠炒饭', NULL);
INSERT INTO recipes VALUES (102, 1, '清炒时蔬', NULL);
INSERT INTO ingredients VALUES (201, 101, '大米', '1碗', NULL);
INSERT INTO ingredients VALUES (202, 101, '鸡蛋', '2个', NULL);

查询厨师ID=1，会得到如下JSON：

{
  "chef_id": 1,
  "chef_name": "张三",
  "recipes": [
    {
      "recipe_id": 101,
      "recipe_name": "香肠炒饭",
      "ingredients": [
        {
          "ingredient_id": 201,
          "name": "大米",
          "quantity": "1碗",
          "ancestor_ingredient_id": null
        },
        {
          "ingredient_id": 202,
          "name": "鸡蛋",
          "quantity": "2个",
          "ancestor_ingredient_id": null
        }
      ],
      "sausages": [],
      "ancestor_recipe_id": null
    },
    {
      "recipe_id": 102,
      "recipe_name": "清炒时蔬",
      "ingredients": [],
      "sausages": [],
      "ancestor_recipe_id": null
    }
  ],
  "ancestor_chef_id": null
}

关于你之前的CTE问题 #

如果之前用CTE失败，大概率是没处理空聚合的情况，或者层级关联逻辑有误。比如CTE中如果直接用json_agg，没有加coalesce，空数据会返回NULL而不是[]。上面的写法更直观，逐层确保每个子列表的空值处理正确。

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