|
Grouping functions can't be used within "JSON_ARRAY()". So if I want to create an array with an unknown number of elements, the only way I found out so far was the following. Also the same applies for "JSON_MERGE()" but the use cases for that are very limited.
SELECT
|
JSON_OBJECT(
|
'Staff', JSON_OBJECT(
|
'Staff_ID', Staff_ID,
|
'StaffNumber', Staff_Number,
|
'StaffName', Staff_Name
|
),
|
'Permissions', CONCAT(
|
'[',
|
GROUP_CONCAT(
|
JSON_OBJECT(Permission_ID, Permission_Flag)
|
),
|
']'
|
)
|
) AS StaffObj
|
FROM Staff
|
NATURAL JOIN Company
|
NATURAL JOIN Staff_Permissions
|
GROUP BY Staff_ID;
|
|