The jsonpath type implements support for the SQL/JSON path language in PostgreSQL to effectively query JSON data. It provides a binary representation of the parsed SQL/JSON path expression that specifies the items to be retrieved by the path engine from the JSON data for further processing with the SQL/JSON query functions.
The SQL/JSON path language is fully integrated into the SQL engine: the semantics of its predicates and operators generally follow SQL. At the same time, to provide a most natural way of working with JSON data, SQL/JSON path syntax uses some of the JavaScript conventions:
.
is used for member access[]
are used for array accessAn SQL/JSON path expression is an SQL character string literal, so it must be enclosed in single quotes when passed to an SQL/JSON query function. Following the JavaScript conventions, character string literals within the path expression must be enclosed in double quotes. Any single quotes within this character string literal must be escaped with a single quote by the SQL convention.
A path expression consists of a sequence of path elements, which can be the following:
For details on using
Variable | Description |
---|---|
$ |
A variable representing the JSON text to be queried (the |
$varname |
A named variable. Its value must be set in the |
@ |
A variable representing the result of path evaluation in filter expressions. |
Accessor operator | Description |
---|---|
.key ."$varname" |
Member accessor that returns an object member with the specified key. If the key name is a named variable starting with $ or does not meet the JavaScript rules of an identifier, it must be enclosed in double quotes as a character string literal. |
.* |
Wildcard member accessor that returns the values of all members located at the top level of the current object. |
.** |
Recursive wildcard member accessor that processes all levels of the JSON hierarchy of the current object and returns all the member values, regardless of their nesting level. This is a PostgreSQL extension of the SQL/JSON standard. |
[subscript, ...] [subscript to last] |
Array element accessor. The provided numeric subscripts return the corresponding array elements. The first element in an array is accessed with [0] . The last keyword denotes the last subscript in an array and can be used to handle arrays of unknown length. |
[*] |
Wildcard array element accessor that returns all array elements. |