Overview of SODA Filter Specifications (QBEs)

2 Overview of SODA Filter Specifications (QBEs)

A filter specification is a pattern expressed in JSON. You use it to select, from a collection, the JSON documents whose content matches it, meaning that the condition expressed by the pattern evaluates to true for the content of (only) those documents.

A filter specification is also called a query-by-example (QBE), or simply a filter.

Because a QBE selects documents from a collection, you can use it to drive read and write operations on those documents. For example, you can use a QBE to remove all matching documents from a collection.

Each SODA implementation that supports query-by-example provides its own way to query JSON documents. They all use a SODA filter specification to define the data to be queried. For example, with SODA for REST you use an HTTP POST request, passing URI argument action=query, and providing the filter specification in the POST body.

Note:

In general, query-by-example (QBE) is for querying JSON documents. QBE operators $id and $textContains can exceptionally be used with a heterogeneous collection, that is, a collection that has the media type column. Operator $textContains can only be used with a heterogeneous collection. (A heterogeneous collection can, but it need not, contain JSON documents.)

SODA for Java and SODA for REST do not support operator $textContains, and they do not support operator $id for use with a heterogeneous collection.

QBE patterns use operators for this document selection or matching, including condition operators, which perform operations such as field-value comparison or testing for field existence, and logical combining operators for union ($or) and intersection ($and).

A QBE operator occurs in a QBE as a field of a JSON object. The associated field value is the operand on which the operator acts. SODA operators are predefined fields whose names start with a dollar sign, $.

For example, in this QBE, the object that is the value of field age has as its only field the operator $gt, and the associated operand is the numeric value 45:

{ "age" : { "$gt" : 45 } }

There are different kinds of QBE operators. In particular, there are operators that do the following kinds of things. (This is not an exhaustive list of QBE operators.)

Test whether a field value exists or how it compares with particular values.

This includes the comparison operators: $all, $between, $eq, $exists, $gt, $gte, $hasSubstring, $in, $instr, $like, $lt, $lte, $ne, $nin, $regex, and $startsWith.

For example, this QBE uses operator $gt to test whether the value of a field age is greater than 50.
```
{ "age" : { "$gt" : 50 } }
```
Test spatial (geographic or geometric) properties of a GeoJSON field value.

This includes operators $near, $intersects, and $within.

For example, this QBE uses operator $near to test whether the value of field location is within 60 miles of the given $geometry value (elided here).
```
{ "location" { "$near" : {"$geometry" : {...},
                          "$distance" : 60} } }
```
Full-text search: test whether a field value pattern-matches a given string or number.

This uses QBE operator $contains.

For example, this QBE tests whether the value of a field name contains the word "beth".
```
{ "name" : { "$contains" : "beth" } }
```
Combine conditions logically.

This includes operators $not, $and, $or, and $nor.

For example, this QBE matches either (or both) a field age whose value is not greater than 50 or a field salary whose value is 10000.
```
{ "$or" : [ { "$not" {"age":{"$gt":50} },
            { "salary" : {"$eq":10000} } ] }
```
Sort the objects selected by a QBE query.

This uses QBE operator $orderby in conjunction with operator $query, which provides the QBE that selects the objects to sort.

For example, this QBE first selects all objects in which the value of field salary is greater than 10,000. It then uses operator $orderby to sort those objects by ascending values of field name. The values to sort are interpreted as strings (data type VARCHAR2).
```
{ "$query" : {"salary":{"$gt":10000}},
  "$orderby" : [ { "path" : "name", "datatype" : "varchar2" } ] }
```
Act on a matched value to produce a value that's tested in its place.

This includes the item-method operators: $abs, $boolean, $ceiling, $date, $double, $floor, $length, $lower, $number, $size, $string, $timestamp, $type, and $upper.

For example, in this QBE, item-method $date interprets the value of field birthday as a date value, which is then tested for being greater than (that is, later than) the date represented by ISO 8601 string "2000-01-01". If it is, then the field value is considered a match. The greater-than test uses the interpreted value that results from $date acting on the field value.
```
{ "birthday" : { "$date" : {"$gt":"2000-01-01"} } }
```

2.1 Sample JSON Documents

A few sample JSON documents are presented here. They are referenced in some query-by-example (QBE) examples, as well as in some reference descriptions.

Example 2-1 Sample JSON Document 1

