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

Commit66ea94e

Browse files
committed
Implement various jsonpath methods
This commit implements ithe jsonpath .bigint(), .boolean(),.date(), .decimal([precision [, scale]]), .integer(), .number(),.string(), .time(), .time_tz(), .timestamp(), and .timestamp_tz()methods..bigint() converts the given JSON string or a numeric value tothe bigint type representation..boolean() converts the given JSON string, numeric, or booleanvalue to the boolean type representation. In the numeric case, onlyintegers are allowed. We use the parse_bool() backend functionto convert a string to a bool..decimal([precision [, scale]]) converts the given JSON stringor a numeric value to the numeric type representation. If precisionand scale are provided for .decimal(), then it is converted to theequivalent numeric typmod and applied to the numeric number..integer() and .number() convert the given JSON string or anumeric value to the int4 and numeric type representation..string() uses the datatype's output function to convert numericand various date/time types to the string representation.The JSON string representing a valid date/time is converted to thespecific date or time type representation using jsonpath .date(),.time(), .time_tz(), .timestamp(), .timestamp_tz() methods. Thechanges use the infrastructure of the .datetime() method and performthe datatype conversion as appropriate. Unlike the .datetime()method, none of these methods accept a format template and use ISODateTime format instead. However, except for .date(), thedate/time related methods take an optional precision to adjust thefractional seconds.Jeevan Chalke, reviewed by Peter Eisentraut and Andrew Dunstan.
1 parent924d046 commit66ea94e

File tree

11 files changed

+3586
-47
lines changed

11 files changed

+3586
-47
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 218 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17726,6 +17726,38 @@ strict $.**.HR
1772617726
</para></entry>
1772717727
</row>
1772817728

17729+
<row>
17730+
<entry role="func_table_entry"><para role="func_signature">
17731+
<replaceable>value</replaceable> <literal>.</literal> <literal>boolean()</literal>
17732+
<returnvalue><replaceable>boolean</replaceable></returnvalue>
17733+
</para>
17734+
<para>
17735+
Boolean value converted from a JSON boolean, number, or string
17736+
</para>
17737+
<para>
17738+
<literal>jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()')</literal>
17739+
<returnvalue>[true, true, false]</returnvalue>
17740+
</para></entry>
17741+
</row>
17742+
17743+
<row>
17744+
<entry role="func_table_entry"><para role="func_signature">
17745+
<replaceable>value</replaceable> <literal>.</literal> <literal>string()</literal>
17746+
<returnvalue><replaceable>string</replaceable></returnvalue>
17747+
</para>
17748+
<para>
17749+
String value converted from a JSON boolean, number, string, or datetime
17750+
</para>
17751+
<para>
17752+
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
17753+
<returnvalue>["1.23", "xyz", "false"]</returnvalue>
17754+
</para>
17755+
<para>
17756+
<literal>jsonb_path_query('"2023-08-15"', '$.datetime().string()')</literal>
17757+
<returnvalue>"2023-08-15"</returnvalue>
17758+
</para></entry>
17759+
</row>
17760+
1772917761
<row>
1773017762
<entry role="func_table_entry"><para role="func_signature">
1773117763
<replaceable>value</replaceable> <literal>.</literal> <literal>double()</literal>
@@ -17783,6 +17815,62 @@ strict $.**.HR
1778317815
</para></entry>
1778417816
</row>
1778517817

