JSON_TEXTCONTAINS Condition

Use the SQL/JSON condition JSON_TEXTCONTAINS to test whether a specified character string exists in JSON property values. You can use this condition to filter JSON data on a specific word or number.

This condition takes the following arguments:

A table or view column that contains JSON data. A JSON search index, which is an Oracle Text index designed specifically for use with JSON data, must be defined on the column. Each row of JSON data in the column is referred to as a JSON document.
A SQL/JSON path expression. The path expression is applied to each JSON document in an attempt to match a specific JSON object within the document. The path expression can contain only JSON object steps; it cannot contain JSON array steps.
A character string. The condition searches for the character string in all of the string and numeric property values in the matched JSON object, including array values. The string must exist as a separate word in the property value. For example, if you search for ‘beth’, then a match will be found for string property value “beth smith”, but not for “elizabeth smith”. If you search for ‘10’, then a match will be found for numeric property value 10 or string property value “10 main street”, but a match will not be found for numeric property value 110 or string property value “102 main street”.

This condition returns TRUE if a match is found, and FALSE if a match is not found.

See Also: JSON Full text search queries

JSON_textcontains_condition::=

Description of the illustration json_textcontains_condition.gif

(JSON_basic_path_expression: See Oracle AI Database JSON Developer’s Guide)

column

Specify the name of the table or view column containing the JSON data to be tested. The column must be of data type VARCHAR2, CLOB, or BLOB. A JSON search index, which is an Oracle Text index designed specifically for use with JSON data, must be defined on the column. If a column value is a null or a text literal of length zero, then the condition returns UNKNOWN.

If a column value is not a text literal of well-formed JSON data using strict or lax syntax, then the condition returns FALSE.

JSON_basic_path_expression

Use this clause to specify a SQL/JSON path expression. The condition uses the path expression to evaluate column and determine if a JSON value that matches, or satisfies, the path expression exists. The path expression must be a text literal. See Oracle AI Database JSON Developer’s Guide for the full semantics of JSON_basic_path_expression.

string

The condition searches for the character string specified by string. The string must be enclosed in single quotation marks.

Examples

The following statement creates table families with column family_doc:

CREATE TABLE families (family_doc VARCHAR2(200));

The following statement creates a JSON search index on column family_doc:

CREATE INDEX ix
  ON families(family_doc)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

The following statements insert JSON documents that describe families into column family_doc:

INSERT INTO families
VALUES ('{family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}');

INSERT INTO families
VALUES ('{family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}');

INSERT INTO families
VALUES ('{family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}');

The following statement commits the transaction:

COMMIT;

The following query returns the JSON documents that contain 10 in any property value in the document:

SELECT family_doc FROM families
  WHERE JSON_TEXTCONTAINS(family_doc, '$', '10');

FAMILY_DOC
--------------------------------------------------------------------------------
{family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}
{family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}
{family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}

The following query returns the JSON documents that contain 10 in the id property value:

SELECT family_doc FROM families
  where json_textcontains(family_doc, '$.family.id', '10');

FAMILY_DOC
--------------------------------------------------------------------------------
{family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}

The following query returns the JSON documents that have a 10 in the array of values for the ages property:

SELECT family_doc FROM families
  WHERE JSON_TEXTCONTAINS(family_doc, '$.family.ages', '10');

FAMILY_DOC
--------------------------------------------------------------------------------
{family : {id:11, ages:[42,40,10,5], address : {street : "200 East Street", apt : 20}}}

The following query returns the JSON documents that have a 10 in the address property value:

SELECT family_doc FROM families
  WHERE JSON_TEXTCONTAINS(family_doc, '$.family.address', '10');

FAMILY_DOC
--------------------------------------------------------------------------------
{family : {id:10, ages:[40,38,12], address : {street : "10 Main Street"}}}
{family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}

The following query returns the JSON documents that have a 10 in the apt property value:

SELECT family_doc FROM families
  WHERE JSON_TEXTCONTAINS(family_doc, '$.family.address.apt', '10');

FAMILY_DOC
--------------------------------------------------------------------------------
{family : {id:12, ages:[25,23], address : {street : "300 Oak Street", apt : 10}}}