Overview of Filter Item-Method Operators

A query-by-example filter 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.

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 filter 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 filter 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 filter 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 filter. (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.)

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-language data type of the field value.

So for example, this filter 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 filter {"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:

Related Topics