MySQL能否直接生成嵌套结构响应API，还是需用JS处理数据？

结论先行 #

• 技术上可以通过单条MySQL查询直接生成符合要求的嵌套JSON结构，MySQL 5.7及以上版本原生支持的JSON聚合函数即可实现
• 生产环境更推荐分开查询+JS格式化的方案，可维护性、性能、排查问题的便利性都更高

单条MySQL查询实现方案 #

你可以通过JSON_OBJECT、JSON_ARRAYAGG、JSON_OBJECTAGG等内置JSON函数直接拼装目标结构，参考实现如下：

SELECT 
  JSON_OBJECT(
    'pid', q.product_id,
    'results', JSON_ARRAYAGG(
      JSON_OBJECT(
        'qid', q.id,
        'qbody', q.body,
        'qdate', FROM_UNIXTIME(q.date_written / 1000, '%Y-%m-%dT%H:%i:%s.000Z'),
        'aname', q.asker_name,
        'qhelp', q.helpful,
        'rep', IF(q.reported = 1, true, false),
        'as', (
          SELECT JSON_OBJECTAGG(
            a.id,
            JSON_OBJECT(
              'id', a.id,
              'body', a.body,
              'date', FROM_UNIXTIME(a.date_written / 1000, '%Y-%m-%dT%H:%i:%s.000Z'),
              'aname', a.answerer_name,
              'help', a.helpful,
              'photos', IFNULL((
                SELECT JSON_ARRAYAGG(ap.url)
                FROM Answers_Photos ap
                WHERE ap.answer_id = a.id
              ), JSON_ARRAY())
            )
          )
          FROM Answers a
          WHERE a.question_id = q.id AND a.reported = 0
        )
      )
    )
  ) as response_data
FROM Questions q
WHERE q.product_id = 47600 AND q.reported = 0
GROUP BY q.product_id;

单条查询的优缺点 #

• 优点：代码层面逻辑看起来更简洁，不需要额外写数据拼装逻辑
• 缺点非常突出：
  • 多层嵌套子查询在数据量较大时性能极差，索引优化难度高
  • 业务逻辑耦合在SQL中，后续调整字段、修改格式的维护成本远高于JS代码
  • 排查问题困难，大体积的返回JSON定位错误点远比JS拼装的结构复杂
  • 占用更多数据库CPU资源，而数据库通常是服务的性能瓶颈，会放大系统负载压力

更推荐的分查询+JS拼装方案 #

一共执行3条简单的单表查询，全部可以走主键、外键索引，性能损耗极低：

1. 第一步查询当前product_id对应的所有有效问题：

SELECT id, body, date_written, asker_name, helpful, reported 
FROM Questions 
WHERE product_id = ? AND reported = 0
ORDER BY date_written DESC;

2. 第二步用第一步拿到的所有问题ID批量查询对应有效回答：

SELECT id, question_id, body, date_written, answerer_name, helpful 
FROM Answers 
WHERE question_id IN (?, ?, ?...) AND reported = 0;

3. 第三步用第二步拿到的所有回答ID批量查询对应图片：

SELECT answer_id, url FROM Answers_Photos WHERE answer_id IN (?, ?, ?...);

拿到3批数据后，在JS中做三次遍历即可完成结构拼装：先把图片挂到对应回答下，再把回答挂到对应问题下，哪怕是万级数据量，JS拼装的耗时也可以忽略不计。

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