1. JSON Developer's Guide
  2. Query JSON Data
  3. SQL/JSON Path Expressions

15 SQL/JSON Path Expressions

Oracle Database provides SQL access to JSON data using SQL/JSON path expressions.

  • Overview of SQL/JSON Path Expressions
    Oracle Database provides SQL access to JSON data using SQL/JSON path expressions.
  • SQL/JSON Path Expression Syntax
    SQL/JSON path expressions are matched by SQL/JSON functions and conditions against JSON data, to select portions of it. Path expressions can use wildcards and array ranges. Matching is case-sensitive.
  • SQL/JSON Path Expression Item Methods
    The Oracle item methods available for a SQL/JSON path expression are described.
  • Types in Comparisons
    Comparisons in SQL/JSON path-expression filter conditions are statically typed at compile time. If the effective types of the operands of a comparison are not known to be the same then an attempt is sometimes made to reconcile them by type-casting.

Parent topic: Query JSON Data

15.1 Overview of SQL/JSON Path Expressions

Oracle Database provides SQL access to JSON data using SQL/JSON path expressions.

JSON is a notation for JavaScript values. When JSON data is stored in the database you can query it using path expressions that are somewhat analogous to XQuery or XPath expressions for XML data. Similar to the way that SQL/XML allows SQL access to XML data using XQuery expressions, Oracle Database provides SQL access to JSON data using SQL/JSON path expressions.

SQL/JSON path expressions have a simple syntax. A path expression selects zero or more JSON values that match, or satisfy, it.

SQL/JSON condition json_exists returns true if at least one value matches, and false if no value matches. If a single value matches, then SQL/JSON function json_value returns that value if it is scalar and raises an error if it is non-scalar. If no value matches the path expression then json_value returns SQL NULL.

SQL/JSON function json_query returns all of the matching values, that is, it can return multiple values. You can think of this behavior as returning a sequence of values, as in XQuery, or you can think of it as returning multiple values. (No user-visible sequence is manifested.)

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

The maximum length of a SQL/JSON path expression is 32K bytes.

Related Topics

Parent topic: SQL/JSON Path Expressions

15.2 SQL/JSON Path Expression Syntax

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

You pass a SQL/JSON path expression and some 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 SQL/JSON function or condition. You can think of this matching process in terms of the path expression returning the matched data to the function or condition.

  • Basic SQL/JSON Path Expression Syntax
    The basic syntax of a SQL/JSON path expression is presented. It is composed of a context-item symbol ($) followed by zero or more object, array, and descendant steps, each of which can be followed by a filter expression, followed optionally by a function step. Examples are provided.
  • SQL/JSON Path Expression Syntax Relaxation
    The basic SQL/JSON path-expression syntax is relaxed to allow implicit array wrapping and unwrapping. This means that you need not change a path expression in your code if your data evolves to replace a JSON value with an array of such values, or vice versa. Examples are provided.

Related Topics

Parent topic: SQL/JSON Path Expressions

15.2.1 Basic SQL/JSON Path Expression Syntax

The basic syntax of a SQL/JSON path expression is presented. It is composed of a context-item symbol ($) followed by zero or more object, array, and descendant steps, each of which can be followed by a filter expression, followed optionally by a function step. Examples are provided.