17818+
<row>
17819+
<entry role="func_table_entry"><para role="func_signature">
17820+
<replaceable>value</replaceable> <literal>.</literal> <literal>bigint()</literal>
17821+
<returnvalue><replaceable>bigint</replaceable></returnvalue>
17822+
</para>
17823+
<para>
17824+
Big integer value converted from a JSON number or string
17825+
</para>
17826+
<para>
17827+
<literal>jsonb_path_query('{"len": "9876543219"}', '$.len.bigint()')</literal>
17828+
<returnvalue>9876543219</returnvalue>
17829+
</para></entry>
17830+
</row>
17831+
17832+
<row>
17833+
<entry role="func_table_entry"><para role="func_signature">
17834+
<replaceable>value</replaceable> <literal>.</literal> <literal>decimal( [ <replaceable>precision</replaceable> [ , <replaceable>scale</replaceable> ] ] )</literal>
17835+
<returnvalue><replaceable>decimal</replaceable></returnvalue>
17836+
</para>
17837+
<para>
17838+
Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values.
17839+
</para>
17840+
<para>
17841+
<literal>jsonb_path_query('1234.5678', '$.decimal(6, 2)')</literal>
17842+
<returnvalue>1234.57</returnvalue>
17843+
</para></entry>
17844+
</row>
17845+
17846+
<row>
17847+
<entry role="func_table_entry"><para role="func_signature">
17848+
<replaceable>value</replaceable> <literal>.</literal> <literal>integer()</literal>
17849+
<returnvalue><replaceable>integer</replaceable></returnvalue>
17850+
</para>
17851+
<para>
17852+
Integer value converted from a JSON number or string
17853+
</para>
17854+
<para>
17855+
<literal>jsonb_path_query('{"len": "12345"}', '$.len.integer()')</literal>
17856+
<returnvalue>12345</returnvalue>
17857+
</para></entry>
17858+
</row>
17859+
17860+
<row>
17861+
<entry role="func_table_entry"><para role="func_signature">
17862+
<replaceable>value</replaceable> <literal>.</literal> <literal>number()</literal>
17863+
<returnvalue><replaceable>numeric</replaceable></returnvalue>
17864+
</para>
17865+
<para>
17866+
Numeric value converted from a JSON number or string
17867+
</para>
17868+
<para>
17869+
<literal>jsonb_path_query('{"len": "123.45"}', '$.len.number()')</literal>
17870+
<returnvalue>123.45</returnvalue>
17871+
</para></entry>
17872+
</row>
17873+
1778617874
<row>
1778717875
<entry role="func_table_entry"><para role="func_signature">
1778817876
<replaceable>string</replaceable> <literal>.</literal> <literal>datetime()</literal>
@@ -17814,6 +17902,136 @@ strict $.**.HR
1781417902
</para></entry>
1781517903
</row>
1781617904

17905+
<row>
17906+
<entry role="func_table_entry"><para role="func_signature">
17907+
<replaceable>string</replaceable> <literal>.</literal> <literal>date()</literal>
17908+
<returnvalue><replaceable>date</replaceable></returnvalue>
17909+
</para>
17910+
<para>
17911+
Date value converted from a string
17912+
</para>
17913+
<para>
17914+
<literal>jsonb_path_query('"2023-08-15"', '$.date()')</literal>
17915+
<returnvalue>"2023-08-15"</returnvalue>
17916+
</para></entry>
17917+
</row>
17918+
17919+
<row>
17920+
<entry role="func_table_entry"><para role="func_signature">
17921+
<replaceable>string</replaceable> <literal>.</literal> <literal>time()</literal>
17922+
<returnvalue><replaceable>time without time zone</replaceable></returnvalue>
17923+
</para>
17924+
<para>
17925+
Time without time zone value converted from a string
17926+
</para>
17927+
<para>
17928+
<literal>jsonb_path_query('"12:34:56"', '$.time()')</literal>
17929+
<returnvalue>"12:34:56"</returnvalue>
17930+
</para></entry>
17931+
</row>
17932+
17933+
<row>
17934+
<entry role="func_table_entry"><para role="func_signature">
17935+
<replaceable>string</replaceable> <literal>.</literal> <literal>time(<replaceable>precision</replaceable>)</literal>
17936+
<returnvalue><replaceable>time without time zone</replaceable></returnvalue>
17937+
</para>
17938+
<para>
17939+
Time without time zone value converted from a string, with fractional
17940+
seconds adjusted to the given precision.
17941+
</para>
17942+
<para>
17943+
<literal>jsonb_path_query('"12:34:56.789"', '$.time(2)')</literal>
17944+
<returnvalue>"12:34:56.79"</returnvalue>
17945+
</para></entry>
17946+
</row>
17947+
17948+
<row>
17949+
<entry role="func_table_entry"><para role="func_signature">
17950+
<replaceable>string</replaceable> <literal>.</literal> <literal>time_tz()</literal>
17951+
<returnvalue><replaceable>time with time zone</replaceable></returnvalue>
17952+
</para>
17953+
<para>
17954+
Time with time zone value converted from a string
17955+
</para>
17956+
<para>
17957+
<literal>jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()')</literal>
17958+
<returnvalue>"12:34:56+05:30"</returnvalue>
17959+
</para></entry>
17960+
</row>
17961+
17962+
<row>
17963+
<entry role="func_table_entry"><para role="func_signature">
17964+
<replaceable>string</replaceable> <literal>.</literal> <literal>time_tz(<replaceable>precision</replaceable>)</literal>
17965+
<returnvalue><replaceable>time with time zone</replaceable></returnvalue>
17966+
</para>
17967+
<para>
17968+
Time with time zone value converted from a string, with fractional
17969+
seconds adjusted to the given precision.
17970+
</para>
17971+
<para>
17972+
<literal>jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)')</literal>
17973+
<returnvalue>"12:34:56.79+05:30"</returnvalue>
17974+
</para></entry>
17975+
</row>
17976+
17977+
<row>
17978+
<entry role="func_table_entry"><para role="func_signature">
17979+
<replaceable>string</replaceable> <literal>.</literal> <literal>timestamp()</literal>
17980+
<returnvalue><replaceable>timestamp without time zone</replaceable></returnvalue>
17981+
</para>
17982+
<para>
17983+
Timestamp without time zone value converted from a string
17984+
</para>
17985+
<para>
17986+
<literal>jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()')</literal>
17987+
<returnvalue>"2023-08-15T12:34:56"</returnvalue>
17988+
</para></entry>
17989+
</row>
17990+
17991+
<row>
17992+
<entry role="func_table_entry"><para role="func_signature">
17993+
<replaceable>string</replaceable> <literal>.</literal> <literal>timestamp(<replaceable>precision</replaceable>)</literal>
17994+
<returnvalue><replaceable>timestamp without time zone</replaceable></returnvalue>
17995+
</para>
17996+
<para>
17997+
Timestamp without time zone value converted from a string, with
17998+
fractional seconds adjusted to the given precision.
17999+
</para>
18000+
<para>
18001+
<literal>jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)')</literal>
18002+
<returnvalue>"2023-08-15T12:34:56.79"</returnvalue>
18003+
</para></entry>
18004+
</row>
18005+
18006+
<row>
18007+
<entry role="func_table_entry"><para role="func_signature">
18008+
<replaceable>string</replaceable> <literal>.</literal> <literal>timestamp_tz()</literal>
18009+
<returnvalue><replaceable>timestamp with time zone</replaceable></returnvalue>
18010+
</para>
18011+
<para>
18012+
Timestamp with time zone value converted from a string
18013+
</para>
18014+
<para>
18015+
<literal>jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()')</literal>
18016+
<returnvalue>"2023-08-15T12:34:56+05:30"</returnvalue>
18017+
</para></entry>
18018+
</row>
18019+
18020+
<row>
18021+
<entry role="func_table_entry"><para role="func_signature">
18022+
<replaceable>string</replaceable> <literal>.</literal> <literal>timestamp_tz(<replaceable>precision</replaceable>)</literal>
18023+
<returnvalue><replaceable>timestamp with time zone</replaceable></returnvalue>
18024+
</para>
18025+
<para>
18026+
Timestamp with time zone value converted from a string, with fractional
18027+
seconds adjusted to the given precision.
18028+
</para>
18029+
<para>
18030+
<literal>jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')</literal>
18031+
<returnvalue>"2023-08-15T12:34:56.79+05:30"</returnvalue>
18032+
</para></entry>
18033+
</row>
18034+
1781718035
<row>
1781818036
<entry role="func_table_entry"><para role="func_signature">
1781918037
<replaceable>object</replaceable> <literal>.</literal> <literal>keyvalue()</literal>

