From bffe1bd68457e43925c362d8728ce3b25bdf1c94 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Wed, 25 Sep 2019 21:54:14 +0300 Subject: Implement jsonpath .datetime() method This commit implements jsonpath .datetime() method as it's specified in SQL/JSON standard. There are no-argument and single-argument versions of this method. No-argument version selects first of ISO datetime formats matching input string. Single-argument version accepts template string as its argument. Additionally to .datetime() method itself this commit also implements comparison ability of resulting date and time values. There is some difficulty because exising jsonb_path_*() functions are immutable, while comparison of timezoned and non-timezoned types involves current timezone. At first, current timezone could be changes in session. Moreover, timezones themselves are not immutable and could be updated. This is why we let existing immutable functions throw errors on such non-immutable comparison. In the same time this commit provides jsonb_path_*_tz() functions which are stable and support operations involving timezones. As new functions are added to the system catalog, catversion is bumped. Support of .datetime() method was the only blocker prevents T832 from being marked as supported. sql_features.txt is updated correspondingly. Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov. Heavily revised by me. Comments were adjusted by Liudmila Mantrova. Discussion: https://postgr.es/m/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.com Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com Author: Alexander Korotkov, Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Liudmila Mantrova Reviewed-by: Anastasia Lubennikova, Peter Eisentraut --- doc/src/sgml/func.sgml | 117 ++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 101 insertions(+), 16 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 3d3d9d91836..67f1a828a89 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11910,16 +11910,6 @@ table2-mapping - - - .datetime() item method is not implemented yet - mainly because immutable jsonpath functions and operators - cannot reference session timezone, which is used in some datetime - operations. Datetime support will be added to jsonpath - in future versions of PostgreSQL. - - - A path expression can be a Boolean predicate, although the SQL/JSON @@ -12190,6 +12180,20 @@ table2-mapping $.z.abs() 0.3 + + datetime() + Date/time value converted from a string + ["2015-8-1", "2015-08-12"] + $[*] ? (@.datetime() < "2015-08-2". datetime()) + 2015-8-1 + + + datetime(template) + Date/time value converted from a string using the specified template + ["12:30", "18:40"] + $[*].datetime("HH24:MI") + "12:30:00", "18:40:00" + keyvalue() @@ -12207,6 +12211,37 @@ table2-mapping + + + The result type of datetime() and + datetime(template) + methods can be date, timetz, time, + timestamptz, or timestamp. + Both methods determine the result type dynamically. + + + The datetime() method sequentially tries ISO formats + for date, timetz, time, + timestamptz, and timestamp. It stops on + the first matching format and the corresponding data type. + + + The datetime(template) + method determines the result type by the provided template string. + + + The datetime() and + datetime(template) methods + use the same parsing rules as to_timestamp SQL + function does (see ) with three + exceptions. At first, these methods doesn't allow unmatched template + patterns. At second, only following separators are allowed in the + template string: minus sign, period, solidus, comma, apostrophe, + semicolon, colon and space. At third, separators in the template string + must exactly match the input string. + + + <type>jsonpath</type> Filter Expression Elements @@ -12350,6 +12385,15 @@ table2-mapping
+ + + + When different date/time values are compared, an implicit cast is + applied. A date value can be cast to timestamp + or timestamptz, timestamp can be cast to + timestamptz, and time — to timetz. + + @@ -12582,7 +12626,7 @@ table2-mapping The @? and @@ operators suppress the following errors: lacking object field or array element, unexpected - JSON item type, and numeric errors. + JSON item type, datetime and numeric errors. This behavior might be helpful while searching over JSON document collections of varying structure. @@ -12851,18 +12895,33 @@ table2-mapping jsonb_path_exists + + jsonb_path_exists_tz + jsonb_path_match + + jsonb_path_match_tz + jsonb_path_query + + jsonb_path_query_tz + jsonb_path_query_array + + jsonb_path_query_array_tz + jsonb_path_query_first + + jsonb_path_query_first_tz + JSON Processing Functions @@ -13202,6 +13261,9 @@ table2-mapping jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) + + jsonb_path_exists_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + boolean @@ -13222,6 +13284,9 @@ table2-mapping jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + jsonb_path_match_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + boolean @@ -13243,6 +13308,9 @@ table2-mapping jsonb_path_query(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + jsonb_path_query_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + setof jsonb @@ -13271,6 +13339,9 @@ table2-mapping jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + jsonb_path_query_array_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + jsonb @@ -13291,6 +13362,9 @@ table2-mapping jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + jsonb_path_query_first_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + jsonb @@ -13433,11 +13507,8 @@ table2-mapping - The jsonb_path_exists, jsonb_path_match, - jsonb_path_query, jsonb_path_query_array, and - jsonb_path_query_first - functions have optional vars and silent - arguments. + The jsonb_path_* functions have optional + vars and silent arguments. If the vars argument is specified, it provides an @@ -13451,6 +13522,20 @@ table2-mapping + + + Some of the jsonb_path_* functions have the + _tz suffix. These functions have been implemented to + support comparison of date/time values that involves implicit + timezone-aware casts. Since operations with time zones are not immutable, + these functions are qualified as stable. Their counterparts without the + suffix do not support such casts, so they are immutable and can be used for + such use-cases as expression indexes + (see ). There is no difference + between these functions for other jsonpath operations. + + + See also for the aggregate function json_agg which aggregates record -- cgit v1.2.3