However, this basic syntax is extended by relaxing the matching of arrays and non-arrays against non-array and array patterns, respectively — see SQL/JSON Path Expression Syntax Relaxation.

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

• A SQL/JSON basic path expression (also called just a path expression here) is an absolute path expression or a relative path expression.

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

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

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

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

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

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

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

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

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

  In a path expression, array indexing is zero-based (0, 1, 2,...), as in the JavaScript convention for arrays. A range specification has the form N to M, where N and M are array indexes and N is strictly less than M.^Foot 1 An error is raised at query compilation time if N is not less than M.

  When indexes or range specifications are used, the array elements they collectively specify must be specified in ascending order, without repetitions, or else a compile-time error is raised. For example, an error is raised for each of [3, 1 to 4], [4, 2], [2, 3 to 3], and [2, 3, 3]. Errors are raised on the first two because the order is not ascending, Errors are raised on the last two because of the repetition of array index 3 (which indexes the fourth array element, because of zero-based indexing).

  Similarly, the elements in the array value that results from matching are in ascending order, with no repetitions. If an asterisk is used in the path expression then all of the array elements are returned, in array order.

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

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

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

  For example, consider this query and data:

  json_query(some_json_column, '$.a..z' WITH ARRAY WRAPPER)

  { "a" : { "b" : { "z" : 1 },
            "c" : [ 5, { "z" : 2 } ],
            "z" : 3 }
    "z" : 4 }

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

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

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

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

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

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

• A filter condition applies a predicate (Boolean function) to its arguments and is one of the following, where each of cond, cond1, and cond2 stands for a filter condition.

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

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

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

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

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

  • A comparison, which is one of the following:

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

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

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

    • A relative path expression, followed by has substring, starts with, like, like_regex, or eq_regex, followed by either a JSON string or a SQL/JSON variable that is bound to a SQL string (which is automatically converted from the database character set to UTF8).

      • 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, there is no escape character for path-expression predicate like. Also, Oracle recommends that you avoid using character `, GRAVE ACCENT (U+0060), in your like patterns — that character, also known sometimes as backquote or backtick, is reserved for future use.

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

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

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

        • eq_regex matches its regular expression pattern against the entire JSON string data value — the full string must match the pattern for the comparison to be satisfied. like_regex is satisfied if any portion of the JSON string matches the pattern.

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

      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.

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

    A comparison predicate is ==, <>, !=^Foot 2, <, <=, >=, 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.

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

  • A value list is (, followed by a list of one or more scalar values and SQL/JSON variables separated by commas (,), followed by ).

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

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

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

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

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

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

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

  • Condition a is satisfied.

  • Condition d < 42 is satisfied.

At least one side of a comparison must not be a SQL/JSON variable. The default type for a comparison is defined at compile time, based on the type(s) for the non-variable side(s). You can use a type-specifying item method to override this default with a different type. The type of your matching data is automatically converted, for the comparison, to fit the determined type (default or specified by item method). For example, $.a > 5 imposes numerical comparison because 5 is a number, $.a > "5" imposes string comparison because "5" is a string.

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 must be specified in ascending order, and they are returned in that order: fourth, ninth, tenth, eleventh, thirteenth.

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

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

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

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

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

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

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

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

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

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

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

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

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