However, this basic syntax is extended by relaxing the matching of arrays and non-arrays against non-array 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 (also called just a path expression here) is an absolute path expression or a relative path expression.

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

  • 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 that it uses an at sign instead of a dollar sign ($).

    A relative path expression is used inside a filter expression (filter, for short). The at sign represents the path-expression current filter item, that is, the JSON data that matches the part of the (surrounding) 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 step, an array step, or a descendant step, followed by an optional filter expression.

  • A single function step is optional in a basic path expression (absolute or a relative). If present, it is the last step of the path expression. It is a period (.), sometimes read as "dot", followed by a SQL/JSON item method, followed by a left parenthesis (() and then a right parenthesis ()). The parentheses can have whitespace between them (such whitespace is insignificant).

    The item method is applied to the data that is targeted by the rest of the same path expression, which precedes the function step. The item method is used to transform that data. The SQL function or condition that is passed the path expression as argument uses the transformed data in place of the targeted data.

  • An object step is a period (.), followed by an object field name or an asterisk (*) wildcard, which stands for (the values of) all fields. A field name can be empty, in which case it must be written as "" (no intervening whitespace). A nonempty field name must start with an uppercase or lowercase letter A to Z and contain only such letters or decimal digits (0-9), or else it must be enclosed in double quotation marks (").

    An object step returns the value of the field that is specified. If a wildcard is used for the field then the step returns the values of all fields, in no special order.

  • An array step is a left bracket ([) followed by either an asterisk (*) wildcard, which stands for all array elements, or one or more specific array indexes or range specifications separated by commas (,), followed by a right bracket (]).

    An error is raised if you use both an asterisk and either an array index or a range specification. And an error is raised if no index or range specification is provided: [] is not a valid array step.

    An array index specifies a single array position, which is a whole number (0, 1, 2,...). An array index can thus be a literal whole number: 0, 1, 2,… Array position and indexing are zero-based, as in the JavaScript convention for arrays: the first array element has index 0 (specifying position 0).

    The last element of a nonempty array of any size can be referenced using the index last.

    An array index can also have the form last - N, where - is a minus sign (hyphen) and N is a literal whole number (0, 1, 2,…) that is no greater than the array size minus 1.

    The next-to-last array element can be referenced using index last-1, the second-to-last by index last-2, and so on. Whitespace surrounding the minus sign (hyphen) is ignored.

    For the array ["a", "b", 42], for example, the element at index 1 (position 1) is the string "b" — the second array element. The element at index 2, or index last, is the number 42. The element at index 0, or last-2, is "a".

    For Oracle SQL function json_transform, you can also use an index of the form last + N, where N is a whole number. This lets you append new elements to an existing array, by specifying positions beyond the current array size minus 1. Whitespace surrounding the plus sign is ignored. You cannot use an index of this form in combination with other indexes, including in a range specification (see next). An error is raised in that case.

    A range specification has the form N to M, where N and M are array indexes, and where to is preceded and followed by one or more whitespace characters.Foot 1

    Range specification N to M is equivalent to explicitly specifying all of the indexes from N to M, including N and M, in ascending order.

    In a range specification, the order of N and M is not significant; the range of the third through sixth elements can be written as 2 to 5 or 5 to 2. For a six-element array the same range can be written as 2 to last or last to 2. The range specification N to N (same index N on each side of to) is equivalent to the single index N (it is not equivalent to [N, N]).

    The order in which array indexes and ranges are specified in an array step is significant; it is reflected in the array that results from the function that uses the path expression.

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

    For example, suppose that you query using SQL/JSON function json_query with array wrapper (which wraps multiple query results to return a single JSON array), passing it a path expression with this array step: [3 to 1, 2 to 4, last-1 to last-2, 0, 0]. The data returned by the query will include an array that is made from these elements of an array in your queried data, in order:

    • second through fourth elements (range 3 to 1)

    • third through fifth elements (range 2 to 4)

    • second-from-last through next-to-last elements (range last-1 to last-2)

    • first element (index 0)

    • first element again (index 0)

    When matching the array ["1", "2", "3", "4", "5", "6", "7", "8", "9"] in your data, the array in the query result would be ["2", "3", "4", "3", "4", "5", "7", "8", "1", "1"].

    If you use array indexes that specify positions outside the bounds (0 through the array size minus 1) of an array in your data, no error is raised. The specified path expression simply does not match the data — the array has no such position. (Matching of SQL/JSON path expressions follows this rule generally, not just for array steps.)

    This is the case, for example, if you try to match an index of last-6 against an array with fewer than 7 elements. For an array of 6 elements, last is 5, so last-6 specifies an invalid position (less than 0).

    It is also the case if you try to match any array step against an empty array. For example, array steps [0] and [last] both result in no match against the data array []. Step [0] doesn't match because [] has no first element, and step [last] doesn't match because [] has no element with index -1 (array length minus 1).

    It is also the case, in particular, if you use an index last+N (N non-zero) other than with function json_transform. For json_transform this is used not to match an existing array element but to specify where, when modifying an existing array, to insert a new element.

    Because a range specification is equivalent to an explicit, ascending sequence of array indexes, any of those implicit indexes which are out of bounds cannot match any data. Like explicit indexes, they are ignored.

    Another way to think of this is that range specifications are, in effect, truncated to the nearest bound (0 or last) for a given data array. For example when matching the array ["a", "b", "c"], the range specifications last-3 to 1, 2 to last+1, and last-3 to last+1 are, in effect, truncated to 0 to 1, 2 to 2, and 0 to 2, respectively. The (implicit) out-of-bounds indexes for those ranges, last-3 (which is -1, here) and last+1 (which is 3, here), are ignored.

  • A descendant step is two consecutive periods (..), sometimes read as "dot dot", followed by a field name (which has the same syntax as for an object step).

    It descends recursively into the objects or arrays that match the step immediately preceding it (or into the context item if there is no preceding step).

    At each descendant level, for each object and for each array element that is an object, it gathers the values of all fields that have the specified name. It returns all of the gathered field values.

    For example, consider this query and data:

    json_query(some_json_column, '$.a..z' WITH ARRAY WRAPPER)
    { "a" : { "b" : { "z" : 1 },
          "c" : [ 5, { "z" : 2 } ],
          "z" : 3 },
  "z" : 4 }

    The query returns an array, such as [1,2,3], whose elements are 1, 2, and 3. It gathers the value of each field z within the step that immediately precedes the dot dot (..), which is field a. The topmost field z, with value 4, is not matched because it is not within the value of field a.

    The value of field a is an object, which is descended into.

    • It has a field z, whose value (3) is gathered. It also has a field b whose value is an object, which is descended into to gather the value of its field z, which is 1.

    • It also has a field c whose value is an array, which has an element that is an object with a field z, whose value (2) is gathered.

    The JSON values gathered are thus 3, 1, and 2. They are wrapped in an array, in an undefined order. One of the possible return values is [1,2,3].

  • A filter expression (filter, for short) is a question mark (?) followed by a filter condition enclosed in parentheses (()). A filter is satisfied if its condition is satisfied, that is, returns true.

  • A filter condition applies a predicate (Boolean function) to its arguments.Foot 2

    A filter condition is one of the following, where each of cond, cond1, and cond2 stands for a filter condition.

    • ( cond ): Parentheses are used for grouping, separating filter condition cond as a unit from other filter conditions that may precede or follow it.

    • cond1 && cond2: The conjunction (and) of cond1 and cond2, requiring that both be satisfied.

    • cond1 || cond2: The inclusive disjunction (or) of cond1 and cond2, requiring that cond1, cond2, or both, be satisfied.

    • ! ( cond ): The negation of cond, meaning that cond must not be satisfied.

    • exists ( followed by a relative path expression, followed by ): The condition that the targeted data exists (is present).

    • A relative path expression, followed by in, followed by a value list, meaning that the value is one of those in the value list.

      An in filter condition with two or more value-list elements is equivalent to a disjunction (||) of equality (==) comparisons for the elements of the value list.Foot 3 For example, these are equivalent: 

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

      A value list is ( followed by a list of zero or more scalar values and SQL/JSON variables separated by commas (,), followed by ).Foot 4 A value list can only follow in; otherwise, an error is raised.

      All values in the list (whether literal or variable) must be of the same scalar JSON-language type — for example, they must all be strings — otherwise, an error is raised.

      A JSON null value is an exception to the same-type restriction: null is always allowed in a value list (and it is matched 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.

      • Either 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 either a JSON scalar value or a SQL/JSON variable.

      • A relative path expression, followed by has substring, starts with, like, like_regex, or eq_regex, 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).

        For all of these predicates, a pattern that is the empty string ("") matches data that is the empty string. And for all except like_regex, a pattern that is a nonempty string does not match data that is the empty string. For like_regex a nonempty pattern does match empty-string data.

        • has substring means that the matching data value has the specified string as a substring.

        • starts with means that the matching data value has the specified string as a prefix.

        • like means that the JSON string data value matches the specified string, which is interpreted as a 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.

          Note:

          Unlike the case for SQL LIKE, you cannot choose the escape character for path-expression predicate like — it is always character `, GRAVE ACCENT (U+0060), also known sometimes as backquote or backtick.

          In database releases prior to 21c there is no escape character for path-expression predicate like. For such releases Oracle recommends that you avoid using character `, GRAVE ACCENT (U+0060) in like patterns.

        • like_regex means that the JSON string data value matches the specified string, which is interpreted as a SQL REGEXP LIKE regular expression pattern that uses SQL LIKE4 character-set semantics.

          like_regex is exceptional among the pattern-matching comparisons, in that its pattern matches the empty JSON string ("").

        • eq_regex is just like like_regex, except for these two differences:

          • eq_regex 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. like_regex is satisfied if any portion of the JSON string matches the pattern.

          • The eq_regex pattern does not match the empty JSON string ("").

    A SQL/JSON variable is a dollar sign ($) followed by the name of a SQL identifier that is bound in a PASSING clause for json_exists.

    The predicates that you can use in filter conditions are thus &&, ||, !, exists, ==, <>, !=, <, <=, >=, >, and in.

    As an example, the filter condition (a || b) && (!(c) || d < 42) is satisfied if both of the following criteria are met:

    • At least one of the filter conditions a and b is satisfied: (a || b).

    • Filter condition c is not satisfied or the number d is less than or equal to 42, or both are true: (!(c) || d < 42).

    A comparison predicate is ==, <>, !=Foot 5, <, <=, >=, or >, meaning equals, does not equal, is less than, is less than or equal to, is greater than or equal to, and is greater than, respectively.

    Comparison predicate ! has precedence over &&, which has precedence over ||. You can always use parentheses to control grouping.

    Without parentheses for grouping, the preceding example would be a || b && !(c) || d < 42, which would be satisfied if at least one of the following criteria is met:

    • Condition b && !(c) is satisfied, which means that each of the conditions b and !(c) is satisfied (which in turn means that condition c is not satisfied).

    • Condition a is satisfied.

    • Condition d < 42 is satisfied.

At least one side of a comparison must not be a SQL/JSON variable. The default type for a comparison is defined at compile time, based on the type(s) for the non-variable side(s). 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, $.a > 5 imposes numerical comparison because 5 is a number, $.a > "5" imposes string comparison because "5" is a string.

Tip:

For queries that you use often, use a PASSING clause to define SQL bind variables, which you use as SQL/JSON variables in path expressions. This can improve performance by avoiding query recompilation when the (variable) values change.

For example, this query passes the value of bind variable v1 as SQL/JSON variable $v1: 

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,                    
                    '$.LineItems.Part?(@.UPCCode == $v1)'
                    PASSING '85391628927' AS "v1");

