PostgreSQL jsonb_path_exists_tz() Function

The PostgreSQL jsonb_path_exists_tz() function checks whether a specified path returns a value in a given JSON. This function differs from jsonb_path_exists() in that it provides support for date/time with time zones.

jsonb_path_exists_tz() Syntax

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

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

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. It is a JSON object.

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_exists_tz() function returns a boolean value that is the result of checking whether the specified path returns a value in a JSONB value. t indicates that the specified path has a return value, and f indicates that the specified path has no return value.

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

jsonb_path_exists_tz() Examples

Basic Usage

The following example shows how to use the PostgreSQL jsonb_path_exists_tz() function to check if a specified JSON path has a value in a JSON array.

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

 jsonb_path_exists_tz
----------------------
 t

Here, we use path $[*] ? (@ > 2) to get items greater than 2 in the JSON array [1, 2, 3]. We can use the jsonb_path_query_array() function to verify that if the above result is correct:

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

 jsonb_path_query_array
------------------------
 [2, 3]

We can use variables in JSON paths like this:

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

 jsonb_path_exists_tz
----------------------
 t

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

Time zone

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

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

 jsonb_path_exists_tz
----------------------
 t