Overview of SODA Filter Specifications (QBEs)

2 Overview of SODA Filter Specifications (QBEs)

A filter specification, also called a query-by-example (QBE) or simply a filter, is a pattern expressed in JSON. It is used to select, from a collection, the JSON documents whose content satisfies it, meaning that it evaluates to true for the content of (only) those documents.

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

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

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

2.1 Sample JSON Documents

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

Example 2-1 Sample JSON Document 1

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

Example 2-2 Sample JSON Document 2

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

Example 2-3 Sample JSON Document 3

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

Related Topics

Field-Condition Clause (Reference)

2.2 Overview of Paths in SODA QBEs

A filter specification, or query-by-example (QBE), contains zero or more paths to JSON document fields. (In the context of a QBE, "path to a field" is often shortened informally to "field".) A path to a field can have multiple steps, and it can cross the boundaries of objects and arrays.

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 particular elements of an array in a JSON document, by enclosing the array position 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 particular 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 — * acts as a wildcard. For example, if the value of field drinks is an array then the following QBE matches if the value of any array element is the string "tea":

{"drinks" : "tea"}

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

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

Related Topics

2.3 Overview of QBE Comparison Operators

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

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

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

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

{ "name" : "Jason" }

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

The comparison operators are the following:

$all — whether an array field value contains all of a set of values
$between — whether a field value is between two string or number values (inclusive)
$eq — whether a field is equal to a given scalar
$exists — whether a given field exists
$gt — whether a field is greater than a given scalar value
$gte — whether a field is greater than or equal to a given scalar
$hasSubstring — whether a string field has a given substring (same as $instr)
$in — whether a field is a member of a given set of scalar values
$instr — whether a field has a given substring (same as $hasSubstring)
$like — whether a field matches a given SQL LIKE pattern
$lt — whether a field is less than a given scalar value
$lte — whether a field is less than or equal to a given scalar value
$ne — whether a field is different from a given scalar value
$nin — whether a field is not a member of a given set of scalar values
$regex — whether a string field matches a given regular expression
$startsWith — whether a string field 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 ($gt) 45 and less than ($lt) 55.

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

Note:

Though both the operand of a SODA operator and the data matched in your documents by a QBE are JSON data, 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.

Related Topics

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 the comparison clauses is false, the application of $not evaluates to true; when all of them are 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}}}

Related Topics

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 name field 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. Operator $startsWith matches string prefixes only 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 a field name, but only after converting its value 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 deadline field 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 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.

Related Topics

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

Related Topics

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.

Related Topics

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

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

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

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

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

Related Topics

ID Clause (Reference)

2.9 Overview of QBE Operator $orderby

Query-by-example (QBE) operator $orderby is described.

It sorts query results in ascending or descending order.

The following QBE specifies the order of fields age and salary. A value of 1 specifies ascending order for age. A value of -2 specifies descending order for salary. Sorting is done first by age and then by salary, because the absolute value of 1 is less than the absolute value of -2.

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

When you use operator $orderby in a filter specification together with one or more filter conditions, you must wrap those conditions with operator $query. In the preceding query, the returned documents are restricted to those that satisfy a filter condition that specifies that field age must have a value greater than 40.

Related Topics

Orderby Clause Sorts Selected Objects