Overview of SODA Filter Specifications

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.

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

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

Note: In general, a filter is for querying JSON documents. Filter 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.

Filter 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 filter operator occurs in a filter 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 filter, the object that is the value of field age has as its only field the operator $gt, and the associated operand is the numeric value 45:

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

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

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

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

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

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

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

This uses filter operator $contains.

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

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

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

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

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

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

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

See Also:

Introducing JSON for information about JSON
GeoJSON.org for information about GeoJSON geographic JSON data

Sample JSON Documents

A few sample JSON documents are presented here. They are referenced in some query-by-example (filter) examples, as well as in some reference descriptions.
Overview of Paths in SODA Filters

A filter specification 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.
Overview of Filter Comparison Operators

A comparison operator tests whether a given JSON object field satisfies some conditions.
Overview of Filter Operator $not Operator $not negates the behavior of its operand, which is a JSON object containing one or more comparison clauses, which are implicitly ANDed.
Overview of Filter Item-Method Operators

An 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 filter operators that would otherwise act on the field value then act on the transformed field value instead.
Overview of Filter Logical Combining Operators

You use logical combining operators, $and, $or, and $nor, to combine conditions to form more complex filter. Each accepts an array of conditions as its argument.
Overview of Nested Conditions in filters

You can use a filter with a nested condition to match a document that has a field with an array value with object elements, where a given object in the array satisfies multiple conditions.
Overview of Filter Operator $id Other filter 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 filter.
Overview of Filter Operator $orderby

Qperator $orderby is described. It sorts query results in ascending or descending order.
Overview of Filter Spatial Operators

You can use operator $near, $intersects, or $within to select documents that have a field whose value is a GeoJSON geometry object that is neara specified position, intersectsa specified geometric object, or is withinanother specified geometric object, respectively.
Overview of Filter Operator $contains Operator $contains performs full-text search of JSON documents in a SODA collection.
Overview of Filter Operator $textContains

Filter operator $textContains performs full-text search of documents in a heterogeneous SODA collection, that is, a collection that has the media type column.

Related Topics