MySQL如何将三级1对多关联表的嵌套查询结果返回为JSON对象/数组？

方案1：单条SQL查询实现（推荐小数据量场景使用） #

MySQL 5.7及以上版本支持JSON聚合函数，可以通过嵌套的JSON_OBJECT+JSON_ARRAYAGG组合直接生成你需要的层级结构，不需要额外在应用层组装数据。

核心函数说明 #

• JSON_OBJECT(key1, val1, key2, val2...)：按照指定的键值对生成JSON对象
• JSON_ARRAYAGG(expr)：将多行表达式的返回结果聚合为JSON数组

示例查询语句 #

你可以根据自己表的实际字段调整键值对部分：

SELECT 
  JSON_OBJECT(
    'id', q.id,
    'title', q.title,
    'content', q.content,
    'created_at', q.created_at,
    -- 补充questions表其他需要返回的字段
    'answers', IFNULL(a.answers_json, JSON_ARRAY())
  ) AS question_item
FROM questions q
LEFT JOIN (
  SELECT 
    a.question_id,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id', a.id,
        'content', a.content,
        'created_at', a.created_at,
        -- 补充answers表其他需要返回的字段
        'photos', IFNULL(p.photos_json, JSON_ARRAY())
      )
    ) AS answers_json
  FROM answers a
  LEFT JOIN (
    SELECT 
      answer_id,
      JSON_ARRAYAGG(url) AS photos_json
    FROM answer_photos -- 替换为你的图片表实际表名
    GROUP BY answer_id
  ) p ON a.id = p.answer_id
  GROUP BY a.question_id
) a ON q.id = a.question_id
WHERE q.product_id = ?; -- ?对应传入的product_id参数

如果需要直接拿到最外层包裹的results数组，可以在外层再套一层聚合：

SELECT JSON_ARRAYAGG(question_item) AS results
FROM (
  -- 把上面完整的查询语句放在这里
) t;

语句中使用IFNULL是为了处理没有回答、回答没有图片的场景，默认返回空数组避免出现NULL值。

方案2：分三次查询手动组装（推荐大数据量/团队协作场景使用） #

操作流程 #

1. 第一次查询：根据product_id查询所有符合条件的question数据，得到问题列表，提取所有question_id
2. 第二次查询：用第一步拿到的question_id批量查询所有关联的answer数据，得到回答列表，提取所有answer_id
3. 第三次查询：用第二步拿到的answer_id批量查询所有关联的图片URL，得到图片列表
4. 应用层遍历组装：将图片按answer_id分组对应到每个回答，再将回答按question_id分组对应到每个问题，最终生成目标结构

两种方案选型建议 #

如果是学校项目，数据量不大的情况下优先选择单条查询方案，代码更简洁；如果后续预期数据量会大幅增长，或者团队成员普遍不熟悉MySQL JSON函数，选择分查询组装方案更稳妥。

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