SODA Filter Specifications (Reference)

5 SODA Filter Specifications (Reference)

You can select JSON documents in a collection by pattern-matching. A detailed definition of SODA filter specifications (QBEs) is presented.

A filter specification, also known as a query-by-example (QBE) or simply a filter, is a SODA query that uses a pattern expressed in JSON. A QBE is itself a JSON object. SODA query operations use a QBE to select all JSON documents in a collection that satisfy it, meaning that the filter evaluates to true for only those documents. A QBE thus specifies characteristics that the documents that satisfy it must possess.

A filter can use QBE operators, which are predefined JSON fields whose names start with a dollar-sign character ($). The JSON value associated with an operator field is called its operand or its argument.^Foot 1

Although a SODA operator is itself a JSON field, for ease of exposition in the context of filter specification descriptions, the term “field” generally refers here to a JSON field that is not a SODA operator. Also, in the context of a QBE, “field” is often used informally to mean “path to a field”.

Note:

You must use strict JSON syntax in a SODA filter specification, enclosing each nonnumeric, non-Boolean, and non-null JSON value in double quotation marks ("). In particular, the names of all JSON fields, including SODA operators, must be enclosed in double quotation marks.

A filter specification is a JSON object. There are two kinds of filter specification:

Composite filter.
Filter-condition filter.

A filter specification (QBE) can appear only at the top (root) level of a query. However, a filter condition can be used either on its own, as a filter-condition filter (a QBE), or at a lower level, in the query clause of a composite filter.

Note:

Query-by-example is not supported on a heterogeneous collection, that is, a collection that has the media type column. Such a collection is designed for storing both JSON and non-JSON content. QBE is supported only for collections that contain only JSON documents.

See Also:

Oracle Database JSON Developer’s Guide for information about strict and lax JSON syntax

Composite Filters (Reference)
A composite filter specification (query-by-example, or QBE) can appear only at the top level. That is, you cannot nest a composite filter inside another composite filter or inside a filter condition.
Filter Conditions (Reference)
A filter condition can be used either on its own, as a filter specification, or at a lower level, in the query clause of a composite filter specification.

Related Topics

Media Type Column Name

5.1 Composite Filters (Reference)

A composite filter specification (query-by-example, or QBE) can appear only at the top level. That is, you cannot nest a composite filter inside another composite filter or inside a filter condition.

A composite filter consists of one or both of these clauses:

Query clause

It has the form $query filter_condition.
Orderby clause

It has the form $orderby orderby_spec.

Neither clause can appear more than once.

The following composite filter contains both clauses:

{ "$query" : { "salary" : { "gt" : 10000 } },
  "$orderby" : { "age" : -1, "zip" : 2 } }

In this example, the query clause selects documents that have a salary field whose value is greater than 10,000, and the orderby clause sorts the selected documents first by descending age and then by ascending zip code.

Orderby Clause Sorts Selected Objects
A filter specification (query-by-example, or QBE) with an orderby clause returns the selected JSON documents in sorted order.

Related Topics

Filter Conditions (Reference)

5.1.1 Orderby Clause Sorts Selected Objects

A filter specification (query-by-example, or QBE) with an orderby clause returns the selected JSON documents in sorted order.

An orderby clause specifies the fields to use for sorting, along with their individual directions, and the order of sorting among the fields.

The syntax of an orderby clause is "$orderby" followed by an object with one or more members, whose fields are used for sorting:

"$orderby" : { field1 : direction1, field2 : direction2, ... }

Each field is a string that is interpreted as a path from the root of the candidate object.

Each direction is a non-zero integer. The returned documents are sorted by the field value in ascending or descending order, depending on whether the value is positive or negative, respectively.

The fields in the $orderby operand are sorted in the order of their magnitudes (absolute values), smaller magnitudes before larger ones. For example, a field with value -1 sorts before a field with value 2, which sorts before a field with value 3. As usual, the order of the fields in the object value of $orderby is immaterial.

If the absolute values of two or more sort directions are equal then the order in which the fields are sorted is determined by the order in which they appear in the serialized JSON content that you use to create the JSON document.

Oracle recommends that you use sort directions that have unequal absolute values, to precisely govern the order in which the fields are used, especially if you use an external tool or library to create the JSON content and you are unsure of the order in which the resulting content is serialized.

This filter specification selects objects in which field salary has a value greater than or equal to 10,000 and less than or equal to 20,000. It sorts the objects first by descending age and then by ascending name. The order of the object members (age, name) is irrelevant.

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

Related Topics

SODA Paths (Reference)

5.2 Filter Conditions (Reference)

A filter condition can be used either on its own, as a filter specification, or at a lower level, in the query clause of a composite filter specification.

A filter condition is a JSON object whose members form one or more of these clauses:

scalar-equality clause
field-condition clause
logical combining clause
nested-condition clause
ID clause
special-criterion clause

A filter condition is true if and only if all of its clauses are true. A filter condition can be empty (the empty object, {}), in which case all of its (zero) clauses are vacuously true (the filter condition is satisfied).

For example, if a QBE involves only one filter condition and it is empty then all documents of the collection are selected. In this case, a find operation returns all of the documents, and a remove operation removes them all.

Scalar-Equality Clause (Reference)
A scalar-equality clause tests whether a given object field is equal to a given scalar value.
Field-Condition Clause (Reference)
A field-condition clause specifies that a given object field must satisfy a given set of criteria. It constrains a field using one or more condition-operator clauses, each of which is a comparison clause, a not clause, or an item-method clause.
Logical Combining Clause (Reference)
A logical combining clause combines the effects of multiple non-empty filter conditions.
Nested-Condition Clause (Reference)
A nested-condition clause consists of a parent field followed by a single, non-empty filter condition. All fields contained in this nested condition are scoped to the parent field.
ID Clause (Reference)
Other query-by-example (QBE) operators generally look for particular JSON fields within the content of documents and try to match their values. An ID clause, which uses operator $id, instead matches document keys. It thus matches document metadata, not document content.
Special-Criterion Clause (Reference)
A special criterion clause is a contains clause (operator $contains).

5.2.1 Scalar-Equality Clause (Reference)

A scalar-equality clause tests whether a given object field is equal to a given scalar value.

A scalar-equality clause is an object member with a scalar value. It tests whether the value of the field is equal to the scalar.

field : scalar

(Reminder: a JSON scalar is a value other than an object or an array; that is, it is a JSON number, string, true, false, or null.)

A scalar-equality clause is equivalent in behavior to a field-condition clause with a comparison clause that tests the same field value using operator $eq. That is, field : scalar is equivalent to field : { "$eq" scalar }.

Though the behavior is equivalent, a scalar-equality clause cannot be used in some contexts where the corresponding "$eq" : field member can be used. For example, a scalar-equality clause cannot be used in a not clause. The array elements in the argument array of a not clause must be comparison clauses.

5.2.2 Field-Condition Clause (Reference)

A field-condition clause specifies that a given object field must satisfy a given set of criteria. It constrains a field using one or more condition-operator clauses, each of which is a comparison clause, a not clause, or an item-method clause.

A field-condition clause is JSON-object member whose field is not an operator and whose value is an object with one or more members, each of which is a condition-operator clause:

field : { condition-operator-clause ... }

A field-condition clause tests whether the field satisfies all of the condition-operator clauses, which are thus implicitly ANDed.

A condition-operator clause is any of these:

A comparison clause
A not clause
An item-method clause

Note:

When a path that does not end in an array step uses a comparison clause or a not clause, and the path targets an array, the test applies to each element of the array.

For example, the QBE {"animal" : {"$eq" : "cat"}} matches the JSON data {"animal" : ["dog", "cat"]}, even though "cat" is an array element. The QBE {"animal" : {$not : {"$eq" : "frog"}}} matches the same data, because each of the array elements is tested for equality with "frog" and this test fails.

Comparison Clause (Reference)
A comparison clause is an object member whose field is a comparison operator. Example: "$gt" : 200.
Not Clause (Reference)
A not clause logically negates the truth value of a set of comparison clauses. When any of the comparison clauses is true, the not clause evaluates to false; when all of them are false, the not clause evaluates to true.
Item-Method Clause (Reference)
An item-method clause is an item-method equality clause or an item-method modifier clause. It applies an item method to the field of the field-condition clause in which it appears, typically to modify the field value. It then matches the result against the operand of the item-method.
ISO 8601 Date and Time Support
International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates and times. Oracle Database supports many of the ISO 8601 date and time formats.

Related Topics

5.2.2.1 Comparison Clause (Reference)

A comparison clause is an object member whose field is a comparison operator. Example: "$gt" : 200.

Table 5-1 describes the comparison operators. See Sample JSON Documents for the documents used in the examples in column Description.

Table 5-1 Query-By-Example (QBE) Comparison Operators

Operator	Description
`$exists`	Tests whether the field exists. Matches document if either: The field exists and the operand represents true, meaning that it is any scalar value except `false`, `null`, or `0`. The field does not exist and the operand represents false, meaning that it is `false`, `null`, or `0`. Operand JSON scalar. Example `{drinks : { "$exists" : true }}` matches sample document 3. `{drinks : { "$exists" : false }}` matches sample documents 1 and 2.
`$eq`	Matches document only if field value equals operand value. Operand JSON scalar. Example `{"name" : { "$eq" : "Jason" }}` matches sample document 1.
`$ne`	Matches document only if field value does not equal operand value or there is no such field in the document. Operand JSON scalar. Example `{"name" : { "$ne" : "Jason" }}` matches sample documents 2 and 3.
`$gt`	Matches document only if field value is greater than operand value. Operand JSON number or string. Example `{"age" : { "$gt" : 50 }}` matches sample document 2.
`$lt`	Matches document only if field value is less than operand value. Operand JSON number or string. Example `{"age" : { "$lt" : 50 }}` matches sample document 1.
`$gte`	Matches document only if field value is greater than or equal to operand value. Operand JSON number or string. Example `{"age" : { "$gte" : 45 }}` matches sample documents 1, 2, and 3.
`$lte`	Matches document only if field value is less than or equal to operand value. Operand JSON number or string. Example `{"age" : { "$lte" : 45 }}` matches sample document 1.
`$between`	Matches document only if string or number field value is between the two operand array elements or equal to one of them. Operand JSON array of two scalar elements. The first must be the smaller of the two. (For string values, smaller means first, lexicographically.) At most one of the elements can be `null`, which means no limit. An error is raised if both are `null` or if there are not exactly two array elements. Example `{"age" : { "$between" : [49, 70] }}` matches sample documents 2 and 3. `{"age" : { "$between" : [45, null] }}` matches sample documents 1, 2, and 3. It is equivalent to `{"age" : { "$gte" : 45 }}`
`$startsWith`	Matches document only if field value starts with operand value. Operand JSON string. Example `{"name" : {"$startsWith" : "J"}}` matches sample document 1.
`$hasSubstring` or `$instr`	Matches document only if field value is a string with a substring equal to the operand. Operand Non-empty JSON string. Example `{"street" : { "$hasSubstring" : "street" }}` matches sample documents 1 and 2.
`$regex`	Matches document only if field value matches operand regular expression. Operand SQL regular expression, as a JSON string. See Oracle Database SQL Language Reference. Example `{"name" : { "$regex" : ".*son"}}` matches sample document 1.
`$like`	Matches document only if field value matches operand pattern. Operand SQL `LIKE` condition pattern, as a JSON string. See Oracle Database SQL Language Reference. Example `{"city" : { "$like" : "Mar_" }}` matches sample documents 2 and 3.
`$in`	Matches document only if field exists and its value equals at least one value in the operand array. Operand Non-empty JSON array of scalars.^Foot 2 Example `{"address.zip" : { "$in" : [ 94088, 90001 ] }}` matches sample documents 1 and 2.
`$nin`	Matches document only if one of these is true: Field exists, but its value is not equal to any value in the operand array. Field does not exist. Operand Non-empty JSON array of scalars.^Footref 2 Example `{"address.zip" : { "$nin" : [ 90001 ] }}` matches sample documents 1 and 2.
`$all`	Matches document only if one of these is true: Field value is an array that contains all values in the operand array. Field value is a scalar value and the operand array contains a single matching value. Operand Non-empty JSON array of scalars.^Footref 2 Example `{"drinks" : { "$all" : ["soda", "tea"]}}` matches sample document 2. `{"drinks": { "$all" : ["tea"]}}` matches sample documents 1 and 2.

^Footnote 2

A syntax error is raised if the array does not contain at least one element.

5.2.2.2 Not Clause (Reference)

A not clause logically negates the truth value of a set of comparison clauses. When any of the comparison clauses is true, the not clause evaluates to false; when all of them are false, the not clause evaluates to true.

A not clause is an object member whose field is operator $not and whose value is an object whose members are comparison clauses, which are implicitly ANDed before negating the truth value of that conjunction.

"$not" : { comparison-clause ... }

Example: "$not" : {"$eq" : 200, "$lt" : 40}.

The following field-condition clause matches documents that have no field address.zip, as well as documents that have such a field but whose value is a scalar other than "90001" or an array that has no elements equal to "90001":

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

In contrast, the following field-condition clause has the complementary effect: it matches documents that have a field address.zip whose value is either the scalar "90001" or an array that contains that scalar value.

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

Here is an example of a field-condition clause with field salary and with value a not clause whose operand object has more than one comparison clause. It matches salary values that are not both greater than 20,000 and less than 100,000. That is, it matches salary values that are either less than or equal to 20,000 or greater than or equal to 100,000.

"salary" : {"$not" : {"$gt":20000, "$lt":100000}}

5.2.2.3 Item-Method Clause (Reference)

An item-method clause is an item-method equality clause or an item-method modifier clause. It applies an item method to the field of the field-condition clause in which it appears, typically to modify the field value. It then matches the result against the operand of the item-method.

For example, item-method operator $timestamp interprets as a time stamp a string-valued field that is in one of the supported ISO 8601 date formats. After the operator is applied to the value of the targeted field, other processing takes place, including the evaluation of any not clause and comparison clauses that make up the item-method modifier clause. The QBE uses the modified data in place of the raw field data that is in your JSON documents.

In some cases, the application of an item-method operator acts only as a filter, removing targeted data from the QBE result set. For example, if item-method $timestamp is applied to a string value that is not in one of the supported ISO 8601 date formats then there is no match — the query treats that field occurrence as if it were not present in the document.

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 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.

Item-Method Equality Clause

An item-method equality clause is an object member whose field is an item-method operator and whose value is a JSON scalar.^Foot 3

item-method-operator : scalar

The clause first applies the item method to the field of the field-condition clause. It then tests whether the result is equal to the scalar value (operand).

Example: "$upper" : "john"

(An item-method equality clause is equivalent to an item-method modifier clause (see next) whose field value (operand) is an object with a single comparison clause with comparison operator $eq. For example, "$upper" : "john" is equivalent to "$upper" : {"$eq" : "john"}.)

Item-Method Modifier Clause

An item-method modifier clause is an object member whose field is an item-method operator and whose value (operand) is an object whose members are comparison clauses or at most one not clause. The operand of the item-method operator cannot be an empty object.

item-method-operator : { comparison-or-not-clause ... }^Foot 4

The clause first applies the item method to the field of the field-condition clause. It then tests whether the result of that operation satisfies all of the comparison clauses and not clause in its object value.

Example: "$upper" : { "$between" : [ "ALPHA", "LAMBDA" ], "$not" : { "$startsWith" : "BE" } }

Item-Method Operators

Here is a brief description of each item-method operator. The target of the operator is the data matched by the field of the field-condition clause in which the item-method clause appears — the parent field of the operator. It is not the operand of the operator.

Table 5-2 Item-Method Operators

Operator	Description^Foot 5
`$abs`	Absolute value of the targeted JSON number. Target of Operator JSON number Example `{"ordinate" : {"$abs" : {"$gt" : 1.0}}}` matches a negative or positive `ordinate` value whose magnitude is greater than 1.0. It matches, for example, `–1.3` and `1.3`.
`$boolean`	A Boolean interpretation of the targeted JSON value. Target of Operator JSON Boolean value (`true` or `false`) or a string that when converted to lowercase is either `"true"` or `"false"` Example `{"retired" : {"$boolean" : true}}` matches (only) a `retired` value of `true` or a string that matches `"true"` case-insensitively.
`$ceiling`	The targeted JSON number, rounded up to the nearest integer. Target of Operator JSON number Example `{"age" : {"$ceiling" : {"$lt" : 65}}}` matches an `age` value of `63.9`. It does not match a value of `64.1`, because 64.1 rounds up to 65.
`$date`^Foot 6	A date interpretation of the targeted JSON string. Target of Operator JSON string in supported ISO 8601 format Example `{"birthday" : {"$date" : "2018–06–30"}}` matches a `"birthday"` value of `"2018–06–30"` or `"2018–06–30T17:29:08Z"`, because they are supported ISO 8601 formats for the same date.
`$double`	A SQL `BINARY_DOUBLE` interpretation of the targeted JSON number or numeric string value. Target of Operator JSON number or numeric string Example `{"thickness" : {"$double" : {"$lt" : 1.0}}}` matches a `thickness` value of `"0.999999999"`.
`$floor`	The targeted JSON number, rounded down to the nearest integer. Target of Operator JSON number Example `{"age" : {"$floor" : {"$le" : 65}}}` matches an `age` value of `65.2`. It does not match a value of `66.3`, because 66.3 rounds down to 66.
`$length`	The number of characters in the targeted JSON string. Target of Operator JSON string Example `{"name" : {"$length" : {"$gt" : 4}}}` matches `"Jason"`. It does not match `"Mary"` because that string has only 4 characters.
`$lower`	The lowercase string that corresponds to the characters in the targeted JSON string. Target of Operator JSON string Example `{"name" : {"$lower" : "mary"}}` matches `"Mary"`.
`$number`	A SQL `NUMBER` interpretation of the targeted JSON number or numeric string value. Using `$number` is equivalent to specifying a numeric constant. Target of Operator JSON number or numeric string Example `{"thickness" : {"$number" : {"$lt" : 1.0}}}` matches a `thickness` value of `"0.9999"`. `{"thickness" : {"$number" : {"$lt" : 1.0}}}` is equivalent to `{"thickness" : {"$lt" : 1.0}}`.
`$size`	The number of elements in an array, or 1 for a scalar or an object. Target of Operator JSON value of any kind Example `{"drinks" : {"$size" : {"$gt" : 1}}}` matches a `drinks` value of `["soda", "coffee"]` because the value is an array with more than one element. `{"address" : {"$size" : 1}}` matches an `address` value that is a JSON object.
`$string`	A SQL `VARCHAR2(4000)` interpretation of the targeted JSON scalar. Using `$string` is equivalent to specifying a string constant (literal). Target of Operator JSON scalar other than `null` Example `{"age" : {"$string" : {"$lt" : "45"}}}` matches a numeric `age` value of `100`, because the string `"100"` is lexicographically less than the string `"45"`. `{"age" : {"$string" : {"$lt" : "45"}}}` is equivalent to `{"age" : {"$lt" : "45"}}`.
`$timestamp`^Foot 7	A date-with-time interpretation of the targeted JSON string. Target of Operator JSON string in supported ISO 8601 format Example `{"meeting—time" : {"$timestamp" : VALUE}}`, where `VALUE` is any of the following, matches any of the same values: `"2016-07-26T02:06:01Z"` `"2016-07-26T02:06:01"` (UTC by default) `"2016-07-26T01:06:01-01:00"` (1:00 am in a time zone that is one hour behind UTC is equivalent to 2:00 am UTC.) If `VALUE` is a date-only ISO 8601 string then its equivalent date-with-time value is used. For example, a date value of `"2016-07-26"` is treated as the date-with-time zone value `"2016-07-26T00:00:00Z"`.
`$type`	The name of the JSON data type of the targeted data, as a lowercase JSON string. `"null"` for a value of `null`. `"boolean"` for a value of `true` or `false`. `"number"` for a number. `"string"` for a string. `"array"` for an array. `"object"` for an object. Target of Operator JSON value of any kind Example `{"address" : {"$type" : "object"}}` matches an `address` value that is a JSON object.
`$upper`	The uppercase string that corresponds to the characters in the targeted JSON string. Target of Operator JSON string Example `{"name" : {"$upper" : "MARY"}}` matches `"Mary"`.

^Footnote 5The scalar-equality abbreviation {field : {operator : value}} is used everywhere in examples here, in place of the equivalent {field : {operator : {"$eq" : value}}}.

^Footnote 6The operand of operator $date must be a JSON string that has a supported ISO 8601 format. Otherwise, no match is found.

^Footnote 7The operand of operator $timestamp must be a JSON string that has a supported ISO 8601 format. Otherwise, no match is found.

Note:

If an item-method conversion fails for any reason, such as the operand being of the wrong type, then the path cannot be matched (it refers to no data), and no error is raised.
If an item-method operator is applied to an array then it is in effect applied to each of the array elements.

For example, QBE {"color" : {"$upper" : "RED"}} matches data {"color" : ["Red", "Blue"]} because the array has an element that when converted to uppercase matches "RED". The QBE is equivalent to {"color[*]" : {"$upper" : "RED"}} — operator $upper is applied to each array element of the target data.

Related Topics

See Also:

Oracle Database JSON Developer’s Guide

5.2.2.4 ISO 8601 Date and Time Support

International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates and times. Oracle Database supports many of the ISO 8601 date and time formats.

International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates and times. You can manipulate strings that are in the most common ISO 8601 date and time formats as proper Oracle Database date and time values. The ISO 8601 formats that are supported are essentially those that are numeric-only, language-neutral, and unambiguous.

This is the allowed syntax for dates and times:

Date (only): YYYY-MM-DD
Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

where:

YYYY specifies the year, as four decimal digits.
MM specifies the month, as two decimal digits, 00 to 12.
DD specifies the day, as two decimal digits, 00 to 31.
hh specifies the hour, as two decimal digits, 00 to 23.
mm specifies the minutes, as two decimal digits, 00 to 59.
ss[.s[s[s[s[s]]]]] specifies the seconds, as two decimal digits, 00 to 59, optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
Z specifies UTC time (time zone 0). (It can also be specified by +00:00, but not by –00:00.)
(+|-)hh:mm specifies the time-zone as difference from UTC. (One of + or – is required.)

For a time value, the time-zone part is optional. If it is absent then UTC time is assumed.

No other ISO 8601 date-time syntax is supported. In particular:

Negative dates (dates prior to year 1 BCE), which begin with a hyphen (e.g. –2018–10–26T21:32:52), are not supported.
Hyphen and colon separators are required: so-called “basic” format, YYYYMMDDThhmmss, is not supported.
Ordinal dates (year plus day of year, calendar week plus day number) are not supported.
Using more than four digits for the year is not supported.

Supported dates and times include the following:

2018–10–26T21:32:52
2018-10-26T21:32:52+02:00
2018-10-26T19:32:52Z
2018-10-26T19:32:52+00:00
2018-10-26T21:32:52.12679

Unsupported dates and times include the following:

2018-10-26T21:32 (if a time is specified then all of its parts must be present)
2018-10-26T25:32:52+02:00 (the hours part, 25, is out of range)
18-10-26T21:32 (the year is not specified fully)

Related Topics

Item-Method Clause (Reference)

See Also:

5.2.3 Logical Combining Clause (Reference)

A logical combining clause combines the effects of multiple non-empty filter conditions.

A logical combining clause is a logical combining operator — $and, $or, or $nor — followed by a non-empty array of one or more non-empty filter conditions.^Foot 8

This logical combining clause uses operator $or. It is satisfied if either of its conditions is true (or if both are true). That is, it is satisfied if the document contains a field name whose value is "Joe", or if it contains a field salary whose value is 10000.

"$or" : [ {"name" : "Joe"}, {"salary" : 10000} ]

The following logical combining clause uses operator $and. Its array operand has two filter conditions as its members. The second of these is a condition with a logical combining clause that uses operator $or. This logical combining clause is satisfied if both of its conditions are true. That is, it is satisfied if the document contains a field age whose value is at least 60, and either it contains a field name whose value is "Jason" or it contains a field drinks whose value is "tea".

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

Omitting $and
Sometimes you can omit the use of $and.

5.2.3.1 Omitting $and

Sometimes you can omit the use of $and.

A filter condition is true if and only if all of its clauses are true. And a field-condition clause can contain multiple condition clauses, all of which must be true for the field-condition clause as whole to be true. In each of these, logical conjunction (AND) is implied. Because of this you can often omit the use of $and, for brevity.

This is illustrated by Example 5-1 and Example 5-2, which are equivalent in their effect. Operator $and is explicit in Example 5-1 and implicit (omitted) in Example 5-2.

The filter specifies objects for which the name starts with "Fred" and the salary is greater than 10,000 and less than or equal to 20,000 and either address.city is "Bedrock" or address.zip is 12345 and married is true.

A rule of thumb for $and omission is this: If you omit $and, make sure that no field or operator in the resulting filter appears multiple times at the same level in the same object.

This rule precludes using a QBE such as this, where field salary appears twice at the same level in the same object:

{ "salary" : { "$gt" : 10000 },
  "age"    : { "$gt" : 40 },
  "salary" : { "$lt" : 20000 } }

And it precludes using a QBE such as this, where the same condition operator, $regex, is applied more than once to field name in the same condition clause:

{ "name" : { "$regex" : "son", "$regex" : "Jas" } }

The behavior here is not that the field condition is true if and only if both of the $regex criteria are true. To be sure to get that effect, you would use a QBE such as this one:

{ $and : [ { "name" : { "$regex" : "son" }, { "name" : { "$regex" : "Jas" } ] }

If you do not follow the rule of thumb for $and omission then only one of the conflicting condition clauses that use the same field or operator is evaluated; the others are ignored, and no error is raised. For the salary example, only one of the salary field-condition clauses is evaluated; for the name example, only one of the $regex condition clauses is evaluated. Which one of the set of multiple condition clauses gets evaluated is undefined.

Example 5-1 Filter Specification with Explicit $and Operator

{ "$and" : [ { "name"    : { "$startsWith" : "Fred" } },
             { "salary"  : { "$gt"  : 10000, "$lte" : 20000 } },
             { "$or"     : [ { "address.city"    : "Bedrock" },
                             { "address.zip" : 12345 } ] },
             { "married" : true } ] }

Example 5-2 Filter Specification with Implicit $and Operator

{ "name"    : { "$startsWith" : "Fred" },
  "salary"  : { "$gt"  : 10000, "$lte" : 20000 },
  "$or"     : [ { "address.city"    : "Bedrock" },
                { "address.zip" : 12345 } ],
  "married" : true }

5.2.4 Nested-Condition Clause (Reference)

A nested-condition clause consists of a parent field followed by a single, non-empty filter condition. All fields contained in this nested condition are scoped to the parent field.

parent_field : filter-condition

Note:

Since the condition of a nested-condition clause follows a field, it cannot contain an ID clause or a special-criterion clause. Those clauses can occur only at root level.

For example, suppose that field address has child fields city and state. The following nested-condition clause tests whether field address.city has the value "Boston" and field address.state has the value "MA":

"address" : { "city" : "Boston", "state" : "MA" }

Similarly, this nested-condition clause tests whether the value of address.city starts with Bos and address.state has the value "MA":

"address" : { "city" : { "$startsWith : "Bos" }, "state" : "MA" }

Suppose that you have this document:

{ "address" : [ { "city" : "Boston", "state" : "MA" },
                { "city" : "Los Angeles", "state" : "CA" } ] }

The following query matches each path in the document independently. Each object element of an address array is matched independently to see if it has a city value of "Boston" or a state value of "CA".

{ "address.city" : "Boston", "address.state" : "CA" }

This query without a nested condition thus matches the preceding document, which has no single object with both city "Boston" and state "CA".

The following query, with a nested-condition clause for parent field address, does not match the preceding document, because that document has no single object in an address array with both a field city of value "Boston" and a field state of value "CA".

{ "address" : { "city" : "Boston", "state" : "CA" } }

Related Topics

Special-Criterion Clause (Reference)

5.2.5 ID Clause (Reference)

Other query-by-example (QBE) operators generally look for particular JSON fields within the content of documents and try to match their values. An ID clause, which uses operator $id, instead matches document keys. It thus matches document metadata, not document content.

A document key uniquely identifies a given document. It is metadata, like the creation time stamp, last-modified time stamp, and version. It pertains to the document as a whole and is not part of the document content.

The syntax of an ID clause is QBE operator $id followed by either a scalar key (document identifier) or a non-empty array of scalar keys.^Footref 8 The scalar key must be either an integer or a string. The array elements must be either all integers or all strings. For example:

"$id" : "USA"
"$id" : [1001,1002,1003]

Like a special-criterion clause, you can use operator $id only in the outermost condition of a QBE, that is, in a condition used in a composite filter or in a filter-condition filter. More precisely, if a QBE also uses other operators, in addition to $id, then the outermost condition must have operator $and, and the sole occurrence of a $id condition must be an element of the array argument to that $and occurrence.

Example 5-3 illustrates this. It finds documents that have at least one of the keys key1 and key2 and that have a color field with value "red".

Example 5-3 Use of Operator $id in the Outermost QBE Condition

{ "$and" : [ { $id : [ "key1", "key2" ] }, { "color" : "red" } ] }

Related Topics

Special-Criterion Clause (Reference)

5.2.6 Special-Criterion Clause (Reference)

A special criterion clause is a contains clause (operator $contains).

Like an ID clause, you can use a special-criterion clause only in the outermost condition of a QBE, that is, in a condition used in a composite filter or in a filter-condition filter. More precisely, if a QBE also uses other operators, in addition to the operators for a special-criterion clause, then the outermost condition must have operator $and, and the special-criterion clauses must be elements of the array argument to that $and occurrence.

Contains Clause (Reference)
A contains clause is a field followed by an object with one $contains operator, whose value is a string. It matches a document only if a string or number in the field value matches the string operand somewhere, including in array elements. Matching is Oracle Text full-text.

Related Topics

ID Clause (Reference)

5.2.6.1 Contains Clause (Reference)

A contains clause is a field followed by an object with one $contains operator, whose value is a string. It matches a document only if a string or number in the field value matches the string operand somewhere, including in array elements. Matching is Oracle Text full-text.

For example, $contains operand "beth" matches the string "Beth Smith", but not the string "Elizabeth Smith". Operand "10" matches the number 10 or the string "10 Main Street", but not the number 110 or the string "102 Main Street".

Oracle Text technology underlies SODA QBE operator $contains. This means, for instance, that you can query for text that is near some other text, or query use fuzzy pattern-matching.

For details about the behavior of a SODA QBE contains clause see the Oracle Database documentation for SQL condition json_textcontains.

To be able to use operator $contains you first must create a JSON search index; otherwise, a QBE with $contains raises a SQL error.

You can use a contains clause only in the outermost condition of a QBE. You can have multiple contains clauses at the top level, provided their fields are different (objects in QBEs must not have duplicate fields). For example, this QBE checks for a "name" field that contains the word "beth" and an "address" field that contains the number 10 or the string "10" as a word:

{ "name"    : { "$contains" : "beth" }
  "address" : { "$contains" : "10" } }

To have the effect of multiple contains clauses for the same field (search the same field for multiple word or number patterns), the outermost condition must have operator $and, and the contains clauses must occur in object elements of the array argument to that $and occurrence.

For example, this QBE checks for an "address" field that contains both the word "street" and either the number 10 or the word "10":

{"$and" : [ { "address" : { "$contains" : "street" },
            { "address" : { "$contains" : "10" } } } ] }

Related Topics

See Also:

Oracle Database SQL Language Reference for reference information about SQL condition json_textcontains
Oracle Database JSON Developer’s Guide for information about full-text search of JSON documents using SQL condition json_textcontains

Footnote Legend

Footnote 1:

A syntax error is raised if the argument to a QBE operator is not of the required type (for example, if operator $gt is passed an argument that is not a string or a number).

Footnote 3:

Reminder: a JSON scalar is a value other than an object or an array; that is, it is a JSON number, string, true, false, or null.

Footnote 4: At most one not clause is allowed in the operand.
Footnote 8:

A syntax error is raised if the array does not contain at least one element.