Note:

Oracle SQL function json_textcontains provides powerful full-text search of JSON data. If you need only simple string pattern-matching then you can instead use a path-expression filter condition with any of these pattern-matching comparisons: has substring, starts with, like, like_regex, or eq_regex.

Here are some examples of path expressions, with their meanings spelled out in detail.

  • $ — The context item.

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

  • $.friends[0] — An object that is the first element of an array that is the value of field friends of a context-item object. The bracket notation indicates that the value of field friends is an array.

  • $.friends[0].name — Value of field name of an object that is the first element of an array that is the value of field friends of a context-item object. The second dot (.) indicates that the first element of array friends is an object (with a name field).

  • $.friends[*].name — Value of field name of each object in an array that is the value of field friends of a context-item object.

  • $.*[*].name — Field name values for each object in an array value of a field of a context-item object.

  • $.friends[3, 8 to 10, 12] — The fourth, ninth through eleventh, and thirteenth elements of an array friends (field of a context-item object). The elements are returned in the order in which they are specified: fourth, ninth, tenth, eleventh, thirteenth.

    If an array to be matched has fewer than 13 elements then there is no match for index 12. If an array to be matched has only 10 elements then, in addition to not matching index 12, the range 8 to 10 is in effect truncated to positions 8 and 9 (elements 9 and 10).

  • $.friends[12, 3, 10 to 8, 12] — The thirteenth, fourth, ninth through eleventh, and thirteenth elements of array friends, in that order. The elements are returned in the order in which they are specified. The range 10 to 8 specifies the same elements, in the same order, as the range 8 to 10. The thirteenth element (at position 12) is returned twice.

  • $.friends[last-1, last, last, last] — The next-to-last, last, last, and last elements of array friends, in that order. The last element is returned three times.

  • $.friends[last to last-1, last, last] — Same as the previous example. Range last to last-1, which is the same as range last-1 to last, returns the next-to-last through the last elements.

  • $.friends[3].cars — The value of field cars of an object that is the fourth element of an array friends. The dot (.) indicates that the fourth element is an object (with a cars field).

  • $.friends[3].* — The values of all of the fields of an object that is the fourth element of an array friends.

  • $.friends[3].cars[0].year — The value of field year of an object that is the first element of an array that is the value of field cars of an object that is the fourth element of an array friends.

  • $.friends[3].cars[0]?(@.year > 2016) — The first object of an array cars (field of an object that is the fourth element of an array friends), provided that the value of its field year is, or can be converted to, a number greater than 2016. A year value such as "2017" is converted to the number 2017, which satisfies the test. A year value such as "recent" fails the test — no match.

  • $.friends[3].cars[0]?(@.year.number() > 2016) — Same as the previous. Item method number() allows only a number or a string value that can be converted to a number, and that behavior is already provided by numeric comparison predicate >.

  • $.friends[3].cars[0]?(@.year.numberOnly() > 2016) — Same as the previous, but only if the year value is a number. Item method numberOnly() excludes a car with a year value that is a string numeral, such as "2017".

  • $.friends[3]?(@.addresses.city == "San Francisco") — An object that is the fourth element of an array friends, provided that it has an addresses field whose value is an object with a field city whose value is the string "San Francisco".

  • $.friends[*].addresses?(@city starts with "San ").zip — Zip codes of all addresses of friends, where the name of the address city starts with "San ". (In this case the filter is not the last path step.)

  • $..zip — All values of a zip field, anywhere, at any level.

  • $.friends[3]?(@.addresses.city == "San Francisco" && @.addresses.state == "Nevada") — Objects that are the fourth element of an array friends, provided that there is a match for an address with a city of "San Francisco" and there is a match for an address with a state of "Nevada".

    Note: The filter conditions in the conjunction do not necessarily apply to the same object — the filter tests for the existence of an object with city San Francisco and for the existence of an object with state Nevada. It does not test for the existence of an object with both city San Francisco and state Nevada. See Using Filters with JSON_EXISTS.

  • $.friends[3].addresses?(@.city == "San Francisco" && @.state == "Nevada") — An object that is the fourth element of array friends, provided that object has a match for city of "San Francisco" and a match for state of "Nevada".

    Unlike the preceding example, in this case the filter conditions in the conjunction, for fields city and state, apply to the same addresses object. The filter applies to a given addresses object, which is outside it.

  • $.friends[3].addresses?(@.city == $City && @.state == $State) — Same as the previous, except the values used in the comparisons are SQL/JSON variables, $City and $State. The variable values would be provided by SQL bind variables City and State in a json_exists PASSING clause: PASSING … AS "City", … AS "State". Use of variables in comparisons can improve performance by avoiding query recompilation.

