23.1 Oracle SQL Condition JSON_TEXTCONTAINS

You can use Oracle SQL condition json_textcontains in a CASE expression or the WHERE clause of a SELECT statement to perform a full-text search of JSON data.

Oracle Text technology underlies condition json_textcontains. This condition acts like SQL function contains when the latter uses parameter INPATH. The syntax of the search-pattern argument of json_textcontains is the same as that of SQL function contains. This means, for instance, that you can query for text that is near some other text, or query use fuzzy pattern-matching. If the search-pattern argument contains a character or a word that is reserved with respect to Oracle Text search then you must escape that character or word.

To be able to use condition json_textcontains you must first do one of the following; otherwise, an error is raised when you use json_textcontains. (You cannot do both — an error is raised if you try.)

  • Create a JSON search index for the JSON column.

  • Store the column of JSON data to be queried in the In-Memory Column Store (IM column store), specifying keyword TEXT. The column must of data type JSON; otherwise an error is raised. (JSON type is available only if database initialization parameter compatible is at least 20.)

Note:

By default, a JSON search index supports case-insensitive searching. To enable or disable case-sensitive indexing, use the mixed_case attribute of the BASIC_LEXER preference when creating the index. See BASIC_LEXER in Oracle Text Reference.

Note:

Oracle SQL function json_textcontains provides powerful full-text search of JSON data. If you need only simple string pattern-matching then you can instead use a path-expression filter condition with any of these pattern-matching comparisons: has substring, starts with, like, like_regex, or eq_regex.

Example 23-1 shows a full-text query that finds purchase-order documents that contain the keyword Magic in any of the line-item part descriptions.

You can order the results returned by json_textcontains according to their search-hit relevance, by passing an optional scoring-label argument and using ORDER BY SCORE with that same label number. Example 23-2 illustrates this.

See Also:

Example 23-1 Full-Text Query of JSON Data with JSON_TEXTCONTAINS

SELECT data FROM j_purchaseorder
  WHERE json_textcontains(data,
                          '$.LineItems.Part.Description',
                          'Magic');

Example 23-2 JSON_TEXTCONTAINS: Sorting Query Results By Relevance Using SCORE

This query selects the PO numbers of purchase orders whose descriptions contain the text run. It orders the results by relevance using an optional scoring-label argument. The query returns also the relevance score for each purchase order.

The scoring label passed to json_textcontains must be the same as the label used with SCORE. In this case the label is 1.

The first 17 purchase orders listed have score 18; the remaining 85 purchase orders have score 9. The former group match pattern run better than the latter (they match it twice per purchase order instead of once). 

SELECT po.data.PONumber, SCORE(1)
  FROM  j_purchaseorder po
  WHERE json_textcontains (po.data,
                           '$.LineItems.Part.Description',
                           'run',
                           1)
  ORDER BY SCORE(1) DESC;

Results (some elided):


PONUMBER           SCORE(1)
--------           --------
1                    18
9958                 18
...
1388                 18
36                    9
22                    9
...
8637                  9

102 rows selected.

Related Topics

Parent topic: Full-Text Search Queries