SQL/JSON Path Expressions

TimesTen provides SQL access to JSON data using SQL/JSON path expressions. A path expression has a simple syntax. It selects zero or more JSON values that match, or satisfy, the expression.

The JSON_EXISTS condition returns true if at least one value matches the path expression; false if no value matches the expression.

If a single value matches the path expression, the JSON_VALUE SQL/JSON function returns that value if it is scalar, or returns an error if it is nonscalar. If no value matches the expression, then JSON_VALUE returns SQL NULL.

The JSON_QUERY SQL/JSON function returns all values that match the path expression, as it can return multiple values.

In all cases, path-expression matching attempts to match each step of a path expression. If matching any step fails, no attempt is made to match the subsequent steps, and the matching of the path expression fails. If matching each step succeeds, the matching of the path expression succeeds.

The maximum length of the text of a path expression is 32 KB. However, the effective length of a expression is essentially unlimited, because the expression can make use of SQL/JSON variables that are bound to string values, each of which is limited to 32 KB.

Topics:

SQL/JSON Path Expression Syntax

SQL/JSON path expressions are matched by SQL/JSON functions or conditions against JSON data, to select or test portions of it. Path expressions can use wildcards and array ranges. Matching is case-sensitive.

You pass a path expression and JSON data to a SQL/JSON function or condition. The path expression is matched against the data, and the matching data is processed by the particular function or condition.

Topics:

Basic SQL/JSON Path Expression Syntax

The basic syntax of path expression is comprised of a context-item symbol ($) followed by zero or more object, array, or descendant steps, each of which can be followed by a filter expression, optionally followed by a function step.

This basic syntax is extended by relaxing the matching of arrays and nonarrays against nonarray and array patterns, respectively. See SQL/JSON Path Expression Syntax Relaxation.