{ "name"    : "Jason",  
  "age"     : 45,   
  "address" : [ { "street" : "25 A street", 
                  "city"   : "Mono Vista", 
                  "zip"    : 94088,
                  "state"  : "CA" } ],  
  "drinks"  : "tea" }

Example 2-2 Sample JSON Document 2

{ "name"    : "Mary", 
  "age"     : 50, 
  "address" : [ { "street" : "15 C street", 
                  "city"   : "Mono Vista", 
                  "zip"    : 97090, 
                  "state"  : "OR" }, 
                { "street" : "30 ABC avenue", 
                  "city"   : "Markstown",
                  "zip"    : 90001, 
                  "state"  : "CA" } ] }

Example 2-3 Sample JSON Document 3

{ "name"   : "Mark", 
  "age"    : 65, 
  "drinks" : ["soda", "tea"] }

Related Topics

Field-Condition Clause (Reference)

Parent topic: Overview of SODA Filter Specifications (QBEs)

2.2 Overview of Paths in SODA QBEs

A filter specification, or query-by-example (QBE), contains zero or more paths to fields in JSON documents. A path to a field can have multiple steps, and it can cross the boundaries of objects and arrays.

(In the context of a QBE, the term "path to a field" is sometimes shortened informally to "field".)

For example, this QBE matches all JSON documents where a zip field exists under field address and has value 94088:

{ "address.zip" : 94088 }

The path in the preceding QBE is address.zip, which matches Example 2-1.

Note:

