sql-json-path-expression
An SQL/JSON path expression defines access to the elements of a JSON text.
- lax or strict
- Specifies the JSON path mode.
- lax
- Specifies that certain structural errors are tolerated when navigating
the current JSON text. These include:
- automatic unnesting of arrays
- automatic wrapping of scalar values to be a one element array if referenced as an array
- specification of nonexistent items, including array index values that are out of range
- strict
- Specifies that an error is reported when the specified path expression cannot be used to navigate the current JSON text. The error is handled according to the current ON ERROR clause.
- sql-json-accessor-expression
- $
- Specifies the start of the context item to which the rest of the SQL/JSON path expression is applied.
- json-path-key-name
- Specifies the key name of a key,value pair in the JSON text. If the name contains any special characters, it must be delimited with " characters.
- *
- Specifies that the values for all the keys will be returned as an SQL/JSON sequence.
- array-specifier
- Specifies a list of one or more array index values to apply to
an array. The values can be specified as individual numbers or as
ranges. They can be specified in any order and may contain duplicates,
but the result will be returned in document order without duplicates.
If a range of index values is specified and the from and to values
are out of order, in lax mode all index values
in the range are used; in strict mode it is
an error.
- index-value
- Specifies an array index value.
- number
- An unsigned integer constant representing an array element. The first element of the array has an index of 0.
- last
- Indicates the last element of the array. This value cannot be specified as the first value in an index range.
- last - number
- Indicates a position relative to the last element of the array.
- *
- Indicates all array elements are to be selected.
Example
- Consider the following text:
Here are the results of using various SQL/JSON path expressions to access items in the JSON text.{ "isbn": "123-456-222", "author": [ { "name":"Jones"},{"name","Smith"}]}
Path Value $.isbn "123-456-222" $.author[0].name "Jones" $.author[1].name "Smith"