优化带外键多表SQL查询：合并食材数据为数组/JSON

问题背景 #

为餐厅APP开发菜单模块时，多表关联查询因菜品与食材的一对多关系，返回大量冗余行（同一菜品仅食材名称不同）。需要优化查询实现单菜品单行数据，并支持后续扩展食材属性为结构化JSON。

当前查询与冗余结果 #

现有SQL视图 #

select D.dish_id, TD.translation as dish_name, D.dish_price, TC.translation as cat_name, TI.translation as ing_name
from Dish D
join Dish_Category DC on D.dish_id = DC.dish_id
join Category C on DC.category_id = C.category_id
join Translations_Categories TC on C.category_id = TC.category_id and TC.language = "en"
join Translations_Dish TD on D.dish_id = TD.dish_id and TD.language = "en"
join Dish_Ingredient DI on D.dish_id = DI.dish_id
join Ingredient I on DI.ingredient_id = I.ingredient_id
join Translations_Ingredient TI on I.ingredient_id = TI.ingredient_id and TI.language = "en"
ORDER by D.dish_id

冗余查询结果 #

dish_id dish_name       dish_price  cat_name    ing_name
    1   Tomato Sauce    5.00        Pizzas      Tomato
    2   White Sauce     5.00        Pizzas      Mozzarella
    3   Margherita      5.00        Pizzas      Tomato
    3   Margherita      5.00        Pizzas      Mozzarella
    4   Napoli          6.00        Pizzas      Tomato
    4   Napoli          6.00        Pizzas      Mozzarella
    4   Napoli          6.00        Pizzas      Capers
    4   Napoli          6.00        Pizzas      Anchovies

期望结果 #

基础版：食材合并为数组 #

dish_id dish_name       dish_price  cat_name    ing_name
    1   Tomato Sauce    5.00        Pizzas      Tomato
    2   White Sauce     5.00        Pizzas      Mozzarella
    3   Margherita      5.00        Pizzas      [Tomato, Mozzarella]
    4   Napoli          6.00        Pizzas      [Tomato, Mozzarella, Capers, Anchovies]

扩展版：食材带属性的JSON结构 #

dish_id dish_name       dish_price  cat_name    ing_name
    1   Tomato Sauce    5.00        Pizzas      Tomato
    2   White Sauce     5.00        Pizzas      Mozzarella
    3   Margherita      5.00        Pizzas      {"Tomato": {"allergenes": false, "frozen" : false}, "Mozzarella": {"allergenes": true, "frozen" : false} }
    4   Napoli          6.00        Pizzas      {"Tomato": {"allergenes": false, "frozen": false}, "Mozzarella": {"allergenes": true, "frozen": false}, "Capers": {"allergenes": true, "frozen": false}, "Anchovies": {"allergenes": true, "frozen": false}}

解决方案 #

1. 基础版：合并食材为数组（分数据库实现） #

利用数据库原生聚合函数，按菜品维度分组，将食材名称合并为数组或JSON数组：

MySQL 实现

SELECT 
    D.dish_id, 
    TD.translation as dish_name, 
    D.dish_price, 
    TC.translation as cat_name,
    -- 直接生成JSON数组，前端可直接解析
    JSON_ARRAYAGG(TI.translation) as ing_name
FROM Dish D
JOIN Dish_Category DC ON D.dish_id = DC.dish_id
JOIN Category C ON DC.category_id = C.category_id
JOIN Translations_Categories TC ON C.category_id = TC.category_id AND TC.language = "en"
JOIN Translations_Dish TD ON D.dish_id = TD.dish_id AND TD.language = "en"
JOIN Dish_Ingredient DI ON D.dish_id = DI.dish_id
JOIN Ingredient I ON DI.ingredient_id = I.ingredient_id
JOIN Translations_Ingredient TI ON I.ingredient_id = TI.ingredient_id AND TI.language = "en"
GROUP BY D.dish_id, TD.translation, D.dish_price, TC.translation
ORDER BY D.dish_id;

PostgreSQL 实现