A SODA QBE is itself a JSON object. You must use strict JSON syntax in a QBE. In particular, you must enclose all field names in double quotation marks ("). This includes field names, such as address.zip, that act as SODA paths. For example, you must write {"address.zip" : 94088}, not {address.zip : 94088}.

Paths can target a particular element of an array in a JSON document, by enclosing the array position of the element in square brackets ([ and ]).

For example, path address[1].zip targets all zip fields in the second object of array addresses. (Array position numbers start at 0, not 1.) The following QBE matches Example 2-2 because the second object of its address array has a zip field with value 90001.

{ "address[1].zip" : 90001}

Instead of specifying a single array position, you can specify a list of positions (for example, [1,2]) or a range of positions (for example, [1 to 3]). The following QBE matches Example 2-3 because it has "soda" as the first element (position 0) of array drinks.

{ "drinks[0,1]" : "soda" }

And this QBE does not match any of the sample documents because they do not have "soda" as the second or third array element (position 1 or 2).

{ "drinks[1 to 2]" : "soda" }

If you do not specify an array step then [*] is assumed, which matches any array element — the asterisk, *, acts as a wildcard. For example, if the value of field drinks is an array then the following QBE matches a document if the value of any array element is the string "tea":

{"drinks" : "tea"}

This QBE thus matches sample documents 1 and 2. An equivalent QBE that uses the wildcard explicitly is the following:

{"drinks[*]" : "tea"}

Related Topics

2.3 Overview of QBE Comparison Operators

A query-by-example (QBE) comparison operator tests whether a given JSON object field satisfies some conditions.

One of the simplest and most useful filter specifications tests a field for equality to a specific value. For example, this filter specification matches any document that has a field name whose value is "Jason". It uses the QBE operator $eq, which tests field-value equality.

{ "name" : { "$eq" : "Jason" } }

For convenience, for such a scalar-equality QBE you can generally omit operator $eq. This scalar-equality filter specification is thus equivalent to the preceding one, which uses $eq:

{ "name" : "Jason" }

Both of the preceding filter specifications match Example 2-1.

The comparison operators are the following:

$all — whether an array field value contains all of a set of values
$between — whether a field value is between two string or number values (inclusive)
$eq — whether a field value is equal to a given scalar
$exists — whether a given field exists
$gt — whether a field value is greater than a given scalar value
$gte — whether a field value is greater than or equal to a given scalar
$hasSubstring — whether a string field value has a given substring (same as $instr)
$in — whether a field value is a member of a given set of scalar values
$instr — whether a string field value has a given substring (same as $hasSubstring)
$like — whether a field value matches a given SQL LIKE pattern
$lt — whether a field value is less than a given scalar value
$lte — whether a field value is less than or equal to a given scalar value
$ne — whether a field valueis different from a given scalar value
$nin — whether a field value is not a member of a given set of scalar values
$regex — whether a string field value matches a given regular expression
$startsWith — whether a string field value starts with a given substring

You can combine multiple comparison operators in the object that is the value of a single QBE field. The operators are implicitly ANDed. For example, the following QBE uses comparison operators $gt and $lt. It matches Example 2-2, because that document contains an age field with a value (50), which is both greater than 45 and less than 55.

{ "age" : { "$gt" : 45, "$lt" : 55 } }

Note:

Both the operand of a SODA operator and the data matched in your documents by a QBE are JSON data. But a comparison operator can in some cases interpret such JSON values specially before comparing them. The use of item-method operators can specify that a comparison should first interpret JSON string data as, for example, uppercase or as a date or a time stamp (date with time). This is explained in the sections about item-method operators.

2.4 Overview of QBE Operator $not

Query-by-example (QBE) operator $not negates the behavior of its operand, which is a JSON object containing one or more comparison clauses, which are implicitly ANDed.

When any of those clauses evaluates to false, the application of $not to them evaluates to true. When all of them evaluate to true, it evaluates to false.

For example, this QBE matches Example 2-1 and Example 2-3: document 1 has a field matching path address.zip and whose value is not "90001", and document 3 has no field matching path address.zip.

{"address.zip" : {"$not" : {"$eq" : "90001"}}}

The $not operand in the following QBE has two comparison clauses. It too matches Example 2-1 and Example 2-3, because each of them has an age field whose value is not both greater than 46 and less than 65.

{"age" : {"$not" : {"$gt" : 46, "$lt" : 65}}}

2.5 Overview of QBE Item-Method Operators

A query-by-example (QBE) item-method operator acts on a JSON-object field value to modify or transform it in some way (or simply to filter it from the query result set). Other QBE operators that would otherwise act on the field value then act on the transformed field value instead.

Suppose you want to select documents whose string-valued field name starts with “Jo”, irrespective of letter case, so that you find matches for name values "Joe", "joe", "JOE", "joE", "Joey", "joseph", "josé", and so on. You might think of using operator $startsWith, but that matches string prefixes literally, considering J and j as different characters, for example.

This is where an item-method operator can come in. Your QBE can use item-method operator $upper to, in effect, transform the raw field data, whether it is "Joey" or "josé", to an uppercase string, before operator $startsWith is applied to test it.

The following QBE matches the prefix of the value of field name, but only after converting it to uppercase. The uppercase value is matched using the condition that it starts with JO.

{ "name" : { "$upper" : { "$startsWith" : "JO" } } }

As another example, suppose that you have documents with a string-valued field deadline that uses an ISO 8601 date-with-time format supported by SODA, and you want to select those documents whose deadline is prior to 7:00 am, January 31, 2019, UTC. You can use item-method operator $timestamp to convert the field string values to UTC time values (not strings), and then perform a time comparison using an operator such as $lt. This QBE does the job:

{ "deadline" : { "$timestamp" : { "$lt" : "2019-01-31T07:00:00Z" } } }

That matches each of the following deadline field values, because each of them represents a time prior to the one specified in the QBE. (The last two represent the exact same time, since 7 pm in a time zone that is 3 hours behind UTC is the same as 10 pm UTC.)

{ "deadline" : "2019-01-28T14:59:43Z" }
{ "deadline" : "2019-01-30T22:00:00Z" }
{ "deadline" : "2019-01-30T19:00:00–03:00" }

Not all item-method operators convert data to a given data type. Some perform other kinds of conversion. Operator $upper, for instance, converts a string value to uppercase — the result is still a string.

Some item-method operators even return data that is wholly different from the field values they are applied to. Operator $type, for instance, returns a string value that names the JSON-language data type of the field value.

So for example, this QBE selects only Example 2-3 of the three sample documents, because it is the only one that has a drinks field whose value is an array (["soda", "tea"]). In particular, it does not match Example 2-1, even though that document has a field drinks, because the value of that field is the string "tea" — a scalar, not an array.

{ "drinks" : { "$type" : "array" } }

Note:

An item-method field (operator) does not, itself, use or act on its associated value (its operand). Instead, it acts on the value of the JSON data that matches its parent field.

For example, in the QBE {"birthday" : {"$date" : {"$gt" : "2000-01-01"}}}, item-method operator $date acts on the JSON data that matches its parent field, birthday. It does not use or act on its operand, which is the JSON object (a comparison clause in this case) {"$gt" : "2000-01-01"}. The birthday data (a JSON string of format ISO 8601) in your JSON document is interpreted as a date, and that date is then matched against the condition that it be greater than the date represented by the (ISO date) string "2000-01-01" (later than January 1, 2000).

This can take some getting used to. The operand is used after the item-method operator does its job. It is matched against the result of the action of the operator on the value of its parent field. A item-method operator is a filter of sorts — it stands syntactically between the field (to its left) that matches the data it acts on and (to its right) some tests that are applied to the result of that action.

Note:

To use item method operator $abs, $date, $size, $timestamp, or $type you need Oracle Database Release 18c or later.
To use any other item method you need Oracle Database Release 12c (12.2.0.1) or later.

2.6 Overview of QBE Logical Combining Operators

You use the query-by-example (QBE) logical combining operators, $and, $or, and $nor, to combine conditions to form more complex QBEs. Each accepts an array of conditions as its argument.

QBE logical combining operator $and matches a document if each condition in its array argument matches it. For example, this QBE matches Example 2-1, because that document contains a field name whose value starts with "Ja", and it contains a field drinks whose value is "tea".

{"$and" : [ {"name" : {"$startsWith" : "Ja"}}, {"drinks" : "tea"} ]}

Often you can omit operator $and — it is implicit. For example, the following query is equivalent to the previous one:

{"name" : {"$startsWith" : "Ja"}, "drinks" : "tea"}

QBE logical combining operator $or matches a document if at least one of the conditions in its array argument matches it.

For example, the following QBE matches Example 2-2 and Example 2-3, because those documents contain a field drinks whose value is "soda" or they contain a field zip under a field address, where the value of address.zip is less than 94000, or they contain both:

{"$or" : [ {"drinks" : "soda"}, {"address.zip" : {"$le" : 94000}} ]}

QBE logical combining operator $nor matches a document if no condition in its array argument matches it. (Operators $nor and $or are logical complements.)

The following query matches sample document 1, because in that document there is neither a field drinks whose value is "soda" nor a field zip under a field address, where the value of address.zip is less than 94000:

{"$nor" : [ {"drinks" : "soda"}, {"address.zip" : {"$le" : 94000}} ]}

Each element in the array argument of a logical combining operator is a condition.

For example, the following condition has a single logical combining clause, with operator $and. The array value of $and has two conditions: the first condition restricts the value of field age. The second condition has a single logical combining clause with $or, and it restricts either the value of field name or the value of field drinks.

{ "$and" : [ { "age" : {"$gte" : 60} },
             { "$or" : [ {"name" :  "Jason"},
                         {"drinks" : {"$in" : ["tea", "soda"]}} ] } ] }

The condition with the comparison for field age matches sample document 3.
The condition with logical combining operator $or matches sample documents 1 and 3.
The overall condition matches only sample document 3, because that is the only document that satisfies both the condition on age and the condition that uses $or.

The following condition has two conditions in the array argument of operator $or. The first of these has a single logical combining clause with $and, and it restricts the values of fields name and drinks. The second has a single logical combining clause with $nor, and it restricts the values of fields age and name.

{ "$or" : [ { "$and" : [ {"name" : "Jason"},
                         {"drinks" : {"$in" : ["tea", "soda"]}} ] },
            { "$nor" : [ {"age" : {"$lt" : 65}},
                         {"name" : "Jason"} ] } ] }

The condition with operator $and matches sample document 1.
The condition with operator $nor matches sample document 3.
The overall condition matches both sample documents 1 and 3, because each of these documents satisfies at least one condition in the $or argument.

2.7 Overview of Nested Conditions in QBEs

You can use a query-by-example (QBE) with a nested condition to match a document that has a field with an array value with object elements, where a given object in the array satisfies multiple conditions.

The following nested-condition query matches documents that have both a city value of "Mono Vista" and a state value of "CA" in the same object element of array address.

{ "address[*]" : { "city" : "Mono Vista", "state" : "CA" } }

It specifies that a matching document must have a field address, and if the value of that field is an array then it must have at least one object element that has a city field with value "Mono Vista" and a state field with value "CA".

Of the three sample JSON documents, this QBE matches only Example 2-1.

The following QBE also matches sample document 1, but it also matches Example 2-2, which has two addresses, one of which has city Mono Vista and the other of which has state CA.

{ "address.city" : "Mono Vista", "address.state" : "CA" }

Unlike the preceding QBE, which uses a nested condition clause, nothing here constrains the city and state to belong to the same address. Instead, this QBE specifies only that matching documents must have a city field with value "Mono Vista" in some object child of an address field, and a state field with value "CA" in some object of the same address field. It does not specify that fields address.city and address.state must reside within the same object.

That last QBE is equivalent to the following one, which has the form of a nested-condition clause but without the [*].

{ "address" : { "city" : "Mono Vista", "state" : "CA" } }

Do not forget to use [*], if your intention is to apply multiple conditions to an object in an array.

2.8 Overview of QBE Operator $id

Other query-by-example (QBE) operators generally look for particular JSON fields within documents and try to match their values. Operator $id is an exception in that it instead matches document keys. It thus matches document metadata, not document content. You use operator $id in the outermost condition of a QBE.

Example 2-4 shows three QBEs that use operator $id.

Example 2-4 Using $id To Find Documents That Have Given Keys

Find the unique document that has key key1.

{"$id" : "key1"}

Find the documents that have any of the keys key1, key2, and key3.


{"$id" : ["key1","key2","key3"]}

Find the documents that have at least one of the keys key1 and key2, and that have an object with a field address.zip whose value is at least 94000.

{"$and" : [{$id : ["key1", "key2"]},
           {"address.zip" : { "$gte" : 94000 }}]}

Related Topics

ID Clause (Reference)

Parent topic: Overview of SODA Filter Specifications (QBEs)

2.9 Overview of QBE Operator $orderby

Query-by-example (QBE) operator $orderby is described. It sorts query results in ascending or descending order.

You can specify the sort order for individual fields and the relative sort order among fields.

Operator $orderby can be used with two alternative syntaxes: array and abbreviated.

Regardless of the syntax choice, when you use $orderby in a filter specification together with one or more filter conditions, you must wrap those conditions with operator $query. In the queries shown here, the returned documents are restricted to those that satisfy a filter condition that specifies that field age must have a value greater than 40.

Using the Orderby Clause Array Syntax

The array syntax is the more straightforward of the two. You follow $orderby by an array of the fields to sort, in their relative sort order: the first array element specifies the first field to sort by, the second element specifies the second field to sort by, and so on.

The array syntax also lets you specify the SQL data type to use for sorting a given field, that is, how to interpret the field values, for sorting purposes.

For example, you can specify whether a field that has numeric codes (as a string or as a number) should be sorted lexicographically (as a string of digit characters) or numerically as a sequence of digits interpreted as a number). With "varchar2" as the sort data type, "100" sorts, in ascending order, before "9". With "number" as the sort type, "9" sorts, in ascending order, before "100", since the number 9 is smaller than the number 100.