Related Topics

See Also:

Parent topic: SQL/JSON Path Expression Syntax

15.2.2 SQL/JSON Path Expression Syntax Relaxation

The basic SQL/JSON path-expression syntax is relaxed to allow implicit array wrapping and unwrapping. This means that you need not change a path expression in your code if your data evolves to replace a JSON value with an array of such values, or vice versa. Examples are provided.

Basic SQL/JSON Path Expression Syntax defines the basic SQL/JSON path-expression syntax. The actual path expression syntax supported relaxes that definition as follows:

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

  • If a path-expression step targets (expects) a non-array but the actual data presents an array then the array is implicitly unwrapped.

This relaxation allows for the following abbreviation: [*] can be elided 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 object step [*].prop, which stands for the value of field prop of each element of a given array of objects, can be abbreviated as .prop, and the object step .prop, which looks as though it stands for the prop value of a single object, stands also for the prop 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 your data originally contains objects that have field Phone whose value is a single object with fields type and number, the path expression $.Phone.number, which matches a single phone number, can still be used if the data evolves to represent an array of phones. Path expression $.Phone.number 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.

Here are some example path expressions from section Basic SQL/JSON Path Expression Syntax, together with an explanation of equivalences.

  • $.friends – The value of field friends of either:

    • The (single) context-item object.

    • (equivalent to $[*].friends) Each object in the context-item array.

  • $.friends[0].name – Value of field name for any of these objects:

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

    • (equivalent to $.friends.name) The value of field friends of the context-item object.

    • (equivalent to $[*].friends.name) The value of field friends of each object in the context-item array.

    • (equivalent to $[*].friends[0].name) The first element of each array that is the value of field friends of each object in the context-item array.

    The context item can be an object or an array of objects. In the latter case, each object in the array is matched for a field friends.

    The value of field friends can be an object or an array of objects. In the latter case, the first object in the array is used.

  • $.*[*].name – Value of field name for any of these objects:

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

    • (equivalent to $.*.name) The value of a field of the context-item object.

    • (equivalent to $[*].*.name) The value of a field of an object in the context-item array.

    • (equivalent to $[*].*[*].name) Each object in an array value of a field of an object in the context-item array.

