MySQL多关联表合并查询：将评论聚合为JSON数组的需求

我懂你折腾数小时却没摸到门道的感觉——当前查询能运行，但返回的是每条评论对应一个报告条目，完全不是你想要的“同一报告下评论聚合成数组”的结构化格式。咱们来一步步调整代码，实现你要的结果。

问题分析 #

你当前用GROUP_CONCAT只是把字段值拼接成字符串，没法生成带结构的JSON对象数组。要实现嵌套数组格式，得用MySQL（5.7+）或MariaDB支持的JSON聚合函数，把每个评论先构造成JSON对象，再聚合成数组。

另外注意：你之前关联users表时用的是r.user_id（报告作者的ID），但评论的作者是c.user_id，这部分关联逻辑需要修正，不然拿到的是报告作者的名字，不是评论者的。

调整后的SQL代码 #

SELECT
  r.text,
  -- 把每条评论构造成JSON对象，再聚合成数组
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'comment', c.text,
      'display_name', u.display_name
    )
  ) AS comments
FROM report r
-- 左关联评论表，保留没有评论的报告
LEFT JOIN report_comments c 
  ON c.report_id = r.id
-- 关联评论的发布者用户表
LEFT JOIN users u 
  ON u.id = c.user_id
WHERE r.user_id = :userId
-- 按报告ID和text分组，确保同一报告的评论被聚合到一起
GROUP BY r.id, r.text

代码解释 #

1. JSON_OBJECT：把单条评论的text（评论内容）和对应的用户display_name（评论者昵称）打包成一个JSON对象，格式是{"comment": "xxx", "display_name": "xxx"}。
2. JSON_ARRAYAGG：把同一报告下的所有评论对象聚合为一个JSON数组，最终就是你想要的comments字段。
3. 表关联修正：将users表的关联条件改为u.id = c.user_id，这样拿到的是评论发布者的昵称，而不是报告作者的。
4. 分组逻辑：依旧按r.id和r.text分组，保证同一报告的所有评论会被聚合到同一条结果里。

针对旧版本MySQL的兼容方案（如果你的版本<5.7） #

如果你的MySQL版本不支持JSON_ARRAYAGG，可以用GROUP_CONCAT拼接JSON对象字符串，再用JSON_ARRAY转换：

SELECT
  r.text,
  JSON_ARRAY(
    GROUP_CONCAT(
      JSON_OBJECT(
        'comment', c.text,
        'display_name', u.display_name
      ) SEPARATOR ','
    )
  ) AS comments
FROM report r
LEFT JOIN report_comments c ON c.report_id = r.id
LEFT JOIN users u ON u.id = c.user_id
WHERE r.user_id = :userId
GROUP BY r.id, r.text

这样执行后，返回的结果格式就会和你期望的一致：

{"text":"My first report","comments":[{"comment": "Great Report","display_name":"Xavier"}, {"comment": "Do you call this a report?","display_name":"Logan"}]}

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