Matching of data against SQL/JSON path expressions is case-sensitive.

  • A SQL/JSON basic path expression (path expression) is an absolute or relative path expression.

  • An absolute path expression is a dollar sign ($) followed by zero or more nonfunction steps, followed by an optional function step. The dollar sign represents the context item. The context item is the JSON data to be matched. The matching data is determined by evaluating the SQL expression that is passed as argument to the SQL/JSON function.

  • A relative path expression is an at sign (@) followed by zero or more nonfunction steps, followed by an optional function step. It has the same syntax as an absolute path expression, except for the use of an at sign instead of a dollar sign.

    A relative path expression is used inside a filter expression. The at sign represents the current filter item. The current filter item is the JSON data that matches the part of path expression that precedes the filter containing the relative path expression. A relative path expression is matched against the current filter item in the same way that an absolute path expression is matched against the context item.

  • A nonfunction step is an object, array, or descendant step, followed by an optional filter expression.

  • An object step is a dot or period (.) followed by an object field name or an asterisk (*) wildcard. An asterisk wildcard stands for the values of all fields. Field names that are empty or contain whitespace or characters other than uppercase or lowercase letters (A to Z) or decimal digits (0-9) must be enclosed in double quotation marks (").

  • An array step is a set of brackets ([]) enclosing either an asterisk (*) wildcard or one or more specific array indexes or range specifications separated by a comma (,). An asterisk stands for all array elements.

    An error is raised if you use both an asterisk and either an array index or range specification. Also, an error is raised if no array index or range specification is provided—a set of empty brackets is an invalid array step.

    The order in which array indexes and range specifications are specified in an array step matters. The same order is reflected in the array that results from the function using the path expression.

    Multiple range specifications in the same array step are treated independently. In particular, overlapping ranges result in the repetition of the elements that overlap.

    The use of array indexes or range specifications that specify out-of-bounds positions of an array result in no error. The path expression simply does not match the data for the out-of-bounds positions, as the array has no such positions.

  • An array index specifies a single array position (0, 1, 2,…). Array position and indexing are zero-based. The first array element has index 0, which specifies position 0.

    An array index can be one of the following:

    • A whole number (0, 1, 2,…)

    • The last element of a nonempty array of any size (referenced by the index last)

    • The N to last element in the array (referenced by last-N, where N is a whole number that is no greater than the array size minus 1)

      For example, the next-to-last array element can be referenced by index last-1, the second-to-last by index last-2, and so on.

      Note:

      Whitespace surrounding the minus sign (-) is ignored.

  • A range specification specifies a subset of subsequent array positions (referenced by N to M, where N and M are array indexes, and the to keyword is preceded and followed by one or more whitespace characters).

    The N to M and M to N range specifications are equivalent, both are equivalent to explicitly specifying the N, M array indexes, and every index between them—all in ascending order (for example, both [2 to 5] or [5 to 2] array steps are equivalent to the [2, 3, 4, 5] array step). The N to N range specification is equivalent to the single index N.

  • A descendant is two consecutive dots or periods (..) followed by a field name. The field name has the same syntax as for an object step.

    A descendant descends recursively into the objects or arrays that match the preceding step (or into the context item if there is no preceding step). At each descendant level, for each object and for each array element in an object, it gathers the values that have the specified field name. It returns all of the gathered values.

    For example, consider this JSON data (in a JSON type column named data) and query:
    { "a" : { "b" : { "z" : 1 },
          "c" : [ 5,
                  { "z" : 2 } ],
          "z" : 3 },
  "z" : 4 }
    JSON_QUERY(data, '$.a..z' WITH WRAPPER)

    The query returns the [3, 1, 2] array (due to the WRAPPER condition). It gathers the value of each z field within the step that immediately precedes .., the a field. The z field with 4 as value is not a match because it is not within the value of the a field.

  • A filter expression (filter) is a question mark (?) followed by a filter condition enclosed in parenthesis (()). A filter is satisfied if its condition returns true.

  • A filter condition (condition) applies a predicate (a Boolean function) to its arguments. It is defined recursively as follows:

    • !condition: An exclamation mark (!) is used for the negation of condition, meaning that condition must not be satisfied. ! is a prefix unary predicate. See Negation in Path Expressions in Oracle Database JSON Developer's Guide.

    • (condition): Parenthesis (()) are used for grouping. They separate condition as a unit from other filter conditions that may precede or follow it.

      You can also use parenthesis to make the expression more readable, even if the parenthesis have no effect. However, you may need to use parenthesis to delimit the condition argument whenever the beginning and end of the argument are otherwise unclear. For example, you must use parenthesis for !(@.x > 5) instead of !@.x < 5. In contrast, you can use either !exists@.x, !(exists@.x) or !(exists(@.x)).

    • condition1 && condition2: A double ampersand (&&) is used for the conjunction (and) of condition1 and condition2, which requires that both filter conditions are satisfied. && is an infix binary predicate.

    • condition1 || condition2: Two vertical bars (||) are used for the inclusive disjunction (or) of condition1 and condition2, which requires that either or both filter conditions are satisfied. || is an infix binary predicate.

    • exists(relative_path_expression): The exists keyword is used to check if a specified path exists (is present). exists is a prefix unary predicate.

    • relative_path_expression in value_list: The in keyword is used as an inclusive disjunction (or) of the values in the value list for the specified path. in is an infix binary predicate.

      The following are equivalent:

      @.z in ("a", "b", "c")

(@.z == "a") || (@.z == "b") || (@.z == "c")

      An in condition with a singleton value list is equivalent to a single equality comparison. For example, @.z in ("a") is equivalent to @.z == "a". An in condition with no values (such as @.z in ()) is unmatchable.

      A value list consists of a parenthesis (()) enclosing a list zero or more JSON literal values or SQL/JSON variables separated by commas (,). A value list can only follow the in condition. An error is raised, otherwise.

      • If each variable in the list is of JSON data type, then each listed value (whether literal or the value of a variable) is compared for equality against the targeted JSON data, using the canonical sort order described in Comparison and Sorting of JSON Data Type Values in Oracle Database JSON Developer's Guide. The in condition is satisfied if any of the listed values is equal to the targeted data.

      • If at least one variable is not of JSON data type, all values in the list (whether literal or variable) must be scalar values of the same JSON-language type. For example, all values in the list must be a string. An error is raised, otherwise.

      • A JSON null value is an exception to this same-type restriction: null is always allowed in a value list. It is matched (only) by a null value in the targeted data.

    • A comparison, which is one of the following:

      • A JSON scalar value followed by a comparison predicate followed by another JSON scalar value.

      • A relative path expression followed by a comparison predicate followed by another relative path expression.

      • A JSON scalar value or a SQL/JSON variable followed by a comparison predicate followed by a relative path expression.

      • A relative path expression followed by a comparison predicate followed by a JSON scalar value or a SQL/JSON variable.

      • A relative path expression followed by any of the keywords in Table 3-1 followed by either a JSON string or a SQL/JSON variable that is bound to a SQL string (which is automatically converted from the database character set to UTF8).

        Table 3-1 Keywords Supported for a Comparison Filter Condition

        Keywords Meaning

        has substring

        The matching data value has the specified string as a substring.

        starts with

        The matching data value has the specified string as a prefix.

        like

        The matching data value has the specified string, which is interpreted as SQL LIKE pattern that uses SQL LIKE4 character-set semantics.

        A percent sign (%) in the pattern matches zero or more characters. An underscore (_) matches a single character.

        like_regex

        The matching data value has the specified string, which is interpreted as SQL REGEXP LIKE regular expression pattern that uses SQL LIKE4 character-set semantics.

        It matches the empty JSON string ("").

        regex like

        Same as the like_regex keyword.

        regex equals

        Same as the like_regex keyword, except:

        • It matches its regular expression pattern against the entire JSON string data value. The full string must match the pattern for the comparison to be satisfied.

        • It does not match the empty JSON string ("").

        eq_regex

        Same as the regex equals keyword.

        ci_like_regex

        Same as the like_regex keyword, except the matching is case insensitive.

        ci_regex

        Same as the regex equals keyword, except the matching is case insensitive.

        For all these predicates, a pattern that is an empty string ("") matches data is an empty string. A pattern that is a nonempty string does not match data that is an empty string. The only exception is like_regex.

      A comparison predicate can be either of the following:

      • Equals (==)

      • Does not equals (<> or !=)

      • Is less than (<)

      • Is greater than (>)

      • Is less than or equal to (<=)

      • Is greater than or equal to (>=)

      The predicates that you can use in filter conditions are thus &&, ||, !, exists, ==, <>, !=, <, >, <=, >=, in, has substring, starts with, like, like_regex, regex like, regex equals, eq_regex, ci_like_regex, and ci_regex.

      At least one side of a comparison must not be a SQL/JSON variable. If the data targeted by a comparison is of JSON data type, and if all SQL/JSON variables used in the comparison are also of JSON type, then comparison uses the canonical sort order described in Comparison and Sorting of JSON Data Type Values in Oracle Database JSON Developer's Guide. Otherwise, the default type for a comparison is defined at compile time, based on the types for the non-variable sides. You can use a type-specifying item method to override this default with a different type. The type of your matching data is automatically converted, for the comparison, to fit the determined type (default or specified by item method). For example, $.z > 5 imposes numerical comparison because 5 is a number. In contrast, $.z > "5" imposes string comparison because "5" is a string.

    • A SQL/JSON variable is a dollar sign ($) followed with no intervening whitespace by the name of a variable that is bound in a PASSING clause. See PASSING Clause for SQL Functions and Conditions in Oracle Database JSON Developer's Guide.

Basic Path-Expression Examples

Table 3-2 showcases some examples of basic path expressions. The examples are based on the JSON data in Example 2-2.

Table 3-2 Basic Path-Expression Examples

Path Expression Description

$

Context item.

$.LineItems

The value of the LineItems field of the context-item object. The dot (.) after the dollar sign ($) indicates that the context item is a JSON object.

$.LineItems[0]

An object that is the first element of an array that is the value of the LineItems field of the context-item object. The bracket notation indicates that the value of the LineItems field is an array.

$.LineItems[0].Quantity

The value of the Quantity field of an object that is the first element of an array that is the value of the LineItems field of the context-item object. The second dot (.) indicates that the first element of the LineItems array is an object (with a Quantity field).

$.LineItems[*].Quantity

The value of the Quantity field of each object in an array that is the value of the LineItems field of the context-item object.

$.*[*].Quantity

The value of the Quantity field for each object in an array value of a field of the context-item object.

$.LineItems[1 to 2, 0]

The second to third and first elements of an array that is the value of the LineItems field of the context-item object. The elements are returned in the order specified: second, third, first.

$.LineItems[last to last-1, last]

The last, next-to-last, and last elements of an array that is the value of the LineItems field of the context-item object. The range last to last-1, which is the same as last-1 to last, returns the elements ordered from next-to-last through last. The last element is returned twice.

$.LineItems[2].*

The value of all the fields of an object that is the third element of an array that is the value of the LineItems field of the context-item object.

$.LineItems[2].Part?(@.UnitPrice > 20)

The value of the Part field of an object that is the third element of an array that is the value of the LineItems field of the context-item object, provided that it has a UnitPrice field whose value is—or can be converted to —a number greater than 20. A UnitPrice value such as "27.95 USD" fails the test and is unmatched.

$.LineItems[*]?(@.Part.Description == "One Magic Christmas")

The value of each object in an array that is the value of the LineItems field of the context-item object, provided that it has a Part field whose value is an object with a Description field whose value equals the string "One Magic Christmas".

$.LineItems[*].Part?(@.Description starts with "One").UPCCode

The value of the UPCCode field of an object that is the value of the Part field of each object in an array that is the value of the LineItems field of the context-item object, provided that the object of that is the value of Part field has a Description field whose value starts with the string "One".

$.LineItems[*].Part?(@.Description like "O_e%").UPCCode

The value of the UPCCode field of an object that is the value of the Part field of each object in an array that is the value of the LineItems field of the context-item object, provided that the object of that is the value of Part field has a Description field whose value is O followed by any single character, then e, then any sequence of zero or more characters. Underscore (_) matches a single character, and percent (%) matches multiple characters.

$.LineItems[*].Part?(@.Description regex like "M.+c").UPCCode

The value of the UPCCode field of an object that is the value of the Part field of each object in an array that is the value of the LineItems field of the context-item object, provided that the object of that is the value of Part field has a Description field whose value contains M followed any sequence of one or more characters, then c. Matching is case sensitive, and it is not anchored to the start of the Description string.

$.LineItems[*].Part?(@.Description ci_regex "o.+s").UPCCode

The value of the UPCCode field of an object that is the value of the Part field of each object in an array that is the value of the LineItems field of the context-item object, provided that the object of that is the value of Part field has a Description field whose value starts with o or O followed any sequence of one or more characters and ends with s. Matching is case insensitive, and the entire Description string must match.

$..UPCCode

All values of the UPCCode field, anywhere, at any level.

$.LineItems.?(@.Part.Description == "Sirens" && @.Part.UnitPrice == 27.95)

The value of the LineItems field of the context-item object, provided that it has a Part field whose value is an object with a Description field whose value equals the string "Sirens" and it has a Part field whose value is an object with a UnitPrice field whose value equals the number 27.95.

Note: The filter conditions in the conjunction do not necessarily apply to the same Part object (part). The filters test for the existence of a part with Sirens as description and for the existence of a part with a 27.95 unit price. It does not test for the existence of a part with both.

$.LineItems[*].Part?(@.Description == "Sirens" && @.UnitPrice == 19.95)

The value of the Part field of each object in an array that is the value of the LineItems field of the context-item object, provided that it has a Description field whose value equals the string "Sirens" and it has a UnitPrice field whose value equals the number 19.95.

Unlike the preceding path expression, the filter conditions in the conjunction apply to the same Part object (part). The filter applies to a given part, which is outside the filter.

$.LineItems[*].Part?(@.Description == $Description && @.UnitPrice == $Price)

Same as the previous path expression, except the values used in the comparisons are the $Description and $Price SQL/JSON variables. The values are provided by the Description and Price SQL bind variables in a PASSING clause: PASSING … AS "Description", … AS "Price".

Use of variables in comparisons can improve performance by avoiding query recompilation.

SQL/JSON Path Expression Syntax Relaxation

The basic SQL/JSON path-expression syntax is relaxed to allow implicit array wrapping and unwrapping.

  • If a path-expression step expects an array but the actual data presents no array, then the data is implicitly wrapped in an array.

  • If a path-expression step expects a nonarray but the actual data presents an array, then the array is implicitly unwrapped.

The [*] abbreviation can be omitted whenever it precedes the object accessor (.) followed by an object field name, with no change in effect. The reverse is also true. [*] can always be inserted in front of the object accessor (.) with no change in effect.

This means that the [*].type object step, which stands for the value of type field of each element of a given array of objects, can be abbreviated as .type. It also means that the .type object step, which looks as though it stands for the type value of a single object, stands also for the type value of each element of an array to which the object accessor is applied.

This is an important feature, because it means that you need not change a path expression in your code if your data evolves to replace a given JSON value with an array of such values, or vice versa.

For example, if you consider the JSON data in Example 2-2, the first JSON document has a Phone field whose value is a single object with type and number fields. The $..Phone.number path expression, which matches a single phone number, can still be used if the data evolves to represent an array of phones, as shown in the second JSON document. The $..Phone.number path expression matches either a single phone object, selecting its number, or an array of phone objects, selecting the number of each.

Similarly, if your data mixes both kinds of representation—there are some data entries that use a single phone object and some that use an array of phone objects, or even some entries that use both—you can use the same path expression to access the phone information from these different kinds of entry.

These are some examples from Table 3-2 with their equivalences.

  • $.LineItems - The value of the LineItems field of either:

    • A single context-item object.

    • Each object in the context-item array, which is equivalent to $[*].LineItems.

  • $.LineItems[0].Quantity - The value of the Quantity field for any of these objects:

    • The first element of the array that is the value of the LineItems field of the context-item object.

    • The value of the LineItems field of the context-item object, which is equivalent to $.LineItems.Quantity.

    • The value of the LineItems field of each object in the context-item array, which is equivalent to $[*].LineItems.Quantity.

    • The first element of each array that is the value of the value of the LineItems field of each object in the context-item array, which is equivalent to $[*].LineItems[0].Quantity.

  • $.*[*].Quantity - The value of the Quantity field for any of these objects:

    • An element of an array value of a field of the context-item object.

    • The value of a field of the context-item object, which is equivalent to $.*.Quantity

    • The value of a field of an object in the context-item array, which is equivalent to $[*].*.Quantity

    • Each object in an array value of a field of an object in the context-item array, which is equivalent to $[*].*[*].Quantity

SQL/JSON Path Expression Item Methods

You apply an item method to transform the targeted data in a path expression. The targeted data acts as the implicit argument to the item method. Some item methods require, or accept, one or more explicit arguments. These explicit arguments are separated by commas and follow the method name in a parenthesis (()).

The SQL/JSON function or condition uses the transformed data instead 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 raise an error (for JSON_VALUE semantics) or act as filter (when used with JSON_EXISTS), removing the non-matching data from the result set.

If an item-method conversion fails (such as when the targeted data is of the wrong type), then the path cannot be matched and the error handling for the function or condition is applied. For JSON_VALUE semantics, the default error-handling behavior is to return a SQL NULL on error. For JSON_EXISTS semantics, the default behavior is to return FALSE, which means that the non match just serves as a filter.

An item method always transforms the targeted JSON data to (possibly other) JSON data, which is always scalar. However, a query using a path expression (with or without an item method) can return data as a scalar SQL data type. This is 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 BLOB, CLOB, JSON, or VARCHAR2 data type. 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 targeted JSON data that is targeted and possibly transformed by the item method. 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; 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:

You can also use item methods with JSON_EXISTS. In this context, you can only use an item method at the end of path expression in a filter-condition comparison. The transformed JSON value that results from the item method is not returned as a SQL value.

Application of an Item Method to an Array

With the exception of the count(), size(), size2() and type() item methods, if any array is targeted by an item method, then the method is applied to each element of the array, not the array itself, and multiple values (the resulting set of converted array elements) are returned in place of the array.

  • For a JSON_VALUE query, a SQL NULL is returned. This is because mapping the item method over the array elements results in multiple return values, which represents a mismatch for JSON_VALUE.

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

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

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

The count(), size(), size2() and type() item methods—in contrast—when applied to an array, they treat it as such, instead of acting on its elements. For example, this query: 

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

returns a single VARCHAR2 value of 'array'.

Data-Type Conversion Item Methods

An item method always transforms its targeted JSON data to (possible other) JSON data. However, when the method is used in a JSON_VALUE query (or other function that returns SQL data), the transformed JSON data is in turn converted to a SQL value.

  • If present, a RETURNING clause specifies the SQL type for the data.

  • If a RETURNING clause is absent, each item method results in a particular default SQL type, as indicated in Table 3-3

Table 3-3 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

BINARY orBLOB

None.

binary()

string

binary

BINARY orBLOB

Error if any input characters are not hexadecimal numerals.

binaryOnly()

binary (both identifier and non identifier)

binary

BINARY orBLOB

None.

boolean()

Boolean

Boolean

VARCHAR2

None.

boolean()

string

Boolean

VARCHAR2

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

booleanOnly()

Boolean

Boolean

VARCHAR2

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.

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

BINARY

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.

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.

Item-Method Descriptions

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

  • binary(): A SQL BINARY interpretation of the targeted JSON value, which can be a hexadecimal string or a JSON binary value.

  • binaryOnly(): A SQL BINARY 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 such values).

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

  • booleanOnly(): A SQL VARCHAR2 interpretation of the targeted JSON data, but only if it is a JSON Boolean value. It allows matches only for JSON Boolean values.

  • 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 a JSON string in a supported ISO 8601 format for a date or a date with time or 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). However, 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.

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

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

  • idOnly(): A SQL BINARY 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 the$oid field (only JSON data stored as JSON type can have JSON binary values).

  • 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 the LENGTH SQL function.

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

  • 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 returns an error. The value returned is always of JSON data type.

    The max() and min() methods 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 Comparison and Sorting of JSON Data Type Values in Oracle Database JSON Developer's Guide.

    • For data that is not of JSON type, only scalar JSON values are comparable. Nonscalar data values are ignored. The specified JSON values must all be scalar—otherwise, an error is returned.

  • maxNumber(): The maximum of all targeted JSON numbers. The number() item method 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. The string() item method 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.

  • minNumber(): The minimum of all targeted JSON numbers. The number() item method 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. The string() item method 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.

  • name(): The string that corresponds to the field name of the targeted JSON value.

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

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

    • If the single targeted value is a scalar, then the result is 1.

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

    • If the single targeted value is an object, then the result is 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(): Same as size(), except that if the single targeted value is an object then the value is the number of members in the object.

  • stddev(): The statistical standard-deviation function of the targeted JSON values, which must be numbers (otherwise, an error is returned). 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 returned). This is an aggregate method.

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

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

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

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

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

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

    • "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 the UPPER SQL function.

  • variance(): The statistical variance function of the targeted JSON values, which must be numbers (otherwise, an error is returned). This is an aggregate method.

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

