[SOLVED] How to return DISTINCT values in a JSON_ARRAYAGG when using JSON

How to return DISTINCT values in a JSON_ARRAYAGG when using JSON_OBJECT

How to use DISTINCT with JSON_ARRAYAGG?

Let's consider the below query as an example.

SELECT 
    staff.company,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'uuid', UuidFromBin(staff.uuid),
            'username', staff.username,
            'name', staff.name,
            'surname', staff.surname
        )
    )
FROM events_staff
JOIN staff ON staff.id = staff_id
LEFT JOIN skills s ON s.id = events_staff.skill_id
GROUP BY staff.company

Now, How can I use DISTINCT with JSON_ARRAYAGG in this query so that JSON objects will be distinct? It will be better if we can apply DISTINCT based on any key like uuid.

After googling for half an hour, I found the below options but was not able to apply these in the above query.

A JSON_ARRAYAGG DISTINCT returns a JSON array composed of all the different (unique) values for string-expr in the selected rows: JSON_ARRAYAGG(DISTINCT col1). The NULL string-expr is not included in the JSON array. JSON_ARRAYAGG(DISTINCT BY(col2) col1) returns a JSON array containing only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.

Solution

I have came to a workaround to solve this issue, First addressing the issue that using JSON_ARRAYAGG(DISTINCT JSON_OBJECT()) Will simply not work.

So the workaround is CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT("key": value)), ']'); this will result in something like this [ {"key": <value1>},{"key":<value2>}, ...]. this will return distinct result.

Note: You might need to cast this as JSON in the end this can be done like this => CAST(CONCAT('[', GROUP_CONCAT(JSON_OBJECT("key": value)), ']') AS JSON);