1. Introduction to Simple Oracle Document Access (SODA)
  2. 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 (a) on its own, as a filter-condition filter (a QBE), or (b) at a lower level, in the query clause of a composite filter.

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.

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

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 is an object that has at most one member of each of these clauses:Foot 2

  • Query clause

    It has the form $query : filter_condition.

  • Orderby clause

    It has the form $orderby : orderby_spec, where is an array or an object specifying the sort order for the selected documents.

The order of the clauses is not significant. Absence of a clause has the same effect as applying its operator to an operand that is an empty object: Absence of a query clause selects all documents; absence of an orderby clause imposes no order.

The following composite filter contains a query clause and an orderby clause. The query clause selects documents that have a salary field whose value is greater than 10,000. The orderby clause sorts the selected documents first by descending age and then by ascending zip code. 

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

Related Topics

Parent topic: SODA Filter Specifications (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.

There are two ways of controlling the ordering behavior, with different orderby-clause syntaxes:

  • An array syntax lets you specify the SQL data types used and provides simple control over the field order. Sorting is by the first field specified, then by the second, and so on.

    There are two variants of this syntax, depending on whether you need to change the default behavior for handling of errors or empty fields.

  • An abbreviated syntax does not let you specify the SQL data types used. In its most abbreviated form it also does not provide control over the order of the fields used for sorting.

Orderby Clause Array Syntax

The simplest orderby array syntax is operator $orderby followed by an array of objects, each of which has a path field, which targets a particular field from the root of the candidate object, followed by at most one of each of these fields:

  • datatype, which specifies the SQL data type to use — one of: "varchar2" (default), "number", "date", "datetime", "timestamp", "string" or "varchar". (Value datetime is a synonym for timestamp. Values "string" and "varchar" are synonyms for "varchar2".)

    These values correspond to SQL data types VARCHAR2, NUMBER, DATE, and TIMESTAMP, respectively.

    Note:

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

  • order, which specifies whether the field values are to be in ascending ("asc") or descending ("desc") order (default: "asc")

  • maxLength, which is a positive integer that specifies the maximum length, in characters, of a targeted string value. If a string exceeds this limit then raise an error. The use of $lax (see below) inhibits raising the error and ignores the overlong string for sorting purposes. Field maxLength applies only when datatype is "varchar2".

For example, this filter specification selects objects in which field salary has a value greater than 10,000 and less than or equal to 20,000. It sorts the objects first by descending age, interpreted as a number, then by ascending name, interpreted as a string. 

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

The following SQL SELECT statement fragment is analogous: 

WHERE (salary > 10000) AND (salary <= 20000) ORDER BY age DESC, name ASC

This syntax serves most purposes. No error is raised just because the targeted field is absent, and any other error encountered is raised.Foot 3

If you need to specify special handling of missing fields or errors then you need to use the more elaborate array syntax. This wraps the array in a $fields object, which lets you add another field, $scalarRequired or $lax, to the $orderby object. You cannot specify a true value for both $lax and $scalarRequired, or else a syntax error is raised at query time.

  • $scalarRequired — Boolean. Optional. When set to true the targeted field must be present, and its value must be a JSON scalar that is convertible to data type datatype. Raise an error at query time if, for any matched document, that is not the case.Foot 4

  • $lax — Boolean. Optional. When set to true the targeted field need not be present or have a value that is a JSON scalar convertible to data type datatype. Do not raise an error at query time if, for any matched document, that is the case.Foot 5

If neither scalarRequired nor lax is specified as true then the default error-handling behavior applies (no error is raised just because the targeted field is absent, and any other error encountered is raised).

For example, this filter specification has the same behavior as the preceding one, except that it raises an error if any of the targeted fields is missing.

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

Note:

If you use Oracle Database Release 12c (12.1.0.2) then you must specify either $scalarRequired or $lax; otherwise a syntax error is raised.

Note:

If you have defined a B-tree index for any of the fields targeted by a QBE that has an orderby clause then that index must be specified with a true value of indexNulls for it to be picked up for that query.

See Also:

Orderby Clause Abbreviated Syntax

The abbreviated $orderby syntax specifies the fields to use for sorting, along with their individual directions and the order of sorting among the fields. It does not specify the SQL data types to use when interpreting field values for sorting, and it does not let you limit string sorting to the first N characters.

The orderby abbreviated syntax 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 query acts like the one in Orderby Clause Array Syntax, except that interpretation of data types is not specified here, and (assuming that field name has string values) all characters in the name are used for sorting here. Note that the order of the object members is irrelevant here. In particular, it does not specify which field is sorted first — that is determined by the value magnitudes. 

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

The following SQL SELECT statement fragment is analogous: 

WHERE (salary >= 10000) AND (salary <= 20000) ORDER BY age DESC, name ASC

Related Topics

Parent topic: Composite Filters (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

  • text-contains 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)
    You use a QBE nested-condition clause to apply multiple conditions at the same time, to array elements that are objects.
  • 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.
  • Text-Contains Clause (Reference)
    A text-contains clause is operator $textContains with a string value that is used as a full-text search pattern: $textcontains:pattern. It matches a non-JSON document only if some text in the document matches that pattern. Matching is Oracle Text full-text matching.
  • Special-Criterion Clause (Reference)
    A special criterion clause is a spatial clause (with operator $near, $intersects, or $within), or a contains clause (with operator $contains).

Parent topic: SODA Filter Specifications (Reference)

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.

Parent topic: Filter Conditions (Reference)

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, Time, and Duration Support
    International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates, times, and durations. Oracle Database supports the most common ISO 8601 formats as proper Oracle SQL date, time, and interval (duration) values. The formats that are supported are essentially those that are numeric-only, language-neutral, and unambiguous.

Related Topics

Parent topic: Filter Conditions (Reference)

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 6

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

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

Example

{"drinks" : { "$all" : ["soda", "tea"]}}

matches sample document 2.

{"drinks": { "$all" : ["tea"]}}

matches sample documents 1 and 2.

Footnote 6

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

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 7

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 8

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 DescriptionFoot 9

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

$dateFoot 10

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

$timestampFoot 11

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-language 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 9 The scalar-equality abbreviation {field : {operator : value}} is used everywhere in examples here, in place of the equivalent {field : {operator : {"$eq" : value}}}.

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

Footnote 11 The 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.

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.

5.2.2.4 ISO 8601 Date, Time, and Duration Support

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

(Simple Oracle Document Access (SODA) does not support durations.)

Oracle Database Syntax for ISO Dates and Times

This is the syntax that Oracle Database supports for ISO 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)

