From 66ea94e8e606529bb334515f388c62314956739e Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Thu, 25 Jan 2024 10:15:43 -0500 Subject: 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 to the bigint type representation. .boolean() converts the given JSON string, numeric, or boolean value to the boolean type representation. In the numeric case, only integers are allowed. We use the parse_bool() backend function to convert a string to a bool. .decimal([precision [, scale]]) converts the given JSON string or a numeric value to the numeric type representation. If precision and scale are provided for .decimal(), then it is converted to the equivalent numeric typmod and applied to the numeric number. .integer() and .number() convert the given JSON string or a numeric value to the int4 and numeric type representation. .string() uses the datatype's output function to convert numeric and various date/time types to the string representation. The JSON string representing a valid date/time is converted to the specific date or time type representation using jsonpath .date(), .time(), .time_tz(), .timestamp(), .timestamp_tz() methods. The changes use the infrastructure of the .datetime() method and perform the datatype conversion as appropriate. Unlike the .datetime() method, none of these methods accept a format template and use ISO DateTime format instead. However, except for .date(), the date/time related methods take an optional precision to adjust the fractional seconds. Jeevan Chalke, reviewed by Peter Eisentraut and Andrew Dunstan. --- src/test/regress/sql/jsonb_jsonpath.sql | 492 ++++++++++++++++++++++++++++++++ 1 file changed, 492 insertions(+) (limited to 'src/test/regress/sql/jsonb_jsonpath.sql') diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql index e0ce509264a..418eeac5ec7 100644 --- a/src/test/regress/sql/jsonb_jsonpath.sql +++ b/src/test/regress/sql/jsonb_jsonpath.sql @@ -372,8 +372,335 @@ select jsonb_path_query('"10-03-2017T12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH2 select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); +-- Test .bigint() +select jsonb_path_query('null', '$.bigint()'); +select jsonb_path_query('true', '$.bigint()'); +select jsonb_path_query('null', '$.bigint()', silent => true); +select jsonb_path_query('true', '$.bigint()', silent => true); +select jsonb_path_query('[]', '$.bigint()'); +select jsonb_path_query('[]', 'strict $.bigint()'); +select jsonb_path_query('{}', '$.bigint()'); +select jsonb_path_query('[]', 'strict $.bigint()', silent => true); +select jsonb_path_query('{}', '$.bigint()', silent => true); +select jsonb_path_query('"1.23"', '$.bigint()'); +select jsonb_path_query('"1.23aaa"', '$.bigint()'); +select jsonb_path_query('1e1000', '$.bigint()'); +select jsonb_path_query('"nan"', '$.bigint()'); +select jsonb_path_query('"NaN"', '$.bigint()'); +select jsonb_path_query('"inf"', '$.bigint()'); +select jsonb_path_query('"-inf"', '$.bigint()'); +select jsonb_path_query('"inf"', '$.bigint()', silent => true); +select jsonb_path_query('"-inf"', '$.bigint()', silent => true); +select jsonb_path_query('123', '$.bigint()'); +select jsonb_path_query('"123"', '$.bigint()'); +select jsonb_path_query('1.23', '$.bigint()'); +select jsonb_path_query('1.83', '$.bigint()'); +select jsonb_path_query('1234567890123', '$.bigint()'); +select jsonb_path_query('"1234567890123"', '$.bigint()'); +select jsonb_path_query('12345678901234567890', '$.bigint()'); +select jsonb_path_query('"12345678901234567890"', '$.bigint()'); +select jsonb_path_query('"+123"', '$.bigint()'); +select jsonb_path_query('-123', '$.bigint()'); +select jsonb_path_query('"-123"', '$.bigint()'); +select jsonb_path_query('123', '$.bigint() * 2'); + +-- Test .boolean() +select jsonb_path_query('null', '$.boolean()'); +select jsonb_path_query('null', '$.boolean()', silent => true); +select jsonb_path_query('[]', '$.boolean()'); +select jsonb_path_query('[]', 'strict $.boolean()'); +select jsonb_path_query('{}', '$.boolean()'); +select jsonb_path_query('[]', 'strict $.boolean()', silent => true); +select jsonb_path_query('{}', '$.boolean()', silent => true); +select jsonb_path_query('1.23', '$.boolean()'); +select jsonb_path_query('"1.23"', '$.boolean()'); +select jsonb_path_query('"1.23aaa"', '$.boolean()'); +select jsonb_path_query('1e1000', '$.boolean()'); +select jsonb_path_query('"nan"', '$.boolean()'); +select jsonb_path_query('"NaN"', '$.boolean()'); +select jsonb_path_query('"inf"', '$.boolean()'); +select jsonb_path_query('"-inf"', '$.boolean()'); +select jsonb_path_query('"inf"', '$.boolean()', silent => true); +select jsonb_path_query('"-inf"', '$.boolean()', silent => true); +select jsonb_path_query('"100"', '$.boolean()'); +select jsonb_path_query('true', '$.boolean()'); +select jsonb_path_query('false', '$.boolean()'); +select jsonb_path_query('1', '$.boolean()'); +select jsonb_path_query('0', '$.boolean()'); +select jsonb_path_query('-1', '$.boolean()'); +select jsonb_path_query('100', '$.boolean()'); +select jsonb_path_query('"1"', '$.boolean()'); +select jsonb_path_query('"0"', '$.boolean()'); +select jsonb_path_query('"true"', '$.boolean()'); +select jsonb_path_query('"false"', '$.boolean()'); +select jsonb_path_query('"TRUE"', '$.boolean()'); +select jsonb_path_query('"FALSE"', '$.boolean()'); +select jsonb_path_query('"yes"', '$.boolean()'); +select jsonb_path_query('"NO"', '$.boolean()'); +select jsonb_path_query('"T"', '$.boolean()'); +select jsonb_path_query('"f"', '$.boolean()'); +select jsonb_path_query('"y"', '$.boolean()'); +select jsonb_path_query('"N"', '$.boolean()'); +select jsonb_path_query('true', '$.boolean().type()'); +select jsonb_path_query('123', '$.boolean().type()'); +select jsonb_path_query('"Yes"', '$.boolean().type()'); +select jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()'); + +-- Test .date() +select jsonb_path_query('null', '$.date()'); +select jsonb_path_query('true', '$.date()'); +select jsonb_path_query('1', '$.date()'); +select jsonb_path_query('[]', '$.date()'); +select jsonb_path_query('[]', 'strict $.date()'); +select jsonb_path_query('{}', '$.date()'); +select jsonb_path_query('"bogus"', '$.date()'); + +select jsonb '"2023-08-15"' @? '$.date()'; +select jsonb_path_query('"2023-08-15"', '$.date()'); +select jsonb_path_query('"2023-08-15"', '$.date().type()'); + +select jsonb_path_query('"12:34:56"', '$.date()'); +select jsonb_path_query('"12:34:56 +05:30"', '$.date()'); +select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()'); + +select jsonb_path_query('"2023-08-15"', '$.date(2)'); + +-- Test .decimal() +select jsonb_path_query('null', '$.decimal()'); +select jsonb_path_query('true', '$.decimal()'); +select jsonb_path_query('null', '$.decimal()', silent => true); +select jsonb_path_query('true', '$.decimal()', silent => true); +select jsonb_path_query('[]', '$.decimal()'); +select jsonb_path_query('[]', 'strict $.decimal()'); +select jsonb_path_query('{}', '$.decimal()'); +select jsonb_path_query('[]', 'strict $.decimal()', silent => true); +select jsonb_path_query('{}', '$.decimal()', silent => true); +select jsonb_path_query('1.23', '$.decimal()'); +select jsonb_path_query('"1.23"', '$.decimal()'); +select jsonb_path_query('"1.23aaa"', '$.decimal()'); +select jsonb_path_query('1e1000', '$.decimal()'); +select jsonb_path_query('"nan"', '$.decimal()'); +select jsonb_path_query('"NaN"', '$.decimal()'); +select jsonb_path_query('"inf"', '$.decimal()'); +select jsonb_path_query('"-inf"', '$.decimal()'); +select jsonb_path_query('"inf"', '$.decimal()', silent => true); +select jsonb_path_query('"-inf"', '$.decimal()', silent => true); +select jsonb_path_query('123', '$.decimal()'); +select jsonb_path_query('"123"', '$.decimal()'); +select jsonb_path_query('12345678901234567890', '$.decimal()'); +select jsonb_path_query('"12345678901234567890"', '$.decimal()'); +select jsonb_path_query('"+12.3"', '$.decimal()'); +select jsonb_path_query('-12.3', '$.decimal()'); +select jsonb_path_query('"-12.3"', '$.decimal()'); +select jsonb_path_query('12.3', '$.decimal() * 2'); +select jsonb_path_query('12345.678', '$.decimal(6, 1)'); +select jsonb_path_query('12345.678', '$.decimal(6, 2)'); +select jsonb_path_query('1234.5678', '$.decimal(6, 2)'); +select jsonb_path_query('12345.678', '$.decimal(4, 6)'); +select jsonb_path_query('12345.678', '$.decimal(0, 6)'); +select jsonb_path_query('12345.678', '$.decimal(1001, 6)'); +select jsonb_path_query('1234.5678', '$.decimal(+6, +2)'); +select jsonb_path_query('1234.5678', '$.decimal(+6, -2)'); +select jsonb_path_query('1234.5678', '$.decimal(-6, +2)'); +select jsonb_path_query('1234.5678', '$.decimal(6, -1001)'); +select jsonb_path_query('1234.5678', '$.decimal(6, 1001)'); +select jsonb_path_query('-1234.5678', '$.decimal(+6, -2)'); +select jsonb_path_query('0.0123456', '$.decimal(1,2)'); +select jsonb_path_query('0.0012345', '$.decimal(2,4)'); +select jsonb_path_query('-0.00123456', '$.decimal(2,-4)'); +select jsonb_path_query('12.3', '$.decimal(12345678901,1)'); +select jsonb_path_query('12.3', '$.decimal(1,12345678901)'); + +-- Test .integer() +select jsonb_path_query('null', '$.integer()'); +select jsonb_path_query('true', '$.integer()'); +select jsonb_path_query('null', '$.integer()', silent => true); +select jsonb_path_query('true', '$.integer()', silent => true); +select jsonb_path_query('[]', '$.integer()'); +select jsonb_path_query('[]', 'strict $.integer()'); +select jsonb_path_query('{}', '$.integer()'); +select jsonb_path_query('[]', 'strict $.integer()', silent => true); +select jsonb_path_query('{}', '$.integer()', silent => true); +select jsonb_path_query('"1.23"', '$.integer()'); +select jsonb_path_query('"1.23aaa"', '$.integer()'); +select jsonb_path_query('1e1000', '$.integer()'); +select jsonb_path_query('"nan"', '$.integer()'); +select jsonb_path_query('"NaN"', '$.integer()'); +select jsonb_path_query('"inf"', '$.integer()'); +select jsonb_path_query('"-inf"', '$.integer()'); +select jsonb_path_query('"inf"', '$.integer()', silent => true); +select jsonb_path_query('"-inf"', '$.integer()', silent => true); +select jsonb_path_query('123', '$.integer()'); +select jsonb_path_query('"123"', '$.integer()'); +select jsonb_path_query('1.23', '$.integer()'); +select jsonb_path_query('1.83', '$.integer()'); +select jsonb_path_query('12345678901', '$.integer()'); +select jsonb_path_query('"12345678901"', '$.integer()'); +select jsonb_path_query('"+123"', '$.integer()'); +select jsonb_path_query('-123', '$.integer()'); +select jsonb_path_query('"-123"', '$.integer()'); +select jsonb_path_query('123', '$.integer() * 2'); + +-- Test .number() +select jsonb_path_query('null', '$.number()'); +select jsonb_path_query('true', '$.number()'); +select jsonb_path_query('null', '$.number()', silent => true); +select jsonb_path_query('true', '$.number()', silent => true); +select jsonb_path_query('[]', '$.number()'); +select jsonb_path_query('[]', 'strict $.number()'); +select jsonb_path_query('{}', '$.number()'); +select jsonb_path_query('[]', 'strict $.number()', silent => true); +select jsonb_path_query('{}', '$.number()', silent => true); +select jsonb_path_query('1.23', '$.number()'); +select jsonb_path_query('"1.23"', '$.number()'); +select jsonb_path_query('"1.23aaa"', '$.number()'); +select jsonb_path_query('1e1000', '$.number()'); +select jsonb_path_query('"nan"', '$.number()'); +select jsonb_path_query('"NaN"', '$.number()'); +select jsonb_path_query('"inf"', '$.number()'); +select jsonb_path_query('"-inf"', '$.number()'); +select jsonb_path_query('"inf"', '$.number()', silent => true); +select jsonb_path_query('"-inf"', '$.number()', silent => true); +select jsonb_path_query('123', '$.number()'); +select jsonb_path_query('"123"', '$.number()'); +select jsonb_path_query('12345678901234567890', '$.number()'); +select jsonb_path_query('"12345678901234567890"', '$.number()'); +select jsonb_path_query('"+12.3"', '$.number()'); +select jsonb_path_query('-12.3', '$.number()'); +select jsonb_path_query('"-12.3"', '$.number()'); +select jsonb_path_query('12.3', '$.number() * 2'); + +-- Test .string() +select jsonb_path_query('null', '$.string()'); +select jsonb_path_query('null', '$.string()', silent => true); +select jsonb_path_query('[]', '$.string()'); +select jsonb_path_query('[]', 'strict $.string()'); +select jsonb_path_query('{}', '$.string()'); +select jsonb_path_query('[]', 'strict $.string()', silent => true); +select jsonb_path_query('{}', '$.string()', silent => true); +select jsonb_path_query('1.23', '$.string()'); +select jsonb_path_query('"1.23"', '$.string()'); +select jsonb_path_query('"1.23aaa"', '$.string()'); +select jsonb_path_query('1234', '$.string()'); +select jsonb_path_query('true', '$.string()'); +select jsonb_path_query('1234', '$.string().type()'); +select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); +select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()'); +select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()'); + +-- Test .time() +select jsonb_path_query('null', '$.time()'); +select jsonb_path_query('true', '$.time()'); +select jsonb_path_query('1', '$.time()'); +select jsonb_path_query('[]', '$.time()'); +select jsonb_path_query('[]', 'strict $.time()'); +select jsonb_path_query('{}', '$.time()'); +select jsonb_path_query('"bogus"', '$.time()'); + +select jsonb '"12:34:56"' @? '$.time()'; +select jsonb_path_query('"12:34:56"', '$.time()'); +select jsonb_path_query('"12:34:56"', '$.time().type()'); + +select jsonb_path_query('"2023-08-15"', '$.time()'); +select jsonb_path_query('"12:34:56 +05:30"', '$.time()'); +select jsonb_path_query('"2023-08-15 12:34:56"', '$.time()'); + +select jsonb_path_query('"12:34:56.789"', '$.time(-1)'); +select jsonb_path_query('"12:34:56.789"', '$.time(2.0)'); +select jsonb_path_query('"12:34:56.789"', '$.time(12345678901)'); +select jsonb_path_query('"12:34:56.789"', '$.time(0)'); +select jsonb_path_query('"12:34:56.789"', '$.time(2)'); +select jsonb_path_query('"12:34:56.789"', '$.time(5)'); +select jsonb_path_query('"12:34:56.789"', '$.time(10)'); +select jsonb_path_query('"12:34:56.789012"', '$.time(8)'); + +-- Test .time_tz() +select jsonb_path_query('null', '$.time_tz()'); +select jsonb_path_query('true', '$.time_tz()'); +select jsonb_path_query('1', '$.time_tz()'); +select jsonb_path_query('[]', '$.time_tz()'); +select jsonb_path_query('[]', 'strict $.time_tz()'); +select jsonb_path_query('{}', '$.time_tz()'); +select jsonb_path_query('"bogus"', '$.time_tz()'); + +select jsonb '"12:34:56 +05:30"' @? '$.time_tz()'; +select jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()'); +select jsonb_path_query('"12:34:56 +05:30"', '$.time_tz().type()'); + +select jsonb_path_query('"2023-08-15"', '$.time_tz()'); +select jsonb_path_query('"2023-08-15 12:34:56"', '$.time_tz()'); + +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(-1)'); +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2.0)'); +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(12345678901)'); +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(0)'); +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)'); +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(5)'); +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(10)'); +select jsonb_path_query('"12:34:56.789012 +05:30"', '$.time_tz(8)'); + +-- Test .timestamp() +select jsonb_path_query('null', '$.timestamp()'); +select jsonb_path_query('true', '$.timestamp()'); +select jsonb_path_query('1', '$.timestamp()'); +select jsonb_path_query('[]', '$.timestamp()'); +select jsonb_path_query('[]', 'strict $.timestamp()'); +select jsonb_path_query('{}', '$.timestamp()'); +select jsonb_path_query('"bogus"', '$.timestamp()'); + +select jsonb '"2023-08-15 12:34:56"' @? '$.timestamp()'; +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()'); +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().type()'); + +select jsonb_path_query('"2023-08-15"', '$.timestamp()'); +select jsonb_path_query('"12:34:56"', '$.timestamp()'); +select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp()'); + +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(-1)'); +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2.0)'); +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(12345678901)'); +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(0)'); +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)'); +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(5)'); +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(10)'); +select jsonb_path_query('"2023-08-15 12:34:56.789012"', '$.timestamp(8)'); + +-- Test .timestamp_tz() +select jsonb_path_query('null', '$.timestamp_tz()'); +select jsonb_path_query('true', '$.timestamp_tz()'); +select jsonb_path_query('1', '$.timestamp_tz()'); +select jsonb_path_query('[]', '$.timestamp_tz()'); +select jsonb_path_query('[]', 'strict $.timestamp_tz()'); +select jsonb_path_query('{}', '$.timestamp_tz()'); +select jsonb_path_query('"bogus"', '$.timestamp_tz()'); + +select jsonb '"2023-08-15 12:34:56 +05:30"' @? '$.timestamp_tz()'; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()'); + +select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()'); +select jsonb_path_query('"12:34:56"', '$.timestamp_tz()'); +select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp_tz()'); + +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(-1)'); +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2.0)'); +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(12345678901)'); +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(0)'); +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)'); +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(5)'); +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(10)'); +select jsonb_path_query('"2023-08-15 12:34:56.789012 +05:30"', '$.timestamp_tz(8)'); + + set time zone '+00'; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()'); +select jsonb_path_query('"12:34:56"', '$.time_tz()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()'); + select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); select jsonb_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); @@ -389,6 +716,12 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); set time zone '+10'; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()'); + select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); select jsonb_path_query('"10-03-2017 12:34 +05"', '$.datetime("dd-mm-yyyy HH24:MI TZH")'); @@ -404,6 +737,11 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); set time zone default; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()'); + select jsonb_path_query('"2017-03-10"', '$.datetime().type()'); select jsonb_path_query('"2017-03-10"', '$.datetime()'); select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime().type()'); @@ -446,6 +784,34 @@ select jsonb_path_query_tz( '["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', '$[*].datetime() ? (@ < "10.03.2017".datetime("dd.mm.yyyy"))'); +select jsonb_path_query_tz( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].datetime() ? (@ == "2017-03-10".date())'); +select jsonb_path_query_tz( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].datetime() ? (@ >= "2017-03-10".date())'); +select jsonb_path_query_tz( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].datetime() ? (@ < "2017-03-10".date())'); +select jsonb_path_query( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].date() ? (@ == "2017-03-10".date())'); +select jsonb_path_query( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].date() ? (@ >= "2017-03-10".date())'); +select jsonb_path_query( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].date() ? (@ < "2017-03-10".date())'); +select jsonb_path_query_tz( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].date() ? (@ == "2017-03-10".date())'); +select jsonb_path_query_tz( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].date() ? (@ >= "2017-03-10".date())'); +select jsonb_path_query_tz( + '["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]', + '$[*].date() ? (@ < "2017-03-10".date())'); + -- time comparison select jsonb_path_query( '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', @@ -466,6 +832,38 @@ select jsonb_path_query_tz( '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', '$[*].datetime() ? (@ < "12:35".datetime("HH24:MI"))'); +select jsonb_path_query_tz( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].datetime() ? (@ == "12:35:00".time())'); +select jsonb_path_query_tz( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].datetime() ? (@ >= "12:35:00".time())'); +select jsonb_path_query_tz( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].datetime() ? (@ < "12:35:00".time())'); +select jsonb_path_query( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].time() ? (@ == "12:35:00".time())'); +select jsonb_path_query( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].time() ? (@ >= "12:35:00".time())'); +select jsonb_path_query( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].time() ? (@ < "12:35:00".time())'); +select jsonb_path_query_tz( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].time() ? (@ == "12:35:00".time())'); +select jsonb_path_query_tz( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].time() ? (@ >= "12:35:00".time())'); +select jsonb_path_query_tz( + '["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]', + '$[*].time() ? (@ < "12:35:00".time())'); +select jsonb_path_query( + '["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]', + '$[*].time(2) ? (@ >= "12:35:00.123".time(2))'); + + -- timetz comparison select jsonb_path_query( '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', @@ -486,6 +884,37 @@ select jsonb_path_query_tz( '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]', '$[*].datetime() ? (@ < "12:35 +1".datetime("HH24:MI TZH"))'); +select jsonb_path_query_tz( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].datetime() ? (@ == "12:35:00 +1".time_tz())'); +select jsonb_path_query_tz( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].datetime() ? (@ >= "12:35:00 +1".time_tz())'); +select jsonb_path_query_tz( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].datetime() ? (@ < "12:35:00 +1".time_tz())'); +select jsonb_path_query( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())'); +select jsonb_path_query( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].time_tz() ? (@ >= "12:35:00 +1".time_tz())'); +select jsonb_path_query( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())'); +select jsonb_path_query_tz( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())'); +select jsonb_path_query_tz( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].time_tz() ? (@ >= "12:35:00 +1".time_tz())'); +select jsonb_path_query_tz( + '["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]', + '$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())'); +select jsonb_path_query( + '["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]', + '$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))'); + -- timestamp comparison select jsonb_path_query( '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', @@ -506,6 +935,37 @@ select jsonb_path_query_tz( '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', '$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].datetime() ? (@ == "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].datetime() ? (@ >= "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].datetime() ? (@ < "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp() ? (@ >= "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp() ? (@ >= "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())'); +select jsonb_path_query( + '["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]', + '$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))'); + -- timestamptz comparison select jsonb_path_query( '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', @@ -526,6 +986,38 @@ select jsonb_path_query_tz( '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]', '$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].datetime() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].datetime() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].datetime() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query_tz( + '["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())'); +select jsonb_path_query( + '["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]', + '$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))'); + + -- overflow during comparison select jsonb_path_query('"1000000-01-01"', '$.datetime() > "2020-01-01 12:00:00".datetime()'::jsonpath); -- cgit v1.2.3