Oracle JSON_ARRAYAGG不支持DISTINCT的解决方案咨询

由于Oracle原生JSON_ARRAYAGG不支持DISTINCT关键字，要实现聚合数组去重，可以通过以下几种实用方法解决：

方法一：先去重再聚合（保持字段组合关联） #

先对分组字段（key1、key2）和要聚合的字段（foo、bar）做全局去重，确保每个(key1,key2,foo,bar)组合唯一，再执行聚合操作：

SELECT 
    key1, 
    key2, 
    JSON_ARRAYAGG(foo) AS foo, 
    JSON_ARRAYAGG(bar) AS bar 
FROM (
    -- 内层去重，避免分组后聚合出重复值
    SELECT DISTINCT key1, key2, foo, bar 
    FROM (
        select 1 as key1, 2 as key2, '1.0' as foo, 'A' as bar from dual
        UNION 
        select 1, 2, '2.0' , 'A' as bar from dual
        UNION 
        select 3, 4, '2.0' , 'A' as bar from dual
        UNION 
        select 3, 4, '2.0' , 'B' as bar from dual
        UNION 
        select 3, 4, '2.0' , 'B' as bar from dual
    ) z
) deduplicated_data
GROUP BY key1, key2

执行后即可得到期望的去重数组结果。

方法二：独立字段去重（分别对foo、bar去重） #

如果需要对foo和bar各自独立去重（不考虑两者的组合关系），可以用CTE分别处理两个字段的去重聚合，再关联结果：

WITH foo_dedup AS (
    SELECT key1, key2, JSON_ARRAYAGG(foo) AS foo
    FROM (SELECT DISTINCT key1, key2, foo FROM your_table)
    GROUP BY key1, key2
),
bar_dedup AS (
    SELECT key1, key2, JSON_ARRAYAGG(bar) AS bar
    FROM (SELECT DISTINCT key1, key2, bar FROM your_table)
    GROUP BY key1, key2
)
SELECT f.key1, f.key2, f.foo, b.bar
FROM foo_dedup f
INNER JOIN bar_dedup b USING (key1, key2);

将your_table替换为实际数据源即可，该方法能确保foo和bar数组各自无重复值。

方法三：借助LISTAGG转JSON数组（适合简单场景） #

利用支持DISTINCT的LISTAGG函数先拼接去重后的字符串，再转换为JSON数组：

SELECT 
    key1, 
    key2,
    JSON_ARRAY(REPLACE(LISTAGG(DISTINCT foo, '","') WITHIN GROUP (ORDER BY foo), '"', '')) AS foo,
    JSON_ARRAY(REPLACE(LISTAGG(DISTINCT bar, '","') WITHIN GROUP (ORDER BY bar), '"', '')) AS bar
FROM (
    select 1 as key1, 2 as key2, '1.0' as foo, 'A' as bar from dual
    UNION 
    select 1, 2, '2.0' , 'A' as bar from dual
    UNION 
    select 3, 4, '2.0' , 'A' as bar from dual
    UNION 
    select 3, 4, '2.0' , 'B' as bar from dual
    UNION 
    select 3, 4, '2.0' , 'B' as bar from dual
) z
GROUP BY key1, key2;

注意：该方法仅适合聚合字段无特殊字符（如双引号、逗号）的场景，否则会导致JSON格式错误。

内容的提问来源于stack exchange，提问作者JShao