Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                Please post sample data. By the way, what's the purpose of doing a left join with skills table? I don't see it being used anywhere in the query.
– FanoFN
                Feb 5, 2022 at 5:34
                That's understandable but you still haven't post data sample. In any case, I think there's nothing wrong with JSON_ARRAYAGG(DISTINCT col1) IF the col1 is actually a column that stores valid JSON value. Your col1 is basically a generated JSON value from a few different columns and I'm guessing since you're grouping by company, your results returned duplicate staff. I suggest you apply the DISTINCT on the columns before you do JSON_TABLE() then only you do JSON_ARRAYAGG() - possibly without even including DISTINCT.
– FanoFN
                Feb 7, 2022 at 0:28
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);
                This seemed to work for me. Not sure why MySQL doesn't support DISTINCT with JSON_ARRAYAGG. Seems like other DBs do.
– Regis
                Sep 13, 2022 at 15:43
                Let's hope MySQL implements this soon. bugs.mysql.com/bug.php?id=91993 MariaDB has DISTINCT in JSON_ARRAYAGG for at least 2 years already mariadb.com/kb/en/json_arrayagg
– Tim
                Sep 23, 2022 at 5:32
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.