MySQL - 在存在 GROUP BY 的情况下删除 JSON_ARRAYAGG 中的重复结果

Question

我有疑问:

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

此查询的结果在urlLinks列中重复相同的图像网址:

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

如何才能只留下唯一的图像网址？

["index.html"]

GROUP BY 无法从请求中删除!!!

Answer 1

JSON_ARRAYAGG() 不支持 DISTINCT。您可以在子查询中SELECT DISTINCT，然后聚合:

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"]