JSON类型列使用CONTAINS函数单字符查询无结果的问题排查请求

The root cause here is Oracle Text's default lexical analyzer behavior, which filters out very short words (like single characters) from being indexed. Here's a straightforward breakdown:

What's Going On #

When you set up a full-text index on a JSON column using Oracle Text's default settings (which relies on the BASIC_LEXER), it applies a minimum word length filter. By default, this filter ignores words shorter than 3 characters (the exact threshold can vary by Oracle version, but single characters are almost always excluded).

• For your first case with "OriginalData": "I": The single character "I" gets discarded during the indexing process—it never makes it into the full-text index. So when you run your CONTAINS query, there’s nothing to match against, hence no results.
• For your second case with "OriginalData": "II": The two-character string "II" crosses the default minimum length threshold, so it gets indexed properly. That’s why your query returns results as expected.

Quick Side Note: Fix Your JSON Syntax #

First, your sample JSON has a syntax error that could cause unexpected behavior down the line:

{"MarketInfo": "ABCDEFGHE"{"OriginalData": "I"}}

This should be valid JSON like:

{"MarketInfo": {"ABCDEFGHE": {"OriginalData": "I"}}}

Make sure your column data uses properly formatted JSON to avoid indexing quirks unrelated to your original issue.

How to Fix the Single-Character Search Issue #

To allow indexing and searching of single-character terms, you’ll need to create a custom lexical analyzer with a lower min_word_length setting, then rebuild your full-text index using this analyzer.

Step 1: Create a Custom Lexer #

BEGIN
  CTX_DDL.CREATE_PREFERENCE('JSON_SINGLE_CHAR_LEXER', 'BASIC_LEXER');
  CTX_DDL.SET_ATTRIBUTE('JSON_SINGLE_CHAR_LEXER', 'MIN_WORD_LENGTH', 1);
END;
/

Step 2: Rebuild or Create the Full-Text Index #

If you already have an index on JSON_COL, drop it first (or rebuild it with the new preference):

-- Drop existing index if needed
DROP INDEX json_col_ft_idx;

-- Create new index with custom lexer
CREATE INDEX json_col_ft_idx ON table1(JSON_COL) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('LEXER JSON_SINGLE_CHAR_LEXER');

Step 3: Test Your Corrected Query #

Now your CONTAINS query for the single character "I" should return the matching row (I fixed syntax errors in your original query too):

SELECT * FROM table1 
WHERE CONTAINS(JSON_COL, '"I" INPATH(''/MarketInfo/ABCDEFGHE/OriginalData'')') > 0;

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