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).
Usually, in an SQL query, an SQL/JSON path expression is written as an SQL character string literal. This means that the path expression must be enclosed in single quotes. Also, any single quotes desired within the value must be doubled. Some forms of path expressions require string literals within them. The embedded string literals adhere to JavaScript/ECMAScript conventions:
  • They must be surrounded by double quotes.
  • Backslash escapes might be used within them to represent otherwise-hard-to-type characters.
To get a double quote within an embedded string literal, you must type \". To get a backslash, you must type \\.
Other special backslash sequences include those recognized in JSON strings:
For various ASCII control characters
  • \b
  • \f
  • \n
  • \r
  • \t
  • \v
For a Unicode character identified by its 4-hex-digit code point
  • \uNNNN
The backslash syntax also includes two cases not allowed by JSON:
For a character code written with only two hex digits
  • \xNN
For a character code written with 1 to 6 hex digits
  • \u{N...}
A path expression consists of a sequence of path elements, which can be:
  • 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 or start_index to end_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 provided start_index and end_index.
The specified index can be an integer, as well as an expression returning a single numeric value, which is automatically cast to integer. Zero index corresponds to the first array element. You can also use the last keyword to denote the last array element, which is useful for handling arrays of unknown length.
[*]
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