MySQL 8中如何将一对多关联的子表记录按规则与状态聚合为JSON对象

当然可以实现！而且用MySQL 8.0提供的原生JSON函数就能直接生成你想要的嵌套统计结构，完全不用复杂的字符串拼接。我来给你详细说下可行的方案：

核心思路 #

我们需要分两步完成统计与聚合：

1. 先按parent_id、rule、status三级分组，统计每个组合下的记录数量
2. 把同一rule下的status-count对聚合为JSON对象，再把同一父记录下的所有rule对象聚合为最终的嵌套JSON

具体SQL实现 #

基础统计+JSON聚合 #

直接运行下面的SQL就能得到你想要的结果：

SELECT
  parent_id,
  JSON_OBJECT_AGG(
    rule,
    JSON_OBJECT_AGG(status, cnt)
  ) AS rule_status_stats
FROM (
  -- 第一步：三级分组统计数量
  SELECT 
    parent_id,
    rule,
    status,
    COUNT(*) AS cnt
  FROM child_table
  GROUP BY parent_id, rule, status
) AS grouped_stats
GROUP BY parent_id;

针对你给出的示例数据，这条SQL返回的结果会是：

关联父表的完整查询 #

如果需要关联父表（确保即使父表没有子记录也能被返回），可以用左连接+COALESCE处理空值：

SELECT
  p.id AS parent_id,
  COALESCE(
    JSON_OBJECT_AGG(
      rule,
      JSON_OBJECT_AGG(status, cnt)
    ),
    JSON_OBJECT() -- 无匹配子记录时返回空JSON对象
  ) AS rule_status_stats
FROM parent_table p
LEFT JOIN (
  SELECT 
    parent_id,
    rule,
    status,
    COUNT(*) AS cnt
  FROM child_table
  GROUP BY parent_id, rule, status
) AS grouped_stats ON p.id = grouped_stats.parent_id
GROUP BY p.id;

为什么这个方案可行？ #

• JSON_OBJECT_AGG是MySQL 8.0新增的聚合函数，专门用来将分组后的键值对聚合为JSON对象，比用GROUP_CONCAT手动拼接字符串靠谱得多，不会出现格式错误
• 嵌套使用JSON_OBJECT_AGG刚好能实现你需要的两层嵌套结构：外层是rule为键，内层是status为键、统计数为值的对象
• 生成的JSON格式可以直接被Node.js用JSON.parse()解析，完全符合你的客户端需求

替代方案（非JSON格式） #

如果真的不需要JSON，也可以用GROUP_CONCAT生成结构化字符串，比如：

SELECT
  parent_id,
  GROUP_CONCAT(
    CONCAT(rule, ':', GROUP_CONCAT(CONCAT(status, '(', cnt, ')') SEPARATOR ','))
    SEPARATOR ';'
  ) AS rule_status_str
FROM (
  SELECT 
    parent_id,
    rule,
    status,
    COUNT(*) AS cnt
  FROM child_table
  GROUP BY parent_id, rule, status
) AS grouped_stats
GROUP BY parent_id;

这条SQL会返回类似a:PASS(1),FAIL(2);b:PASS(1),FAIL(2)的字符串，Node.js也能通过字符串分割解析，但显然JSON格式更规范、更易维护。

内容的提问来源于stack exchange，提问作者J-Deq87