Oracle中JSON_ARRAYAGG返回重复JSON_OBJECT问题解决方案咨询

解决方案 #

造成数组重复的核心原因是原始数据中存在多组完全相同的test_col1、test_col2、test_col3组合，聚合时会全部纳入生成JSON对象，有两种实现去重的方案：

方案1：使用JSON_ARRAYAGG自带的DISTINCT去重（推荐，Oracle 12cR2及以上版本支持）

直接在内层生成list的JSON_ARRAYAGG中增加DISTINCT关键字，对生成的JSON对象直接去重：

SELECT
    JSON_OBJECT (
        'output' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'common' VALUE test_col1,
                'list' VALUE JSON_ARRAYAGG(
                    DISTINCT JSON_OBJECT('key1' VALUE test_col2, 'key2' VALUE test_col3)
                )
            )
        )
    )
FROM
    test_tbl
WHERE
    test_col4 = 'val7'
GROUP BY
    test_col1

方案2：预去重原始数据（兼容低版本Oracle）

先通过子查询对需要聚合的字段去重，再执行后续的JSON生成逻辑：

SELECT
    JSON_OBJECT (
        'output' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'common' VALUE test_col1,
                'list' VALUE JSON_ARRAYAGG(
                    JSON_OBJECT('key1' VALUE test_col2, 'key2' VALUE test_col3)
                )
            )
        )
    )
FROM (
    SELECT DISTINCT test_col1, test_col2, test_col3
    FROM test_tbl
    WHERE test_col4 = 'val7'
) t
GROUP BY test_col1

两种方案执行后都可以得到你预期的去重后JSON结果。

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