JSONPath

Eugenia Kuzina

The JSONPath type implements support for the SQL/JSON path language in PostgreSQL to efficiently query JSON data stored in the JSONB format. JSONPath is a binary representation of the parsed SQL/JSON path expression that specifies the items to be retrieved for further operations.

JSONPath uses the following JavaScript convention syntax:

  • The dot (.) is used for member access.

  • Square brackets ([]) are used for array access.

  • SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.

Enclose an SQL/JSON path expression in single quotes to include it in an SQL query. To use a single-quote character in a value, double it. For example: 'Dianne''s horse'.

Some forms of path expressions require string literals. These embedded string literals must be surrounded by double quotes. These string literals can contain backslash escapes. To write a double quote, use \". To add a backslash, type \\. Other special backslash sequences that can be used in JSON strings: \b, \f, \n, \r, \t, \v for ASCII control characters; \xNN, \uNNNN, and \u{N…​} for a Unicode character identified by two hex digits, by four hex digits, and by from one to six hex digits respectively.

A path expression can consist of the following path elements:

  • Path variables:

    • $ is the current context of the element. The expression with $ specifies the area to process and is used in a filter. Example: $.items.

    • @ is the current context in the filter expression (the result of path evaluation in filter expressions). Example: $.items[*] ? (@.qty > 2).

    • $varname is a named variable. Its value can be set by the parameter vars. Example: '$[*] ? (@ > $x)',vars ⇒ '{"x": 2}'.

  • A filter expression that starts with a question mark (?) and contains a condition in parentheses:

    ? (condition)

    It operates similar to the WHERE clause.

  • JSONPath operators and methods listed in the following section: SQL/JSON Path Operators And Methods.

  • Parentheses that are used to specify filter expressions or define the order of path evaluation.

  • Accessor operators that are listed in the table below.

Accessor operator Description Example

.key

."$varname"

A member accessor that returns an object member with the specified key. If the key name matches a named variable starting with $ or does not meet the JavaScript rules for an identifier, it must be enclosed in double quotes

$.items

.*

A wildcard member accessor that returns the values of all members located at the top level of the current object

$.items.*

.**

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

$.**

.**{level}

.**{start_level to end_level}

This member accessor selects only the specified levels of the JSON hierarchy. A nesting level is an integer value. The 0 level corresponds to the current object. To access the lowest nesting level, use the last keyword

$.**{2}

$.**{1 to 3}

[index]

[start_index to end_index]

An array element accessor that can be used 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, including the elements that correspond to the start_index and end_index.

The specified index can be an integer or an expression returning a single numeric value, which is automatically cast to integer. The 0 index corresponds to the first array element. You can also use the last keyword to denote the last array element

$.a[1]

$.a[3 to 5]

[*]

A wildcard array element accessor that returns all array elements

$.items[*]

You can use JSONPath syntax to create simpler queries than the SQL syntax allows.

JSONPath functions

The functions described below manipulate JSONB data. All these functions have vars and silent optional arguments. vars allows you to pass variables into a JSONPath expression. silent determines whether error handling is suppressed. These arguments are described below.

Create the book_orders and demand tables for test examples:

CREATE TABLE book_orders (
    id serial NOT NULL PRIMARY KEY,
    content jsonb NOT NULL
);

INSERT INTO book_orders (content)
VALUES('{ "supplier": "Jacob Johnson", "items": {"book": "Hyperion","qty": 3, "price": 21}}'),
        ('{ "supplier": "Adam Brown", "items": {"book": "War and Peace","qty": 2, "price": 26}}'),
        ('{ "supplier": "Andrew Nelson", "items": {"book": "1984","qty": 4, "price": 31}}');

CREATE TABLE demand(name text, max_price int);

INSERT INTO demand VALUES ('Jane', 25), ('Alex', 27 ), ('Max', 35);

jsonb_path_exists