Note:

To use a datatype value of date or timestamp you need Oracle Database Release 18c or later.

Finally, the array syntax also lets you specify, for a string-valued field, a maximum number of characters at the start of the string. An error is raised if a string value for the targeted field is too long.

The following QBE selects objects in which field salary is between 10,000 and 20,000, inclusive. It sorts the objects first by descending age, interpreted as a number, then by ascending name, interpreted as a string. An error is raised if the string value of field name is longer than 100 characters in any matching document. The default error handling also applies: raise an error if the value of any of the specified fields is not convertible to the specified datatype, but do not raise an error just because some of the specified fields are missing.

{ "$query"   : { "salary" : { "$gt" : 10000, "$lte" : 20000 } },
  "$orderby" : [ { "path" :      "age",
                   "datatype" :  "number",
                   "order" :     "desc" },
                 { "path" :      "name",
                   "datatype" :  "varchar2",
                   "order" :     "asc",
                   "maxLength" : 100 } ] }

The following QBE is the same, except that it specifies scalarRequired = true, to require that field name be present in each matching document (as well as requiring that its value be convertible to a string). Otherwise, an error is raised at query time.

{ "$query"   : { "salary" : { "$gt" : 10000, "$lte" : 20000 } },
  "$orderby" : { "$fields" :
                 [ { "path" :      "age",
                     "datatype" :  "number",
                     "order" :     "desc" },
                   { "path" :      "name",
                     "datatype" :  "varchar2",
                     "order" :     "asc",
                     "maxLength" : 100 } ],
                 "$scalarRequired" : true } }

