I'm trying preparing an array of objects using MySQL
my query look like this
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
the above query returns list students by classRoom in a particular branch/institute, for the above query im getting the result like this
| classRoomId | students |
|---|---|
| 1 | [{"studentId": 1, "studentName": "a"}, {"studentId": 2, "studentName": "b"}] |
| 2 | [{"studentId": 3, "studentName": "c"}, {"studentId": 4, "studentName": "d"}] |
| 3 | [{"studentId": null, "studentName": null}] |
Here problem is, if there are no students in a class then it is retuning null values like ({"studentId": null, "studentName": null}). so is it possible to return an empty array ([]) if there are no students
COALESCE(...your JSON expression..., CAST('[]' AS JSON)).branch.id = classRoom.id = students.id?