Related Topics

Parent topic: SQL/JSON Path Expression Syntax

15.3 SQL/JSON Path Expression Item Methods

The Oracle item methods available for a SQL/JSON path expression are described.

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 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 acts as a filter, removing the targeted data from the result set.

If an item-method conversion fails for any reason, such as its argument being of the wrong type, then the path cannot be matched (it refers to no data), and no error is raised. In particular, this means that such an error is not handled by an error clause in the SQL function or condition to which the path expression is passed.

An item method always transforms the targeted JSON data to (possibly other) JSON data. But a query using a path expression (with or without an item method) can return data as a SQL data type that does not support JSON data. That is the case for a json_value query or an equivalent dot-notation query.

  • The return value of SQL/JSON function json_query (or a json_table column expression that has 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.

  • The return value of SQL/JSON function json_value (or a json_table column expression that has json_value semantics) is always of a SQL data type other than JSON type: a scalar type, an object type, or a collection type; it does not return JSON data. Though the path expression targets JSON data and an item method transforms targeted JSON data to JSON data, json_value converts the resulting JSON data to a scalar SQL value in a data type that does not necessarily support JSON data.

  • 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 an item method. Thus, a dot-notation query with an item method always returns a SQL scalar value.

Application of an Item Method to an Array

With the exception of item methods count(), size() and type(), 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 non-array 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"]', '$[*].stringOnly()'
                  WITH ARRAY WRAPPER)
  FROM dual;

    returns this JSON array: ["alpha", "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() and type() 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()')
  FROM dual;

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.

Note:

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

Item-Method Descriptions

The following item methods are data-type conversion methods: binary(), boolean(), booleanOnly(), date(), dateWithTime(), number(), numberOnly(), double(), dsInterval(), float(), number(), numberOnly(), string(), stringOnly(), timestamp(), and ymInterval().

A targeted JSON value targeted by a data-type conversion item method is said to be interpreted as a value of a given SQL data type. This means that, in a query that has json_value semantics, it 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 value is thus treated by string() as "true" or "false"; a null value is treated as "null"; and a number is represented in a canonical string form.

The data-type conversion methods with “only” in their name are the same as the corresponding methods with names without “only”, except that the former convert only JSON values that are of the given type (e.g., number) to the related SQL data type (e.g. NUMBER). The methods without “only” in the name allow conversion, when possible, of any JSON value to the given SQL data type. (When an “only” method targets an array, the conversion applies to each array element, as usual.)

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

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

  • binary(): A SQL RAW interpretation of the targeted JSON value. Only JSON data stored as JSON type matches.

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

  • booleanOnly(): A SQL VARCHAR2(20) interpretation of the targeted JSON data, but only if it is a JSON Boolean value; otherwise, there is no match. Acts as a filter, allowing matches only for JSON Boolean values.

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

  • count(): The number of targeted JSON values, regardless of their types.

  • date(): A SQL DATE interpretation of the targeted JSON string. The targeted string data must be in a supported ISO 8601 format for a date or a date with time; otherwise, there is no match. If the JSON string has an ISO 8601 date-with-time format then the SQL DATE instance has its time component truncated (set to zero).

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

  • float(): A SQL BINARY_FLOAT interpretation of the targeted JSON string or number. Only JSON data stored as JSON type matches.

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

  • length(): The number of characters in the targeted JSON string, interpreted as a SQL NUMBER.

  • lower(): The lowercase string that corresponds to the characters in the targeted JSON string.

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

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

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

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

  • 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. Acts as a filter, allowing matches only for JSON numbers.

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

    • 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, in addition to json_value and json_table. 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.)

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

  • stringOnly(): A SQL VARCHAR2(4000) interpretation of the targeted scalar JSON value, but only if it is a JSON string; otherwise, there is no match. Acts as a filter, allowing matches only for JSON strings.

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

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

  • type(): The name of the JSON data type of the targeted data, interpreted as a SQL VARCHAR2(20) value. This item method can be used with json_query, in addition to json_value and json_table. 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.)

    • "null" for a value of null.

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

    • "number" for a number.

    • "string" for a string.

    • "array" for an array.

    • "object" for an object.

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

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

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

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

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

    • "yearmonthInterval" for a value 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.

  • 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 binary(), boolean(), booleanOnly(), date(), dateWithTime(), dsInterval(), float(), length(), lower(), number(), numberOnly(), string(), stringOnly(), timestamp(), upper(), and ymInterval() are Oracle extensions to the SQL/JSON standard. The other item methods, abs(), ceiling(), double(), floor(), size(), and type() are part of the standard.

