MySQL如何多表关联查询生成带嵌套JSON对象的结果

你不需要使用JSON_EXTRACT()函数，直接嵌套调用JSON构造函数即可实现需求，核心逻辑分两步：

• 关联cars与owners表，为每一条车辆记录生成独立JSON对象，owner字段直接嵌套JSON_OBJECT()生成子对象
• 用JSON_ARRAYAGG()把所有单条车辆JSON对象聚合为最终的JSON数组

适用于MySQL 5.7.22+/8.0+的查询语句 #

SELECT
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'brand', c.brand,
      'model', c.model,
      'owner', JSON_OBJECT(
        'id', o.id,
        'firstname', o.firstname,
        'lastname', o.lastname
      )
    )
  ) AS car_owner_list
FROM cars c
LEFT JOIN owners o 
  ON c.owner_id = o.id;

执行后返回的结果和你给出的目标JSON结构完全一致，同一车主名下多辆车时，会在每条车辆记录下自动嵌套对应车主信息。

低版本MySQL兼容方案 #

如果你的MySQL版本低于5.7.22，没有内置JSON_ARRAYAGG()函数，可以用CONCAT+GROUP_CONCAT手动拼接JSON数组：

-- 先调大GROUP_CONCAT长度限制，避免长JSON被截断
SET SESSION group_concat_max_len = 1048576;

SELECT
  CONCAT(
    '[',
    GROUP_CONCAT(
      JSON_OBJECT(
        'brand', c.brand,
        'model', c.model,
        'owner', JSON_OBJECT(
          'id', o.id,
          'firstname', o.firstname,
          'lastname', o.lastname
        )
      )
    ),
    ']'
  ) AS car_owner_list
FROM cars c
LEFT JOIN owners o 
  ON c.owner_id = o.id;

额外提示 #

你给出的建表语句里用单引号包裹表名、字段名是语法错误，MySQL中需要用反引号包裹标识符，修正后的建表语句如下：

CREATE TABLE `owners` (
    `id` int PRIMARY KEY AUTO_INCREMENT, 
    `firstname` varchar(20) NOT NULL, 
    `lastname` varchar(20) NOT NULL
);

CREATE TABLE `cars` (
    `id` int PRIMARY KEY AUTO_INCREMENT, 
    `owner_id` int NOT NULL, 
    `brand` varchar(20), 
    `model` varchar(20)
);

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