使用MySQL生成对象数组时遇NULL值，如何返回空数组？

我尝试用MySQL生成指定分校下各教室的学生列表对象数组，查询语句如下：

SELECT c.id AS classRoomId, 
JSON_ARRAYAGG(json_object("studentId", st.id, "studentName", st.name)) AS students
FROM branch b
LEFT JOIN classRoom c ON b.id = c.id
LEFT JOIN students st ON c.id = st.id
WHERE b.id = 2
GROUP BY c.id

当前查询返回结果：

问题：当教室没有学生时，返回的是包含NULL值的对象数组，希望改为返回空数组[]。

解决方案 #

修改查询语句，通过判断教室是否有学生来返回对应结果：

SELECT 
    c.id AS classRoomId,
    CASE 
        WHEN COUNT(st.id) = 0 THEN JSON_ARRAY()
        ELSE JSON_ARRAYAGG(JSON_OBJECT("studentId", st.id, "studentName", st.name))
    END AS students
FROM branch b
LEFT JOIN classRoom c ON b.id = c.id
LEFT JOIN students st ON c.id = st.id
WHERE b.id = 2
GROUP BY c.id

说明 #

• COUNT(st.id)会统计当前教室关联的非空学生ID数量，若为0则表示该教室无学生。
• 无学生时用JSON_ARRAY()生成空数组，有学生时正常聚合生成学生对象数组。

修改后的结果中，classRoomId为3的记录students字段会变为[]。

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