sql-json-path-expression

An SQL/JSON path expression defines access to the elements of a JSON text.

Read syntax diagramSkip visual syntax diagram laxstrict sql-json-accessor-expression
sql-json-accessor-expression
Read syntax diagramSkip visual syntax diagram$  . json-path-key-name .  * array-specifier
array-specifier
Read syntax diagramSkip visual syntax diagram[ ,index-valueindex-valuetoindex-value *  ]
index-value
Read syntax diagramSkip visual syntax diagramnumberlastlast - number
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
If an item does not exist, the SQL/JSON path expression returns an empty string which is handled according to the current ON EMPTY clause.
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:
    { "isbn": "123-456-222", "author": [ { "name":"Jones"},{"name","Smith"}]}
    Here are the results of using various SQL/JSON path expressions to access items in the JSON text.
    Path Value
    $.isbn "123-456-222"
    $.author[0].name "Jones"
    $.author[1].name "Smith"