Item methods avg(), count(), maxNumber(), minNumber(), maxString(), minString(), and sum() 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 15-1 and Example 15-2.

Item Methods and JSON_VALUE RETURNING Clause

Because some item methods interpret the targeted JSON data as if it were of a SQL data type, they can be used with json_value in place of a RETURNING clause, and they can be used with json_table in place of a column type specification. That is, the item methods can be used to specify the returned SQL data type for the extracted JSON data.

You can also use such 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 controlled by both an 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 15-1 Compatibility of Type-Conversion Item Methods and RETURNING Types

Item Method Compatible RETURNING Clause Data Types
string(), stringOnly(), minString(), or maxString() VARCHAR2 or CLOB, except that string() returns SQL NULL for a JSON null value
number(), numberOnly(), avg(), sum(), count(), minNumber(), or maxNumber() NUMBER
double() BINARY_DOUBLE
float() BINARY_FLOAT
date() DATE, with truncated time component (set to zero), corresponding to RETURNING DATE TRUNCATE TIME
dateWithTime() DATE, with time component, corresponding to RETURNING DATE PRESERVE TIME
timestamp() TIMESTAMP
ymInterval() INTERVAL YEAR TO MONTH
dsInterval() INTERVAL DAY TO SECOND
boolean() or booleanOnly() VARCHAR2
binary() RAW

