2 Overview of SODA Filter Specifications (QBEs)

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

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

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

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

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

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

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

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

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:

Introducing JSON for information about JSON

2.1 Sample JSON Documents

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

Example 2-1 Sample JSON Document 1

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

Example 2-2 Sample JSON Document 2

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

Example 2-3 Sample JSON Document 3

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

2.2 Overview of Paths in SODA QBEs

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

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

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

{ "address.zip" : 94088 }

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

Note:

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

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

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

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

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

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

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

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

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

{"drinks" : "tea"}

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

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

See Also:

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

2.3 Overview of QBE Comparison Operators

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

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

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

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

{ "name" : "Jason" }

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

The comparison operators are the following:

  • $all — whether an array field value contains all of a set of values

  • $between — whether a field value is between two string or number values (inclusive)

  • $eq — whether a field value is equal to a given scalar

  • $exists — whether a given field exists

  • $gt — whether a field value is greater than a given scalar value

  • $gte — whether a field value is greater than or equal to a given scalar

  • $hasSubstring — whether a string field value has a given substring (same as $instr)

  • $in — whether a field value is a member of a given set of scalar values

  • $instr — whether a string field value has a given substring (same as $hasSubstring)

  • $like — whether a field value matches a given SQL LIKE pattern

  • $lt — whether a field value is less than a given scalar value

  • $lte — whether a field value is less than or equal to a given scalar value

  • $ne — whether a field valueis different from a given scalar value

  • $nin — whether a field value is not a member of a given set of scalar values

  • $regex — whether a string field value matches a given regular expression

  • $startsWith — whether a string field value starts with a given substring

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

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

Note:

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

2.4 Overview of QBE Operator $not

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

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

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

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

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

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

2.5 Overview of QBE Item-Method Operators

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

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

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

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

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

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

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

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

  • { "deadline" : "2019-01-28T14:59:43Z" }

  • { "deadline" : "2019-01-30T22:00:00Z" }

  • { "deadline" : "2019-01-30T19:00:00–03:00" }

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

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

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

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

Note:

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

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

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

Note:

  • To use item method operator $abs, $date, $size, $timestamp, or $type you need Oracle Database Release 18c or later.

  • To use any other item method you need Oracle Database Release 12c (12.2.0.1) or later.

2.6 Overview of QBE Logical Combining Operators

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

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

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

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

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

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

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

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

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

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

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

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

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

{ "$and" : [ { "age" : {"$gte" : 60} },
             { "$or" : [ {"name" :  "Jason"},
                         {"drinks" : {"$in" : ["tea", "soda"]}} ] } ] }
  • The condition with the comparison for field age matches sample document 3.

  • The condition with logical combining operator $or matches sample documents 1 and 3.

  • The overall condition matches only sample document 3, because that is the only document that satisfies both the condition on age and the condition that uses $or.

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

{ "$or" : [ { "$and" : [ {"name" : "Jason"},
                         {"drinks" : {"$in" : ["tea", "soda"]}} ] },
            { "$nor" : [ {"age" : {"$lt" : 65}},
                         {"name" : "Jason"} ] } ] }
  • The condition with operator $and matches sample document 1.

  • The condition with operator $nor matches sample document 3.

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

2.7 Overview of Nested Conditions in QBEs

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

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

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

It specifies that there must be a parent field address, and if the value of that field is an array then at least one object in the array must have a city field with value "Mono Vista" and a state field with value "CA". Of the three sample JSON documents, this QBE matches only Example 2-1.

The following QBE also matches sample document 1, but it matches Example 2-2 as well:

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

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

2.8 Overview of QBE Operator $id

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

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

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

// Find the unique document that has key "key1".
{"$id" : "key1"}

// Find the documents that have any of the keys "key1", "key2", and "key3".
{"$id" : ["key1","key2","key3"]}

// Find the documents that have at least one of the keys "key1" and "key2",
// and that have an object with a field address.zip whose value is at least 94000.
{"$and" : [{$id : ["key1", "key2"]},
           {"address.zip" : { "$gte" : 94000 }}]}

Related Topics

2.9 Overview of QBE Operator $orderby

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

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

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

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

Using the Orderby Clause Array Syntax

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

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

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

Note:

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

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

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

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

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

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

Using the Orderby Clause Abbreviated Syntax

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

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

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

2.10 Overview of QBE Spatial Operators

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

Note:

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

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

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

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

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

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

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

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

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

  • A true value for field $lax means ignore not only a missing location field but also a location field whose value is not a GeoJSON geometry object.

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

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

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

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

Note:

If you have created a SODA spatial index, for a field whose value is a GeoJSON geometry object, and you use a QBE that targets that field, 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