jsonpath
jsonpath
implements support for the SQL/JSON path language in PostgreSQL to
query JSON data. It provides a binary representation of the parsed SQL/JSON path expression that
specifies the items that are to be retrieved by the path engine from the JSON data for further
processing with the SQL/JSON query functions. The semantics of SQL/JSON path predicates and
operators generally follow SQL. The SQL/JSON path syntax uses some of the JavaScript conventions: - A dot (
.
) is used for member access. - Square brackets (
[]
) are used for array access. - SQL/JSON arrays are 0-relative (as opposed to regular SQL arrays that start from 1).
- They must be surrounded by double quotes.
- Backslash escapes might be used within them to represent otherwise-hard-to-type characters.
\"
. To
get a backslash, you must type \\
. - For various ASCII control characters
-
\b
\f
\n
\r
\t
\v
- For a Unicode character identified by its 4-hex-digit code point
-
\u
NNNN
- For a character code written with only two hex digits
-
\x
NN
- For a character code written with 1 to 6 hex digits
-
\u{
N...
}
- Path literals of JSON primitive types: Unicode text, numeric, true, false, or null.
- Path variables.
- Accessor operators.
jsonpath
operators and methods, which you can use to provide filter expressions or define the order of path evaluation.
jsonpath variables
- $
- A variable that represents the JSON text to be queried (the context item).
- $varname
- A named variable. You can set the value by the parameter
vars
of several JSON processing functions. - @
- A variable that represents the result of path evaluation in filter expressions.
jsonpath accessors
- .key
- ."$varname"
- A 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.
- .*
- A wildcard member accessor that returns the values of all members located at the top level of the current object.
- .**
- A 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.
- .**{level}
- .**{start_level to end_level}
- Same as .**, but with a filter over nesting levels of JSON hierarchy. Nesting levels are specified as integers. Zero level corresponds to the current object. To access the lowest nesting level, you can use the last keyword. This is a PostgreSQL extension of the SQL/JSON standard.
- [subscript, ...]
- An array element accessor.
subscript
can be given in two forms:index
orstart_index
toend_index
. The first form returns a single array element by its index. The second form returns an array slice by the range of indexes, including the elements that correspond to the providedstart_index
andend_index
. - [*]
- A wildcard array element accessor that returns all array elements.
The SQL/JSON path language
Similarly to XPath
expressions that are used for SQL
access to
XML, SQL
/JSON
path expressions specify which items to retrieve
from the JSON
data.
In PostgreSQL, path expressions are implemented as the jsonpath
data type. They
can use any elements described in jsonpath.
JSON
query functions and operators pass the provided path expression to the
path engine for evaluation. If the expression matches the queried JSON data, the
corresponding SQL/JSON item is returned.
Path expressions are written in the SQL/JSON path language. They can include arithmetic expressions and functions. The provided expression must be enclosed in single quotes as query functions treat such expression as a text string.
A path expression is made up of a sequence of elements that are allowed by
jsonpath
. The path expression is evaluated from left to right. You can use
parentheses to change the order of operations. If the evaluation is successful, an SQL/JSON
sequence is produced. The evaluation result is returned to the JSON query function that
completes the specified computation.
To refer to the context item (JSON data to be queried), you can use the $
sign in the path expression. You can follow the sign with one or more jsonpath accessors. They go down the JSON structure level by
level to retrieve the content of context item. Each operator that follows deals with the result of
the previous evaluation step.
For more information about the jsonpath
operators and methods, see jsonpath