Skip to content

Commit bffe1bd

Browse files
akorotkovakorotkov
committed
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
1 parent 6dda292 commit bffe1bd

File tree

15 files changed

+1355
-45
lines changed

15 files changed

+1355
-45
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 101 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -11910,16 +11910,6 @@ table2-mapping
1191011910
</para>
1191111911

1191211912
<itemizedlist>
11913-
<listitem>
11914-
<para>
11915-
<literal>.datetime()</literal> item method is not implemented yet
11916-
mainly because immutable <type>jsonpath</type> functions and operators
11917-
cannot reference session timezone, which is used in some datetime
11918-
operations. Datetime support will be added to <type>jsonpath</type>
11919-
in future versions of <productname>PostgreSQL</productname>.
11920-
</para>
11921-
</listitem>
11922-
1192311913
<listitem>
1192411914
<para>
1192511915
A path expression can be a Boolean predicate, although the SQL/JSON
@@ -12190,6 +12180,20 @@ table2-mapping
1219012180
<entry><literal>$.z.abs()</literal></entry>
1219112181
<entry><literal>0.3</literal></entry>
1219212182
</row>
12183+
<row>
12184+
<entry><literal>datetime()</literal></entry>
12185+
<entry>Date/time value converted from a string</entry>
12186+
<entry><literal>["2015-8-1", "2015-08-12"]</literal></entry>
12187+
<entry><literal>$[*] ? (@.datetime() &lt; "2015-08-2". datetime())</literal></entry>
12188+
<entry><literal>2015-8-1</literal></entry>
12189+
</row>
12190+
<row>
12191+
<entry><literal>datetime(<replaceable>template</replaceable>)</literal></entry>
12192+
<entry>Date/time value converted from a string using the specified template</entry>
12193+
<entry><literal>["12:30", "18:40"]</literal></entry>
12194+
<entry><literal>$[*].datetime("HH24:MI")</literal></entry>
12195+
<entry><literal>"12:30:00", "18:40:00"</literal></entry>
12196+
</row>
1219312197
<row>
1219412198
<entry><literal>keyvalue()</literal></entry>
1219512199
<entry>
@@ -12207,6 +12211,37 @@ table2-mapping
1220712211
</tgroup>
1220812212
</table>
1220912213

12214+
<note>
12215+
<para>
12216+
The result type of <literal>datetime()</literal> and
12217+
<literal>datetime(<replaceable>template</replaceable>)</literal>
12218+
methods can be <type>date</type>, <type>timetz</type>, <type>time</type>,
12219+
<type>timestamptz</type>, or <type>timestamp</type>.
12220+
Both methods determine the result type dynamically.
12221+
</para>
12222+
<para>
12223+
The <literal>datetime()</literal> method sequentially tries ISO formats
12224+
for <type>date</type>, <type>timetz</type>, <type>time</type>,
12225+
<type>timestamptz</type>, and <type>timestamp</type>. It stops on
12226+
the first matching format and the corresponding data type.
12227+
</para>
12228+
<para>
12229+
The <literal>datetime(<replaceable>template</replaceable>)</literal>
12230+
method determines the result type by the provided template string.
12231+
</para>
12232+
<para>
12233+
The <literal>datetime()</literal> and
12234+
<literal>datetime(<replaceable>template</replaceable>)</literal> methods
12235+
use the same parsing rules as <literal>to_timestamp</literal> SQL
12236+
function does (see <xref linkend="functions-formatting"/>) with three
12237+
exceptions. At first, these methods doesn't allow unmatched template
12238+
patterns. At second, only following separators are allowed in the
12239+
template string: minus sign, period, solidus, comma, apostrophe,
12240+
semicolon, colon and space. At third, separators in the template string
12241+
must exactly match the input string.
12242+
</para>
12243+
</note>
12244+
1221012245
<table id="functions-sqljson-filter-ex-table">
1221112246
<title><type>jsonpath</type> Filter Expression Elements</title>
1221212247
<tgroup cols="5">
@@ -12350,6 +12385,15 @@ table2-mapping
1235012385
</tbody>
1235112386
</tgroup>
1235212387
</table>
12388+
12389+
<note>
12390+
<para>
12391+
When different date/time values are compared, an implicit cast is
12392+
applied. A <type>date</type> value can be cast to <type>timestamp</type>
12393+
or <type>timestamptz</type>, <type>timestamp</type> can be cast to
12394+
<type>timestamptz</type>, and <type>time</type> &mdash; to <type>timetz</type>.
12395+
</para>
12396+
</note>
1235312397
</sect3>
1235412398