jsonb_path_exists ( <target> jsonb, <path> jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

The function checks whether path returns any item for the target JSON value.

Examples:

SELECT jsonb_path_exists('{"a": 1}', '$.a');
-- returns true

SELECT jsonb_path_exists('{"a": 1}', '$.b');
-- returns false

SELECT jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- returns true

The following query determines if the JSON data stored in the content column of the book_orders table contains strings that have the Hyperion value:

SELECT jsonb_path_exists(content, '$.** ? (@ == "Hyperion")')
FROM book_orders ORDER by id;

$.** iterates through the JSON value hierarchy of the content field and returns all element values, regardless of their nesting level. @ refers to the current element value in the filter expression.

The result:

 jsonb_path_exists
-------------------
 true
 false
 false

jsonb_path_match

jsonb_path_match ( <target> jsonb, <path> jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

The function returns the result of the path predicate check for the target JSON value. The function takes into account only the first item of the result. If the result is not boolean, jsonb_path_match returns null.

Examples:

SELECT jsonb_path_match('{"a": 1}', '$.a == 1');
-- returns true

SELECT jsonb_path_match('{"a": 1}', '$.a >= 2');
-- returns false

SELECT jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}');
-- returns true

The following query also determines if the content column of the book_orders table contains strings that have the Hyperion value:

SELECT jsonb_path_match(content, 'exists($.** ? (@ == "Hyperion"))')
FROM book_orders ORDER by id;

The result:

 jsonb_path_match
-------------------
 true
 false
 false

jsonb_path_query

jsonb_path_query ( <target> jsonb, <path> jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

The function returns all JSON items that path extracts from the target JSON value.

Examples:

SELECT jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
-- returns 3, 4, 5

SELECT jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)');
-- returns 0 rows

The following query returns items where the qty field more than 2:

SELECT jsonb_path_query(content, '$.items[*] ? (@.qty > 2)')
FROM book_orders;

The result:

              jsonb_path_query
---------------------------------------------
 {"qty": 3, "book": "Hyperion", "price": 21}
 {"qty": 4, "book": "1984", "price": 31}

jsonb_path_query_array

jsonb_path_query_array ( <target> jsonb, <path> jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

The function returns all JSON items that path extracts from the target JSON value as a JSON array.

Examples:

SELECT jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
-- returns [3, 4, 5]

SELECT jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)');
-- returns []

jsonb_path_query_first

jsonb_path_query_first ( <target> jsonb, <path> jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

The function returns the first JSON item that path extracts from the target JSON value. jsonb_path_query_first returns null if there are no results.

Examples:

SELECT jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
-- returns 3

SELECT jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)');
-- returns null

JSONPath functions with the _tz suffix

PostgreSQL provides the following functions with the _tz suffix: jsonb_path_exists_tz, jsonb_path_match_tz,jsonb_path_query_tz, jsonb_path_query_array_tz, and jsonb_path_query_first_tz. These functions operate as their counterparts described above without the _tz suffix, but support comparisons of date/time values that require timezone-aware conversions.

The example below requires interpretation of the date-only value 2022-08-02 as a timestamp with time zone. The result depends on the current TimeZone setting. Because of this dependency, PostgreSQL marks these functions as stable, which means that you cannot use these functions in indexes.

Examples:

SELECT jsonb_path_exists_tz('["2022-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2022-08-02".datetime())');
-- returns false

SELECT jsonb_path_exists_tz('["2022-08-01 12:00:00"]', '$[*] ? (@.datetime() < "2022-08-02".datetime())');
-- returns true

The vars argument

The vars argument allows you to pass variables into a JSONPath expression. It must be a JSON object, and its fields must contain named values to be substituted into the JSONPath expression.

Execute the following query with the vars argument to find books with a price lower than max_price specified for Alex in the demand table:

SELECT jsonb_path_query(content, '$.items[*]? (@.price < $max_price)',
    vars => jsonb_build_object('max_price', demand.max_price))
    FROM book_orders, demand WHERE name = 'Alex';

The vars argument passes the result of the query that determines max_price specified for Alex to the jsonb_path_query function.

The result:

                 jsonb_path_query
--------------------------------------------------
 {"qty": 3, "book": "Hyperion", "price": 21}
 {"qty": 2, "book": "War and Peace", "price": 26}

The silent argument

silent suppresses error handling. Execute the following query, in which the strict modifier changes the error handling mode:

SELECT jsonb_path_query('[]', 'strict $.a');

An error occurs:

ERROR: jsonpath member accessor can only be applied to an object

Set silent to true:

SELECT jsonb_path_query('[]', 'strict $.a', silent => true);

The error is not displayed. The result:

 jsonb_path_query
------------------
(0 rows)

JSONPath operators

The match operator @@

The @@ operator returns the result of the JSONPath predicate check for the specified JSON value. PostgreSQL takes into account only the first item of the result. If the result is not boolean, @@ returns null.

The following query determines if the content column of the book_orders table contains strings that have the Hyperion value:

SELECT content @@ '$.** == "Hyperion"' AS hyperion FROM book_orders;

The result:

hyperion
----------
true
null
null

The existence check operator @?

The @? operator determines whether a JSONPath expression returns any item for the specified JSON value.

The following query uses @? to select books with the price value more than 25.

SELECT jsonb_path_query(content, '$.items.book')
FROM book_orders WHERE content @? '$.items[*] ? (@.price > 25)';

The result:

 jsonb_path_query
------------------
 "War and Peace"
 "1984"

The strict and lax modes

When you query JSON data, the JSONPath expression may not match the actual JSON data structure. An attempt to access a non-existent member or element is a structural error. JSONPath expressions have two modes of handling structural errors:

  • lax (default) — the path engine implicitly adapts the queried data to the specified path. Any remaining structural errors are suppressed and converted to empty SQL/JSON sequences.

  • strict — if a structural error occurs, an error is raised.

Use the lax and strict modifiers to switch modes.

To see how these modes operate, search the b key in a JSON value that does not contain this key. In the lax mode, the result is false:

SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)';

The result:

 ?column?
----------
 false

In the strict mode, it returns null:

SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)';

The result:

 ?column?
----------
null

In the lax mode, an array with a complex hierarchy [1,3,[5,7,9]] is processed as [1,3,5,7,9]:

SELECT jsonb '[1,3,[5,7,9]]' @? 'lax $[*] ? (@ == 9)';

The result is true.

In the strict mode, the query returns false:

SELECT jsonb '[1,3,[5,7,9]]' @? 'strict $[*] ? (@ == 9)';

Replace @ with @[*] to get the true result in the strict mode:

SELECT jsonb '[1,3,[5,7,9]]' @? 'strict $[*] ? (@[*] == 9)';

For more information on modes, see Strict and lax modes.

Found a mistake? Seleсt text and press Ctrl+Enter to report it