Oracle 19c中获取所有行共有的唯一JSON数组元素

核心思路 #

先筛选出符合动态谓词的目标行，统计这些行的总数；再将每行的FLAGS数组展开，统计每个flag的出现次数；最后筛选出出现次数等于目标总行数的flag，将其聚合为数组，无符合条件的flag时返回null。

具体SQL实现 #

WITH filtered_rows AS (
    -- 替换为你的动态谓词条件，比如 WHERE someID = 'xxx'
    SELECT FLAGS
    FROM MY_TABLE
    WHERE -- 你的筛选条件
),
total_rows AS (
    SELECT COUNT(*) AS row_count FROM filtered_rows
),
flag_stat AS (
    SELECT 
        jt.FLAG,
        COUNT(*) AS occurrence
    FROM filtered_rows fr,
         json_table(fr.FLAGS, '$[*]' columns FLAG path '$') jt
    GROUP BY jt.FLAG
)
SELECT 
    CASE 
        WHEN COUNT(fs.FLAG) = 0 THEN NULL
        ELSE JSON_ARRAYAGG(fs.FLAG ORDER BY fs.FLAG) 
    END AS common_flags
FROM flag_stat fs, total_rows tr
WHERE fs.occurrence = tr.row_count;

逻辑拆解 #

• filtered_rows：先过滤出符合业务条件的行，仅保留FLAGS字段，缩减后续处理的数据范围。
• total_rows：统计筛选后的总行数，作为判断flag是否存在于所有行的基准值。
• flag_stat：将每行的FLAGS数组拆分为单个flag，统计每个flag在筛选结果中的出现次数。
• 最终查询：匹配出现次数等于总行数的flag，用JSON_ARRAYAGG聚合成JSON数组；若无匹配项，返回null。

示例验证 #

• 当筛选结果为["A","B","C"]和["A","B","D"]时，总行数为2，A、B的出现次数均为2，返回["A","B"]。
• 新增["A","D","E"]后，总行数变为3，仅A的出现次数为3，返回["A"]。
• 无共有flag时，无匹配的统计结果，返回null。

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