MySQL - removal of duplicate results in JSON_ARRAYAGG in the presence of GROUP BY

Question

I have query:

SELECT id, JSON_ARRAYAGG(url) AS urlLinks
FROM links
WHERE id=832781
GROUP BY id;

The result of this query duplicates the same image urls in column urlLinks:

["index.html", "index.html", "index.html", "index.html", "index.html"]

How can I leave as a result only unique image urls?

["index.html"]

GROUP BY cannot be removed from the request!!!

Answer 1

JSON_ARRAYAGG() does not support DISTINCT. You can SELECT DISTINCT in a subquery, and then aggregate:

SELECT id, JSON_ARRAYAGG(url) AS urlLinks
FROM (SELECT DISTINCT id, url from links) l
WHERE id=832781
GROUP BY id;

Demo on DB Fiddle:

WITH links AS (
    SELECT 832781 id, 'index.html' url
    UNION ALL SELECT 832781, 'index.html'
    UNION ALL SELECT 832781, 'page.html'
)
SELECT id, JSON_ARRAYAGG(url) AS urlLinks
FROM (SELECT DISTINCT id, url from links) l
WHERE id=832781
GROUP BY id;

    id | urlLinks                   
-----: | :--------------------------
832781 | ["index.html", "page.html"]