1235512399
</sect2>
@@ -12582,7 +12626,7 @@ table2-mapping
1258212626
<para>
1258312627
The <literal>@?</literal> and <literal>@@</literal> operators suppress
1258412628
the following errors: lacking object field or array element, unexpected
12585-
JSON item type, and numeric errors.
12629+
JSON item type, datetime and numeric errors.
1258612630
This behavior might be helpful while searching over JSON document
1258712631
collections of varying structure.
1258812632
</para>
@@ -12851,18 +12895,33 @@ table2-mapping
1285112895
<indexterm>
1285212896
<primary>jsonb_path_exists</primary>
1285312897
</indexterm>
12898+
<indexterm>
12899+
<primary>jsonb_path_exists_tz</primary>
12900+
</indexterm>
1285412901
<indexterm>
1285512902
<primary>jsonb_path_match</primary>
1285612903
</indexterm>
12904+
<indexterm>
12905+
<primary>jsonb_path_match_tz</primary>
12906+
</indexterm>
1285712907
<indexterm>
1285812908
<primary>jsonb_path_query</primary>
1285912909
</indexterm>
12910+
<indexterm>
12911+
<primary>jsonb_path_query_tz</primary>
12912+
</indexterm>
1286012913
<indexterm>
1286112914
<primary>jsonb_path_query_array</primary>
1286212915
</indexterm>
12916+
<indexterm>
12917+
<primary>jsonb_path_query_array_tz</primary>
12918+
</indexterm>
1286312919
<indexterm>
1286412920
<primary>jsonb_path_query_first</primary>
1286512921
</indexterm>
12922+
<indexterm>
12923+
<primary>jsonb_path_query_first_tz</primary>
12924+
</indexterm>
1286612925

1286712926
<table id="functions-json-processing-table">
1286812927
<title>JSON Processing Functions</title>
@@ -13202,6 +13261,9 @@ table2-mapping
1320213261
<para><literal>
1320313262
jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])
1320413263
</literal></para>
13264+
<para><literal>
13265+
jsonb_path_exists_tz(target jsonb, path jsonpath [, vars jsonb, silent bool])
13266+
</literal></para>
1320513267
</entry>
1320613268
<entry><type>boolean</type></entry>
1320713269
<entry>
@@ -13222,6 +13284,9 @@ table2-mapping
1322213284
<para><literal>
1322313285
jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool])
1322413286
</literal></para>
13287+
<para><literal>
13288+
jsonb_path_match_tz(target jsonb, path jsonpath [, vars jsonb, silent bool])
13289+
</literal></para>
1322513290
</entry>
1322613291
<entry><type>boolean</type></entry>
1322713292
<entry>
@@ -13243,6 +13308,9 @@ table2-mapping
1324313308
<para><literal>
1324413309
jsonb_path_query(target jsonb, path jsonpath [, vars jsonb, silent bool])
1324513310
</literal></para>
13311+
<para><literal>
13312+
jsonb_path_query_tz(target jsonb, path jsonpath [, vars jsonb, silent bool])
13313+
</literal></para>
1324613314
</entry>
1324713315
<entry><type>setof jsonb</type></entry>
1324813316
<entry>
@@ -13271,6 +13339,9 @@ table2-mapping
1327113339
<para><literal>
1327213340
jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb, silent bool])
1327313341
</literal></para>
13342+
<para><literal>
13343+
jsonb_path_query_array_tz(target jsonb, path jsonpath [, vars jsonb, silent bool])
13344+
</literal></para>
1327413345
</entry>
1327513346
<entry><type>jsonb</type></entry>
1327613347
<entry>
@@ -13291,6 +13362,9 @@ table2-mapping
1329113362
<para><literal>
1329213363
jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb, silent bool])
1329313364
</literal></para>
13365+
<para><literal>
13366+
jsonb_path_query_first_tz(target jsonb, path jsonpath [, vars jsonb, silent bool])
13367+
</literal></para>
1329413368
</entry>
1329513369
<entry><type>jsonb</type></entry>
1329613370
<entry>
@@ -13433,11 +13507,8 @@ table2-mapping
1343313507