Using a RETURNING clause or a 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 RETURNING VARCHAR2(150) then the data type of the returned data is VARCHAR2(150), not VARCHAR2(4000).

Example 15-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(po_document, 
                  '$.LineItems[*].Quantity.avg()')
  FROM j_purchaseorder;

Example 15-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 document collection 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(po_document, 
                      '$.LineItems[*].Quantity.avg()'))
  FROM j_purchaseorder;

Related Topics

Parent topic: SQL/JSON Path Expressions

15.4 Types in Comparisons

Comparisons in SQL/JSON path-expression filter conditions are statically typed at compile time. If the effective types of the operands of a comparison are not known to be the same then an attempt is sometimes made to reconcile them by type-casting.

A SQL/JSON path expression targets JSON data, so the operands of a comparison are JSON values. Type comparison of JSON values is straightforward: JSON data types string, number, null, object, and array are mutually exclusive and incomparable.

But comparison operands are sometimes interpreted (essentially cast) as values of SQL data types. This is the case, for example, when some item methods, such as number(), are used. This section addresses the type-checking of such effective values.

You can prevent such type-casting by explicitly using one of the “only” item methods. For example, applying method numberOnly() prevents implicit type-casting to a number.

SQL is a statically typed language; types are determined at compile time. The same applies to SQL/JSON path expressions, and in particular to comparisons in filter conditions. This means that you get the same result for a query regardless of how it is evaluated — whether functionally or using features such as indexes, materialized views, and In-Memory scans.

