Question 174604 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Comparing date strings with jsonpath expression

I’m trying to run the following query, which unless I’m misunderstanding the documentation looks as like it should be possible.

SELECT id, profile
FROM end_user
WHERE profile @? '$.first_segment_exited_at ? (@.datetime() < "2023-05-25".datetime())'::jsonpath;

This is the documentation which leads me to think I should be able to achieve this:

string . datetime() → datetime_type (see note)

Date/time value converted from a string

jsonb_path_query(‘["2015-8-1", "2015-08-12"]’, ‘$[*] ? (@.datetime() <
"2015-08-2".datetime())’) → "2015-8-1"

Example profile data, note the dates are valid ISO format;

{
  "first_segment_exited_at": "2023-05-22T03:09:37.825Z",
}

The following query returns results, but I’m keen on switching to jsonpath syntax in order to utilize an index

select id, profile
from end_user
where '2023-05-25'::timestamptz > (profile->>'first_segment_exited_at')::timestamptz

I’m running Postgres v14.

Tags:

2

Answers


  1. 0

    Illegal JSON datetime format in the table column. This works:

    CREATE TABLE end_user (id int, profile jsonb);
INSERT INTO end_user VALUES
  (1, '{"first_segment_exited_at": "2023-05-22T03:09:37.825Z"}')
, (2, '{"first_segment_exited_at": "2023-05-22T03:09:37Z"}')  -- no fractional sec
, (3, '{"first_segment_exited_at": "2023-05-22T03:09:37"}')   -- no trailing "Z", either
;

SELECT *
FROM   end_user
WHERE  profile @? '$.first_segment_exited_at ? (@.datetime() < "2023-05-25".datetime())';

    fiddle

    Row 3 is the only one with legal format.
    (The filter expression needs to be valid, too.)

    You didn’t get an error message because, quoting the manual:

    The jsonpath operators @? and @@ suppress the following errors:
    missing object field or array element, unexpected JSON item type,
    datetime and numeric errors. […] This behavior might be helpful when
    searching JSON document collections of varying structure.

    To debug input strings, test with a function that doesn’t suppress the error. Like jsonb_path_query_first():

    SELECT jsonb_path_query_first('{"d": "2023-05-22T03:09:37.1"}', '$.d.datetime()')

    ERROR:  datetime format is not recognized: "2023-05-22T03:09:37.1"
HINT:  Use a datetime template argument to specify the input data format.

    But this one flies:

    SELECT jsonb_path_query_first('{"d": "2023-05-22T03:09:37"}', '$.d.datetime()')

    Also, you can supply a template pattern to the .datetime() method like Laurenz demonstrates in his added answer. (More obstacles ahead …)

    Login or Signup to reply.
  2. 0

    The problem is that your date is not in a format that datetime() recognizes, and you have to specify a template string with datetime(). Use the following jsonpath:

    '$.first_segment_exited_at ? (@.datetime("YYYY-MM-DD"T"HH24:MI:SS.MS"Z"") < "2023-05-25".datetime())'::jsonpath

    I had to use the (I believe undocumented) PostgreSQL extension of double quoted literal format strings to match the T and Z in the string.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search