Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbffe1bd

Browse files
committed
Implement jsonpath .datetime() method
This commit implements jsonpath .datetime() method as it's specified inSQL/JSON standard. There are no-argument and single-argument versions ofthis method. No-argument version selects first of ISO datetime formatsmatching input string. Single-argument version accepts template string asits argument.Additionally to .datetime() method itself this commit also implementscomparison ability of resulting date and time values. There is some difficultybecause exising jsonb_path_*() functions are immutable, while comparison oftimezoned and non-timezoned types involves current timezone. At first, currenttimezone could be changes in session. Moreover, timezones themselves are notimmutable and could be updated. This is why we let existing immutable functionsthrow errors on such non-immutable comparison. In the same time this commitprovides jsonb_path_*_tz() functions which are stable and support operationsinvolving timezones. As new functions are added to the system catalog,catversion is bumped.Support of .datetime() method was the only blocker prevents T832 from beingmarked 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.comDiscussion:https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.comAuthor: Alexander Korotkov, Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Liudmila MantrovaReviewed-by: Anastasia Lubennikova, Peter Eisentraut
1 parent6dda292 commitbffe1bd

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,datetimeand 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 @@ T828JSON_QUERYNO
544544
T829JSON_QUERY: array wrapper optionsNO
545545
T830Enforcing unique keys in SQL/JSON constructor functionsNO
546546
T831SQL/JSON path language: strict modeYES
547-
T832SQL/JSON path language: item methodNOdatetime() not yet implemented
547+
T832SQL/JSON path language: item methodYES
548548
T833SQL/JSON path language: multiple subscriptsYES
549549
T834SQL/JSON path language: wildcard member accessorYES
550550
T835SQL/JSON path language: filter expressionsYES

‎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+
CREATEOR REPLACE FUNCTION
1291+
jsonb_path_exists_tz(target jsonb,path jsonpath, vars jsonb DEFAULT'{}',
1292+
silentboolean DEFAULT false)
1293+
RETURNSboolean
1294+
LANGUAGE INTERNAL
1295+
STRICT STABLE PARALLEL SAFE
1296+
AS'jsonb_path_exists_tz';
1297+
1298+
CREATEOR REPLACE FUNCTION
1299+
jsonb_path_match_tz(target jsonb,path jsonpath, vars jsonb DEFAULT'{}',
1300+
silentboolean DEFAULT false)
1301+
RETURNSboolean
1302+
LANGUAGE INTERNAL
1303+
STRICT STABLE PARALLEL SAFE
1304+
AS'jsonb_path_match_tz';
1305+
1306+
CREATEOR REPLACE FUNCTION
1307+
jsonb_path_query_tz(target jsonb,path jsonpath, vars jsonb DEFAULT'{}',
1308+
silentboolean DEFAULT false)
1309+
RETURNS SETOF jsonb
1310+
LANGUAGE INTERNAL
1311+
STRICT STABLE PARALLEL SAFE
1312+
AS'jsonb_path_query_tz';
1313+
1314+
CREATEOR REPLACE FUNCTION
1315+
jsonb_path_query_array_tz(target jsonb,path jsonpath, vars jsonb DEFAULT'{}',
1316+
silentboolean DEFAULT false)
1317+
RETURNS jsonb
1318+
LANGUAGE INTERNAL
1319+
STRICT STABLE PARALLEL SAFE
1320+
AS'jsonb_path_query_array_tz';
1321+
1322+
CREATEOR REPLACE FUNCTION
1323+
jsonb_path_query_first_tz(target jsonb,path jsonpath, vars jsonb DEFAULT'{}',
1324+
silentboolean 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
casejpiPlus:
338338
casejpiMinus:
339339
casejpiExists:
340+
casejpiDatetime:
340341
{
341342
int32arg=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
casejpiDouble:
693695
appendBinaryStringInfo(buf,".double()",9);
694696
break;
697+
casejpiDatetime:
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
casejpiKeyValue:
696707
appendBinaryStringInfo(buf,".keyvalue()",11);
697708
break;
@@ -754,6 +765,8 @@ jspOperationName(JsonPathItemType type)
754765
return"floor";
755766
casejpiCeiling:
756767
return"ceiling";
768+
casejpiDatetime:
769+
return"datetime";
757770
default:
758771
elog(ERROR,"unrecognized jsonpath item type: %d",type);
759772
returnNULL;
@@ -889,6 +902,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
889902
casejpiPlus:
890903
casejpiMinus:
891904
casejpiFilter:
905+
casejpiDatetime:
892906
read_int32(v->content.arg,base,pos);
893907
break;
894908
casejpiIndexArray:
@@ -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)
[8]ページ先頭
©2009-2025 Movatter.jp