To realize this:

  • If the types of both operands are known and they are the same then type-checking is satisfied.

  • If the types of both operands are unknown then a compile-time error is raised.

  • If the type of one operand is known and the other is unknown then the latter operand is cast to the type of the former.

    For example, in $.a?(@.b.c == 3) the type of $a.b.c is unknown at compile time. The path expression is compiled as $.a?(@.b.c.number() == 3). At runtime an attempt is thus made to cast the data that matches $a.b.c to a number. A string value "3" would be cast to the number 3, satisfying the comparison.Foot 7

  • If the types of both operands are known and they are not the same then an attempt is made to cast the type of one to the type of the other. Details are presented below.

An attempt is made to reconcile comparison operands used in the following combinations, by type-casting. You can think of a type-casting item method being implicitly applied to one of the operands in order to make it type-compatible with the other operand.

  • Number compared with double — double() is implicitly applied to the number to make it a double value.

  • Number compared with float — float() is implicitly applied to the number to make it a float value.

  • String in a supported ISO 8601 format compared with date — date() is implicitly applied to the string to make it a date value. For this, the UTC time zone (Coordinated Universal Time, zero offset) is used as the default, taking into account any time zone specified in the string.

  • String in a supported ISO 8601 format compared with timestamp without time zone — timestamp() is implicitly applied to the string to make it a timestamp value. For this, the UTC time zone (Coordinated Universal Time, zero offset) is used as the default, taking into account any time zone specified in the string.

Comparison operands used in the following combinations are not reconciled; a compile-time error is raised.

  • Number, double, or float compared with any type other than number, double, or float.

  • Boolean compared with any type other than Boolean.

  • Date or timestamp compared with string, unless the string has a supported ISO 8601 format.

  • Date compared with any non-date type other than string (in supported ISO 8601 format).

  • Timestamp (with or without time zone) compared with any non-timestamp type other than string (in supported ISO 8601 format).

  • Timestamp compared with timestamp with time zone.

  • JSON null type compared with any type other than JSON null.

Related Topics

Parent topic: SQL/JSON Path Expressions


Footnote Legend

Footnote 1: The to in a range specification is sometimes informally called the array slice operator.
Footnote 2: A filter condition or a filter expression is sometimes informally called a "predicate". But they are actually applications of predicates to arguments.
Footnote 3: An in condition with a singleton value list is equivalent to a single equality comparison. An in condition with no values is unmatchable.
Footnote 4: An empty value list (no values or variables) does not raise an error, but it also is never matched.
Footnote 5: != is an Oracle alias for the SQL/JSON standard comparison predicate <>.
Footnote 6: 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 7: To prevent such casting here, you can explicitly apply item method numberOnly(): $.a?(@.b.c.numberOnly() == 3). Data with a string value "3" would simply not match; it would be filtered out.