17 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 or conditions against JSON data, to select or test 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 presented. How they act on targeted JSON data is described in general terms and for each item method. - Types in Filter-Condition 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
17.1 Overview of SQL/JSON Path Expressions
Oracle Database provides SQL access to JSON data using SQL/JSON path expressions.
SQL/JSON path expressions are somewhat analogous to XQuery or XPath expressions for XML data. They provide SQL access to JSON data similarly to how SQL/XML allows SQL access to XML data using XQuery 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 nonscalar. 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 a 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 the text of a SQL/JSON path expression is 32K bytes. However, the effective length of a path 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 32K bytes.
Related Topics
See Also:
Ask Tom video SQL-JSON Path Expressions for an overview
Parent topic: SQL/JSON Path Expressions
17.2 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 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. - Negation in Path Expressions
Negation in a path expression can be confusing when the data matched by the path is multiple. Some simple examples are explained.
Parent topic: SQL/JSON Path Expressions
17.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 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 (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. Matching data is located by 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 ((
), possibly an argument list, and then a right parenthesis ()
). The parentheses can have whitespace between them (such whitespace is insignificant).The item method is applied to (1) the data that is targeted by the rest of the same path expression, which precedes the function step and (2) the arguments, if any, within the parentheses. The item method is used to transform the targeted data. The SQL function or condition that is passed the path expression as an argument uses the transformed data in place of the targeted data.
Only some item methods allow for an argument list between the parentheses. The arguments are scalar JSON values, separated by commas (
,
). Some item methods require one or more such arguments. Other methods allow, but don't require, such arguments. -
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 index0
(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) andN
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 indexlast-2
, and so on. Whitespace surrounding the minus sign (hyphen) is ignored.For the array
["a", "b", 42]
, for example, the element at index1
(position 1) is the string"b"
— the second array element. The element at index2
, or indexlast
, is the number42
. The element at index0
, orlast-2
, is"a"
.For Oracle SQL function
json_transform
, you can also use an index of the formlast + N
, whereN
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
toM
, whereN
andM
are array indexes, and whereto
is preceded and followed by one or more whitespace characters.Foot 1Range specifications
N
toM
andM
toN
are equivalent. Each is equivalent to explicitly specifyingN
,M
, and the indexes betweenN
andM
, all in ascending order.That is, the order of
N
andM
is not significant; the range of the third through sixth elements can be written as2 to 5
or5 to 2
. For a six-element array the same range can be written as2 to last
orlast to 2
. The range specificationN to N
(same indexN
on each side ofto
) is equivalent to the single indexN
(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
is5
, solast-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, if you use an index
last+N
(N
non-zero) other than with functionjson_transform
. Forjson_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
orlast
) for a given data array. For example when matching the array["a", "b", "c"]
, the range specificationslast-3 to 1
,2 to last+1
, andlast-3 to last+1
are, in effect, truncated to0 to 1
,2 to 2
, and0 to 2
, respectively. The (implicit) out-of-bounds indexes for those ranges,last-3
(which is-1
, here) andlast+1
(which is3
, 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 are1
,2
, and3
. It gathers the value of each fieldz
within the step that immediately precedes the dot dot (..
), which is fielda
. The topmost fieldz
, with value 4, is not matched because it is not within the value of fielda
.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 fieldb
whose value is an object, which is descended into to gather the value of its fieldz
, which is1
. -
It also has a field
c
whose value is an array, which has an element that is an object with a fieldz
, whose value (2
) is gathered.
The JSON values gathered are thus
3
,1
, and2
. 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 (a Boolean function) to its arguments. It is defined recursively as one of the following, where each of
cond
,cond1
, andcond2
stands for a filter condition.Foot 2-
! cond
: The negation ofcond
, meaning thatcond
must not be satisfied.!
is a prefix unary predicate. (See Negation in Path Expressions.) -
( cond )
: Parentheses are used for grouping, separating filter conditioncond
as a unit from other filter conditions that may precede or follow it.You can also use parentheses wherever they have no effect, if you find the code more readable. For example, if you prefer you can place them around the argument(s) to a predicate, as in
exists
(cond)
instead of justexists
cond
.Parentheses are needed whenever the beginning and end of the condition argument are otherwise unclear. They're needed, for instance, in
!(cond)
whenever cond is a comparison condition (see below). For example, you must use!(@.x > 5)
, not!@.x > 5
. (But you can use either!exists@.x
or!(exists@.x)
.) -
cond1 && cond2
: The conjunction (and) ofcond1
andcond2
, requiring that both be satisfied.&&
is an infix binary predicate. -
cond1 || cond2
: The inclusive disjunction (or) ofcond1
andcond2
, requiring thatcond1
,cond2
, or both, be satisfied.||
is an infix binary predicate. -
exists
followed by a relative path expression: The condition that the targeted data exists (is present).exists
is a prefix unary predicate. -
A relative path expression, followed by
in
, followed by a value list, meaning that the value is one of those in the value list.in
is an infix binary predicate.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 JSON literal values or SQL/JSON variables separated by commas (,
), followed by)
.Foot 4 A value list can only followin
; otherwise, an error is raised.-
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. Conditionin
is satisfied if any of the listed values is equal to the targeted data. -
Otherwise (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, they must all be strings — otherwise, an error is raised.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 anull
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
has substring
,starts with
,like
,like_regex
,regex like
,regex
,eq_regex
,ci_like_regex
, orci_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 exceptlike_regex
, a pattern that is a nonempty string does not match data that is the empty string. Forlike_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 SQLLIKE
pattern that uses SQLLIKE4
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 predicatelike
— 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) inlike
patterns. -
like_regex
or its synonymregex like
(no underscore) means that the JSON string data value matches the specified string, which is interpreted as a SQLREGEXP LIKE
regular expression pattern that uses SQLLIKE4
character-set semantics.ci_like_regex
is the same aslike_regex
, except that matching is case-insensitive.like_regex
andci_like_regex
are exceptional among the pattern-matching comparisons, in that their pattern matches the empty JSON string (""
). -
eq_regex
and its synonymsregex equals
(no underscore) andregex
are the same aslike_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 (""
).
ci_regex
is the same aseq_regex
, except that matching is case-insensitive. -
-
-
A SQL/JSON variable is a dollar sign (
$
) followed by the name of a variable that is bound in aPASSING
clause. (See PASSING Clause for SQL Functions and Conditions for the required syntax of a SQL/JSON variable name.)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. (See Negation in Path Expressions, for more about using the not-equals predicate,<>
or its Oracle alias!=
.)The predicates that you can use in filter conditions are thus
&&
,||
,!
,exists
,==
,<>
,!=
,<
,<=
,>=
,>
,in
,has substring
,starts with
,like
,like_regex
,regex like
,regex
,eq_regex
,ci_like_regex
, andci_regex
.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
andb
is satisfied:(a || b)
. -
Filter condition
c
is not satisfied or the numberd
is less than or equal to 42, or both are true:(!(c) || d < 42)
.
Condition predicate
!
has precedence over&&
, which has precedence over||
. You can always use parentheses to control grouping.If the preceding example,
(a || b) && (!(c) || d < 42)
, did not use parentheses for grouping, so that it was justa || b && !(c) || d < 42
, then it would instead be satisfied if at least one of the following criteria is met:-
Condition
b && !(c)
is satisfied, which means that each of the conditionsb
and!(c)
is satisfied (which in turn means that conditionc
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.
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.
Otherwise, 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
.
Basic Path-Expression Examples
Here are some examples of path expressions, with their meanings spelled out in detail.
-
$
— The context item. -
$.friends
— The value of fieldfriends
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 fieldfriends
of a context-item object. The bracket notation indicates that the value of fieldfriends
is an array. -
$.friends[0].name
— Value of fieldname
of an object that is the first element of an array that is the value of fieldfriends
of a context-item object. The second dot (.
) indicates that the first element of arrayfriends
is an object (with aname
field). -
$.friends[*].name
— Value of fieldname
of each object in an array that is the value of fieldfriends
of a context-item object. -
$.*[*].name
— Fieldname
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 arrayfriends
(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 index12
, the range8 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 arrayfriends
, in that order. The elements are returned in the order in which they are specified. The range10 to 8
specifies the same elements, in the same order, as the range8 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 arrayfriends
, in that order. The last element is returned three times. -
$.friends[last to last-1, last, last]
— Same as the previous example. Rangelast to last-1
, which is the same as rangelast-1 to last
, returns the next-to-last through the last elements. -
$.friends[3].cars
— The value of fieldcars
of an object that is the fourth element of an arrayfriends
. The dot (.
) indicates that the fourth element is an object (with acars
field). -
$.friends[3].*
— The values of all of the fields of an object that is the fourth element of an arrayfriends
. -
$.friends[3].cars[0].year
— The value of fieldyear
of an object that is the first element of an array that is the value of fieldcars
of an object that is the fourth element of an arrayfriends
. -
$.friends[3].cars[0]?(@.year > 2016)
— The first object of an arraycars
(field of an object that is the fourth element of an arrayfriends
), provided that the value of its fieldyear
is, or can be converted to, a number greater than 2016. Ayear
value such as"2017"
is converted to the number2017
, which satisfies the test. Ayear
value such as"recent"
fails the test — no match. -
$.friends[3].cars[0]?(@.year.number() > 2016)
— Same as the previous. Item methodnumber()
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 theyear
value is a number. Item methodnumberOnly()
excludes a car with ayear
value that is a string numeral, such as"2017"
. -
$.friends[3]?(@.addresses.city == "San Francisco")
— An object that is the fourth element of an arrayfriends
, provided that it has anaddresses
field whose value is an object with a fieldcity
whose value is the string"San Francisco"
. -
$.friends[*].addresses?(@.city starts with "San ").zip
— Zip codes of alladdresses
offriends
, where the name of the addresscity
starts with "San ". (In this case the filter is not the last path step.) -
$.friends[*].addresses?(@.city has substring "Fran").zip
— Zip codes of alladdresses
offriends
, where the name of the addresscity
contains "Fran". -
$.friends[*].addresses?(@.city like "S_n%").zip
— Zip codes of alladdresses
offriends
, where the name of the addresscity
is "S" followed by any single character, then "n", then any sequence of zero or more characters. Underscore (_
) matches a single character, and percent (%
) matches multiple characters. -
$.friends[*].addresses?(@.city like_regex "n +F").zip
— Zip codes of alladdresses
offriends
, where the name of the addresscity
contains "n" followed by at least one space character. Matching is case-sensitive, and it is not anchored at the start of thecity
string. -
$.friends[*].addresses?(@.city ci_regex "s.+o").zip
— Zip codes of alladdresses
offriends
, where the name of the addresscity
starts with "s" or "S" and ends with "o" or "O". Matching is case-insensitive (ci_
) and the entirecity
string must match (nolike_
). -
$..zip
— All values of azip
field, anywhere, at any level. -
$.friends[3]?(@.addresses.city == "San Francisco" && @.addresses.state == "Nevada")
— Objects that are the fourth element of an arrayfriends
, provided that there is a match for an address with acity
of"San Francisco"
and there is a match for an address with astate
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 arrayfriends
, provided that object has a match forcity
of"San Francisco"
and a match forstate
of"Nevada"
.Unlike the preceding example, in this case the filter conditions in the conjunction, for fields
city
andstate
, apply to the sameaddresses
object. The filter applies to a givenaddresses
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 variablesCity
andState
in aPASSING
clause:PASSING
…AS "City",
…AS "State"
. Use of variables in comparisons can improve performance by avoiding query recompilation.
Related Topics
- Using Filters with JSON_EXISTS
- RETURNING Clause for SQL Functions
- Diagrams for Basic SQL/JSON Path Expression Syntax
- Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
- ISO 8601 Date, Time, and Duration Support
- SQL/JSON Path Expression Syntax Relaxation
- Negation in Path Expressions
- SQL/JSON Path Expression Item Methods
See Also:
-
ISO 8601 for information about the ISO date formats
-
Oracle Database SQL Language Reference for information about SQL condition
REGEXP LIKE
-
Oracle Database SQL Language Reference for information about SQL condition
LIKE
andLIKE4
character-set semantics
Parent topic: SQL/JSON Path Expression Syntax
17.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 nonarray 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 fieldfriends
of either:-
The (single) context-item object.
-
(equivalent to
$[*].friends
) Each object in the context-item array.
-
-
$.friends[0].name
– Value of fieldname
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 fieldfriends
of the context-item object. -
(equivalent to
$[*].friends.name
) The value of fieldfriends
of each object in the context-item array. -
(equivalent to
$[*].friends[0].name
) The first element of each array that is the value of fieldfriends
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 fieldname
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.
-
Parent topic: SQL/JSON Path Expression Syntax
17.2.3 Negation in Path Expressions
Negation in a path expression can be confusing when the data matched by the path is multiple. Some simple examples are explained.
A negation filter condition has this form: predicate
!
(read "not") followed by a filter condition, perhaps in
parentheses: !(
condition
)
. Its semantics are to succeed (return true) whenever the
condition
fails (returns false).
SQL/JSON condition json_exists
checks for the
existence of given JSON data. And the SQL query functions, such as
json_value
, find and return existing JSON data. Predicate
!
checks that the existence posited by its argument condition is
false, which means it checks for nonexistence.
The infix not-equals comparison predicate, which can be written
!=
or <>
, checks whether its two arguments
are different (returning true) or the same (returning false).
That all likely sounds straightforward, but when the data matched by a path expression is multiple, things can seem to get complicated...
Consider these documents:
{"customer" : "A",
"locations" : [ {"country" : "France"} ]}
{"customer" : "B",
"locations" : [ {"country" : "Germany"} ]}
{"customer" : "C",
"locations" : [ {"country" : "France"}, {"country" : "Spain"} ]}
{"customer" : "D",
"locations" : [ {"country" : "Spain"} ]}
{"customer" : "E",
"locations" : []}
{"customer" : "F"}
Consider these path expressions:
-- Path 1: locations that include the country of France.
$.locations?( @.country == "France" )
-- Path 2: locations that include a country other than France.
$.locations?( @.country != "France" )
-- Path 3: locations that do NOT include the country of France.
$.locations?( !(@.country == "France") )
-- Path 4: locations with one or more countries, NONE of which is France.
$.locations?( exists@.country && !(@.country == "France") )
-- Path 5: locations with a country other than France or Germany.
$.locations?( (@.country != "France") || (@.country != "Germany") )
-
Path 1 returns the documents for customers A and C, because their
locations
array has an element with fieldcountry
whose value is"France"
. -
Path 2 returns the documents for customers B, C, and D, because their
locations
array has an element with fieldcountry
whose value is not"France"
("Spain"
for C and D, "Germany"
for B). No path returns the document for customer E, because itslocations
array has no such element (country France or not): itslocations
array has no elements at all. And none of the paths return the document for customer F, because it has nolocations
field. -
Path 3 returns the documents for customers B, D, and E, because their
locations
array does not have an element with fieldcountry
whose value is"France"
. Path 3 does not return the documents for customers A and C, because theirlocations
array does have an element with fieldcountry
whose value is"France"
. And it doesn't return the document for customer F, because it has nolocations
field.Note in particular that paths 2 and 3 have different results. Including a country other than France isn't the same thing as not including the country of France. Path 2 requires a country that is not France, whereas path 3 requires there not be any country whose value is France. Path 2 includes C and excludes E, because Germany is not France and E has no country. Path 3 includes E and excludes C, because E has no country and C's locations include France.
-
Path 4 returns the documents for customers B and D. It is the same as path 3, except that it requires that field
country
exist, which excludes the document for customer E. -
Path 5 returns the documents for all customers except F, which has no
locations
field. The!=
tests succeed for customer E because it has nocountry
field to compare. And any document with acountry
field succeeds because every country is either not France or not Germany. Only the document for customer F has no country field.
Consider also these paths that use predicate in
:
-- Path 6: locations that include France or Germany.
@.locations?( @.country in ("France", "Germany") )
-- Path 7: locations that do NOT include France or Germany.
@.locations?( !(@.country in ("France", "Germany")) )
-- Path 8: locations that have one or more countries, NONE of which is France or Germany.
@.locations?( exists(@.country)
&&
!(@.country in ("France", "Germany")) )
-
Path 6 returns the documents for customers A, B, and C, because their
locations
array has acountry
field whose value isin
the set ("France"
,"Germany"
) —"France"
for A and C,"Germany"
for B. -
Path 7 excludes documents for customers in France and Germany. It returns the documents for customer D, which is located only in Spain, and customer E, which has an empty
locations
array. It doesn't return the document for customer F because it has nolocations
field. -
Path 8 returns only the document for customer D. Documents for customers A, B, and C are excluded because they have a location in France or Germany. The document for customer E is excluded because it has no
country
field, and the document for customer F is excluded because it has nolocations
field.
Parent topic: SQL/JSON Path Expression Syntax
17.3 SQL/JSON Path Expression Item Methods
The Oracle item methods available for a SQL/JSON path expression are presented. How they act on targeted JSON data is described in general terms and for each item method.
General Behavior of Item Methods
An item method is applied to the JSON data that is targeted by (the rest of) the path expression that is terminated by that method. The method is used to transform that data.
The targeted data acts as the first, and typically the only, argument to
the method; it is implicit. Some item methods require or accept one or more
explicit, comma-separated arguments, within the parentheses
(()
) that follow the method name.
For example: $myArray.indexOf("car", 3, 20)
. That
application of method indexOf
to four arguments targets an array in
the data, myArray
, looking for the first occurrence of the value
"car"
as an element, but skipping the first 3 elements, and not
looking at more than 20 elements (so not checking past position 23). The first
explicit argument ("car"
) is required; the other two are
optional.
The SQL function or condition that is passed the path expression uses
the transformed data in place of the targeted data. In some cases the
application of an item method limits what data can match a path expression. Such
match-limiting can either (1) raise an error (for json_value
semantics) or (2) act as a filter (when used with
json_exists
), removing nonmatching targeted data from the result
set.
If an item-method conversion fails for any reason, such as the targeted
data being of the wrong type, then the path cannot be matched (it refers to
no data), and error-handling applies for the SQL function or condition to which the
path expression is passed. For json_value
semantics, the default
error-handling behavior is to return SQL NULL
on error. For
json_exists
semantics, the default behavior is to return
FALSE
, which means that the nonmatch just serves as a
filter.
An item method always transforms the targeted JSON data to (possibly other) JSON data, which is always scalar. But a query using a path expression (with or without an item method) can return data as a SQL scalar data type.
That's the case for a query using json_value
semantics, whether explicitly with json_value
or implicitly
with either dot-notation syntax or a json_table
column
specification that returns a scalar SQL value. Item methods behave the same in these
contexts.
-
The return value of
json_query
or ajson_table
column expression withjson_query
semantics is always JSON data, of SQL data typeJSON
,VARCHAR2
,CLOB
, orBLOB
. The default return data type isJSON
if the targeted data is also ofJSON
type. Otherwise, it isVARCHAR2
. -
A dot-notation query with an item method implicitly applies
json_value
with aRETURNING
clause that specifies a scalar SQL type to the JSON data that is targeted and possibly transformed by the item method. Thus, a dot-notation query with an item method always returns a SQL scalar value. -
The return value of a query that has
json_value
semantics (whether fromjson_query
, ajson_table
column expression, or dot notation) is always of a scalar SQL data type other thanJSON
;Foot 6 it does not return JSON data. Though the path expression targets JSON data, and an item method always transforms targeted JSON data to JSON data,json_value
query semantics convert the transformed JSON data to a scalar SQL value in a data type that does not necessarily support JSON data.
Note:
Item methods can also be used with SQL/JSON condition
json_exists
, which checks for the existence of a particular
value within JSON data. In this context, an item method always appears at the
end of a SQL/JSON path expression used in a filter-condition comparison. The
transformed JSON value that results from using the item method isn't returned as
a SQL value.
Application of an Item Method to an Array
With the exception of item methods count()
,
size()
, size2()
, type()
, and
vector()
, if an array is targeted by an item method then the
method is applied to each of the array elements, not to the array itself. The
results of these applications are returned in place of the array, as multiple
values. That is, the resulting set of matches includes the converted array elements,
not the targeted array.
(This is similar, in its effect, to the implied unwrapping of an array when a nonarray is expected for an object step.)
For example, $.a.method()
applies item-method
method()
to each element of array
a
, to convert that element and use it in place of the array.
-
For a
json_value
query that specifies a SQL collection type (varray or nested table) as the return type, an instance of that collection type is returned, corresponding to the JSON array that results from applying the item method to each of the array elements, unless there is a type mismatch with respect to the collection type definition. -
For a
json_value
query that returns any other SQL type, SQLNULL
is returned. This is because mapping the item method over the array elements results in multiple return values, and that represents a mismatch forjson_value
. -
For
json_query
or ajson_table
column expression withjson_query
semantics, you can use a wrapper clause to capture all of the converted array-element values as an array. For example, this query:SELECT json_query('[ "alpha", 42, "10.4" ]', '$[*].string()' WITH ARRAY WRAPPER) FROM dual;
returns this JSON array:
[ "alpha", "42", "10.4" ]
. The SQL data type returned is the same as the JSON data that was targeted:JSON
,VARCHAR2(4000)
,CLOB
, orBLOB
.
Item methods count()
, size()
,
size2()
, type()
, and vector()
are exceptional in this regard. When applied to an array they treat it as
such, instead of acting on its elements. For example:
SELECT json_value('[ 19, "Oracle", {"a":1}, [1,2,3] ]', '$.type()')
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.
Data-Type Conversion Item Methods
The following item methods are data-type conversion methods:
binary()
, binaryOnly()
,
boolean()
, booleanOnly()
,
date()
, dateTimeOnly()
,
dateWithTime()
, idOnly()
,
number()
, numberOnly()
,
double()
, dsInterval()
,
float()
, number()
,
numberOnly()
, string()
,
stringify()
, stringOnly()
,
timestamp()
, toBoolean()
,
toDateTime()
, vector()
Foot 7, and ymInterval()
.
As mentioned, an item method always transforms its targeted JSON data to
(possibly other) JSON data. But when the method is used in a
json_value
query, (or another function that returns SQL data),
the JSON data resulting from its transformation is in turn converted to a SQL return
value. If present, a RETURNING
clause specifies the SQL type for
that data; if absent, each item method results in a particular default SQL type. For
example, the default SQL type for item-method string()
is
VARCHAR2(4000)
.
In a query that has json_value
semantics, a value
targeted by a data-type conversion item method can
generally be thought of as being interpreted as a SQL value of that method's
default SQL data type, meaning that the value is handled as if it were controlled by
a RETURNING
clause with that SQL data type.
For example, item-method string()
interprets its target
as would json_value
with clause RETURNING
VARCHAR2(4000)
. A Boolean JSON value is thus treated by
string()
as "true"
or
"false"
; a null
value is treated by
string()
as "null"
; and a number is treated by
string()
as a numeral in a canonical string form.
Most data-type conversion methods can be used at the end of a SQL/JSON
path expression, which means that a query with json_value
semantics
can return the corresponding SQL scalar value. The type-conversion methods that
cannot be used at the end of a path have names that begin with
"to".
The data-type conversion methods without "only" in their name allow conversion, when possible, of a JSON value — in some cases even a value that is not in the type family named by the method — to the method's JSON type, and they then interpret the result as a value of the method's default SQL type.
The "only" data-type conversion methods convert only JSON values that are in the type family named by the method. Other targeted values are not matched by the path expression. The "only" methods convert the value to the default JSON-language type for the method, and then interpret the result as a value of the method's default SQL type.
For numberOnly()
, the family type is number (numeric
JSON types), its default JSON type for the family is number
, and
the default SQL type is NUMBER
. For
dateTimeOnly()
, the default family type is
timestamp
, and the default SQL type is
TIMESTAMP
.
(When an “only” method targets an array, the conversion applies to each array element, as usual.)
An aggregate method, such as avg()
, converts targeted
values to the method's default type, and then interprets them as the method's
default SQL type. For avg()
, targeted values of type
number
, float
, and double
are
all converted to JSON type number
, and are interpreted as SQL
NUMBER
values.
Nonaggregate methods, such as abs()
, do no conversion
within the relevant type family. So abs()
converts the string
"-3.14"
to a JSON number, but it leaves a targeted JSON float
or double value as it is, and interprets it as a SQL BINARY_FLOAT
or BINARY_DOUBLE
value, respectively.
Table 17-1 Item Method Data-Type Conversion
Item Method | Input JSON-Language Type | Output JSON-Language Type | SQL Type | Notes |
---|---|---|---|---|
|
binary (both identifier and nonidentifier) |
binary |
|
None. |
|
string |
binary |
|
Error if any input characters are not hexadecimal numerals. |
|
binary (both identifier and nonidentifier) |
binary |
|
None. |
|
boolean |
boolean |
|
None. |
|
string |
boolean |
|
Error if input is not |
|
boolean |
boolean |
|
None. |
|
date, timestamp, or timestamp with time zone |
date |
|
JSON output is UTC with no time components. |
|
string |
date |
|
JSON output is UTC with no time components. Error if input is not ISO UTC, with no time components. |
|
date, timestamp, or timestamp with time zone |
timestamp |
|
None. |
|
date, timestamp, or timestamp with time zone |
date |
|
UTC, with no fractional seconds. |
|
string |
date |
|
UTC, with no fractional seconds. Error if input is not ISO. |
|
number, double, or float |
double |
|
None. |
|
string |
double |
|
Error if input is not a number representation. |
|
number, double, or float |
float |
|
Error if input is out of range. |
|
string |
float |
|
Error if input is not a number representation. |
|
binary identifier |
binary identifier |
|
None. |
|
number, double, or float |
number |
|
Error if input is out of range. |
|
string |
number |
|
Error if input is not a number representation. |
|
number, double, or float |
number |
|
None. |
|
Any. |
string |
|
Resulting SQL value is in the database character set, even though the output JSON-language string is UTF-8. |
|
Any. |
string |
|
Same as Method |
|
string |
string |
|
Same as |
|
date, timestamp, or timestamp with time zone |
timestamp |
|
None. |
|
string |
timestamp |
|
Error if input is not ISO UTC. |
|
boolean |
boolean |
|
None. |
|
string |
boolean |
|
Error if input string is not |
|
number, double, or float |
boolean |
|
Zero is converted to |
|
date, timestamp, or timestamp with time zone |
timestamp |
|
None. |
|
string |
timestamp |
|
Error if input is not ISO UTC. |
|
number, double, or float |
timestamp |
|
Numbers are interpreted as the number of seconds since 1970-01-01. Only non-negative numbers are matched. |
vector() |
Either an array of numbers or a JSON vector scalar value. If applied to any other JSON value then an error is raised. | vector | VECTOR |
Method |
Footnote 8 This method can't be used at the end of a SQL/JSON path expression.
Item-Method Descriptions
-
abs()
: The absolute value of the targeted JSON number. Corresponds to the use of SQL functionABS
. -
atan()
: The trigonometric arctangent function of the targeted JSON number (in radians). Corresponds to the use of SQL functionATAN
. -
avg()
: The average of all targeted JSON numbers. If any targeted value is not a number then an error is raised. Corresponds to the use of SQL functionAVG
(without any optional behavior). This is an aggregate method. -
binary()
: A SQLRAW
interpretation of the targeted JSON value, which can be a hexadecimal string or a JSON binary value. If a string, SQL functionhextoraw
is used for conversion to a SQLRAW
value. This item method is applicable only to JSON data stored asJSON
type. -
binaryOnly()
: A SQLRAW
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 asJSON
type can have JSON binary values.) -
boolean()
: A SQLBOOLEAN
interpretation of the targeted JSON value.Note:
Prior to Release 23ai, this used a SQL
VARCHAR2(20)
interpretation. If you need to obtain aVARCHAR2
value (for compatibility reasons, for example) then you can wrap the value with SQL functionto_char
. -
booleanOnly()
: A SQLBOOLEAN
interpretation of the targeted JSON data, but only if it is a JSON Boolean value (true
orfalse
); otherwise, there is no match. It allows matches only for JSON Boolean values.Note:
Prior to Release 23ai, this used a SQL
VARCHAR2(20)
interpretation. If you need to obtain aVARCHAR2
value (for compatibility reasons, for example) then you can wrap the value with SQL functionto_char
. -
ceiling()
: The targeted JSON number, rounded up to the nearest integer. Corresponds to the use of SQL functionCEIL
. -
concat()
: The concatenation of the (two or more) string arguments. This item method can only be used in the right-hand-side (RHS) path expression of a json_transform operation (otherwise an error is raised). -
cos()
: The trigonometric cosine function of the targeted JSON number (in radians). Corresponds to the use of SQL functionCOS
. -
cosh()
: The trigonometric hyperbolic-cosine function of the targeted JSON number (in radians). Corresponds to the use of SQL functionCOSH
. -
count()
: The number of targeted JSON values, regardless of their types. This is an aggregate method. -
date()
: A SQLDATE
interpretation of the targeted JSON value. The targeted value must be either (1) a JSONstring
in a supported ISO 8601 format for a date or a date with time or (2) (if the data is of SQL typeJSON
) adate
,timestamp
, ortimestamp with time zone
value. Otherwise, there is no match.A SQL
DATE
value has no time component (it is set to zero). But before any time truncation is done, if the value represented by an ISO 8601 date-with-time string has a time-zone component then the value is first converted to UTC, to take any time-zone information into account.For example, the JSON string
"2021-01-01T05:00:00+08:00"
is interpreted as a SQLDATE
value that corresponds to the UTC string"2020-12-31 00:00:00"
.The resulting date faithfully reflects the time zone of the data — target and result represent the same date — but the result can differ from what a simple time truncation would produce. (This behavior is similar to that of SQL/JSON function
json_scalar
.) -
dateTimeOnly()
: A SQLTIMESTAMP
interpretation of the targeted JSON value. The targeted value must be adate
,timestamp
, ortimestamp with time zone
value. (Only JSON data stored asJSON
type can have such values.) -
dateWithTime()
: Likedate()
, except that the time component of an ISO 8601 date-with-time format is preserved in the SQLDATE
instance. -
double()
: A SQLBINARY_DOUBLE
interpretation of the targeted JSON string or number. dsInterval()
: A SQLINTERVAL DAY TO SECOND
interpretation of the targeted JSON string. The targeted string data must be in one of the supported ISO 8601 duration formats; otherwise, there is no match.-
exp()
: The mathematical exponential function of the targeted JSON number. That is, the mathematical constant e (Euler's number, 2.71828183...), raised to the power of the targeted JSON number. Corresponds to the use of SQL functionEXP
. -
float()
: A SQLBINARY_FLOAT
interpretation of the targeted JSON string or number. -
floor()
: The targeted JSON number, rounded down to the nearest integer. Corresponds to the use of SQL functionFLOOR
. -
idOnly()
: A SQLRAW
interpretation of the targeted JSON value. It allows matches only for JSON binary values that are tagged internally as having been derived from an extended object with field$rawid
or$oid
. (Only JSON data stored asJSON
type can have JSON binary values.) -
indexOf()
: The position (index) of the first element of the specified JSON array that is equal to the specified JSON value.The array is the first, implicit argument of the method (the targeted data), and the value to find in the array is the second, (first) explicit argument — both are required. This item method can only be used in the right-hand-side (RHS) path expression of a json_transform operation (otherwise an error is raised).
One or two optional arguments are also accepted: the second explicit argument is the array position of the first element to check (positions before that are skipped). The third explicit argument is the maximum number of array elements to check. You can, for example, use the optional arguments to loop over an array to locate matching elements, in array order.
-
For data that is of
JSON
data type, all JSON-language values are comparable. Comparison is according to the canonical sort order. -
For data that is not of
JSON
type, only scalar JSON values are comparable. Nonscalar data values are ignored, and the specified JSON value to locate must be scalar (otherwise an error is raised).
-
-
length()
: The number of characters in the targeted JSON string, or the number of bytes in the targeted binary value, interpreted as a SQLNUMBER
. Corresponds to the use of SQL functionLENGTH
. For a targeted string value, an optional argument whose value is"chars"
or"bytes"
is allowed, which specifies the length in characters or bytes, respectively. -
listagg()
: The concatenation of the targeted JSON values, which must be strings (otherwise an error is raised). Accepts an optional delimiter-string argument, which is inserted between consecutive targeted strings. Corresponds to the use of SQL functionLISTAGG
. This is an aggregate method. -
log()
: The mathematical logarithm function of the targeted JSON number. Corresponds to the use of SQL functionLOG
. Accepts an optional numeric argument, which is the logarithm base. The default base is the mathematical constant e (Euler's number, 2.71828183...), which means that by default this computes the natural logarithm. -
lower()
: The lowercase string that corresponds to the characters in the targeted JSON string. Corresponds to the use of SQL functionLOWER
. -
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 withjson_exists
or in a query withjson_query
semantics; using it in a query withjson_value
semantics raises an error. The value returned is always ofJSON
data type.Methods
max()
andmin()
are the only methods that can return a nonscalar JSON value (an object or array).-
For data that is of
JSON
data type, all JSON-language values are comparable. Comparison is according to the canonical sort order. -
For data that is not of
JSON
type, only scalar JSON values are comparable. Nonscalar data values are ignored, and the specified JSON values must all be scalar (otherwise an error is raised).
-
-
maxDateTime()
: The maximum of all targeted JSON dates with times. Item methoddateWithTime()
is first applied implicitly to each of the possibly multiple values. Their maximum (a singleTIMESTAMP
value) is then returned. Targeted JSON values that cannot be converted to dates with times are ignored. This is an aggregate method. -
maxNumber()
: The maximum of all targeted JSON numbers. Item methodnumber()
is first applied implicitly to each of the possibly multiple values. Their maximum (a singleNUMBER
value) is then returned. Targeted JSON values that cannot be converted to numbers are ignored. This is an aggregate method. -
maxString()
: The greatest of all targeted JSON strings, using collation order. Item methodstring()
is first applied implicitly to each of the possibly multiple values. The greatest of these (a singleVARCHAR2
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. Seemax()
for more information;min()
is the same, but it returns the minimum, not the maximum, value. -
minDateTime()
: The minimum of all targeted JSON dates with times. Item methoddateWithTime()
is first applied implicitly to each of the possibly multiple values. Their minimum (a singleTIMESTAMP
value) is then returned. Targeted JSON values that cannot be converted to dates with times are ignored. This is an aggregate method. -
minNumber()
: The minimum of all targeted JSON numbers. Item methodnumber()
is first applied implicitly to each of the possibly multiple values. Their minimum (a singleNUMBER
value) is then returned. Targeted JSON values that cannot be converted to numbers are ignored. This is an aggregate method. -
minString()
: The least of all targeted JSON strings, using collation order. Item methodstring()
is first applied implicitly to each of the possibly multiple values. The least of these (a singleVARCHAR2
value) is then returned. Targeted JSON values that cannot be converted to strings are ignored. This is an aggregate method. -
nullOnly()
: Returns JSONnull
if the targeted data is JSONnull
. Otherwise there's no match, and error handling applies for the SQL function or condition to which the path expression is passed. A common usage is with conditionjson_exists
, where it's used to filter — for example:json_exists(mytable.jcol, $?(@.a.nullOnly() == null))
. -
number()
: A SQLNUMBER
interpretation of the targeted JSON string or number. -
numberOnly()
: A SQLNUMBER
interpretation of the targeted JSON data, but only if it is a JSON number; otherwise, there is no match. It allows matches only for JSON numbers. -
pow()
: The mathematical power function of the targeted JSON number. This raises the targeted JSON number to the specified power, which is a required numeric argument. Corresponds to the use of SQL functionPOWER
. -
round()
: Corresponds to the use of SQL functionROUND
.An optional integer argument N specifies rounding to the nearest 10-N. By default (N = 0), rounds to the decimal point, that is, to the nearest integer. Nonnegative rounds to N digits after the decimal point; negative rounds to N digits before the decimal point. For example,
round(31415.92653, 3)
= 31415.927,round(31415.92653, 0)
= 31415,round(31415.92653, -3)
= 31400. -
sin()
: The trigonometric sine function of the targeted JSON number (in radians). Corresponds to the use of SQL functionSIN
. -
sinh()
: The trigonometric hyperbolic-sine function of the targeted JSON number (in radians). Corresponds to the use of SQL functionSINH
. -
size()
: If multiple JSON values are targeted then the result of applyingsize()
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
semantics, in addition to using it withjson_value
semantics. If applied to data that is an array, no implicit iteration over the array elements occurs: the resulting value is just the number of array elements. (This is an exception to the rule of implicit iteration.) -
-
size2()
: This the same as standard methodsize()
, except that if the single targeted value is an object then the value is the number of members in the object (instead of 1). -
stddev()
: The statistical standard-deviation function of the targeted JSON values, which must be numbers (otherwise an error is raised). Corresponds to the use of SQL functionSTDDEV
. This is an aggregate method. -
stddevp()
: The statistical population standard-deviation function of the targeted JSON values, which must be numbers (otherwise an error is raised). Corresponds to the use of SQL functionSTDDEV_POP
. This is an aggregate method. -
string()
: A SQLVARCHAR2(4000)
orCLOB
interpretation of the targeted scalar JSON value. -
stringify()
: A SQLCLOB
interpretation of the targeted scalar JSON value. Methodstringify()
can only be used with the simple dot-notation, not with a SQL/JSON path expression. -
stringOnly()
: A SQLVARCHAR2(4000)
orCLOB
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. -
substr()
: A substring of the targeted JSON string. Corresponds to the use of SQL functionSUBSTR
, but it is zero-based, not one-based. The starting position of the substring in the targeted string is a required argument. The maximum length of the substring is an optional (second) argument. -
sum()
: The sum of all targeted JSON numbers. If any targeted value is not a number then an error is raised. Corresponds to the use of SQL functionSUM
(without any optional behavior). This is an aggregate method. -
tan()
: The trigonometric tangent function of the targeted JSON number (in radians). Corresponds to the use of SQL functionTAN
. -
tanh()
: The trigonometric hyperbolic-tangent function of the targeted JSON number (in radians). Corresponds to the use of SQL functionTANH
. -
timestamp()
: A SQLTIMESTAMP
interpretation of the targeted JSON value. The targeted string data must be either (1) a JSONstring
in a supported ISO 8601 format for a date or a date with time or (2) (if the data is of SQL typeJSON
) adate
,timestamp
, ortimestamp with time zone
value. Otherwise, there is no match.Foot 9 -
toBoolean()
: A SQLVARCHAR2(20)
interpretation of the targeted JSON value. This is the same as methodboolean()
, except that the targeted value can be a numeric value, in which case zero corresponds tofalse
and any other number corresponds totrue
. -
toDateTime()
: A SQLTIMESTAMP
interpretation of the targeted JSON value. The targeted string data must be either (1) a JSONstring
in a supported ISO 8601 format for a date or a date with time, (2) a non-negative numeric value, or (3) (if the data is of SQL typeJSON
) adate
,timestamp
, ortimestamp with time zone
value. Otherwise, there is no match.Foot 10 -
truncate()
: The targeted JSON number, rounded by truncating. Corresponds to the use of SQL functionTRUNC
.An optional integer argument N (default 0) specifies the number of digits to keep to the left (if negative) or right (if nonnegative) of the decimal point.
-
type()
: The name of the JSON-language data type family of the targeted data, or one of its family members, interpreted as a SQLVARCHAR2(20)
value. For example, for the numeric type family, the value returned can be"double"
,"float"
, or"number"
. See Comparison and Sorting of JSON Data Type Values.This item method can be used in queries with
json_query
semantics, in addition tojson_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
orfalse
). -
"binary"
for a value that corresponds to a SQLRAW
value. (ForJSON
type data only.) -
"date"
for a value that corresponds to a SQLDATE
value. (ForJSON
type data only.) -
"daysecondInterval"
for a value that corresponds to a SQLINTERVAL DAY TO SECOND
value. (ForJSON
type data only.) -
"double"
for a number that corresponds to a SQLBINARY_DOUBLE
value. (ForJSON
type data only.) -
"float"
for a number that corresponds to a SQLBINARY_FLOAT
value. (ForJSON
type data only.) -
"null"
for anull
value. -
"number"
for a number. -
"object"
for an object. -
"string"
for a string. -
"timestamp"
for a value that corresponds to a SQLTIMESTAMP
value. (ForJSON
type data only.) -
"timestamp with time zone"
for a value that corresponds to a SQLTIMESTAMP WITH TIME ZONE
value. (ForJSON
type data only.) -
"vector"
for a value that corresponds to a SQLVECTOR
value. -
"yearmonthInterval"
for a value that corresponds to a SQLINTERVAL YEAR TO MONTH
value. (ForJSON
type data only.)
-
-
upper()
: The uppercase string that corresponds to the characters in the targeted JSON string. Corresponds to the use of SQL functionUPPER
. -
variance()
: The statistical variance function of the targeted JSON values, which must be numbers (otherwise an error is raised). Corresponds to the use of SQL functionVARIANCE
. This is an aggregate method. -
vector()
: A SQLVECTOR
interpretation of the targeted JSON value. If the targeted data is a JSON array of numbers then that value is converted to a vector. If the targeted data is a JSON-scalar vector value then that is returned. If the data is any other JSON value, including an array with any non-number elements, then an error is raised.Method
vector()
can only be used with the simple dot-notation, not with a SQL/JSON path expression. ymInterval()
: A SQLINTERVAL YEAR TO MONTH
interpretation of the targeted JSON string. The targeted string data must be in one of the supported ISO 8601 duration formats; otherwise, there is no match.
Item methods abs()
, ceiling()
,
double()
, floor()
, size()
,
and type()
are part of the SQL/JSON standard. The other methods are
Oracle extensions to the SQL/JSON standard: atan()
,
avg()
, binary()
,
binaryOnly()
, boolean()
,
booleanOnly()
, concat()
,
cos()
, cosh()
, count()
,
date()
, dateTimeOnly()
,
dateWithTime()
, double(),
dsInterval()
, exp()
, float()
,
idOnly()
, indexOf()
,
length()
, listagg()
, log()
,
lower()
, max()
,
maxDateTime()
, maxNumber()
,
maxString()
, min()
,
minDateTime()
, minNumber()
,
minString()
, nullOnly()
,
number()
, numberOnly()
,
pow()
, round()
, sin()
,
sinh()
, size2()
, stddev()
,
stddevp()
, string()
,
stringify()
, stringOnly()
, substr()
,
sum()
, tan()
, tanh()
,
timestamp()
, toBoolean()
,
toDateTime()
, truncate()
,
upper()
, variance()
,
vector()
, and ymInterval()
.
Item methods avg()
, count()
,
listagg()
, max()
,
maxDateTime()
, maxNumber()
,
maxString()
, min()
,
minDateTime()
, minNumber()
,
minString()
, stddev()
,
stddevp()
, sum()
, and
variance()
are aggregate item methods. Instead of acting
individually on each targeted value they act on all targeted values together.
For example, if a path expression targets multiple values that can be converted to
numbers then sum()
returns the sum of those numbers.
Note that when a path expression targets an array, applying an
aggregate item method to it, the array is handled as a single value — there is no
implicit iteration over the array elements. For example,
count()
counts any targeted array as one value, and
size()
returns the size of the array, not the sizes of its
elements.
If you want an aggregate item method to act on the array elements then
you need to explicitly iterate over those elements, using wildcard
*
. For example, if the value of field
LineItems
in a given document is an array then
$.LineItems.count()
returns 1
, but
$.LineItems[*].count()
returns the number of array
elements.
An aggregate item method applies to a single JSON document at a time, just like the path expression (or dot-notation) of which it is part. It aggregates the multiple values that the path expression targets in that document. In a query it returns a row for each document. It does not aggregate information across multiple documents, returning a single row for all documents, as do SQL aggregate functions. See Example 17-1 and Example 17-2.
See Also:
-
ABS in Oracle Database SQL Language Reference
-
ATAN in Oracle Database SQL Language Reference
-
AVG in Oracle Database SQL Language Reference
-
CEIL in Oracle Database SQL Language Reference
-
COS in Oracle Database SQL Language Reference
-
COSH in Oracle Database SQL Language Reference
-
EXP in Oracle Database SQL Language Reference
-
FLOOR in Oracle Database SQL Language Reference
-
LENGTH in Oracle Database SQL Language Reference
-
LISTAGG in Oracle Database SQL Language Reference
-
LOG in Oracle Database SQL Language Reference
-
LOWER in Oracle Database SQL Language Reference
-
POWER in Oracle Database SQL Language Reference
-
ROUND (number) in Oracle Database SQL Language Reference
-
SIN in Oracle Database SQL Language Reference
-
SINH in Oracle Database SQL Language Reference
-
STDDEV in Oracle Database SQL Language Reference
-
STDDEV_POP in Oracle Database SQL Language Reference
-
SUBSTR in Oracle Database SQL Language Reference
-
SUM in Oracle Database SQL Language Reference
-
TAN in Oracle Database SQL Language Reference
-
TANH in Oracle Database SQL Language Reference
-
TRUNC (number) in Oracle Database SQL Language Reference
-
UPPER in Oracle Database SQL Language Reference
-
VARIANCE in Oracle Database SQL Language Reference
Item Methods and Specified Query Return Types
Because some item methods interpret the targeted JSON data as if it were of a SQL data type, they can be used at the end of a SQL/JSON path expression to provide the data to be returned by a query.
All data-type conversion methods except those whose names start with
"to" can be used at path end. It also applies to methods (e.g.
minString()
, that implicitly first apply a type-conversion
method (e.g. string()
).
Some other methods, such as the aggregation methods except
max()
and min()
, can also be used at path end.
The methods listed in Table 17-2 are the only item methods that can be used at the end of a
path expression.
An item method that cannot be used at the end of a path expression can only be
used in a filter condition with json_exists
or in a query with
json_query
semantics; using it in a query with
json_value
semantics raises an error.
You can use such path-end item methods at the end of a path
expression in any query that has json_value
semantics (it returns a
scalar SQL value), whether it uses simple dot notation, json_value
,
or a (scalar) json_table
column. For example, they can be used with
json_value
in place of a RETURNING
clause to specify the returned
SQL data type for the extracted JSON data.
You can also use path-end item methods together with a
json_value
RETURNING
clause or a json_table
column type
specification. What happens if the SQL data type to use for extracted JSON data is
specified by both a path-end item method and either a
json_value
RETURNING
clause or a json_table
column type?
-
If the two data types are compatible then the data type for the
RETURNING
clause or the column is used. For these purposes,VARCHAR2
is compatible with bothVARCHAR2
andCLOB
. -
If the data types are incompatible then a static, compile-time error is raised.
Table 17-2 details the compatibility between path-end item methods and specified SQL return types for a SQL query.
Table 17-2 Compatibility of Path-End Item Methods and Scalar SQL Return Types
Item Method | Compatible SQL Query Return Data Type |
---|---|
|
VARCHAR2 or
CLOB , except that
string() returns SQL NULL for
a JSON null value
|
stringify() |
CLOB ,
except that it returns SQL NULL for a JSON
null value
|
|
NUMBER |
double() |
BINARY_DOUBLE |
float() |
BINARY_FLOAT |
|
If the JSON value is an ISO string with time-zone information, the represented date-with-time is first converted to UTC, to take the time zone into account. |
dateWithTime() |
DATE , with time component,
corresponding to RETURNING DATE PRESERVE
TIME |
|
TIMESTAMP |
ymInterval() |
INTERVAL YEAR TO
MONTH |
dsInterval() |
INTERVAL DAY TO
SECOND |
|
VARCHAR2 or
BOOLEAN |
|
RAW |
vector() |
VECTOR |
Using a json_value
RETURNING
clause or a json_table
column
specification, you can specify a length for character data and a precision and scale
for numerical data. This lets you assign a more precise SQL data type for extraction
than what is provided by an item method for target-data comparison purposes.
For example, if you use item method string()
and
json_value
with clause RETURNING VARCHAR2(150)
then the data type of the returned data is VARCHAR2(150)
, not
VARCHAR2(4000)
.
Example 17-1 Aggregating Values of a Field for Each Document
This example uses item method
avg()
to aggregate the values of field
Quantity
across all LineItems
elements of a
JSON document, returning the average for each document as a separate result
row.
SELECT json_value(po_document,
'$.LineItems[*].Quantity.avg()')
FROM j_purchaseorder;
Example 17-2 Aggregating Values of a Field Across All Documents
This example uses SQL function avg
to aggregate the
average line-item Quantity
values for all JSON documents, returning
the overall average for the entire set of documents as a single row. The
average quantity for all line items of a given document is computed using item
method avg()
.
SELECT avg(json_value(po_document,
'$.LineItems[*].Quantity.avg()'))
FROM j_purchaseorder;
Related Topics
- Basic SQL/JSON Path Expression Syntax
- Simple Dot-Notation Access to JSON Data
- ISO 8601 Date, Time, and Duration Support
- Types in Filter-Condition Comparisons
- RETURNING Clause for SQL Functions
- SQL/JSON Function JSON_VALUE
- SQL/JSON Function JSON_TABLE
- Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
- Textual JSON Objects That Represent Extended Scalar Values
- SQL/JSON Function JSON_SCALAR
- Oracle SQL Function JSON_TRANSFORM
Parent topic: SQL/JSON Path Expressions
17.4 Types in Filter-Condition 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. Strict type comparison of standard JSON values is straightforward: JSON data types string, number, null, object, and array are mutually exclusive and incomparable.
But values of JSON
type are comparable (see Comparison and Sorting of JSON Data Type Values). And in path expressions, 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 in either of these ways:
-
Explicitly using “only” item methods. For example, applying method
numberOnly()
prevents implicit type-casting to a number. -
Use the clause
TYPE (STRICT)
(withjson_transform
,json_value
,json_transform
, orjson_exists
). This has the same effect as applying the relevant "only" item methods throughout the path expression being used.
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 number3
, satisfying the comparison.Foot 11 -
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.
Note:
When comparing values of JSON
data type in SQL,
the size of the values being compared, as encoded for SQL comparison, must be
less than 32K bytes. Otherwise, an error is raised. In practice, this SQL
encoded-for-comparison size is roughly the size of a textual representation
of the same JSON data.
For example, in this query the encoded sizes of fields
dept
and name
must each be less than 32K:
SELECT *
FROM emp t
WHERE t.data.dept = 'SALES' ORDER BY t.data.name
This limit applies to SQL clauses ORDER BY
and
GROUP BY
, as well as to the use of SQL-value comparison
operators (such as >
in a WHERE
clause).
More precisely, the limit applies only to comparison and sorting done by
SQL itself. It does not apply to comparison or sorting done within the JSON
language. That is, there's no size limit for comparison or sorting done by a SQL
operator for JSON, such as json_transform
or
json_exists
. In particular, the limit doesn't apply to
comparisons made in SQL/JSON path expressions.
Footnote Legend
Footnote 1: Theto
in a range
specification is sometimes informally called the array slice
operator.Footnote 2: Filter conditions and filter expressions are sometimes referred to informally as "predicates". But a filter condition is actually the application of a predicate to its arguments.
Footnote 3: 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 (for example @.z
in ()
) 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: (Function
json_value
can also
return an object type or a collection type, but an item method can't be
applied to the result.)Footnote 7: Method
vector()
is a conversion method only when applied to an
array of numbers. When applied to a JSON-scalar vector value it returns
that value as an instance of SQL type VECTOR
. Method
vector()
can only be used with the simple dot-notation, not
with a SQL/JSON path expression.Footnote 9: 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 10: Applying item method
toDateTime()
to a supported ISO 8601 string
<ISO-STRING>
has the effect of SQL
sys_extract_utc(to_utc_timestamp_tz(<ISO-STRING>)
.
A non-negative numeric value is interpreted as the number of seconds
since 1970-01-01.Footnote 11: 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.