Aggregate methods, instead of acting individually on each targeted value, 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 the asterisk (*) 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 3-3 and Example 3-4.

Example 3-3 Aggregating Values of a Field for Each Document

The query in this example uses the avg() method to aggregate the values of the Quantity field across all elements of the LineItems array of a JSON document. It returns the average for each document as a separate result. 

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

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2. 

< 8 >
< 7 >
2 rows found.

Example 3-4 Aggregating Values of a Field for Each Document

The query in this example uses the avg() method to aggregate the average Quantity values for all JSON documents. The average Quantity value for a given document is calculated using the avg() item method.. 

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

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2. 

< 7.5 >
1 row found.

Item Methods and Specified Query Return Types

Given that some item methods interpret the targeted JSON data as if it were a SQL data type, they can be used at the end of a SQL/JSON path expression to provide the data type to be returned by a query. All data-type conversion methods (except toBoolean() and toDateTime()) can be used at path end. This also applies for methods that implicitly first apply a type-conversion methods (such as minString(), which implicitly applies string()).

Some other methods, such as the aggregation methods (except max() and min()), can be used at path end. The methods in Table 3-4 are the only methods that can be used at the end of a path expression. The remaining item methods can only be used in a filter condition with JSON_EXISTS or in a query with JSON_QUERY semantics—using these methods in a query with JSON_VALUE semantics returns an error.