SELECT 
    D.dish_id, 
    TD.translation as dish_name, 
    D.dish_price, 
    TC.translation as cat_name,
    -- 生成原生数组，或用json_agg转换为JSON数组
    json_agg(TI.translation) as ing_name
FROM Dish D
JOIN Dish_Category DC ON D.dish_id = DC.dish_id
JOIN Category C ON DC.category_id = C.category_id
JOIN Translations_Categories TC ON C.category_id = TC.category_id AND TC.language = 'en'
JOIN Translations_Dish TD ON D.dish_id = TD.dish_id AND TD.language = 'en'
JOIN Dish_Ingredient DI ON D.dish_id = DI.dish_id
JOIN Ingredient I ON DI.ingredient_id = I.ingredient_id
JOIN Translations_Ingredient TI ON I.ingredient_id = TI.ingredient_id AND TI.language = 'en'
GROUP BY D.dish_id, TD.translation, D.dish_price, TC.translation
ORDER BY D.dish_id;

2. 扩展版：打包食材为带属性的JSON对象 #

假设Ingredient表包含allergenes（是否含过敏原）、frozen（是否冷冻）字段，用JSON聚合函数生成结构化对象：

MySQL 实现

SELECT 
    D.dish_id, 
    TD.translation as dish_name, 
    D.dish_price, 
    TC.translation as cat_name,
    JSON_OBJECTAGG(
        TI.translation, 
        JSON_OBJECT('allergenes', I.allergenes, 'frozen', I.frozen)
    ) as ing_name
FROM Dish D
JOIN Dish_Category DC ON D.dish_id = DC.dish_id
JOIN Category C ON DC.category_id = C.category_id
JOIN Translations_Categories TC ON C.category_id = TC.category_id AND TC.language = "en"
JOIN Translations_Dish TD ON D.dish_id = TD.dish_id AND TD.language = "en"
JOIN Dish_Ingredient DI ON D.dish_id = DI.dish_id
JOIN Ingredient I ON DI.ingredient_id = I.ingredient_id
JOIN Translations_Ingredient TI ON I.ingredient_id = TI.ingredient_id AND TI.language = "en"
GROUP BY D.dish_id, TD.translation, D.dish_price, TC.translation
ORDER BY D.dish_id;

PostgreSQL 实现

SELECT 
    D.dish_id, 
    TD.translation as dish_name, 
    D.dish_price, 
    TC.translation as cat_name,
    json_object_agg(
        TI.translation, 
        json_build_object('allergenes', I.allergenes, 'frozen', I.frozen)
    ) as ing_name
FROM Dish D
JOIN Dish_Category DC ON D.dish_id = DC.dish_id
JOIN Category C ON DC.category_id = C.category_id
JOIN Translations_Categories TC ON C.category_id = TC.category_id AND TC.language = 'en'
JOIN Translations_Dish TD ON D.dish_id = TD.dish_id AND TD.language = 'en'
JOIN Dish_Ingredient DI ON D.dish_id = DI.dish_id
JOIN Ingredient I ON DI.ingredient_id = I.ingredient_id
JOIN Translations_Ingredient TI ON I.ingredient_id = TI.ingredient_id AND TI.language = 'en'
GROUP BY D.dish_id, TD.translation, D.dish_price, TC.translation
ORDER BY D.dish_id;

3. 数据高效打包建议 #

• 优先数据库聚合：利用数据库原生JSON函数处理数据合并，减少前端数据传输量与逻辑复杂度
• 参数化多语言：将language = "en"改为参数传入，方便后续支持多语言菜单
• 避免视图硬编码：复杂聚合逻辑建议直接在业务查询中实现，或创建存储过程，比视图更灵活

4. 前端展示建议 #

• 结构化解析：直接解析数据库返回的JSON数据，无需前端二次合并
• 差异化展示：
  • 单食材：直接显示名称
  • 多食材：用列表或标签组展示，将过敏原、冷冻状态用图标（如红色感叹号、雪花）标注
• 交互优化：食材详情可通过hover tooltip或点击弹窗展示，保持菜单列表简洁

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