PostgreSQL jsonb_path_match_tz() Function

The PostgreSQL jsonb_path_match_tz() function returns the result of executing a JSON path assertion against a specified JSON value. This function differs from jsonb_path_match() in that it provides support for date/time with time zones.

jsonb_path_match_tz() Syntax

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

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

Parameters

target

Required. The JSONB value to check.

path

Required. The JSON path assertion to perform.

vars

Optional. The variable value 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_match_tz() function returns a boolean value that is the result of a JSON path assertion performed on a specified JSON value. t indicates that the specified JSON value matches the specified JSON path, and f indicates that the specified JSON value does not match the specified JSON path.

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

jsonb_path_match_tz() Examples

Basic example

The following example shows how to use the PostgreSQL jsonb_path_match_tz() function to check if a JSON array contains a value greater than 1.

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

 jsonb_path_match_tz
---------------------
 t

Here, we use path $[*] ? (@ > 2) to get items greater than 2 in the JSON array [1, 2, 3]. Instead, exists($[*] ? (@ > 1)) check if the JSON array contains items greater than 2. The statement is the same as jsonb_path_exists():

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

 jsonb_path_match_tz
---------------------
 t

Time zone

The PostgreSQL jsonb_path_match_tz() function supports timestamps with time zones, as follows:

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

 jsonb_path_match_tz
---------------------
 t