MySQL如何关联两表查询生成含嵌套对象数组的JSON结果

核心依赖函数（MySQL 5.7.22+ 原生支持） #

• JSON_OBJECT(键1, 值1, 键2, 值2...)：传入键值对自动生成标准JSON对象，自动处理特殊字符转义
• JSON_ARRAYAGG(字段)：将分组内的多行值聚合为JSON数组
• IFNULL(值, 默认值)：处理无关联数据时的空值异常场景

直接可用的查询语句 #

针对你给出的表结构和测试数据，以下语句可以直接返回你需要的JSON结构，同时兼容「岗位无关联文件」的边界场景：

SELECT
  JSON_OBJECT(
    'job_id', j.job_id,
    'name', j.name,
    'files', IFNULL(
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'name', f.name,
          'path', f.path
        )
      ),
      JSON_ARRAY()
    )
  ) AS result
FROM job j
LEFT JOIN file f 
  ON j.job_id = f.job_id
GROUP BY j.job_id, j.name;

返回结果说明 #

执行上述语句，针对你提供的测试数据，会返回如下标准JSON：

{
  "job_id": "1",
  "name": "test-job",
  "files": [
    {
      "name": "a.txt",
      "path": "D:/sourcefile/1/a.txt"
    },
    {
      "name": "b.txt",
      "path": "D:/sourcefile/2/b.txt"
    }
  ]
}

注意：你的job表中job_id字段定义为VARCHAR类型，所以上述返回的job_id值为字符串格式，如果需要数值格式可以在字段前加CAST(j.job_id AS UNSIGNED)做类型转换。

低版本MySQL兼容方案（<5.7.22，无JSON_ARRAYAGG支持） #

如果你的MySQL版本低于5.7.22，可以用GROUP_CONCAT手动拼接JSON数组，写法如下：

SELECT
  CONCAT(
    '{"job_id":', CAST(j.job_id AS UNSIGNED),
    ',"name":"', j.name, '"',
    ',"files":[',
      IFNULL(
        GROUP_CONCAT(
          CONCAT('{"name":"', f.name, '","path":"', f.path, '"}')
          SEPARATOR ','
        ),
        ''
      ),
    ']}'
  ) AS result
FROM job j
LEFT JOIN file f 
  ON j.job_id = f.job_id
GROUP BY j.job_id, j.name;

注意：手动拼接JSON时要注意特殊字符转义问题，生产环境优先使用高版本自带的JSON函数，避免出现JSON格式解析错误。

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