mysql JSON_ARRAYAGG联合JSON_OBJECT使用查询整合（数组对象）字段

父表数据（表名：class）

id	name
1	一年级
2	二年级
3	三年级

子表数据（表名：students）

id	name	classId
11	张三	1
12	李四	1
13	小明	3

关联子表sql查询（推荐使用方法一）

方法一 (使用IFNull判断子表数据是否为空，为空的话使用JSON_ARRAY显示空数组):

JSON_ARRAYAGG 是 MySQL 5.7.22+ 和 MySQL 8.0+ 版本支持的 JSON 聚合函数。具体支持情况如下：

版本	是否支持 JSON_ARRAYAGG
MySQL 5.7.22+	✅ 支持（5.7.22 引入）
MySQL 8.0+	✅ 支持（默认可用）
MariaDB 10.5+	✅ 支持（类似 MySQL 8.0）
MySQL 5.7.21 及更早版本	❌ 不支持

SELECT   u.id,  u.name,  IFNULL(  (SELECT JSON_ARRAYAGG(  JSON_OBJECT('id', o.id, 'name', o.name)  )  FROM students o   WHERE o.classId = u.id),  JSON_ARRAY()  ) AS students
FROM   class u;  

方法二

SELECT   u.id,   u.name,   IF(  COUNT(o.id) = 0, JSON_ARRAY(),  JSON_ARRAYAGG(  JSON_OBJECT(  'id', o.id,   'name', o.name  )  )  ) AS students
FROM   class u   
LEFT JOIN   students o ON u.id = o.classIdGROUP BY   u.id;

方法三（不支持JSON_ARRAYAGG，JSON_ARRAY，JSON_OBJECT等方法时使用）

SELECT   u.id,  u.name,  IFNULL(  (  SELECT GROUP_CONCAT(  CONCAT('{"id":"', o.id, '","name":"', o.name, '"}')  )  FROM students o   WHERE o.classId = u.id  ),  '[]'  ) AS students  
FROM   class u;  

查出来的数据

id	name	students
1	一年级	[{"id": 11, "name": "张三"}, {"id": 12, "name": "李四"}]
2	二年级	[]
3	三年级	[{"id": 13, "name": "小明"}]

单独查询students表

方法一

SELECT   u.classId,  JSON_ARRAYAGG(  JSON_OBJECT('id', u.id, 'name', u.name)  ) AS students  
FROM   students u
group by u.classId;  

方法二（不支持JSON_ARRAYAGG，JSON_ARRAY，JSON_OBJECT等方法时使用）

SELECTu.classId,CONCAT('[',GROUP_CONCAT(CONCAT('{"id":"', u.id, '","name":"', u.name, '"}')),']') AS students
FROM students u
GROUP BY u.classId

查出来的数据

classId	students
1	[{"id": 11, "name": "张三"}, {"id": 12, "name": "李四"}]
2	[]
3	[{"id": 13, "name": "小明"}]