Oracle查询返回JSON CLOB重复值问题求助

问题根源 #

重复条目是因为关联GRUPOMCC_ITEM表时，同一个ID_MCC对应多条GRUPOMCC_ITEM记录，导致FATURAMENTOS_MCC的同一条数据被多次匹配，最终JSON_ARRAYAGG将这些重复行全部聚合进数组。

可行解决方案 #

方案1：提前去重再聚合 #

通过子查询先筛选出不重复的FATURAMENTOS_MCC记录，再进行JSON聚合，能有效减少后续处理的数据量：

select  
    ftmcc.id_mcc as idMcc, 
    ( 
        JSON_ARRAYAGG 
        ( 
            JSON_OBJECT 
            ( 
                'idFaturamentoMcc' VALUE ftmcc.id_faturamento_mcc, 
                'faturamentoInicial' VALUE ftmcc.faturamento_ini, 
                'faturamentoFinal' VALUE ftmcc.faturamento_final 
            ) RETURNING CLOB 
        ) 
    ) as faturamentos 
from  
    (
        SELECT DISTINCT ftmcc_inner.*
        FROM FATURAMENTOS_MCC ftmcc_inner
        JOIN MCC mcc ON mcc.id_mcc = ftmcc_inner.id_mcc
        JOIN GRUPOMCC_ITEM gMccItem ON gMccItem.id_mcc = mcc.id_mcc
        WHERE 
            ( 
                COALESCE(:#{#searchDTO.monthlyGrossIncome}, NULL) IS NULL 
                OR 
                ( 
                    ftmcc_inner.faturamento_ini <= TO_NUMBER(:#{#searchDTO.monthlyGrossIncome}) 
                    AND 
                    ftmcc_inner.faturamento_final >= TO_NUMBER(:#{#searchDTO.monthlyGrossIncome}) 
                ) 
            )
            AND mcc.mcc_cod IN :#{#searchDTO.mccCodes}
    ) ftmcc
group by ftmcc.id_mcc

方案2：在JSON_ARRAYAGG中直接去重 #

Oracle 12cR2及以上版本支持在JSON_ARRAYAGG内添加DISTINCT，对生成的JSON对象去重：

select  
    ftmcc.id_mcc as idMcc, 
    ( 
        JSON_ARRAYAGG 
        ( 
            DISTINCT JSON_OBJECT 
            ( 
                'idFaturamentoMcc' VALUE ftmcc.id_faturamento_mcc, 
                'faturamentoInicial' VALUE ftmcc.faturamento_ini, 
                'faturamentoFinal' VALUE ftmcc.faturamento_final 
            ) RETURNING CLOB 
        ) 
    ) as faturamentos 
from  
    FATURAMENTOS_MCC ftmcc 
join  
    MCC mcc 
on  
    mcc.mcc_cod in :#{#searchDTO.mccCodes} 
join  
    GRUPOMCC_ITEM gMccItem 
on  
    gMccItem.id_mcc = mcc.id_mcc 
where 
    ( 
        COALESCE(:#{#searchDTO.monthlyGrossIncome}, NULL) IS NULL 
        OR 
        ( 
            ftmcc.faturamento_ini <= TO_NUMBER(:#{#searchDTO.monthlyGrossIncome}) 
            AND 
            ftmcc.faturamento_final >= TO_NUMBER(:#{#searchDTO.monthlyGrossIncome}) 
        ) 
    ) 
group by ftmcc.id_mcc

方案3：基于唯一主键分组去重 #

如果方案2的DISTINCT失效（比如JSON对象存在隐式格式差异），可以通过主键id_faturamento_mcc分组，确保每一条区间记录唯一：

select  
    id_mcc as idMcc, 
    ( 
        JSON_ARRAYAGG 
        ( 
            JSON_OBJECT 
            ( 
                'idFaturamentoMcc' VALUE id_faturamento_mcc, 
                'faturamentoInicial' VALUE faturamento_ini, 
                'faturamentoFinal' VALUE faturamento_final 
            ) RETURNING CLOB 
        ) 
    ) as faturamentos 
from  
    (
        select 
            ftmcc.id_mcc,
            ftmcc.id_faturamento_mcc,
            ftmcc.faturamento_ini,
            ftmcc.faturamento_final
        from FATURAMENTOS_MCC ftmcc 
        join MCC mcc on mcc.mcc_cod in :#{#searchDTO.mccCodes} 
        join GRUPOMCC_ITEM gMccItem on gMccItem.id_mcc = mcc.id_mcc 
        where 
            ( 
                COALESCE(:#{#searchDTO.monthlyGrossIncome}, NULL) IS NULL 
                OR 
                ( 
                    ftmcc.faturamento_ini <= TO_NUMBER(:#{#searchDTO.monthlyGrossIncome}) 
                    AND 
                    ftmcc.faturamento_final >= TO_NUMBER(:#{#searchDTO.monthlyGrossIncome}) 
                ) 
            )
        group by ftmcc.id_mcc, ftmcc.id_faturamento_mcc, ftmcc.faturamento_ini, ftmcc.faturamento_final
    )
group by id_mcc

补充建议 #

优先选择方案1或方案3，提前去重能降低聚合阶段的计算压力，性能更稳定；方案2虽简洁，但依赖Oracle版本且易受JSON格式隐式差异影响。

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