Using the Orderby Clause Abbreviated Syntax

The abbreviated syntax lets you list the fields to sort by and their relative sort order in a succinct way. You cannot use it to specify how to interpret the values of a given field for sorting purposes, that is, which data type to interpret the values as. And you cannot specify a maximum number of characters to take into account when sorting a string field.

The following QBE specifies the order of fields age and name when sorting documents where the salary is between 10,000 and 20,000. A value of –1 specifies descending order for age. A value of 2 specifies ascending order for name. Sorting is done first by age and then by name, because the absolute value of –1 is less than the absolute value of 2 — not because -1 is less than 2, and not because field age appears before field name in the $orderby object.

{ "$query" : { "salary" : { $between [10000, 20000] } },
  "$orderby" :  { "age" : -1, "name" : 2 } }

Related Topics

Orderby Clause Sorts Selected Objects

Parent topic: Overview of SODA Filter Specifications (QBEs)

2.10 Overview of QBE Spatial Operators

You can use query-by-example (QBE) operator $near, $intersects, or $within to select documents that have a field whose value is a GeoJSON geometry object that is near a specified position, intersects a specified geometric object, or is within another specified geometric object, respectively.

Note:

To use QBE spatial operators you need Oracle Database Release 12c (12.2.0.1) or later.

The following QBE selects only documents that have a location field whose value is a Point GeoJSON geometry object that represents a position within 50 kilometers of the coordinates [34.0162, -118.2019].

