Item-Method Clause (Reference)

The script content on this page is for navigation purposes only and does not alter the content in any way.

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

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

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

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 2 Item-Method Operators

Operator	Description²
`$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.
`$date`³	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"}}`.
`$timestamp`⁴	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"`.

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, filter {"color" : {"$upper" : "RED"}} matches data {"color" : ["Red", "Blue"]} because the array has an element that when converted to uppercase matches "RED". The filter 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.

Related Topics

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. ↩
The scalar-equality abbreviation {field : {operator : value}} is used everywhere in examples here, in place of the equivalent {field : {operator : {"$eq" : value}}}. ↩
The operand of operator $date must be a JSON string that has a supported ISO 8601 format. Otherwise, no match is found. ↩
The operand of operator $timestamp must be a JSON string that has a supported ISO 8601 format. Otherwise, no match is found. ↩