MySQL如何将JOIN关联结果转为JSON对象或数组而非扁平行列

MySQL 5.7及以上版本内置了JSON相关函数，完全支持你需要的查询需求，无需额外业务层处理即可直接返回符合要求的JSON格式结果。

问题1：1:n关联返回地址数组 #

可以搭配JSON_OBJECT（将单行地址转为JSON对象）和JSON_ARRAYAGG（将多行JSON对象聚合为数组）实现，对应查询语句如下：

SELECT 
  p.id,
  p.name,
  p.departmentId,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', a.id,
      'personId', a.personId,
      'fullAddress', a.fullAddress
    )
  ) AS addresses
FROM people p
LEFT JOIN addresses a ON p.id = a.personId
WHERE p.id = 2
GROUP BY p.id, p.name, p.departmentId;

查询结果仅返回1条人员记录，addresses字段为所有关联地址组成的JSON数组，如果人员无关联地址则返回空数组。

问题2：1:1关联返回部门JSON对象 #

直接用JSON_OBJECT函数将部门字段封装为JSON对象即可，对应查询语句如下：

SELECT 
  p.id,
  p.name,
  p.departmentId,
  JSON_OBJECT(
    'id', d.id,
    'name', d.name
  ) AS departmentIdRecord
FROM people p
LEFT JOIN departments d ON p.departmentId = d.id
WHERE p.id = 2;

返回结果的departmentIdRecord字段即为你要求的部门完整JSON对象，无扁平追加的部门字段。

如果需要同时返回部门对象和地址数组，将两个函数合并到同一条SELECT语句中即可，MySQL 8.0还提供了JSON_OBJECT_AGG等更丰富的JSON函数可满足更复杂的嵌套查询需求。

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