Overview of Filter Operator $textContains

Filter operator $textContains performs full-text search of documents in a heterogeneous SODA collection, that is, a collection that has the media type column.

You can use it to find non-JSON documents that contain text that matches a full-text search pattern. For example, Microsoft Word, Portable Document Format (PDF), and plain-text documents can all be full-text searched using $textContains.

(You can also use $textContains with JSON documents, but for collection with only JSON documents operator $contains is preferable. Operator $contains is not for heterogeneous collections.)

The syntax of the search-pattern value for operator $textContains is the same as that used by SQL function contains, and the resulting behavior is the same. 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 operator $textContains you first must create an Oracle Text search index for the database table that underlies the heterogeneous collection to be searched. This SQL code creates search index mySearchIndex on content column myContentColumn of table myTextCollectionTable:

  CREATE SEARCH INDEX** mySearchIndex ON
  myTextCollectionTable(myContentColumn)

Prior to Oracle Database Release 21c, this more verbose syntax is needed:

  CREATE INDEX mySearchIndex ON
  myTextCollectionTable(myContentColumn)
  INDEXTYPE IS CTXSYS.CONTEXT

If you have a search index for your collection, this simple filter searches for the text Is it about a bicycle?.

{"$textContains" : "Is it about a bicycle?"}

Note:

Operator $textContains is not supported for SODA for Java or SODA for REST.

To use $textContains with SODA for PL/SQL you need Oracle Database Release 21c (21.3) or later.

To use $textContains with SODA for C, SODA for Node.js, or SODA for Python you need the corresponding 21.3 client libraries.

Related Topics