如何用单条SQL关联查询整合多表并聚合子表多行记录

需要用单条SQL查询整合parents、children、prices、attributes四张表的数据，部分表存在关联ID匹配的多行记录。已写出部分查询语句，但不清楚如何将prices和attributes追加到对应的children中，期望得到指定结构的JSON结果。

表结构数据 #

Parents表 #

Children表 #

Prices表 #

Attributes表 #

现有查询代码 #

const query = 'SELECT parents.id, parents.title, JSON_ARRAYAGG(children.append) AS children \
      FROM parents \
      LEFT JOIN children ON (parents.id = children.parent_id) \
      GROUP BY parents.id';

期望的JSON结构 #

[
  {
    "parent_id": 1,
    "title": "Product Name",
    "attributes": [
      {"label": "Colour","value": "Red"},
      {"label": "Colour","value": "Blue"},
      {"label": "Size","value": "Large"}
    ],
    "children": [
      {"child_id": 1,"prices": [{"price": 1.99},{"price": 6.99}]},
      {"child_id": 2,"prices": [{"price": 1.49}]}
    ]
  }
]

直接用MySQL的JSON聚合函数就能实现，先把每个子项的价格单独聚合成数组，再关联到子表，最后把所有数据整合到父表层级。完整SQL如下：

SELECT
  p.id AS parent_id,
  p.name AS title,
  -- 聚合当前产品下所有属性，去重避免重复条目
  COALESCE(JSON_ARRAYAGG(DISTINCT JSON_OBJECT('label', a.label, 'value', a.value)), JSON_ARRAY()) AS attributes,
  -- 聚合带价格数组的子产品列表
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'child_id', c.child_id,
      'prices', COALESCE(pr.child_prices, JSON_ARRAY())
    )
  ) AS children
FROM parents p
LEFT JOIN children c ON p.id = c.parent_id
-- 先把每个子产品的价格聚合成数组
LEFT JOIN (
  SELECT
    child_id,
    JSON_ARRAYAGG(JSON_OBJECT('price', price)) AS child_prices
  FROM prices
  GROUP BY child_id
) pr ON c.child_id = pr.child_id
-- 关联属性表
LEFT JOIN attributes a ON c.child_id = a.child_id
GROUP BY p.id, p.name;

这段SQL的逻辑是：

• 子查询处理prices表，按child_id分组，把每个子产品的所有价格打包成JSON数组
• 关联parents和children表，再对接处理好的价格数据
• 关联attributes表，把所有子产品的属性聚合到父产品层级
• 最后用JSON_ARRAYAGG把所有子产品对象打包成数组，同时用COALESCE处理空值情况，确保无数据时返回空数组而非NULL

执行后就能得到你想要的JSON结构。

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