PostgreSQL jsonb_path_query_tz() Function

Updated on 2026-09-27

The PostgreSQL jsonb_path_query_tz() function returns all JSON items selected by a JSONPath expression as a set. Unlike jsonb_path_query(), it supports comparisons of date/time values that require time zone-aware conversion.

`jsonb_path_query_tz()` Syntax

This is the syntax of the PostgreSQL jsonb_path_query_tz() function:

jsonb_path_query_tz(
     target JSONB
   , path JSONPATH
  [, vars JSONB
  [, silent BOOLEAN]]
) -> SETOF JSONB

Parameters

target: Required. The JSONB value to check.
path: Required. The JSON path to check, it is of JSONPATH type .
vars: Optional. The variable values used in the path.
silent: Optional. If this parameter is provided and is true, the function suppresses the same errors as the @? and @@ operators.

Return value

The PostgreSQL jsonb_path_query_tz() function returns a set of JSONB values that contains all the values in the specified JSON value that match the specified path.

If any parameter is NULL, the jsonb_path_query_tz() function will return NULL.

`jsonb_path_query_tz()` Examples

JSON array

The following example shows how to use the PostgreSQL jsonb_path_query_tz() function to get values from a JSON array by a specified path.

SELECT jsonb_path_query_tz('[1, 2, 3]', '$[*] ? (@ > 1)');

 jsonb_path_query_tz
---------------------
 2
 3

We can use variables in JSON paths like this:

SELECT jsonb_path_query_tz(
    '[1, 2, 3, 4]',
    '$[*] ? (@ >= $min && @ <= $max)',
    '{"min": 2, "max": 3}'
);

 jsonb_path_query_tz
---------------------
 2
 3

Here, we are using two variables min and max in the JSON path $[*] ? (@ >= $min && @ <= $max), and we have provided values for the variables in {"min": 2, "max": 3}, so that the JSON path becomes $[*] ? (@ >= 2 && @ <= 3). That is, this function is used to return all values that are greater than or equal to 2 and less than or equal to 3 in the array [1, 2, 3, 4].

JSON object

The following example shows how to use the PostgreSQL jsonb_path_query_tz() function to get the value from a JSON object according to the specified path.

SELECT jsonb_path_query_tz(
    '{"x": 1, "y": 2, "z": 3}',
    '$.* ? (@ >= 2)'
);

 jsonb_path_query_tz
---------------------
 2
 3

Here, JSON path $.* ? (@ >= 2) represents all values greater than 2 among the values of the top-level members in the JSON object {"x": 1, "y": 2, "z": 3}.

Time zone

These _tz functions are marked STABLE, not IMMUTABLE, because converting a date-only value to timestamptz can depend on the current TimeZone. PostgreSQL therefore does not allow them in index expressions. Their counterparts without _tz are immutable and indexable, but raise an error when a comparison requires time zone-aware conversion. See the PostgreSQL JSON functions documentation.

The following example compares date/time values that need time zone-aware conversion:

select
    jsonb_path_query_tz(
        '["2015-08-01 12:00:00 +00", "2015-08-01 13:00:00 +00"]',
        '$[*] ? (@.datetime() < "2015-08-02".datetime())'
    );

    jsonb_path_query_tz
---------------------------
 "2015-08-01 12:00:00 +00"
 "2015-08-01 13:00:00 +00"

jsonb_path_query_tz() Syntax