Path-end item methods can be used in any query with JSON_VALUE semantics, whether it uses simple dot notation or a scalar JSON_TABLE column. For example, you can use them with JSON_VALUE in place of a RETURNING clause to specify the return SQL data type for the targeted JSON data. Also, you can you use path-end item methods together with a RETURNING clause (JSON_VALUE) or a column type specification (JSON_TABLE).

  • If the two data types are compatible, then the data type for the RETURNING clause or the column is used.

  • If the two data types are incompatible, then an error is returned.

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

Table 3-4 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

avg()

count()

maxNumber()

minNumber()

number()

numberOnly()

stddev()

stddevp()

sum()

NUMBER

double()

BINARY_DOUBLE

float()

BINARY_FLOAT

date()

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.

timestamp()

TIMESTAMP

ymInterval()

INTERVAL YEAR TO MONTH

dsInterval()

INTERVAL DAY TO SECOND

boolean()

booleanOnly()

VARCHAR2 or BOOLEAN

binary()

binaryOnly()

idOnly()

BINARY

Using a RETURNING clause (JSON_VALUE) or a column specification (JSON_TABLE), 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 the string() item method and JSON_VALUE with the RETURNING VARCHAR2(150) clause, then the data type of the returned data is VARCHAR2(150), not VARCHAR2(4000).