如何编写Oracle SQL实现按id_recipe聚合食材数组的GET请求输出

实现方案：利用Oracle JSON聚合函数 #

Oracle 12c R2及以上版本提供的JSON_ARRAYAGG和JSON_OBJECT函数可以直接实现将同一菜谱下的食材聚合为嵌套数组的需求，完美适配GET请求的结构化输出。

假设当前扁平结构SQL（示例） #

假设你现有生成扁平记录的SQL如下：

SELECT 
    r.id_recipe,
    r.recipe_name,
    i.id_ingredient,
    i.ingredient_name,
    i.quantity,
    i.unit
FROM 
    recipes r
JOIN 
    recipe_ingredients ri ON r.id_recipe = ri.id_recipe
JOIN 
    ingredients i ON ri.id_ingredient = i.id_ingredient;

修改后的嵌套结构SQL #

通过分组聚合+JSON函数改造，得到嵌套输出：

SELECT 
    r.id_recipe,
    r.recipe_name,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id_ingredient' VALUE i.id_ingredient,
            'ingredient_name' VALUE i.ingredient_name,
            'quantity' VALUE i.quantity,
            'unit' VALUE i.unit
        ) ORDER BY i.id_ingredient
    ) AS ingredients
FROM 
    recipes r
JOIN 
    recipe_ingredients ri ON r.id_recipe = ri.id_recipe
JOIN 
    ingredients i ON ri.id_ingredient = i.id_ingredient
GROUP BY 
    r.id_recipe, r.recipe_name;

核心逻辑说明 #

• JSON_OBJECT：将单条食材的多个字段封装为一个JSON对象，定义嵌套数组的元素结构。
• JSON_ARRAYAGG：按菜谱ID分组，将同一菜谱下的所有食材对象聚合为一个JSON数组，可选ORDER BY保证数组内元素顺序稳定。
• GROUP BY：必须包含所有非聚合字段（id_recipe、recipe_name），确保分组维度正确。

期望输出示例（JSON格式） #

[
    {
        "id_recipe": 1,
        "recipe_name": "番茄炒蛋",
        "ingredients": [
            {"id_ingredient": 1, "ingredient_name": "番茄", "quantity": 2, "unit": "个"},
            {"id_ingredient": 2, "ingredient_name": "鸡蛋", "quantity": 3, "unit": "个"}
        ]
    },
    {
        "id_recipe": 2,
        "recipe_name": "酸辣土豆丝",
        "ingredients": [
            {"id_ingredient": 3, "ingredient_name": "土豆", "quantity": 1, "unit": "个"},
            {"id_ingredient": 4, "ingredient_name": "干辣椒", "quantity": 5, "unit": "个"}
        ]
    }
]

扩展：返回完整JSON文档 #

如果需要直接返回包含所有菜谱的JSON数组，可在外层包裹JSON_ARRAYAGG：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id_recipe' VALUE r.id_recipe,
        'recipe_name' VALUE r.recipe_name,
        'ingredients' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'id_ingredient' VALUE i.id_ingredient,
                'ingredient_name' VALUE i.ingredient_name,
                'quantity' VALUE i.quantity,
                'unit' VALUE i.unit
            ) ORDER BY i.id_ingredient
        )
    )
) AS all_recipes
FROM 
    recipes r
JOIN 
    recipe_ingredients ri ON r.id_recipe = ri.id_recipe
JOIN 
    ingredients i ON ri.id_ingredient = i.id_ingredient
GROUP BY 
    r.id_recipe, r.recipe_name;

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