‎src/backend/catalog/sql_features.txt

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -574,20 +574,20 @@ T861SQL/JSON simplified accessor: case-sensitive JSON member accessorNO
574574
T862SQL/JSON simplified accessor: wildcard member accessorNO
575575
T863SQL/JSON simplified accessor: single-quoted string literal as member accessorNO
576576
T864SQL/JSON simplified accessorNO
577-
T865SQL/JSON item method: bigint()NO
578-
T866SQL/JSON item method: boolean()NO
579-
T867SQL/JSON item method: date()NO
580-
T868SQL/JSON item method: decimal()NO
581-
T869SQL/JSON item method: decimal() with precision and scaleNO
582-
T870SQL/JSON item method: integer()NO
583-
T871SQL/JSON item method: number()NO
584-
T872SQL/JSON item method: string()NO
585-
T873SQL/JSON item method: time()NO
586-
T874SQL/JSON item method: time_tz()NO
587-
T875SQL/JSON item method: time precisionNO
588-
T876SQL/JSON item method: timestamp()NO
589-
T877SQL/JSON item method: timestamp_tz()NO
590-
T878SQL/JSON item method: timestamp precisionNO
577+
T865SQL/JSON item method: bigint()YES
578+
T866SQL/JSON item method: boolean()YES
579+
T867SQL/JSON item method: date()YES
580+
T868SQL/JSON item method: decimal()YES
581+
T869SQL/JSON item method: decimal() with precision and scaleYES
582+
T870SQL/JSON item method: integer()YES
583+
T871SQL/JSON item method: number()YES
584+
T872SQL/JSON item method: string()YES
585+
T873SQL/JSON item method: time()YES
586+
T874SQL/JSON item method: time_tz()YES
587+
T875SQL/JSON item method: time precisionYES
588+
T876SQL/JSON item method: timestamp()YES
589+
T877SQL/JSON item method: timestamp_tz()YES
590+
T878SQL/JSON item method: timestamp precisionYES
591591
T879JSON in equality operationsYESwith jsonb
592592
T880JSON in grouping operationsYESwith jsonb
593593
T881JSON in ordering operationsNOwith jsonb, partially supported

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp