17.3 SQL/JSON Path Expression Item Methods

The Oracle item methods available for a SQL/JSON path expression are presented. How they act on targeted JSON data is described in general terms and for each item method.

General Behavior of Item Methods

An item method is applied to the JSON data that is targeted by (the rest of) the path expression that is terminated by that method. The method is used to transform that data.

The targeted data acts as the first, and typically the only, argument to the method; it is implicit. Some item methods require or accept one or more explicit, comma-separated arguments, within the parentheses (()) that follow the method name.

For example: $.myArray.indexOf("car", 3, 20). That application of method indexOf to four arguments targets an array in the data, myArray, looking for the first occurrence of the value "car" as an element, but skipping the first 3 elements, and not looking at more than 20 elements (so not checking past position 23). The first explicit argument ("car") is required; the other two are optional.

The SQL function or condition that is passed the path expression uses the transformed data in place of the targeted data. In some cases the application of an item method limits what data can match a path expression. Such match-limiting can either (1) raise an error (for json_value semantics) or (2) act as a filter (when used with json_exists), removing nonmatching targeted data from the result set.

If an item-method conversion fails for any reason, such as the targeted data being of the wrong type, then the path cannot be matched (it refers to no data), and error-handling applies for the SQL function or condition to which the path expression is passed. For json_value semantics, the default error-handling behavior is to return SQL NULL on error. For json_exists semantics, the default behavior is to return FALSE, which means that the nonmatch just serves as a filter.

An item method always transforms the targeted JSON data to (possibly other) JSON data, which is always scalar. But a query using a path expression (with or without an item method) can return data as a SQL scalar data type.

That's the case for a query using json_value semantics, whether explicitly with json_value or implicitly with either dot-notation syntax or a json_table column specification that returns a scalar SQL value. Item methods behave the same in these contexts.

  • The return value of json_query or a json_table column expression with json_query semantics is always JSON data, of SQL data type JSON, VARCHAR2, CLOB, or BLOB. The default return data type is JSON if the targeted data is also of JSON type. Otherwise, it is VARCHAR2.

  • A dot-notation query with an item method implicitly applies json_value with a RETURNING clause that specifies a scalar SQL type to the JSON data that is targeted and possibly transformed by the item method. Thus, a dot-notation query with an item method always returns a SQL scalar value.

  • The return value of a query that has json_value semantics (whether from json_query, a json_table column expression, or dot notation) is always of a scalar SQL data type other than JSON;Foot 1 it does not return JSON data. Though the path expression targets JSON data, and an item method always transforms targeted JSON data to JSON data, json_value query semantics convert the transformed JSON data to a scalar SQL value in a data type that does not necessarily support JSON data.

Note:

Item methods can also be used with SQL/JSON condition json_exists, which checks for the existence of a particular value within JSON data. In this context, an item method always appears at the end of a SQL/JSON path expression used in a filter-condition comparison. The transformed JSON value that results from using the item method isn't returned as a SQL value.

Application of an Item Method to an Array

With the exception of item methods count(), size(), size2(), type(), and vector(), if an array is targeted by an item method then the method is applied to each of the array elements, not to the array itself. The results of these applications are returned in place of the array, as multiple values. That is, the resulting set of matches includes the converted array elements, not the targeted array.

(This is similar, in its effect, to the implied unwrapping of an array when a nonarray is expected for an object step.)

For example, $.a.method() applies item-method method() to each element of array a, to convert that element and use it in place of the array.

  • For a json_value query that specifies a SQL collection type (varray or nested table) as the return type, an instance of that collection type is returned, corresponding to the JSON array that results from applying the item method to each of the array elements, unless there is a type mismatch with respect to the collection type definition.

  • For a json_value query that returns any other SQL type, SQL NULL is returned. This is because mapping the item method over the array elements results in multiple return values, and that represents a mismatch for json_value.

  • For json_query or a json_table column expression with json_query semantics, you can use a wrapper clause to capture all of the converted array-element values as an array. For example, this query: 

    SELECT json_query('[ "alpha", 42, "10.4" ]', '$[*].string()'
                  WITH ARRAY WRAPPER);

    returns this JSON array: [ "alpha", "42", "10.4" ]. The SQL data type returned is the same as the JSON data that was targeted: JSON, VARCHAR2(4000), CLOB, or BLOB.

Item methods count(), size(), size2(), type(), and vector() are exceptional in this regard. When applied to an array they treat it as such, instead of acting on its elements. For example: 

SELECT json_value('[ 19, "Oracle", {"a":1}, [1,2,3] ]', '$.type()');

returns the single VARCHAR2 value 'array' — json_value returns VARCHAR2(4000) by default.

A similar query, but with json_query instead of json_value, returns the single JSON string "array", of whatever SQL data type is used for the input JSON data: JSON, VARCHAR2(4000), CLOB, or BLOB. But with json_query you need to use keywords WITH ARRAY WRAPPER when you use item method type(). 

SELECT json_query('[ 19, "Oracle", {"a":1}, [1,2,3] ]', '$.type()'
                  WITH ARRAY WRAPPER);
[1,2,3]]'),'$.TYPE()'WITHARRAYWRAPPER)
--------------------------------------
["array"]

Otherwise, an error is raised:

SELECT json_query('[ 19, "Oracle", {"a":1}, [1,2,3] ]', '$.type()');
ERROR at line 2:
ORA-40480: JSON array wrapper needed for result of JSON query '$.type()'

The same thing that happens for json_value (with a SQL return type other than an object or collection type) happens for a simple dot notation query. For example: 


CREATE TABLE tab (data JSON);
INSERT INTO tab VALUES ('{a : [ 1, 2, 3.5 ]}');
SELECT t.data.a[*].sum() from tab t;
T.DATA.A[*].SUM()
-----------------
              6.5

Note:

The presence of an item method in dot notation syntax always results in json_value, not json_query, semantics. This must produce a single scalar SQL value (which can be used with SQL ORDER BY, GROUP BY, and comparisons or join operations). But an item method applied to an array value results in multiple values, which json_value semantics rejects — SQL NULL is returned.

Data-Type Conversion Item Methods

The following item methods are data-type conversion methods: binary(), binaryOnly(), boolean(), booleanOnly(), date(), dateTimeOnly(), dateWithTime(), double(), dsInterval(), float(), idOnly(), number(), numberOnly(), string(), stringify(), stringOnly(), timestamp(), toBoolean(), toDateTime(), vector()Foot 2, and ymInterval().

As mentioned, an item method always transforms its targeted JSON data to (possibly other) JSON data. But when the method is used in a json_value query, (or another function that returns SQL data), the JSON data resulting from its transformation is in turn converted to a SQL return value. If present, a RETURNING clause specifies the SQL type for that data; if absent, each item method results in a particular default SQL type. For example, the default SQL type for item-method string() is VARCHAR2(4000).

In a query that has json_value semantics, a value targeted by a data-type conversion item method can generally be thought of as being interpreted as a SQL value of that method's default SQL data type, meaning that the value is handled as if it were controlled by a RETURNING clause with that SQL data type.

For example, item-method string() interprets its target as would json_value with clause RETURNING VARCHAR2(4000). A Boolean JSON value is thus treated by string() as "true" or "false"; a null value is treated by string() as "null"; and a number is treated by string() as a numeral in a canonical string form.

Most data-type conversion methods can be used at the end of a SQL/JSON path expression, which means that a query with json_value semantics can return the corresponding SQL scalar value. The type-conversion methods that cannot be used at the end of a path have names that begin with "to".

The data-type conversion methods without "only" in their name allow conversion, when possible, of a JSON value — in some cases even a value that is not in the type family named by the method — to the method's JSON type, and they then interpret the result as a value of the method's default SQL type.

The "only" data-type conversion methods convert only JSON values that are in the type family named by the method. Other targeted values are not matched by the path expression. The "only" methods convert the value to the default JSON-language type for the method, and then interpret the result as a value of the method's default SQL type.

For numberOnly(), the family type is number (numeric JSON types), its default JSON type for the family is number, and the default SQL type is NUMBER. For dateTimeOnly(), the default family type is timestamp, and the default SQL type is TIMESTAMP.

(When an “only” method targets an array, the conversion applies to each array element, as usual.)

An aggregate method, such as avg(), converts targeted values to the method's default type, and then interprets them as the method's default SQL type. For avg(), targeted values of type number, float, and double are all converted to JSON type number, and are interpreted as SQL NUMBER values.

Nonaggregate methods, such as abs(), do no conversion within the relevant type family. So abs() converts the string "-3.14" to a JSON number, but it leaves a targeted JSON float or double value as it is, and interprets it as a SQL BINARY_FLOAT or BINARY_DOUBLE value, respectively.

Table 17-1 Item Method Data-Type Conversion

Item Method Input JSON-Language Type Output JSON-Language Type SQL Type Notes

binary()

binary (both identifier and nonidentifier)

binary

RAW or BLOB

None.

binary()

string

binary

RAW or BLOB

Error if any input characters are not hexadecimal numerals.

binaryOnly()

binary (both identifier and nonidentifier)

binary

RAW or BLOB

None.

boolean()

boolean

boolean

BOOLEAN

None.

boolean()

string

boolean

BOOLEAN

Error if input is not "true" or "false"

booleanOnly()

boolean

boolean

BOOLEAN

None.

date()

date, timestamp, or timestamp with time zone

date

DATE

JSON output is UTC with no time components.

date()

string

date

DATE

JSON output is UTC with no time components.

Error if input is not ISO UTC, with no time components.

dateTimeOnly()

date, timestamp, or timestamp with time zone

timestamp

TIMESTAMP

None.

dateWithTime()

date, timestamp, or timestamp with time zone

date

DATE

UTC, with no fractional seconds.

dateWithTime()

string

date

DATE

UTC, with no fractional seconds. Error if input is not ISO.

double()

number, double, or float

double

BINARY_DOUBLE

None.

double()

string

double

BINARY_DOUBLE

Error if input is not a number representation.

float()

number, double, or float

float

BINARY_FLOAT

Error if input is out of range.

float()

string

float

BINARY_FLOAT

Error if input is not a number representation.

idOnly()

binary identifier

binary identifier

RAW

None.

number()

number, double, or float

number

NUMBER

Error if input is out of range.

number()

string

number

NUMBER

Error if input is not a number representation.

numberOnly()

number, double, or float

number

NUMBER

None.

string()

Any.

string

VARCHAR2 or CLOB

Resulting SQL value is in the database character set, even though the output JSON-language string is UTF-8.

stringify()

Any.

string

CLOB

Same as string(), except for the SQL type.

Method stringify() can only be used with the simple dot-notation, not with a SQL/JSON path expression.

stringOnly()

string

string

VARCHAR2 or CLOB

Same as string().

timestamp()

date, timestamp, or timestamp with time zone

timestamp

TIMESTAMP

None.

timestamp()

string

timestamp

TIMESTAMP

Error if input is not ISO UTC.

toBoolean()Foot 3

boolean

boolean

BOOLEAN

None.

toBoolean()Foot 3

string

boolean

BOOLEAN

Error if input string is not "true" or "false"

toBoolean()Foot 3

number, double, or float

boolean

BOOLEAN

Zero is converted to false. All other numeric values are converted to true.

toDateTime()Foot 3

date, timestamp, or timestamp with time zone

timestamp

TIMESTAMP

None.

toDateTime()Foot 3

string

timestamp

TIMESTAMP

Error if input is not ISO UTC.

toDateTime()Foot 3

number, double, or float

timestamp

TIMESTAMP

Numbers are interpreted as the number of seconds since 1970-01-01. Only non-negative numbers are matched.
vector() Either an array of numbers or a JSON vector scalar value. If applied to any other JSON value then an error is raised. vector VECTOR

Method vector() can only be used with the simple dot-notation, not with a SQL/JSON path expression.

Footnote 3 This method can't be used at the end of a SQL/JSON path expression.

Item-Method Descriptions

  • abs(): The absolute value of the targeted JSON number. Corresponds to the use of SQL function ABS.

  • atan(): The trigonometric arctangent function of the targeted JSON number (in radians). Corresponds to the use of SQL function ATAN.

  • avg(): The average of all targeted JSON numbers. If any targeted value is not a number then an error is raised. Corresponds to the use of SQL function AVG (without any optional behavior). This is an aggregate method.

  • binary(): A SQL RAW interpretation of the targeted JSON value, which can be a hexadecimal string or a JSON binary value. If a string, SQL function hextoraw is used for conversion to a SQL RAW value. This item method is applicable only to JSON data stored as JSON type.

  • binaryOnly(): A SQL RAW interpretation of the targeted JSON value, but only if it is a JSON binary value. It allows matches only for JSON binary values. (Only JSON data stored as JSON type can have JSON binary values.)

  • boolean(): A SQL BOOLEAN interpretation of the targeted JSON value.

    Note:

    Prior to Release 23ai, this used a SQL VARCHAR2(20) interpretation. If you need to obtain a VARCHAR2 value (for compatibility reasons, for example) then you can wrap the value with SQL function to_char.

  • booleanOnly(): A SQL BOOLEAN interpretation of the targeted JSON data, but only if it is a JSON Boolean value (true or false); otherwise, there is no match. It allows matches only for JSON Boolean values.

    Note:

    Prior to Release 23ai, this used a SQL VARCHAR2(20) interpretation. If you need to obtain a VARCHAR2 value (for compatibility reasons, for example) then you can wrap the value with SQL function to_char.

  • ceiling(): The targeted JSON number, rounded up to the nearest integer. Corresponds to the use of SQL function CEIL.

  • concat(): The concatenation of the (two or more) string arguments. This item method can only be used in the right-hand-side (RHS) path expression of a json_transform operation (otherwise an error is raised).

  • cos(): The trigonometric cosine function of the targeted JSON number (in radians). Corresponds to the use of SQL function COS.

  • cosh(): The trigonometric hyperbolic-cosine function of the targeted JSON number (in radians). Corresponds to the use of SQL function COSH.

  • count(): The number of targeted JSON values, regardless of their types. This is an aggregate method.

  • date(): A SQL DATE interpretation of the targeted JSON value. The targeted value must be either (1) a JSON string in a supported ISO 8601 format for a date or a date with time or (2) (if the data is of SQL type JSON) a date, timestamp, or timestamp with time zone value. Otherwise, there is no match.

    A SQL DATE value has no time component (it is set to zero). But before any time truncation is done, if the value represented by an ISO 8601 date-with-time string has a time-zone component then the value is first converted to UTC, to take any time-zone information into account.

    For example, the JSON string "2021-01-01T05:00:00+08:00" is interpreted as a SQL DATE value that corresponds to the UTC string "2020-12-31 00:00:00".

    The resulting date faithfully reflects the time zone of the data — target and result represent the same date — but the result can differ from what a simple time truncation would produce. (This behavior is similar to that of SQL/JSON function json_scalar.)

  • dateTimeOnly(): A SQL TIMESTAMP interpretation of the targeted JSON value. The targeted value must be a date, timestamp, or timestamp with time zone value. (Only JSON data stored as JSON type can have such values.)

  • dateWithTime(): Like date(), except that the time component of an ISO 8601 date-with-time format is preserved in the SQL DATE instance.

  • double(): A SQL BINARY_DOUBLE interpretation of the targeted JSON string or number.

  • dsInterval(): A SQL INTERVAL DAY TO SECOND interpretation of the targeted JSON string. The targeted string data must be in one of the supported ISO 8601 duration formats; otherwise, there is no match.

  • exp(): The mathematical exponential function of the targeted JSON number. That is, the mathematical constant e (Euler's number, 2.71828183...), raised to the power of the targeted JSON number. Corresponds to the use of SQL function EXP.

  • float(): A SQL BINARY_FLOAT interpretation of the targeted JSON string or number.

  • floor(): The targeted JSON number, rounded down to the nearest integer. Corresponds to the use of SQL function FLOOR.

  • idOnly(): A SQL RAW interpretation of the targeted JSON value. It allows matches only for JSON binary values that are tagged internally as having been derived from an extended object with field $rawid or $oid. (Only JSON data stored as JSON type can have JSON binary values.)

  • indexOf(): The position (index) of the first element of the specified JSON array that is equal to the specified JSON value.

    The array is the first, implicit argument of the method (the targeted data), and the value to find in the array is the second, (first) explicit argument — both are required. This item method can only be used in the right-hand-side (RHS) path expression of a json_transform operation (otherwise an error is raised).

    One or two optional arguments are also accepted: the second explicit argument is the array position of the first element to check (positions before that are skipped). The third explicit argument is the maximum number of array elements to check. You can, for example, use the optional arguments to loop over an array to locate matching elements, in array order.

    • For data that is of JSON data type, all JSON-language values are comparable. Comparison is according to the canonical sort order.

    • For data that is not of JSON type, only scalar JSON values are comparable. Nonscalar data values are ignored, and the specified JSON value to locate must be scalar (otherwise an error is raised).

  • length(): The number of characters in the targeted JSON string, or the number of bytes in the targeted binary value, interpreted as a SQL NUMBER. Corresponds to the use of SQL function LENGTH. For a targeted string value, an optional argument whose value is "chars" or "bytes" is allowed, which specifies the length in characters or bytes, respectively.

  • listagg(): The concatenation of the targeted JSON values, which must be strings (otherwise an error is raised). Accepts an optional delimiter-string argument, which is inserted between consecutive targeted strings. Corresponds to the use of SQL function LISTAGG. This is an aggregate method.

  • log(): The mathematical logarithm function of the targeted JSON number. Corresponds to the use of SQL function LOG. Accepts an optional numeric argument, which is the logarithm base. The default base is the mathematical constant e (Euler's number, 2.71828183...), which means that by default this computes the natural logarithm.

  • lower(): The lowercase string that corresponds to the characters in the targeted JSON string. Corresponds to the use of SQL function LOWER.

  • max(): The maximum of all targeted JSON values, whether scalar or not. This is an aggregate method, but unlike other aggregate methods it cannot be used at the end of a path expression. It can only be used in a filter condition with json_exists or in a query with json_query semantics; using it in a query with json_value semantics raises an error. The value returned is always of JSON data type.

    Methods max() and min() are the only methods that can return a nonscalar JSON value (an object or array).

    • For data that is of JSON data type, all JSON-language values are comparable. Comparison is according to the canonical sort order.

    • For data that is not of JSON type, only scalar JSON values are comparable. Nonscalar data values are ignored, and the specified JSON values must all be scalar (otherwise an error is raised).

  • maxDateTime(): The maximum of all targeted JSON dates with times. Item method dateWithTime() is first applied implicitly to each of the possibly multiple values. Their maximum (a single TIMESTAMP value) is then returned. Targeted JSON values that cannot be converted to dates with times are ignored. This is an aggregate method.

  • maxNumber(): The maximum of all targeted JSON numbers. Item method number() is first applied implicitly to each of the possibly multiple values. Their maximum (a single NUMBER value) is then returned. Targeted JSON values that cannot be converted to numbers are ignored. This is an aggregate method.

  • maxString(): The greatest of all targeted JSON strings, using collation order. Item method string() is first applied implicitly to each of the possibly multiple values. The greatest of these (a single VARCHAR2 value) is then returned. Targeted JSON values that cannot be converted to strings are ignored. This is an aggregate method.

  • min(): The minimum of all targeted JSON values, whether scalar or not. See max() for more information; min() is the same, but it returns the minimum, not the maximum, value.

  • minDateTime(): The minimum of all targeted JSON dates with times. Item method dateWithTime() is first applied implicitly to each of the possibly multiple values. Their minimum (a single TIMESTAMP value) is then returned. Targeted JSON values that cannot be converted to dates with times are ignored. This is an aggregate method.

  • minNumber(): The minimum of all targeted JSON numbers. Item method number() is first applied implicitly to each of the possibly multiple values. Their minimum (a single NUMBER value) is then returned. Targeted JSON values that cannot be converted to numbers are ignored. This is an aggregate method.

  • minString(): The least of all targeted JSON strings, using collation order. Item method string() is first applied implicitly to each of the possibly multiple values. The least of these (a single VARCHAR2 value) is then returned. Targeted JSON values that cannot be converted to strings are ignored. This is an aggregate method.

  • nullOnly(): Returns JSON null if the targeted data is JSON null. Otherwise there's no match, and error handling applies for the SQL function or condition to which the path expression is passed. A common usage is with condition json_exists, where it's used to filter — for example: json_exists(mytable.jcol, $?(@.a.nullOnly() == null)).

  • number(): A SQL NUMBER interpretation of the targeted JSON string or number.

  • numberOnly(): A SQL NUMBER interpretation of the targeted JSON data, but only if it is a JSON number; otherwise, there is no match. It allows matches only for JSON numbers.

  • pow(): The mathematical power function of the targeted JSON number. This raises the targeted JSON number to the specified power, which is a required numeric argument. Corresponds to the use of SQL function POWER.

  • round(): Corresponds to the use of SQL function ROUND.

    An optional integer argument N specifies rounding to the nearest 10-N. By default (N = 0), rounds to the decimal point, that is, to the nearest integer. Nonnegative rounds to N digits after the decimal point; negative rounds to N digits before the decimal point. For example, round(31415.92653, 3) = 31415.927, round(31415.92653, 0) = 31415, round(31415.92653, -3) = 31400.

  • sin(): The trigonometric sine function of the targeted JSON number (in radians). Corresponds to the use of SQL function SIN.

  • sinh(): The trigonometric hyperbolic-sine function of the targeted JSON number (in radians). Corresponds to the use of SQL function SINH.

  • size(): If multiple JSON values are targeted then the result of applying size() to each targeted value. Otherwise:

    • If the single targeted value is a scalar then 1. (Note that a JSON vector value is a scalar.)

    • If the single targeted value is an array then the number of array elements.

    • If the single targeted value is an object then 1.

    This item method can be used with json_query semantics, in addition to using it with json_value semantics. If applied to data that is an array, no implicit iteration over the array elements occurs: the resulting value is just the number of array elements. (This is an exception to the rule of implicit iteration.)

  • size2(): This the same as standard method size(), except that if the single targeted value is an object then the value is the number of members in the object (instead of 1).

  • stddev(): The statistical standard-deviation function of the targeted JSON values, which must be numbers (otherwise an error is raised). Corresponds to the use of SQL function STDDEV. This is an aggregate method.

  • stddevp(): The statistical population standard-deviation function of the targeted JSON values, which must be numbers (otherwise an error is raised). Corresponds to the use of SQL function STDDEV_POP. This is an aggregate method.

  • string(): A SQL VARCHAR2(4000) or CLOB interpretation of the targeted scalar JSON value. VARCHAR2(4000) is the default.

  • stringify(): A SQL CLOB interpretation of the targeted scalar JSON value. Method stringify() is exceptional, in that it can only be used with the simple dot-notation, not with a SQL/JSON path expression.

  • stringOnly(): A SQL VARCHAR2(4000) or CLOB interpretation of the targeted scalar JSON value, but only if it is a JSON string; otherwise, there is no match. It allows matches only for JSON strings. VARCHAR2(4000) is the default.

  • substr(): A substring of the targeted JSON string. Corresponds to the use of SQL function SUBSTR, but it is zero-based, not one-based. The starting position of the substring in the targeted string is a required argument. The maximum length of the substring is an optional (second) argument.

  • sum(): The sum of all targeted JSON numbers. If any targeted value is not a number then an error is raised. Corresponds to the use of SQL function SUM (without any optional behavior). This is an aggregate method.

  • tan(): The trigonometric tangent function of the targeted JSON number (in radians). Corresponds to the use of SQL function TAN.

  • tanh(): The trigonometric hyperbolic-tangent function of the targeted JSON number (in radians). Corresponds to the use of SQL function TANH.

  • timestamp(): A SQL TIMESTAMP interpretation of the targeted JSON value. The targeted string data must be either (1) a JSON string in a supported ISO 8601 format for a date or a date with time or (2) (if the data is of SQL type JSON) a date, timestamp, or timestamp with time zone value. Otherwise, there is no match.Foot 4

  • toBoolean(): A SQL VARCHAR2(20) interpretation of the targeted JSON value. This is the same as method boolean(), except that the targeted value can be a numeric value, in which case zero corresponds to false and any other number corresponds to true.

  • toDateTime(): A SQL TIMESTAMP interpretation of the targeted JSON value. The targeted string data must be either (1) a JSON string in a supported ISO 8601 format for a date or a date with time, (2) a non-negative numeric value, or (3) (if the data is of SQL type JSON) a date, timestamp, or timestamp with time zone value. Otherwise, there is no match.Foot 5

  • truncate(): The targeted JSON number, rounded by truncating. Corresponds to the use of SQL function TRUNC.

    An optional integer argument N (default 0) specifies the number of digits to keep to the left (if negative) or right (if nonnegative) of the decimal point.

  • type(): The name of the JSON-language data type family of the targeted data, or one of its family members, interpreted as a SQL VARCHAR2(20) value. For example, for the numeric type family, the value returned can be "double", "float", or "number". See Comparison and Sorting of JSON Data Type Values.

    This item method can be used in queries with json_query semantics, in addition to json_value semantics. If applied to data that is an array, no implicit iteration over the array elements occurs: the resulting value is "array". (This is an exception to the rule of implicit iteration.)

    • "array" for an array.

    • "boolean" for a Boolean value (true or false).

    • "binary" for a value that corresponds to a SQL RAW value. (For JSON type data only.)

    • "date" for a value that corresponds to a SQL DATE value. (For JSON type data only.)

    • "daysecondInterval" for a value that corresponds to a SQL INTERVAL DAY TO SECOND value. (For JSON type data only.)

    • "double" for a number that corresponds to a SQL BINARY_DOUBLE value. (For JSON type data only.)

    • "float" for a number that corresponds to a SQL BINARY_FLOAT value. (For JSON type data only.)

    • "null" for a null value.

    • "number" for a number.

    • "object" for an object.

    • "string" for a string.

    • "timestamp" for a value that corresponds to a SQL TIMESTAMP value. (For JSON type data only.)

    • "timestamp with time zone" for a value that corresponds to a SQL TIMESTAMP WITH TIME ZONE value. (For JSON type data only.)

    • "vector" for a value that corresponds to a SQL VECTOR value.

    • "yearmonthInterval" for a value that corresponds to a SQL INTERVAL YEAR TO MONTH value. (For JSON type data only.)

  • upper(): The uppercase string that corresponds to the characters in the targeted JSON string. Corresponds to the use of SQL function UPPER.

  • variance(): The statistical variance function of the targeted JSON values, which must be numbers (otherwise an error is raised). Corresponds to the use of SQL function VARIANCE. This is an aggregate method.

  • vector(): A SQL VECTOR interpretation of the targeted JSON value. If the targeted data is a JSON array of numbers then that value is converted to a vector. If the targeted data is a JSON-scalar vector value then that is returned. If the data is any other JSON value, including an array with any non-number elements, then an error is raised.

    Method vector() can only be used with the simple dot-notation, not with a SQL/JSON path expression.

  • ymInterval(): A SQL INTERVAL YEAR TO MONTH interpretation of the targeted JSON string. The targeted string data must be in one of the supported ISO 8601 duration formats; otherwise, there is no match.

Item methods abs(), ceiling(), double(), floor(), size(), and type() are part of the SQL/JSON standard. The other methods are Oracle extensions to the SQL/JSON standard: atan(), avg(), binary(), binaryOnly(), boolean(), booleanOnly(), concat(), cos(), cosh(), count(), date(), dateTimeOnly(), dateWithTime(), double(), dsInterval(), exp(), float(), idOnly(), indexOf(), length(), listagg(), log(), lower(), max(), maxDateTime(), maxNumber(), maxString(), min(), minDateTime(), minNumber(), minString(), nullOnly(), number(), numberOnly(), pow(), round(), sin(), sinh(), size2(), stddev(), stddevp(), string(), stringify(), stringOnly(), substr(), sum(), tan(), tanh(), timestamp(), toBoolean(), toDateTime(), truncate(), upper(), variance(), vector(), and ymInterval().

Item methods avg(), count(), listagg(), max(), maxDateTime(), maxNumber(), maxString(), min(), minDateTime(), minNumber(), minString(), stddev(), stddevp(), sum(), and variance() are aggregate item methods. Instead of acting individually on each targeted value they act on all targeted values together. For example, if a path expression targets multiple values that can be converted to numbers then sum() returns the sum of those numbers.

Note that when a path expression targets an array, applying an aggregate item method to it, the array is handled as a single value — there is no implicit iteration over the array elements. For example, count() counts any targeted array as one value, and size() returns the size of the array, not the sizes of its elements.

If you want an aggregate item method to act on the array elements then you need to explicitly iterate over those elements, using wildcard *. For example, if the value of field LineItems in a given document is an array then $.LineItems.count() returns 1, but $.LineItems[*].count() returns the number of array elements.

An aggregate item method applies to a single JSON document at a time, just like the path expression (or dot-notation) of which it is part. It aggregates the multiple values that the path expression targets in that document. In a query it returns a row for each document. It does not aggregate information across multiple documents, returning a single row for all documents, as do SQL aggregate functions. See Example 17-1 and Example 17-2.

See Also:

  • ABS in Oracle AI Database SQL Language Reference

  • ATAN in Oracle AI Database SQL Language Reference

  • AVG in Oracle AI Database SQL Language Reference

  • CEIL in Oracle AI Database SQL Language Reference

  • COS in Oracle AI Database SQL Language Reference

  • COSH in Oracle AI Database SQL Language Reference

  • EXP in Oracle AI Database SQL Language Reference

  • FLOOR in Oracle AI Database SQL Language Reference

  • LENGTH in Oracle AI Database SQL Language Reference

  • LISTAGG in Oracle AI Database SQL Language Reference

  • LOG in Oracle AI Database SQL Language Reference

  • LOWER in Oracle AI Database SQL Language Reference

  • POWER in Oracle AI Database SQL Language Reference

  • ROUND (number) in Oracle AI Database SQL Language Reference

  • SIN in Oracle AI Database SQL Language Reference

  • SINH in Oracle AI Database SQL Language Reference

  • STDDEV in Oracle AI Database SQL Language Reference

  • STDDEV_POP in Oracle AI Database SQL Language Reference

  • SUBSTR in Oracle AI Database SQL Language Reference

  • SUM in Oracle AI Database SQL Language Reference

  • TAN in Oracle AI Database SQL Language Reference

  • TANH in Oracle AI Database SQL Language Reference

  • TRUNC (number) in Oracle AI Database SQL Language Reference

  • UPPER in Oracle AI Database SQL Language Reference

  • VARIANCE in Oracle AI Database SQL Language Reference

Item Methods and Specified Query Return Types

Because some item methods interpret the targeted JSON data as if it were of a SQL data type, they can be used at the end of a SQL/JSON path expression to provide the data to be returned by a query.

All data-type conversion methods except those whose names start with "to" can be used at path end. It also applies to methods (e.g. minString(), that implicitly first apply a type-conversion method (e.g. string()).

Some other methods, such as the aggregation methods except max() and min(), can also be used at path end. The methods listed in Table 17-2 are the only item methods that can be used at the end of a path expression.

An item method that cannot be used at the end of a path expression can only be used in a filter condition with json_exists or in a query with json_query semantics; using it in a query with json_value semantics raises an error.

You can use such path-end item methods at the end of a path expression in any query that has json_value semantics (it returns a scalar SQL value), whether it uses simple dot notation, json_value, or a (scalar) json_table column. For example, they can be used with json_value in place of a RETURNING clause to specify the returned SQL data type for the extracted JSON data.

You can also use path-end item methods together with a json_value RETURNING clause or a json_table column type specification. What happens if the SQL data type to use for extracted JSON data is specified by both a path-end item method and either a json_value RETURNING clause or a json_table column type?

  • If the two data types are compatible then the data type for the RETURNING clause or the column is used. For these purposes, VARCHAR2 is compatible with both VARCHAR2 and CLOB.

  • If the data types are incompatible then a static, compile-time error is raised.

Table 17-2 details the compatibility between path-end item methods and specified SQL return types for a SQL query.

Table 17-2 Compatibility of Path-End Item Methods and Scalar SQL Return Types

Item Method Compatible SQL Query Return Data Type
  • lower()
  • maxString()
  • minString()
  • string()
  • stringOnly()
  • upper()
 VARCHAR2 or CLOB, except that string() returns SQL NULL for a JSON null value
stringify() CLOB, except that it returns SQL NULL for a JSON null value
  • avg()
  • count()
  • maxNumber()
  • minNumber()
  • number()
  • numberOnly()
  • stddev()
  • stddevp()
  • sum()
 NUMBER
double() BINARY_DOUBLE
float() BINARY_FLOAT
  • date()
  • dateTimeOnly()

DATE, with truncated time component (set to zero), corresponding to RETURNING DATE TRUNCATE TIME.

If the JSON value is an ISO string with time-zone information, the represented date-with-time is first converted to UTC, to take the time zone into account.
dateWithTime() DATE, with time component, corresponding to RETURNING DATE PRESERVE TIME
  • maxDateTime()
  • minDateTime()
  • timestamp()
 TIMESTAMP
ymInterval() INTERVAL YEAR TO MONTH
dsInterval() INTERVAL DAY TO SECOND
  • boolean()
  • booleanOnly()
 VARCHAR2 or BOOLEAN
  • binary()
  • binaryOnly()
  • idOnly()
 RAW
vector() VECTOR

Using a json_value RETURNING clause or a json_table column specification, you can specify a length for character data and a precision and scale for numerical data. This lets you assign a more precise SQL data type for extraction than what is provided by an item method for target-data comparison purposes.

For example, if you use item method string() and json_value with clause RETURNING VARCHAR2(150) then the data type of the returned data is VARCHAR2(150), not VARCHAR2(4000).

Example 17-1 Aggregating Values of a Field for Each Document

This example uses item method avg() to aggregate the values of field Quantity across all LineItems elements of a JSON document, returning the average for each document as a separate result row. 

SELECT json_value(data, 
                  '$.LineItems[*].Quantity.avg()')
  FROM j_purchaseorder;

Example 17-2 Aggregating Values of a Field Across All Documents

This example uses SQL function avg to aggregate the average line-item Quantity values for all JSON documents, returning the overall average for the entire set of documents as a single row. The average quantity for all line items of a given document is computed using item method avg(). 

SELECT avg(json_value(data, 
                      '$.LineItems[*].Quantity.avg()'))
  FROM j_purchaseorder;

Related Topics

Parent topic: SQL/JSON Path Expressions


Footnote Legend

Footnote 1: (Function json_value can also return an object type or a collection type, but an item method can't be applied to the result.)
Footnote 2: Method vector() is a conversion method only when applied to an array of numbers. When applied to a JSON-scalar vector value it returns that value as an instance of SQL type VECTOR. Method vector() can only be used with the simple dot-notation, not with a SQL/JSON path expression.
Footnote 4: Applying item method timestamp() to a supported ISO 8601 string <ISO-STRING> has the effect of SQL sys_extract_utc(to_utc_timestamp_tz(<ISO-STRING>).
Footnote 5: Applying item method toDateTime() to a supported ISO 8601 string <ISO-STRING> has the effect of SQL sys_extract_utc(to_utc_timestamp_tz(<ISO-STRING>). A non-negative numeric value is interpreted as the number of seconds since 1970-01-01.