如何在Oracle PL/SQL中识别JSON字符串中的重复键条目

由于Oracle在解析JSON时会自动忽略重复的键（仅保留最后一次出现的键值对），所以无法通过常规JSON函数直接检测重复键，必须从原始CLOB字符串入手进行检查。

针对特定重复键的查询 #

如果你要找fl对象中"12/07/2023"重复出现的行，可以用字符串匹配的方式：

SELECT id, json_date
FROM test_json1
WHERE REGEXP_COUNT(json_date, '"fl":\s*\{\s*"12/07/2023":', 1, 'i') >= 2
   OR REGEXP_COUNT(json_date, ',"12/07/2023":', 1, 'i') >= 2;

这个查询通过正则统计特定键的出现次数，判断是否重复。

通用检测任意重复键的方法 #

如果要检测JSON中所有可能的重复键，可以使用以下思路：

1. 提取JSON对象中的所有键
2. 统计每个键的出现次数
3. 筛选出现次数大于1的行

因为直接解析JSON会丢失重复键，所以需要先将原始字符串转换为键的列表，再统计：

WITH key_list AS (
    SELECT 
        id,
        json_date,
        REGEXP_SUBSTR(json_date, '"([^"]+)"\s*:', 1, LEVEL, 'i', 1) AS json_key
    FROM test_json1
    CONNECT BY 
        LEVEL <= REGEXP_COUNT(json_date, '"([^"]+)"\s*:')
        AND PRIOR id = id
        AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT DISTINCT id, json_date
FROM key_list
GROUP BY id, json_date, json_key
HAVING COUNT(*) > 1;

这个CTE先提取所有JSON键，再分组统计每个键在对应行中的出现次数，筛选出有重复键的行。

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