Oracle 12.1.0中使用CONTAINS函数查找JSON键值对的问题及优化方案咨询

你遇到的问题其实是Oracle全文搜索（CONTAINS）的核心逻辑导致的——上下文索引是基于词法分析的，它会把你的搜索字符串拆分成独立的标记（tokens），而不是做精确的子字符串匹配。比如你写的"key":"123424"，会被拆成key和123424两个独立的词，CONTAINS默认会匹配同时包含这两个词的行，而不是包含"key":"123424"这个完整子串的行，这就是结果不符合预期的原因。

下面给你几种适合12.1版本的解决方案，根据你的数据量和需求选择：

1. 用LIKE或正则表达式做精确子串匹配 #

如果你的表数据量不大，直接用LIKE或者REGEXP_LIKE是最简单的方案，完全符合你预期的子串匹配逻辑：

LIKE版本 #

SELECT d.id, d.value 
FROM OD.DOKUMENT d 
WHERE d.VALUE LIKE '%"key":"123424%';

（SQL里用单引号包裹字符串，内部的双引号直接书写即可，无需转义）

正则表达式版本（更严谨） #

如果想避免匹配到类似"key":"1234245"这种部分匹配的情况，可以用正则表达式限定完整的键值对：

SELECT d.id, d.value 
FROM OD.DOKUMENT d 
WHERE REGEXP_LIKE(d.VALUE, '\"key\":\"123424\"');

⚠️ 注意：这两种方法都会触发全表扫描，适合小数据集使用；如果数据量很大，性能会明显下降。

2. 自定义全文索引的词法分析器 #

如果数据量较大，想要保留全文索引的性能优势，你可以自定义一个词法分析器，让Oracle把"key":"123424"这类JSON键值对当成一个完整的词，而不是拆分。

步骤如下：

1. 创建自定义词法分析器偏好设置：

BEGIN
  -- 创建基于BASIC_LEXER的自定义偏好
  CTX_DDL.CREATE_PREFERENCE('JSON_LEXER', 'BASIC_LEXER');
  -- 指定引号和冒号为"连接符"，避免拆分相邻文本
  CTX_DDL.SET_ATTRIBUTE('JSON_LEXER', 'PRINTJOINS', '":');
END;
/

2. 重建上下文索引，使用这个自定义词法分析器：

-- 先删除原有索引
DROP INDEX dokument_value_ctx_idx;
-- 创建新索引
CREATE INDEX dokument_value_ctx_idx 
ON OD.DOKUMENT(VALUE) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('LEXER JSON_LEXER');

3. 用CONTAINS做精确匹配查询（用双引号包裹搜索字符串，指定匹配完整的词）：

SELECT d.id, d.value 
FROM OD.DOKUMENT d 
WHERE CONTAINS(d.VALUE, '"key":"123424"') > 0;

这样既保留了全文索引的高性能，又能准确匹配完整的JSON键值对子串。

3. 用APEX_JSON解析JSON（如果有APEX环境） #

如果你的数据库上安装了Oracle APEX（很多12.1环境会预装），可以用APEX_JSON包直接解析JSON并提取键值，这是最直观的JSON处理方式：

SELECT d.id, d.value 
FROM OD.DOKUMENT d 
WHERE APEX_JSON.GET_VARCHAR2(d.VALUE, 'key') = '123424';

这种方法不需要关心字符串匹配的细节，直接针对JSON结构查询，可读性最好。但要注意，APEX_JSON是逐行解析JSON的，大数据量下性能不如优化后的全文索引。

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