postgresqljsonbjsonpath

Comparing date strings with jsonpath expression

I'm trying to run the following query, which unless I'm misunderstanding the documentation looks 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.

Solution

  • 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 ...)