sql-json-path-expression

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

sql-json-path-expression

sql-json-accessor-expression

array-specifier

lax or strict

Specifies the JSON path mode.

lax

Specifies that certain structural errors are tolerated when the current JSON document is navigated, including the following structural errors:

Automatic unnesting of arrays.
Automatic wrapping of scalar values to be a single element array, if referenced as an array.
Specifying 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 options specified in a function's ON EMPTY clause.

strict

Specifies that an error is reported when the specified path expression cannot be used to navigate the current JSON document. 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 document.

*

Specifies that the values for all the keys are returned as an SQL/JSON sequence.

array-specifier

Specifies the index values to apply to an array. The first element of the array has an index of 0. If specified index is out of range, then it is considered an error.

number: An unsigned integer constant that represents an array element. The first element of the array has an index of 0.
*: Indicates that all array elements are selected.

Special semantics are associated with some characters, when used as part of a SQL/JSON path expression:

.: Used for going to the next level within JSON document.
*: Used for matching all the keys at the current level.
[ and ]: Used for specifying an index of an array.

To allow these characters to be used within json-path-key-name, use an escape backslash character (\) before these characters to indicate that these characters are part of the key name.

Examples

This example is based on the following JSON document:
```
{ "isbn": "123-456-222", "author": [ { "name":"Jones"},{"name":"Smith"}]}
```
The following table shows the results of using various SQL/JSON path expressions to access items in the JSON document:
Path Number of matches Matches

$.isbn 1 "123-456-222"

$.author[0].name 1 "Jones"

$.author[1].name 1 "Smith"

Path	Number of matches	Matches
$.isbn	1	"123-456-222"
$.author[0].name	1	"Jones"
$.author[1].name	1	"Smith"

This example is based on the following JSON document:

{
  "person"   : {"firstname":"Fred", "lastname":"Gauss"},
  "where"    : "General Products",
  "friends"  : [ { "name": "Lili","rank": 5 }, {"name": "Hank", "rank": 7} ],
  "work.area": "Finance"
}

The following table shows the results of using various SQL/JSON path expressions to access items in the JSON document:

Path	Number of matches	Matches
$.person.lastname	1	"Gauss"
$.friends	1	[ { "name": "Lili", "rank": 5 }, { "name": "Hank", "rank": 7} ]
$.*.firstname	1	"Fred"
$.person.*	2	"Fred" and "Gauss"
$.friends[*]	2	{ "name": "Lili", "rank": 5 } and { "name": "Hank", "rank": 7}
$.friends[*].rank	2	5 and 7
$.work.area	0
$.work\.area	1	"Finance"

This example is based on the following JSON document:

{ "a":[{"b1":10}, {"b2":11}], "c":"hi" }

The following table shows the results of using various SQL/JSON path expressions to access items in the JSON document:

Path	Matches	Remark
lax $.a.b1	10	Automatic unnesting of array element at `'a'`. Treated as `'a[*]'`.
strict $.a.b1	Error	No automatic unnesting of array.
lax $.c[0]	"hi"	Automatic wrapping of scalar value into single element array.
strict $.c[0]	Error	No automatic wrapping of scalar value into single element array.

This example illustrates the difference between JSON_VALUE and JSON_QUERY behavior. This example is based on the following JSON document:

{ "a": [1,2], "b": { "c1":1, "c2":2 } }

This example is based on the following path results:

Path expression	Number of values returned	Values returned
$.a	Single	[1,2]
$.b.*	Multiple	1 2

The following table shows the results of using various SQL/JSON path expressions to access items in the JSON document:

Operator	$.a	$.b.*	Remark
JSON_VALUE	Error	Error	Error for accessing array type and multiple matches.
JSON_QUERY WITHOUT ARRAY WRAPPER	[1,2]	Error	Error for multiple values with no array wrapper.
JSON_QUERY WITH UNCONDITIONAL ARRAY WRAPPER	[ [1,2] ]	[1,2]	You must use an array wrapper, even if the type is array.
JSON_QUERY WITH CONDITIONAL ARRAY WRAPPER	[1,2]	[1,2]	An array wrapper is not necessary, if the type is array.