Oracle如何查询指定值集合中不存在于JSON列关联结果的项

核心逻辑 #

先将待校验的REFNAME集合转为行级临时结果集，再和解析JSON后关联两表得到的有效REFNAME结果集做差集运算，即可直接得到不存在的REFNAME值。

实现代码 #

方法1：MINUS 运算实现（逻辑简单直观，适合纯校验场景） #

SELECT COLUMN_VALUE AS NOT_EXIST_REFNAME
FROM TABLE(SYS.ODCIVARCHAR2LIST('MMK06789', 'MMK06790')) -- 替换为你需要校验的REFNAME集合
MINUS
SELECT jt.REFNAME
FROM reference r
CROSS JOIN JSON_TABLE(
    r.PREDECESSOR,
    '$[*]' COLUMNS (
        REFNAME VARCHAR2(100) PATH '$' -- 若JSON存储为对象数组，此处PATH替换为对应字段路径，例如$.refName
    )
) jt
INNER JOIN history h 
    ON r.REFID = h.REFID -- 替换为两表实际的关联字段
WHERE h.CREATDT >= TRUNC(SYSDATE); -- 过滤创建时间大于等于当日的记录

方法2：左连接判空实现（可扩展，适合需要附加校验信息的场景） #

WITH TARGET_CHECK AS (
    -- 待校验的REFNAME集合
    SELECT COLUMN_VALUE AS REFNAME
    FROM TABLE(SYS.ODCIVARCHAR2LIST('MMK06789', 'MMK06790'))
),
VALID_REF AS (
    -- 关联两表后得到的有效REFNAME集合
    SELECT jt.REFNAME
    FROM reference r
    CROSS JOIN JSON_TABLE(
        r.PREDECESSOR,
        '$[*]' COLUMNS (REFNAME VARCHAR2(100) PATH '$')
    ) jt
    INNER JOIN history h ON r.REFID = h.REFID
    WHERE h.CREATDT >= TRUNC(SYSDATE)
)
SELECT t.REFNAME AS NOT_EXIST_REFNAME
FROM TARGET_CHECK t
LEFT JOIN VALID_REF v ON t.REFNAME = v.REFNAME
WHERE v.REFNAME IS NULL;

注意事项 #

• 若你的PREDECESSOR字段存储的是对象数组而非单纯字符串数组，需调整JSON_TABLE中PATH的取值匹配实际JSON结构
• 若使用的Oracle版本低于12cR2不支持JSON_TABLE，可替换为JSON_VALUE配合CONNECT BY语句实现JSON数组拆分
• 待校验集合规模较大时，建议先将集合存入临时表再做关联，可大幅提升查询性能

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