Oracle中JSON_VALUE结合搜索索引与NULL值无法取数的原因

问题场景 #

当表中存储三类JSON数据：{"foo":"111"}、{"notfoo":"222"}、{"foo":null}，执行以下查询期望获取foo为NULL或等于"111"的行时：

SELECT COLUMN1
FROM DUMMY
WHERE JSON_VALUE(COLUMN1, '$.foo') IS NULL
   OR JSON_VALUE(COLUMN1, '$.foo') = '111';

• 创建JSON搜索索引前，查询返回全部3条结果
• 创建索引后，仅返回{"foo":"111"}这1条结果
• 删除索引后，查询恢复正常返回3条结果

原因分析 #

Oracle JSON搜索索引的默认行为导致了该异常：

1. 默认不存储缺失的键（即JSON中不存在foo字段的行）的索引条目
2. 默认不存储显式设置为null的键值（即foo字段值为null的行）的索引条目
3. 当查询使用搜索索引时，JSON_VALUE(COLUMN1, '$.foo') IS NULL条件无法匹配上述两类行——因为索引中没有对应的记录；而全表扫描时，Oracle会直接解析JSON，正确识别缺失键和显式null都属于NULL场景
4. 单独执行IS NULL条件时，优化器可能选择全表扫描，结果正确；但组合OR条件后，优化器倾向于使用索引，导致漏查

解决方案 #

方案1：修改索引参数，存储NULL及缺失键信息 #

创建索引时添加STORE NULLS参数，并将SEARCH_ON设置为ALL（确保包含JSON结构元数据）：

CREATE SEARCH INDEX IX_CNT_DUMMY ON DUMMY(column1) FOR JSON PARAMETERS(
    '
        DATAGUIDE OFF
        SEARCH_ON ALL
        STORE NULLS
        SYNC (EVERY "freq=secondly; interval=1")
        OPTIMIZE (EVERY "freq=weekly; byday=SAT")
    '
);

修改后索引会存储缺失键和显式null的信息，查询即可返回所有符合条件的行。

方案2：强制查询不使用索引 #

通过查询提示NO_INDEX让查询走全表扫描，绕过索引的限制：

select /*+ NO_INDEX(DUMMY IX_CNT_DUMMY) */
    COLUMN1
from
    DUMMY
where
    JSON_VALUE(COLUMN1, '$.foo') IS NULL
    OR JSON_VALUE(COLUMN1, '$.foo') = '111';

方案3：调整查询逻辑（结合JSON_EXISTS） #

明确区分缺失键和显式null的场景，确保两种情况都被覆盖：

select
    COLUMN1
from
    DUMMY
where
    NOT JSON_EXISTS(COLUMN1, '$.foo') -- 匹配缺失foo键的行
    OR JSON_VALUE(COLUMN1, '$.foo') IS NULL -- 匹配foo为null的行
    OR JSON_VALUE(COLUMN1, '$.foo') = '111'; -- 匹配foo为111的行

注：该方案仍需配合STORE NULLS的索引才能正常使用，否则索引依然无法识别缺失键和显式null。

内容的提问来源于stack exchange，提问作者Ismaël