Oracle Database Syntax for ISO Durations

Note:

Oracle Database supports ISO durations, but Simple Oracle Document Access (SODA) does not support them.

There are two supported Oracle Database syntaxes for ISO durations, the ds_iso_format specified for SQL function to_dsinterval and the ym_iso_format specified for SQL function to_yminterval. (to_dsinterval returns an instance of SQL type INTERVAL DAY TO SECOND, and to_yminterval returns an instance of type INTERVAL YEAR TO MONTH.)

These formats are used for data types daysecondInterval and yearmonthInterval, respectively, which Oracle has added to the JSON language.

  • ds_iso_format:

    PdDThHmMsS, where d, h, m, and s are digit sequences for the number of days, hours, minutes, and seconds, respectively. For example: "P0DT06H23M34S".

    s can also be an integer-part digit sequence followed by a decimal point and a fractional-part digit sequence. For example: P1DT6H23M3.141593S.

    Any sequence whose value would be zero is omitted, along with its designator. For example: "PT3M3.141593S". However, if all sequences would have zero values then the syntax is "P0D".

  • ym_iso_format

    PyYmM, where y is a digit sequence for the number of years and m is a digit sequence for the number of months. For example: "P7Y8M".

    If the number of years or months is zero then it and its designator are omitted. Examples: "P7Y", "P8M". However, if there are zero years and zero months then the syntax is "P0Y".

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 12

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"}]} ]

Parent topic: Filter Conditions (Reference)

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)

You use a QBE nested-condition clause to apply multiple conditions at the same time, to array elements that are objects.

A nested-condition clause consists of a parent path, followed by a colon (:), and a single, non-empty nested filter condition. 

parent : filter-condition

The path targets a parent object whose value is a child object that satisfies the nested condition. If the parent path ends with [*], then it targets a parent object whose value is either such a child object or an array with such an object as at least one of its elements. The latter case is typical: you end the parent path with [*].