1343413508
<note>
1343513509
<para>
13436-
The <literal>jsonb_path_exists</literal>, <literal>jsonb_path_match</literal>,
13437-
<literal>jsonb_path_query</literal>, <literal>jsonb_path_query_array</literal>, and
13438-
<literal>jsonb_path_query_first</literal>
13439-
functions have optional <literal>vars</literal> and <literal>silent</literal>
13440-
arguments.
13510+
The <literal>jsonb_path_*</literal> functions have optional
13511+
<literal>vars</literal> and <literal>silent</literal> arguments.
1344113512
</para>
1344213513
<para>
1344313514
If the <parameter>vars</parameter> argument is specified, it provides an
@@ -13451,6 +13522,20 @@ table2-mapping
1345113522
</para>
1345213523
</note>
1345313524

13525+
<note>
13526+
<para>
13527+
Some of the <literal>jsonb_path_*</literal> functions have the
13528+
<literal>_tz</literal> suffix. These functions have been implemented to
13529+
support comparison of date/time values that involves implicit
13530+
timezone-aware casts. Since operations with time zones are not immutable,
13531+
these functions are qualified as stable. Their counterparts without the
13532+
suffix do not support such casts, so they are immutable and can be used for
13533+
such use-cases as expression indexes
13534+
(see <xref linkend="indexes-expressional"/>). There is no difference
13535+
between these functions for other <type>jsonpath</type> operations.
13536+
</para>
13537+
</note>
13538+
1345413539
<para>
1345513540
See also <xref linkend="functions-aggregate"/> for the aggregate
1345613541
function <function>json_agg</function> which aggregates record

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -544,7 +544,7 @@ T828 JSON_QUERY NO
544544
T829 JSON_QUERY: array wrapper options NO
545545
T830 Enforcing unique keys in SQL/JSON constructor functions NO
546546
T831 SQL/JSON path language: strict mode YES
547-
T832 SQL/JSON path language: item method NO datetime() not yet implemented
547+
T832 SQL/JSON path language: item method YES
548548
T833 SQL/JSON path language: multiple subscripts YES
549549
T834 SQL/JSON path language: wildcard member accessor YES
550550
T835 SQL/JSON path language: filter expressions YES

‎src/backend/catalog/system_views.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1287,6 +1287,46 @@ LANGUAGE INTERNAL
12871287
STRICT IMMUTABLE PARALLEL SAFE
12881288
AS 'jsonb_path_query_first';
12891289

