PostgreSQL jsonb_path_query_first_tz() Function

The PostgreSQL jsonb_path_query_first_tz() function gets the value in a given JSON according to the specified path and returns the first matching value. This function jsonb_path_query_first() differs from in that it provides support for date/time with time zones.

jsonb_path_query_first_tz() Syntax

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

jsonb_path_query_first_tz(
    target JSONB
  , path JSONPATH
  [, vars JSONB
  [, silent BOOLEAN]]
) -> 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_first_tz() function returns a JSON value that is the first value in the specified JSON value that matches the specified path.

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

jsonb_path_query_first_tz() Examples

JSON array

The following example shows how to use the PostgreSQL jsonb_path_query_first_tz() function to get the first value matching a specified path from a JSON array.

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

 jsonb_path_query_first_tz
---------------------------
 2

We can use variables in JSON paths like this:

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

 jsonb_path_query_first_tz
---------------------------
 2

Here, we are using two variables min and max in the JSON path $[*] ? (@ >= $min && @ <= $max), and we have provided values ​​for the variables var in {"min": 2, "max": 3}, so that the JSON path becomes $[*] ? (@ >= 2 && @ <= 3).

JSON object

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

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

 jsonb_path_query_first_tz
---------------------------
 2

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

Time zone

The PostgreSQL jsonb_path_query_first_tz() function supports timestamps with time zones. as follows:

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

 jsonb_path_query_first_tz
---------------------------
 "2015-08-01 12:00:00 +00"