All fields contained in the nested condition are scoped to the parent object. They act as multiple conditions on each of the array objects (or the single child object, if the parent's value is not an array).

Note:

Because 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 the root level.

For example, suppose that field address has an array value with object elements that have fields city and state. The following nested-condition clause tests whether array address has at least one object with both a field address.city that has the value "Boston" and a field address.state that 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" }

Now suppose that you have this document:

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

Both of the above nested-condition clauses match that document.

They also match the following document, whose address field value is an object, not an array of objects. The [*] in a nested-condition clause is needed to handle the array case, but it also handles the single-object case. 

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

If you mistakenly omit the [*], then each object element of the array is matched independently against each of the multiple conditions specified.

For example, the following two queries are equivalent. The first one has the form of a nested-condition clause but without the [*]. These queries match each address in a document independently. Each object element of an address array is matched to see if it has a city value of "Boston" or it has a state value of "CA" — it can, but it need not, have both. Each of these queries thus matches the document shown above, which has no single object with both city "Boston" and state "CA". 

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

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

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

Related Topics

Parent topic: Filter Conditions (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.

Operator $id is not supported by SODA for Java or SODA for REST for use with a heterogeneous collection, that is, a collection that has the media type column..

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.Foot 12 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 or a text-contains 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.

An ID clause can be combined with other clauses in a $and clause. Only a single ID clause can be combined with other clauses in the same QBE.

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

Each of these equivalent QBEs finds documents that have at least one of the keys key1 and key2, and that have a color field with value "red". (Operator $and is implicit in the first QBE.) 

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

Related Topics

Parent topic: Filter Conditions (Reference)

5.2.6 Text-Contains Clause (Reference)

A text-contains clause is operator $textContains with a string value that is used as a full-text search pattern: $textcontains:pattern. It matches a non-JSON document only if some text in the document matches that pattern. Matching is Oracle Text full-text matching.

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

Oracle Text technology underlies SODA operator $textContains. This means, for instance, that you can query for text that is near some other text, or query use fuzzy pattern-matching. Operator $textContains acts like SQL function contains, and the syntax of the search-pattern is the same.

To use full-text search with $textContains you must first create an Oracle Text search index for the heterogeneous collection to be searched. (See Overview of QBE Operator $textContains for an example of how to do this.)

You can use a text-contains clause only in the outermost condition of a QBE, either in the sole top-level filter condition or in the sole condition in a query clause ($query). In either case, you can only use one text-contains clause in the condition.

Here is a typical filter condition containing a text-contains clause:

{ "$textContains" : "beth" }

You can use $textContains in conjunction with $id to search only particular documents. For example, these (equivalent) examples search the documents that have keys 1001, 1002, and 1003. 


{ "$textContains" : "beth", "$id" : [1001,1002,1003] }
{ "$and" : [ { "$textContains" : "beth" }, { "$id" : [1001,1002,1003] } ] }

You cannot use a text-contains clause in conjunction with a clause other than ID. For example, this is not allowed: 

  • { "$textcontains" : "beth", "age" : 42 }
  • { "$and" [ { "$textcontains" : "beth" },
           { "age" : 42 } ] }

Related Topics

See Also:

  • Oracle Text Reference for complete information about Oracle Text operator contains, including its search-pattern syntax

  • Oracle Text Reference for information about the use of special characters in SQL function contains search patterns

  • Oracle Text Reference for information about the words and characters that are reserved with respect to Oracle Text search, and Oracle Text Reference for information about how to escape them.

Parent topic: Filter Conditions (Reference)

5.2.7 Special-Criterion Clause (Reference)

A special criterion clause is a spatial clause (with operator $near, $intersects, or $within), or a contains clause (with operator $contains).

Like an ID clause or a text-contains 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 its outermost condition must have operator $and, and the special-criterion clauses must be members of elements in the array argument to that $and occurrence.

  • Spatial Clause (Reference)
    GeoJSON objects are JSON objects that represent geographic data. You can use a SODA QBE spatial clause to match GeoJSON geometry objects in your documents.
  • 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 JSON 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

Parent topic: Filter Conditions (Reference)

5.2.7.1 Spatial Clause (Reference)

GeoJSON objects are JSON objects that represent geographic data. You can use a SODA QBE spatial clause to match GeoJSON geometry objects in your documents.

Note:

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

A spatial QBE clause is a field followed by an object with a spatial operator: $near, $intersects, or $within. It matches the field only if it contains GeoJSON geographic data that is near a specified position, intersects a specified geometric object, or is within a specified geometric object, respectively.

Each of the spatial QBE operators is followed by a JSON object whose fields must include $geometry. Operator $near must also include field $distance, and it can include $unit. A compile-time error is raised if $geometry is missing or if $distance or $unit is present with operator $intersects or $within.

The value of field $geometry is interpreted as a GeoJSON geometry object (other than a geometry collection), such as a point or a polygon. Each such object has a type field, with the geometry type, such as "Point" or "Polygon" as value, and a coordinates field, which defines the shape and location of the object, respectively.

(For a single position, such as an object of type "Point", field coordinates is an array of numbers, the first three of which generally represent longitude, latitude, and altitude, in that order.)

The value of field $distance must be a positive number, the distance from the field preceding spatial operator $near to the geometry object specified by $geometry. For non-point geometry objects, such as lines and polygons, the distance is the minimum distance between them. The distance between two adjacent polygons is zero.

The value of field $unit is a string such as "mile" that specifies the GeoJSON unit to use for the $distance value. The available units are defined in database table SDO_UNITS_OF_MEASURE. The default unit is "mile".

Example 5-4 QBE With a Spatial Clause

This example matches a location field whose value is GeoJSON geometry object of type Point, and which is within 60 miles of the coordinates [-122.417, 37.783] (San Francisco, California). It would match data with a "location" value of [-122.236, 37.483] (Redwood City, California). (Note that the first element of array "coordinates" is the longitude, and the second is the latitude.) 

{"location" : { "$near" : { "$geometry" : { "type" : "Point",
                                            "coordinates" :
                                              [-122.417, 37.783] },
                            "$distance" : 60,
                            "$unit"     : "mile" } } }

The default error-handling behavior for a QBE spatial clause is that the targeted field need not be present, but if it is present then its value must be a single GeoJSON geometry object. An error is raised at query time if, for any matching document, that is not the case.Foot 13

A spatial clause can specify an alternative error-handling behavior from the default by including one of the following Boolean fields with a true value in the object that a spatial operator ($near, $within, $intersects) applies to. (Only one of these error-handling fields can be specified as true; otherwise, a syntax error is raised at query time.)

  • $scalarRequired — Boolean. Optional. The targeted field must be present and have a GeoJSON geometry object as its value. Raise an error at query time if, for any matched document, that is not the case.Foot 14

  • $lax — Boolean. Optional. The targeted field need not be present or have a GeoJSON geometry object as its value. Do not raise an error at query time if, for any matched document, that is the case.Foot 15

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

Related Topics

See Also:

Parent topic: Special-Criterion Clause (Reference)

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

The string operand is matched as a full word or number against strings and numbers in the field value, including in array elements.

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

Note:

To use operator $contains you need Oracle Database Release 12c (12.2.0.1) or later.

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" (case-insensitively) 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:

Parent topic: Special-Criterion Clause (Reference)


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 2: SODA for REST provides additional clauses for use in a composite filter.
Footnote 3: The default error-handling behavior corresponds to the SQL/JSON semantics ERROR ON ERROR NULL ON EMPTY.
Footnote 4: A true value of $scalarRequired corresponds to the use of SQL clause ERROR ON ERROR for a json_value expression.
Footnote 5: A true value of $lax corresponds to the use of SQL clause NULL ON ERROR for a functional index created on a json_value expression.
Footnote 7:

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 8: At most one not clause is allowed in the operand.
Footnote 12:

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


Footnote 13: The default error-handling behavior corresponds to to the use of SQL clauses ERROR ON ERROR and NULL ON EMPTY for a json_value expression.
Footnote 14: A true value of $scalarRequired corresponds to the use of SQL clause ERROR ON ERROR for a json_value expression.
Footnote 15: A true value of $lax corresponds to the use of SQL clause NULL ON ERROR for a functional index created on a json_value expression.