1290+
CREATE OR REPLACE FUNCTION
1291+
jsonb_path_exists_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
1292+
silent boolean DEFAULT false)
1293+
RETURNS boolean
1294+
LANGUAGE INTERNAL
1295+
STRICT STABLE PARALLEL SAFE
1296+
AS 'jsonb_path_exists_tz';
1297+
1298+
CREATE OR REPLACE FUNCTION
1299+
jsonb_path_match_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
1300+
silent boolean DEFAULT false)
1301+
RETURNS boolean
1302+
LANGUAGE INTERNAL
1303+
STRICT STABLE PARALLEL SAFE
1304+
AS 'jsonb_path_match_tz';
1305+
1306+
CREATE OR REPLACE FUNCTION
1307+
jsonb_path_query_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
1308+
silent boolean DEFAULT false)
1309+
RETURNS SETOF jsonb
1310+
LANGUAGE INTERNAL
1311+
STRICT STABLE PARALLEL SAFE
1312+
AS 'jsonb_path_query_tz';
1313+
1314+
CREATE OR REPLACE FUNCTION
1315+
jsonb_path_query_array_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
1316+
silent boolean DEFAULT false)
1317+
RETURNS jsonb
1318+
LANGUAGE INTERNAL
1319+
STRICT STABLE PARALLEL SAFE
1320+
AS 'jsonb_path_query_array_tz';
1321+
1322+
CREATE OR REPLACE FUNCTION
1323+
jsonb_path_query_first_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}',
1324+
silent boolean DEFAULT false)
1325+
RETURNS jsonb
1326+
LANGUAGE INTERNAL
1327+
STRICT STABLE PARALLEL SAFE
1328+
AS 'jsonb_path_query_first_tz';
1329+
12901330
--
12911331
-- The default permissions for functions mean that anyone can execute them.
12921332
-- A number of functions shouldn't be executable by just anyone, but rather

‎src/backend/utils/adt/jsonpath.c

Lines changed: 20 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -337,12 +337,14 @@ flattenJsonPathParseItem(StringInfo buf, JsonPathParseItem *item,
337337
case jpiPlus:
338338
case jpiMinus:
339339
case jpiExists:
340+
case jpiDatetime:
340341
{
341342
int32 arg = reserveSpaceForItemPointer(buf);
342343

343-
chld = flattenJsonPathParseItem(buf, item->value.arg,
344-
nestingLevel + argNestingLevel,
345-
insideArraySubscript);
344+
chld = !item->value.arg ? pos :
345+
flattenJsonPathParseItem(buf, item->value.arg,
346+
nestingLevel + argNestingLevel,
347+
insideArraySubscript);
346348
*(int32 *) (buf->data + arg) = chld - pos;
347349
}
348350
break;
@@ -692,6 +694,15 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
692694
case jpiDouble:
693695
appendBinaryStringInfo(buf, ".double()", 9);
694696
break;
697+
case jpiDatetime:
698+
appendBinaryStringInfo(buf, ".datetime(", 10);
699+
if (v->content.arg)
700+
{
701+
jspGetArg(v, &elem);
702+
printJsonPathItem(buf, &elem, false, false);
703+
}
704+
appendStringInfoChar(buf, ')');
705+
break;
695706
case jpiKeyValue:
696707
appendBinaryStringInfo(buf, ".keyvalue()", 11);
697708
break;
@@ -754,6 +765,8 @@ jspOperationName(JsonPathItemType type)
754765
return "floor";
755766
case jpiCeiling:
756767
return "ceiling";
768+
case jpiDatetime:
769+
return "datetime";
757770
default:
758771
elog(ERROR, "unrecognized jsonpath item type: %d", type);
759772
return NULL;
@@ -889,6 +902,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
889902
case jpiPlus:
890903
case jpiMinus:
891904
case jpiFilter:
905+
case jpiDatetime:
892906
read_int32(v->content.arg, base, pos);
893907
break;
894908
case jpiIndexArray:
@@ -913,7 +927,8 @@ jspGetArg(JsonPathItem *v, JsonPathItem *a)
913927
v->type == jpiIsUnknown ||
914928
v->type == jpiExists ||
915929
v->type == jpiPlus ||
916-
v->type == jpiMinus);
930+
v->type == jpiMinus ||
931+
v->type == jpiDatetime);
917932

918933
jspInitByBuffer(a, v->base, v->content.arg);
919934
}
@@ -961,6 +976,7 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
961976
v->type == jpiFloor ||
962977
v->type == jpiCeiling ||
963978
v->type == jpiDouble ||
979+
v->type == jpiDatetime ||
964980
v->type == jpiKeyValue ||
965981
v->type == jpiStartsWith);
966982

0 commit comments

Comments
 (0)