{ "location" :
  { "$near" :
    { "$geometry" : { "type" : "Point",
                      "coordinates" : [34.0162, -118.2019] },
      "$distance" : 50,
      "$unit"     : "KM" } } }

It can retrieve a document that has an object such as this one, for example:

{ "location" : { "type" : "Point",
                 "coordinates": [33.7243, -118.1579] } }

Any document that does not contain a location field is ignored (skipped) without error. But if the queried collection contains a document with a location field that does not have as value a (single) GeoJSON geometry object then an error is raised. A document with this object, for example, raises an error:

{ "location" : "1600 Pennsylvania Ave NW, Washington, DC 20500" }

You can provide different (non-default) error-handling behavior for your QBE by including a true-valued $scalarRequired or $lax field (but not both together) in the object that is the value of spatial operator $near, $intersects, or $within.

A true value for field $scalarRequired means raise an error if any document does not have a location field. (An error is still also raised for a location field whose value is not a geometry object.)
A true value for field $lax means ignore not only a missing location field but also a location field whose value is not a GeoJSON geometry object.

For example, this QBE raises an error if any document has no location field or if any document has a location field whose value is not a geometry object:

{ "location" :
  { "$near" :
    { "$geometry"      : { "type" : "Point",
                           "coordinates" : [34.0162, -118.2019] },
      "$distance"      : 50,
      "$unit"          : "KM",
      "$scalarRequired : true } } }

And this QBE does not raise an error for a document that has no location field or for a document that has a location field whose value is not a geometry object:

{ "location" :
  { "$near" :
    { "$geometry" : { "type" : "Point",
                      "coordinates" : [34.0162, -118.2019] },
      "$distance" : 50,
      "$unit"     : "KM",
      "$lax"      : true } } }

Note:

If you have created a SODA spatial index for a field whose value is a GeoJSON geometry object, and if you use a QBE that targets that field, then the index can be picked up for the QBE only if both index and QBE specify the same error-handling behavior for that field. Both must specify the same one of these:

scalarRequired : true
lax : true
Neither scalarRequired : true nor lax : true

2.11 Overview of QBE Operator $contains

Query-by-example (QBE) operator $contains performs full-text search of JSON documents in a SODA collection.

A QBE field whose value is an object with a $contains operator matches a JSON document if the document has that field with a full-word string value or a full-number value that matches the string operand of $contains somewhere, including in an array element. Matching is Oracle Text full-text.

The syntax of the search-pattern value for operator $contains is the same as the third parameter of SQL condition json_textcontains, 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 use full-text search with $contains you must create a JSON search index for the document collection. To do that you pass a search-index specification, such as in Example 3-3, to the index-creation function or method for your chosen SODA implementation (language).

Once you have a search index for your collection, this simple QBE searches the street fields of all documents, case-insensitively, for a value that contains the word "abc".

{"street" : { "$contains" : "abc"}}

Example 2-2 is a match, for example, because it has a street field with value "30 ABC avenue".

Related Topics

2.12 Overview of QBE Operator $textContains

Query-by-example (QBE) 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 QBE 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

Text-Contains Clause (Reference)

Parent topic: Overview of SODA Filter Specifications (QBEs)