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 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 specifications N to M and M to N are equivalent. Each is equivalent to explicitly specifying N, M, and the indexes between N and M, all in ascending order.

  That is, 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, 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 (a Boolean function) to its arguments. It is defined recursively as one of the following, where each of cond, cond1, and cond2 stands for a filter condition.^Foot 2

  • ! cond: The negation of cond, meaning that cond must not be satisfied. ! is a prefix unary predicate. (See Negation in Path Expressions.)

  • ( cond ): Parentheses are used for grouping, separating filter condition cond 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 just existscond.

    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) of cond1 and cond2, requiring that both be satisfied. && is an infix binary predicate.

  • cond1 || cond2: The inclusive disjunction (or) of cond1 and cond2, requiring that cond1, 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 follow in; 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. Condition in 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 a null value in the targeted data.

  • A comparison, which is one of the following:

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

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

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

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

    • A relative path expression, followed by any of the keywords has substring, starts with, like, like_regex, regex like, regex, eq_regex, ci_like_regex, or ci_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 or its synonym regex like (no underscore) 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.

        ci_like_regex is the same as like_regex, except that matching is case-insensitive.

        like_regex and ci_like_regex are exceptional among the pattern-matching comparisons, in that their pattern matches the empty JSON string ("").

      • eq_regex and its synonyms regex equals (no underscore) and regex are the same as 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 ("").

        ci_regex is the same as eq_regex, except that matching is case-insensitive.

  A SQL/JSON variable is a dollar sign ($) followed immediately (no intervening whitespace etc.) by the name of a variable that is bound in a PASSING 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, and ci_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 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).

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

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.

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

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

• $.friends[*].addresses?(@.city has substring "Fran").zip — Zip codes of all addresses of friends, where the name of the address city contains "Fran".

• $.friends[*].addresses?(@.city like "S_n%").zip — Zip codes of all addresses of friends, where the name of the address city 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 all addresses of friends, where the name of the address city contains "n" followed by at least one space character. Matching is case-sensitive, and it is not anchored at the start of the city string.

• $.friends[*].addresses?(@.city ci_regex "s.+o").zip — Zip codes of all addresses of friends, where the name of the address city starts with "s" or "S" and ends with "o" or "O". Matching is case-insensitive (ci_) and the entire city string must match (no like_).

• $..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 PASSING clause: PASSING … AS "City", … AS "State". Use of variables in comparisons can improve performance by avoiding query recompilation.