diff options
| -rw-r--r-- | doc/src/sgml/func.sgml | 218 | ||||
| -rw-r--r-- | src/backend/catalog/sql_features.txt | 28 | ||||
| -rw-r--r-- | src/backend/utils/adt/jsonpath.c | 138 | ||||
| -rw-r--r-- | src/backend/utils/adt/jsonpath_exec.c | 718 | ||||
| -rw-r--r-- | src/backend/utils/adt/jsonpath_gram.y | 78 | ||||
| -rw-r--r-- | src/backend/utils/adt/jsonpath_scan.l | 11 | ||||
| -rw-r--r-- | src/include/utils/jsonpath.h | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/jsonb_jsonpath.out | 1814 | ||||
| -rw-r--r-- | src/test/regress/expected/jsonpath.out | 78 | ||||
| -rw-r--r-- | src/test/regress/sql/jsonb_jsonpath.sql | 492 | ||||
| -rw-r--r-- | src/test/regress/sql/jsonpath.sql | 13 |
11 files changed, 3569 insertions, 30 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 968e8d59fb5..11d62ba5949 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17728,6 +17728,38 @@ strict $.**.HR <row> <entry role="func_table_entry"><para role="func_signature"> + <replaceable>value</replaceable> <literal>.</literal> <literal>boolean()</literal> + <returnvalue><replaceable>boolean</replaceable></returnvalue> + </para> + <para> + Boolean value converted from a JSON boolean, number, or string + </para> + <para> + <literal>jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()')</literal> + <returnvalue>[true, true, false]</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>value</replaceable> <literal>.</literal> <literal>string()</literal> + <returnvalue><replaceable>string</replaceable></returnvalue> + </para> + <para> + String value converted from a JSON boolean, number, string, or datetime + </para> + <para> + <literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal> + <returnvalue>["1.23", "xyz", "false"]</returnvalue> + </para> + <para> + <literal>jsonb_path_query('"2023-08-15"', '$.datetime().string()')</literal> + <returnvalue>"2023-08-15"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> <replaceable>value</replaceable> <literal>.</literal> <literal>double()</literal> <returnvalue><replaceable>number</replaceable></returnvalue> </para> @@ -17785,6 +17817,62 @@ strict $.**.HR <row> <entry role="func_table_entry"><para role="func_signature"> + <replaceable>value</replaceable> <literal>.</literal> <literal>bigint()</literal> + <returnvalue><replaceable>bigint</replaceable></returnvalue> + </para> + <para> + Big integer value converted from a JSON number or string + </para> + <para> + <literal>jsonb_path_query('{"len": "9876543219"}', '$.len.bigint()')</literal> + <returnvalue>9876543219</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>value</replaceable> <literal>.</literal> <literal>decimal( [ <replaceable>precision</replaceable> [ , <replaceable>scale</replaceable> ] ] )</literal> + <returnvalue><replaceable>decimal</replaceable></returnvalue> + </para> + <para> + Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values. + </para> + <para> + <literal>jsonb_path_query('1234.5678', '$.decimal(6, 2)')</literal> + <returnvalue>1234.57</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>value</replaceable> <literal>.</literal> <literal>integer()</literal> + <returnvalue><replaceable>integer</replaceable></returnvalue> + </para> + <para> + Integer value converted from a JSON number or string + </para> + <para> + <literal>jsonb_path_query('{"len": "12345"}', '$.len.integer()')</literal> + <returnvalue>12345</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>value</replaceable> <literal>.</literal> <literal>number()</literal> + <returnvalue><replaceable>numeric</replaceable></returnvalue> + </para> + <para> + Numeric value converted from a JSON number or string + </para> + <para> + <literal>jsonb_path_query('{"len": "123.45"}', '$.len.number()')</literal> + <returnvalue>123.45</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> <replaceable>string</replaceable> <literal>.</literal> <literal>datetime()</literal> <returnvalue><replaceable>datetime_type</replaceable></returnvalue> (see note) @@ -17816,6 +17904,136 @@ strict $.**.HR <row> <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>date()</literal> + <returnvalue><replaceable>date</replaceable></returnvalue> + </para> + <para> + Date value converted from a string + </para> + <para> + <literal>jsonb_path_query('"2023-08-15"', '$.date()')</literal> + <returnvalue>"2023-08-15"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>time()</literal> + <returnvalue><replaceable>time without time zone</replaceable></returnvalue> + </para> + <para> + Time without time zone value converted from a string + </para> + <para> + <literal>jsonb_path_query('"12:34:56"', '$.time()')</literal> + <returnvalue>"12:34:56"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>time(<replaceable>precision</replaceable>)</literal> + <returnvalue><replaceable>time without time zone</replaceable></returnvalue> + </para> + <para> + Time without time zone value converted from a string, with fractional + seconds adjusted to the given precision. + </para> + <para> + <literal>jsonb_path_query('"12:34:56.789"', '$.time(2)')</literal> + <returnvalue>"12:34:56.79"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>time_tz()</literal> + <returnvalue><replaceable>time with time zone</replaceable></returnvalue> + </para> + <para> + Time with time zone value converted from a string + </para> + <para> + <literal>jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()')</literal> + <returnvalue>"12:34:56+05:30"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>time_tz(<replaceable>precision</replaceable>)</literal> + <returnvalue><replaceable>time with time zone</replaceable></returnvalue> + </para> + <para> + Time with time zone value converted from a string, with fractional + seconds adjusted to the given precision. + </para> + <para> + <literal>jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)')</literal> + <returnvalue>"12:34:56.79+05:30"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>timestamp()</literal> + <returnvalue><replaceable>timestamp without time zone</replaceable></returnvalue> + </para> + <para> + Timestamp without time zone value converted from a string + </para> + <para> + <literal>jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()')</literal> + <returnvalue>"2023-08-15T12:34:56"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>timestamp(<replaceable>precision</replaceable>)</literal> + <returnvalue><replaceable>timestamp without time zone</replaceable></returnvalue> + </para> + <para> + Timestamp without time zone value converted from a string, with + fractional seconds adjusted to the given precision. + </para> + <para> + <literal>jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)')</literal> + <returnvalue>"2023-08-15T12:34:56.79"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>timestamp_tz()</literal> + <returnvalue><replaceable>timestamp with time zone</replaceable></returnvalue> + </para> + <para> + Timestamp with time zone value converted from a string + </para> + <para> + <literal>jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()')</literal> + <returnvalue>"2023-08-15T12:34:56+05:30"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>string</replaceable> <literal>.</literal> <literal>timestamp_tz(<replaceable>precision</replaceable>)</literal> + <returnvalue><replaceable>timestamp with time zone</replaceable></returnvalue> + </para> + <para> + Timestamp with time zone value converted from a string, with fractional + seconds adjusted to the given precision. + </para> + <para> + <literal>jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')</literal> + <returnvalue>"2023-08-15T12:34:56.79+05:30"</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> <replaceable>object</replaceable> <literal>.</literal> <literal>keyvalue()</literal> <returnvalue><replaceable>array</replaceable></returnvalue> </para> diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 80c40eaf578..4085a2d18d2 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -574,20 +574,20 @@ T861 SQL/JSON simplified accessor: case-sensitive JSON member accessor NO T862 SQL/JSON simplified accessor: wildcard member accessor NO T863 SQL/JSON simplified accessor: single-quoted string literal as member accessor NO T864 SQL/JSON simplified accessor NO -T865 SQL/JSON item method: bigint() NO -T866 SQL/JSON item method: boolean() NO -T867 SQL/JSON item method: date() NO -T868 SQL/JSON item method: decimal() NO -T869 SQL/JSON item method: decimal() with precision and scale NO -T870 SQL/JSON item method: integer() NO -T871 SQL/JSON item method: number() NO -T872 SQL/JSON item method: string() NO -T873 SQL/JSON item method: time() NO -T874 SQL/JSON item method: time_tz() NO -T875 SQL/JSON item method: time precision NO -T876 SQL/JSON item method: timestamp() NO -T877 SQL/JSON item method: timestamp_tz() NO -T878 SQL/JSON item method: timestamp precision NO +T865 SQL/JSON item method: bigint() YES +T866 SQL/JSON item method: boolean() YES +T867 SQL/JSON item method: date() YES +T868 SQL/JSON item method: decimal() YES +T869 SQL/JSON item method: decimal() with precision and scale YES +T870 SQL/JSON item method: integer() YES +T871 SQL/JSON item method: number() YES +T872 SQL/JSON item method: string() YES +T873 SQL/JSON item method: time() YES +T874 SQL/JSON item method: time_tz() YES +T875 SQL/JSON item method: time precision YES +T876 SQL/JSON item method: timestamp() YES +T877 SQL/JSON item method: timestamp_tz() YES +T878 SQL/JSON item method: timestamp precision YES T879 JSON in equality operations YES with jsonb T880 JSON in grouping operations YES with jsonb T881 JSON in ordering operations NO with jsonb, partially supported diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c index d02c03e014d..258ed8eb117 100644 --- a/src/backend/utils/adt/jsonpath.c +++ b/src/backend/utils/adt/jsonpath.c @@ -295,6 +295,7 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext, case jpiDiv: case jpiMod: case jpiStartsWith: + case jpiDecimal: { /* * First, reserve place for left/right arg's positions, then @@ -355,6 +356,10 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext, case jpiMinus: case jpiExists: case jpiDatetime: + case jpiTime: + case jpiTimeTz: + case jpiTimestamp: + case jpiTimestampTz: { int32 arg = reserveSpaceForItemPointer(buf); @@ -444,6 +449,12 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext, case jpiCeiling: case jpiDouble: case jpiKeyValue: + case jpiBigint: + case jpiBoolean: + case jpiDate: + case jpiInteger: + case jpiNumber: + case jpiStringFunc: break; default: elog(ERROR, "unrecognized jsonpath item type: %d", item->type); @@ -742,6 +753,75 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey, if (printBracketes) appendStringInfoChar(buf, ')'); break; + case jpiBigint: + appendStringInfoString(buf, ".bigint()"); + break; + case jpiBoolean: + appendStringInfoString(buf, ".boolean()"); + break; + case jpiDate: + appendStringInfoString(buf, ".date()"); + break; + case jpiDecimal: + appendStringInfoString(buf, ".decimal("); + if (v->content.args.left) + { + jspGetLeftArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + if (v->content.args.right) + { + appendStringInfoChar(buf, ','); + jspGetRightArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + appendStringInfoChar(buf, ')'); + break; + case jpiInteger: + appendStringInfoString(buf, ".integer()"); + break; + case jpiNumber: + appendStringInfoString(buf, ".number()"); + break; + case jpiStringFunc: + appendStringInfoString(buf, ".string()"); + break; + case jpiTime: + appendStringInfoString(buf, ".time("); + if (v->content.arg) + { + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + appendStringInfoChar(buf, ')'); + break; + case jpiTimeTz: + appendStringInfoString(buf, ".time_tz("); + if (v->content.arg) + { + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + appendStringInfoChar(buf, ')'); + break; + case jpiTimestamp: + appendStringInfoString(buf, ".timestamp("); + if (v->content.arg) + { + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + appendStringInfoChar(buf, ')'); + break; + case jpiTimestampTz: + appendStringInfoString(buf, ".timestamp_tz("); + if (v->content.arg) + { + jspGetArg(v, &elem); + printJsonPathItem(buf, &elem, false, false); + } + appendStringInfoChar(buf, ')'); + break; default: elog(ERROR, "unrecognized jsonpath item type: %d", v->type); } @@ -803,6 +883,28 @@ jspOperationName(JsonPathItemType type) return "starts with"; case jpiLikeRegex: return "like_regex"; + case jpiBigint: + return "bigint"; + case jpiBoolean: + return "boolean"; + case jpiDate: + return "date"; + case jpiDecimal: + return "decimal"; + case jpiInteger: + return "integer"; + case jpiNumber: + return "number"; + case jpiStringFunc: + return "string"; + case jpiTime: + return "time"; + case jpiTimeTz: + return "time_tz"; + case jpiTimestamp: + return "timestamp"; + case jpiTimestampTz: + return "timestamp_tz"; default: elog(ERROR, "unrecognized jsonpath item type: %d", type); return NULL; @@ -899,6 +1001,12 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos) case jpiDouble: case jpiKeyValue: case jpiLast: + case jpiBigint: + case jpiBoolean: + case jpiDate: + case jpiInteger: + case jpiNumber: + case jpiStringFunc: break; case jpiString: case jpiKey: @@ -923,6 +1031,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos) case jpiDiv: case jpiMod: case jpiStartsWith: + case jpiDecimal: read_int32(v->content.args.left, base, pos); read_int32(v->content.args.right, base, pos); break; @@ -933,6 +1042,10 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos) case jpiMinus: case jpiFilter: case jpiDatetime: + case jpiTime: + case jpiTimeTz: + case jpiTimestamp: + case jpiTimestampTz: read_int32(v->content.arg, base, pos); break; case jpiIndexArray: @@ -964,7 +1077,11 @@ jspGetArg(JsonPathItem *v, JsonPathItem *a) v->type == jpiMinus || v->type == jpiFilter || v->type == jpiExists || - v->type == jpiDatetime); + v->type == jpiDatetime || + v->type == jpiTime || + v->type == jpiTimeTz || + v->type == jpiTimestamp || + v->type == jpiTimestampTz); jspInitByBuffer(a, v->base, v->content.arg); } @@ -1015,7 +1132,18 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a) v->type == jpiKeyValue || v->type == jpiLast || v->type == jpiStartsWith || - v->type == jpiLikeRegex); + v->type == jpiLikeRegex || + v->type == jpiBigint || + v->type == jpiBoolean || + v->type == jpiDate || + v->type == jpiDecimal || + v->type == jpiInteger || + v->type == jpiNumber || + v->type == jpiStringFunc || + v->type == jpiTime || + v->type == jpiTimeTz || + v->type == jpiTimestamp || + v->type == jpiTimestampTz); if (a) jspInitByBuffer(a, v->base, v->nextPos); @@ -1041,7 +1169,8 @@ jspGetLeftArg(JsonPathItem *v, JsonPathItem *a) v->type == jpiMul || v->type == jpiDiv || v->type == jpiMod || - v->type == jpiStartsWith); + v->type == jpiStartsWith || + v->type == jpiDecimal); jspInitByBuffer(a, v->base, v->content.args.left); } @@ -1062,7 +1191,8 @@ jspGetRightArg(JsonPathItem *v, JsonPathItem *a) v->type == jpiMul || v->type == jpiDiv || v->type == jpiMod || - v->type == jpiStartsWith); + v->type == jpiStartsWith || + v->type == jpiDecimal); jspInitByBuffer(a, v->base, v->content.args.right); } diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index cb2ea048c35..bf37f0195c8 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -1094,6 +1094,11 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp, break; case jpiDatetime: + case jpiDate: + case jpiTime: + case jpiTimeTz: + case jpiTimestamp: + case jpiTimestampTz: if (unwrap && JsonbType(jb) == jbvArray) return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false); @@ -1133,6 +1138,420 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp, } break; + case jpiBigint: + { + JsonbValue jbv; + Datum datum; + + if (unwrap && JsonbType(jb) == jbvArray) + return executeItemUnwrapTargetArray(cxt, jsp, jb, found, + false); + + if (jb->type == jbvNumeric) + { + bool have_error; + int64 val; + + val = numeric_int8_opt_error(jb->val.numeric, &have_error); + if (have_error) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("numeric argument of jsonpath item method .%s() is out of range for type bigint", + jspOperationName(jsp->type))))); + + datum = Int64GetDatum(val); + res = jperOk; + } + else if (jb->type == jbvString) + { + /* cast string as bigint */ + char *tmp = pnstrdup(jb->val.string.val, + jb->val.string.len); + ErrorSaveContext escontext = {T_ErrorSaveContext}; + bool noerr; + + noerr = DirectInputFunctionCallSafe(int8in, tmp, + InvalidOid, -1, + (Node *) &escontext, + &datum); + + if (!noerr || escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("string argument of jsonpath item method .%s() is not a valid representation of a big integer", + jspOperationName(jsp->type))))); + res = jperOk; + } + + if (res == jperNotFound) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("jsonpath item method .%s() can only be applied to a string or numeric value", + jspOperationName(jsp->type))))); + + jb = &jbv; + jb->type = jbvNumeric; + jb->val.numeric = DatumGetNumeric(DirectFunctionCall1(int8_numeric, + datum)); + + res = executeNextItem(cxt, jsp, NULL, jb, found, true); + } + break; + + case jpiBoolean: + { + JsonbValue jbv; + bool bval; + + if (unwrap && JsonbType(jb) == jbvArray) + return executeItemUnwrapTargetArray(cxt, jsp, jb, found, + false); + + if (jb->type == jbvBool) + { + bval = jb->val.boolean; + + res = jperOk; + } + else if (jb->type == jbvNumeric) + { + int ival; + Datum datum; + bool noerr; + char *tmp = DatumGetCString(DirectFunctionCall1(numeric_out, + NumericGetDatum(jb->val.numeric))); + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + noerr = DirectInputFunctionCallSafe(int4in, tmp, + InvalidOid, -1, + (Node *) &escontext, + &datum); + + if (!noerr || escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("numeric argument of jsonpath item method .%s() is out of range for type boolean", + jspOperationName(jsp->type))))); + + ival = DatumGetInt32(datum); + if (ival == 0) + bval = false; + else + bval = true; + + res = jperOk; + } + else if (jb->type == jbvString) + { + /* cast string as boolean */ + char *tmp = pnstrdup(jb->val.string.val, + jb->val.string.len); + + if (!parse_bool(tmp, &bval)) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("string argument of jsonpath item method .%s() is not a valid representation of a boolean", + jspOperationName(jsp->type))))); + + res = jperOk; + } + + if (res == jperNotFound) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("jsonpath item method .%s() can only be applied to a bool, string, or numeric value", + jspOperationName(jsp->type))))); + + jb = &jbv; + jb->type = jbvBool; + jb->val.boolean = bval; + + res = executeNextItem(cxt, jsp, NULL, jb, found, true); + } + break; + + case jpiDecimal: + case jpiNumber: + { + JsonbValue jbv; + Numeric num; + char *numstr = NULL; + + if (unwrap && JsonbType(jb) == jbvArray) + return executeItemUnwrapTargetArray(cxt, jsp, jb, found, + false); + + if (jb->type == jbvNumeric) + { + num = jb->val.numeric; + if (numeric_is_nan(num) || numeric_is_inf(num)) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("numeric argument of jsonpath item method .%s() is out of range for type decimal or number", + jspOperationName(jsp->type))))); + + if (jsp->type == jpiDecimal) + numstr = DatumGetCString(DirectFunctionCall1(numeric_out, + NumericGetDatum(num))); + res = jperOk; + } + else if (jb->type == jbvString) + { + /* cast string as number */ + Datum datum; + bool noerr; + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + numstr = pnstrdup(jb->val.string.val, jb->val.string.len); + + noerr = DirectInputFunctionCallSafe(numeric_in, numstr, + InvalidOid, -1, + (Node *) &escontext, + &datum); + + if (!noerr || escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("string argument of jsonpath item method .%s() is not a valid representation of a decimal or number", + jspOperationName(jsp->type))))); + + num = DatumGetNumeric(datum); + if (numeric_is_nan(num) || numeric_is_inf(num)) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("string argument of jsonpath item method .%s() is not a valid representation of a decimal or number", + jspOperationName(jsp->type))))); + + res = jperOk; + } + + if (res == jperNotFound) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("jsonpath item method .%s() can only be applied to a string or numeric value", + jspOperationName(jsp->type))))); + + /* + * If we have arguments, then they must be the precision and + * optional scale used in .decimal(). Convert them to the + * typmod equivalent and then truncate the numeric value per + * this typmod details. + */ + if (jsp->type == jpiDecimal && jsp->content.args.left) + { + Datum numdatum; + Datum dtypmod; + int32 precision; + int32 scale = 0; + bool have_error; + bool noerr; + ArrayType *arrtypmod; + Datum datums[2]; + char pstr[12]; /* sign, 10 digits and '\0' */ + char sstr[12]; /* sign, 10 digits and '\0' */ + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + jspGetLeftArg(jsp, &elem); + if (elem.type != jpiNumeric) + elog(ERROR, "invalid jsonpath item type for .decimal() precision"); + + precision = numeric_int4_opt_error(jspGetNumeric(&elem), + &have_error); + if (have_error) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("precision of jsonpath item method .%s() is out of range for type integer", + jspOperationName(jsp->type))))); + + if (jsp->content.args.right) + { + jspGetRightArg(jsp, &elem); + if (elem.type != jpiNumeric) + elog(ERROR, "invalid jsonpath item type for .decimal() scale"); + + scale = numeric_int4_opt_error(jspGetNumeric(&elem), + &have_error); + if (have_error) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("scale of jsonpath item method .%s() is out of range for type integer", + jspOperationName(jsp->type))))); + } + + /* + * numerictypmodin() takes the precision and scale in the + * form of CString arrays. + */ + pg_ltoa(precision, pstr); + datums[0] = CStringGetDatum(pstr); + pg_ltoa(scale, sstr); + datums[1] = CStringGetDatum(sstr); + arrtypmod = construct_array_builtin(datums, 2, CSTRINGOID); + + dtypmod = DirectFunctionCall1(numerictypmodin, + PointerGetDatum(arrtypmod)); + + /* Convert numstr to Numeric with typmod */ + Assert(numstr != NULL); + noerr = DirectInputFunctionCallSafe(numeric_in, numstr, + InvalidOid, dtypmod, + (Node *) &escontext, + &numdatum); + + if (!noerr || escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("string argument of jsonpath item method .%s() is not a valid representation of a decimal or number", + jspOperationName(jsp->type))))); + + num = DatumGetNumeric(numdatum); + pfree(arrtypmod); + } + + jb = &jbv; + jb->type = jbvNumeric; + jb->val.numeric = num; + + res = executeNextItem(cxt, jsp, NULL, jb, found, true); + } + break; + + case jpiInteger: + { + JsonbValue jbv; + Datum datum; + + if (unwrap && JsonbType(jb) == jbvArray) + return executeItemUnwrapTargetArray(cxt, jsp, jb, found, + false); + + if (jb->type == jbvNumeric) + { + bool have_error; + int32 val; + + val = numeric_int4_opt_error(jb->val.numeric, &have_error); + if (have_error) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("numeric argument of jsonpath item method .%s() is out of range for type integer", + jspOperationName(jsp->type))))); + + datum = Int32GetDatum(val); + res = jperOk; + } + else if (jb->type == jbvString) + { + /* cast string as integer */ + char *tmp = pnstrdup(jb->val.string.val, + jb->val.string.len); + ErrorSaveContext escontext = {T_ErrorSaveContext}; + bool noerr; + + noerr = DirectInputFunctionCallSafe(int4in, tmp, + InvalidOid, -1, + (Node *) &escontext, + &datum); + + if (!noerr || escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("string argument of jsonpath item method .%s() is not a valid representation of an integer", + jspOperationName(jsp->type))))); + res = jperOk; + } + + if (res == jperNotFound) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("jsonpath item method .%s() can only be applied to a string or numeric value", + jspOperationName(jsp->type))))); + + jb = &jbv; + jb->type = jbvNumeric; + jb->val.numeric = DatumGetNumeric(DirectFunctionCall1(int4_numeric, + datum)); + + res = executeNextItem(cxt, jsp, NULL, jb, found, true); + } + break; + + case jpiStringFunc: + { + JsonbValue jbv; + char *tmp = NULL; + + switch (JsonbType(jb)) + { + case jbvString: + + /* + * Value is not necessarily null-terminated, so we do + * pnstrdup() here. + */ + tmp = pnstrdup(jb->val.string.val, + jb->val.string.len); + break; + case jbvNumeric: + tmp = DatumGetCString(DirectFunctionCall1(numeric_out, + NumericGetDatum(jb->val.numeric))); + break; + case jbvBool: + tmp = (jb->val.boolean) ? "true" : "false"; + break; + case jbvDatetime: + { + switch (jb->val.datetime.typid) + { + case DATEOID: + tmp = DatumGetCString(DirectFunctionCall1(date_out, + jb->val.datetime.value)); + break; + case TIMEOID: + tmp = DatumGetCString(DirectFunctionCall1(time_out, + jb->val.datetime.value)); + break; + case TIMETZOID: + tmp = DatumGetCString(DirectFunctionCall1(timetz_out, + jb->val.datetime.value)); + break; + case TIMESTAMPOID: + tmp = DatumGetCString(DirectFunctionCall1(timestamp_out, + jb->val.datetime.value)); + break; + case TIMESTAMPTZOID: + tmp = DatumGetCString(DirectFunctionCall1(timestamptz_out, + jb->val.datetime.value)); + break; + default: + elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u", + jb->val.datetime.typid); + } + } + break; + case jbvNull: + case jbvArray: + case jbvObject: + case jbvBinary: + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_NON_NUMERIC_SQL_JSON_ITEM), + errmsg("jsonpath item method .%s() can only be applied to a bool, string, numeric, or datetime value", + jspOperationName(jsp->type))))); + break; + } + + res = jperOk; + + jb = &jbv; + Assert(tmp != NULL); /* We must have set tmp above */ + jb->val.string.val = (jb->type == jbvString) ? tmp : pstrdup(tmp); + jb->val.string.len = strlen(jb->val.string.val); + jb->type = jbvString; + + res = executeNextItem(cxt, jsp, NULL, jb, found, true); + } + break; + default: elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type); } @@ -1794,11 +2213,16 @@ executeNumericItemMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, } /* - * Implementation of the .datetime() method. + * Implementation of the .datetime() and related methods. * - * Converts a string into a date/time value. The actual type is determined at run time. + * Converts a string into a date/time value. The actual type is determined at + * run time. * If an argument is provided, this argument is used as a template string. * Otherwise, the first fitting ISO format is selected. + * + * .date(), .time(), .time_tz(), .timestamp(), .timestamp_tz() methods don't + * have a format, so ISO format is used. However, except for .date(), they all + * take an optional time precision. */ static JsonPathExecResult executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, @@ -1814,6 +2238,7 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, bool hasNext; JsonPathExecResult res = jperNotFound; JsonPathItem elem; + int32 time_precision = -1; if (!(jb = getScalar(jb, jbvString))) RETURN_ERROR(ereport(ERROR, @@ -1831,7 +2256,11 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, */ collid = DEFAULT_COLLATION_OID; - if (jsp->content.arg) + /* + * .datetime(template) has an argument, the rest of the methods don't have + * an argument. So we handle that separately. + */ + if (jsp->type == jpiDatetime && jsp->content.arg) { text *template; char *template_str; @@ -1893,6 +2322,30 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, static text *fmt_txt[lengthof(fmt_str)] = {0}; int i; + /* + * Check for optional precision for methods other than .datetime() and + * .date() + */ + if (jsp->type != jpiDatetime && jsp->type != jpiDate && + jsp->content.arg) + { + bool have_error; + + jspGetArg(jsp, &elem); + + if (elem.type != jpiNumeric) + elog(ERROR, "invalid jsonpath item type for %s argument", + jspOperationName(jsp->type)); + + time_precision = numeric_int4_opt_error(jspGetNumeric(&elem), + &have_error); + if (have_error) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("time precision of jsonpath item method .%s() is out of range for type integer", + jspOperationName(jsp->type))))); + } + /* loop until datetime format fits */ for (i = 0; i < lengthof(fmt_str); i++) { @@ -1919,11 +2372,260 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, } if (res == jperNotFound) - RETURN_ERROR(ereport(ERROR, - (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), - errmsg("datetime format is not recognized: \"%s\"", - text_to_cstring(datetime)), - errhint("Use a datetime template argument to specify the input data format.")))); + { + if (jsp->type == jpiDatetime) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("datetime format is not recognized: \"%s\"", + text_to_cstring(datetime)), + errhint("Use a datetime template argument to specify the input data format.")))); + else + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("%s format is not recognized: \"%s\"", + jspOperationName(jsp->type), text_to_cstring(datetime))))); + + } + } + + /* + * parse_datetime() processes the entire input string per the template or + * ISO format and returns the Datum in best fitted datetime type. So, if + * this call is for a specific datatype, then we do the conversion here. + * Throw an error for incompatible types. + */ + switch (jsp->type) + { + case jpiDatetime: /* Nothing to do for DATETIME */ + break; + case jpiDate: + { + /* Convert result type to date */ + switch (typid) + { + case DATEOID: /* Nothing to do for DATE */ + break; + case TIMEOID: + case TIMETZOID: + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("date format is not recognized: \"%s\"", + text_to_cstring(datetime))))); + break; + case TIMESTAMPOID: + value = DirectFunctionCall1(timestamp_date, + value); + break; + case TIMESTAMPTZOID: + value = DirectFunctionCall1(timestamptz_date, + value); + break; + default: + elog(ERROR, "type with oid %d not supported", typid); + } + + typid = DATEOID; + } + break; + case jpiTime: + { + /* Convert result type to time without time zone */ + switch (typid) + { + case DATEOID: + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("time format is not recognized: \"%s\"", + text_to_cstring(datetime))))); + break; + case TIMEOID: /* Nothing to do for TIME */ + break; + case TIMETZOID: + value = DirectFunctionCall1(timetz_time, + value); + break; + case TIMESTAMPOID: + value = DirectFunctionCall1(timestamp_time, + value); + break; + case TIMESTAMPTZOID: + value = DirectFunctionCall1(timestamptz_time, + value); + break; + default: + elog(ERROR, "type with oid %d not supported", typid); + } + + /* Force the user-given time precision, if any */ + if (time_precision != -1) + { + TimeADT result; + + /* Get a warning when precision is reduced */ + time_precision = anytime_typmod_check(false, + time_precision); + result = DatumGetTimeADT(value); + AdjustTimeForTypmod(&result, time_precision); + value = TimeADTGetDatum(result); + + /* Update the typmod value with the user-given precision */ + typmod = time_precision; + } + + typid = TIMEOID; + } + break; + case jpiTimeTz: + { + /* Convert result type to time with time zone */ + switch (typid) + { + case DATEOID: + case TIMESTAMPOID: + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("time_tz format is not recognized: \"%s\"", + text_to_cstring(datetime))))); + break; + case TIMEOID: + value = DirectFunctionCall1(time_timetz, + value); + break; + case TIMETZOID: /* Nothing to do for TIMETZ */ + break; + case TIMESTAMPTZOID: + value = DirectFunctionCall1(timestamptz_timetz, + value); + break; + default: + elog(ERROR, "type with oid %d not supported", typid); + } + + /* Force the user-given time precision, if any */ + if (time_precision != -1) + { + TimeTzADT *result; + + /* Get a warning when precision is reduced */ + time_precision = anytime_typmod_check(true, + time_precision); + result = DatumGetTimeTzADTP(value); + AdjustTimeForTypmod(&result->time, time_precision); + value = TimeTzADTPGetDatum(result); + + /* Update the typmod value with the user-given precision */ + typmod = time_precision; + } + + typid = TIMETZOID; + } + break; + case jpiTimestamp: + { + /* Convert result type to timestamp without time zone */ + switch (typid) + { + case DATEOID: + value = DirectFunctionCall1(date_timestamp, + value); + break; + case TIMEOID: + case TIMETZOID: + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("timestamp format is not recognized: \"%s\"", + text_to_cstring(datetime))))); + break; + case TIMESTAMPOID: /* Nothing to do for TIMESTAMP */ + break; + case TIMESTAMPTZOID: + value = DirectFunctionCall1(timestamptz_timestamp, + value); + break; + default: + elog(ERROR, "type with oid %d not supported", typid); + } + + /* Force the user-given time precision, if any */ + if (time_precision != -1) + { + Timestamp result; + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + /* Get a warning when precision is reduced */ + time_precision = anytimestamp_typmod_check(false, + time_precision); + result = DatumGetTimestamp(value); + AdjustTimestampForTypmod(&result, time_precision, + (Node *) &escontext); + if (escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("numeric argument of jsonpath item method .%s() is out of range for type integer", + jspOperationName(jsp->type))))); + value = TimestampGetDatum(result); + + /* Update the typmod value with the user-given precision */ + typmod = time_precision; + } + + typid = TIMESTAMPOID; + } + break; + case jpiTimestampTz: + { + /* Convert result type to timestamp with time zone */ + switch (typid) + { + case DATEOID: + value = DirectFunctionCall1(date_timestamptz, + value); + break; + case TIMEOID: + case TIMETZOID: + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("timestamp_tz format is not recognized: \"%s\"", + text_to_cstring(datetime))))); + break; + case TIMESTAMPOID: + value = DirectFunctionCall1(timestamp_timestamptz, + value); + break; + case TIMESTAMPTZOID: /* Nothing to do for TIMESTAMPTZ */ + break; + default: + elog(ERROR, "type with oid %d not supported", typid); + } + + /* Force the user-given time precision, if any */ + if (time_precision != -1) + { + Timestamp result; + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + /* Get a warning when precision is reduced */ + time_precision = anytimestamp_typmod_check(true, + time_precision); + result = DatumGetTimestampTz(value); + AdjustTimestampForTypmod(&result, time_precision, + (Node *) &escontext); + if (escontext.error_occurred) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION), + errmsg("numeric argument of jsonpath item method .%s() is out of range for type integer", + jspOperationName(jsp->type))))); + value = TimestampTzGetDatum(result); + + /* Update the typmod value with the user-given precision */ + typmod = time_precision; + } + + typid = TIMESTAMPTZOID; + } + break; + default: + elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type); } pfree(datetime); diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y index 5e4eb52641d..8733a0eac66 100644 --- a/src/backend/utils/adt/jsonpath_gram.y +++ b/src/backend/utils/adt/jsonpath_gram.y @@ -82,15 +82,18 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr, %token <str> ANY_P STRICT_P LAX_P LAST_P STARTS_P WITH_P LIKE_REGEX_P FLAG_P %token <str> ABS_P SIZE_P TYPE_P FLOOR_P DOUBLE_P CEILING_P KEYVALUE_P %token <str> DATETIME_P +%token <str> BIGINT_P BOOLEAN_P DATE_P DECIMAL_P INTEGER_P NUMBER_P +%token <str> STRINGFUNC_P TIME_P TIME_TZ_P TIMESTAMP_P TIMESTAMP_TZ_P %type <result> result %type <value> scalar_value path_primary expr array_accessor any_path accessor_op key predicate delimited_predicate index_elem starts_with_initial expr_or_predicate - datetime_template opt_datetime_template + datetime_template opt_datetime_template csv_elem + datetime_precision opt_datetime_precision -%type <elems> accessor_expr +%type <elems> accessor_expr csv_list opt_csv_list %type <indexs> index_list @@ -248,9 +251,59 @@ accessor_op: | array_accessor { $$ = $1; } | '.' any_path { $$ = $2; } | '.' method '(' ')' { $$ = makeItemType($2); } - | '.' DATETIME_P '(' opt_datetime_template ')' - { $$ = makeItemUnary(jpiDatetime, $4); } | '?' '(' predicate ')' { $$ = makeItemUnary(jpiFilter, $3); } + | '.' DECIMAL_P '(' opt_csv_list ')' + { + if (list_length($4) == 0) + $$ = makeItemBinary(jpiDecimal, NULL, NULL); + else if (list_length($4) == 1) + $$ = makeItemBinary(jpiDecimal, linitial($4), NULL); + else if (list_length($4) == 2) + $$ = makeItemBinary(jpiDecimal, linitial($4), lsecond($4)); + else + ereturn(escontext, false, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid input syntax for type %s", "jsonpath"), + errdetail(".decimal() can only have an optional precision[,scale]."))); + } + | '.' DATETIME_P '(' opt_datetime_template ')' + { $$ = makeItemUnary(jpiDatetime, $4); } + | '.' TIME_P '(' opt_datetime_precision ')' + { $$ = makeItemUnary(jpiTime, $4); } + | '.' TIME_TZ_P '(' opt_datetime_precision ')' + { $$ = makeItemUnary(jpiTimeTz, $4); } + | '.' TIMESTAMP_P '(' opt_datetime_precision ')' + { $$ = makeItemUnary(jpiTimestamp, $4); } + | '.' TIMESTAMP_TZ_P '(' opt_datetime_precision ')' + { $$ = makeItemUnary(jpiTimestampTz, $4); } + ; + +csv_elem: + INT_P + { $$ = makeItemNumeric(&$1); } + | '+' INT_P %prec UMINUS + { $$ = makeItemUnary(jpiPlus, makeItemNumeric(&$2)); } + | '-' INT_P %prec UMINUS + { $$ = makeItemUnary(jpiMinus, makeItemNumeric(&$2)); } + ; + +csv_list: + csv_elem { $$ = list_make1($1); } + | csv_list ',' csv_elem { $$ = lappend($1, $3); } + ; + +opt_csv_list: + csv_list { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } + ; + +datetime_precision: + INT_P { $$ = makeItemNumeric(&$1); } + ; + +opt_datetime_precision: + datetime_precision { $$ = $1; } + | /* EMPTY */ { $$ = NULL; } ; datetime_template: @@ -291,6 +344,17 @@ key_name: | WITH_P | LIKE_REGEX_P | FLAG_P + | BIGINT_P + | BOOLEAN_P + | DATE_P + | DECIMAL_P + | INTEGER_P + | NUMBER_P + | STRINGFUNC_P + | TIME_P + | TIME_TZ_P + | TIMESTAMP_P + | TIMESTAMP_TZ_P ; method: @@ -301,6 +365,12 @@ method: | DOUBLE_P { $$ = jpiDouble; } | CEILING_P { $$ = jpiCeiling; } | KEYVALUE_P { $$ = jpiKeyValue; } + | BIGINT_P { $$ = jpiBigint; } + | BOOLEAN_P { $$ = jpiBoolean; } + | DATE_P { $$ = jpiDate; } + | INTEGER_P { $$ = jpiInteger; } + | NUMBER_P { $$ = jpiNumber; } + | STRINGFUNC_P { $$ = jpiStringFunc; } ; %% diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l index 757cd953d98..7acda778375 100644 --- a/src/backend/utils/adt/jsonpath_scan.l +++ b/src/backend/utils/adt/jsonpath_scan.l @@ -401,24 +401,35 @@ static const JsonPathKeyword keywords[] = { { 2, false, TO_P, "to"}, { 3, false, ABS_P, "abs"}, { 3, false, LAX_P, "lax"}, + { 4, false, DATE_P, "date"}, { 4, false, FLAG_P, "flag"}, { 4, false, LAST_P, "last"}, { 4, true, NULL_P, "null"}, { 4, false, SIZE_P, "size"}, + { 4, false, TIME_P, "time"}, { 4, true, TRUE_P, "true"}, { 4, false, TYPE_P, "type"}, { 4, false, WITH_P, "with"}, { 5, true, FALSE_P, "false"}, { 5, false, FLOOR_P, "floor"}, + { 6, false, BIGINT_P, "bigint"}, { 6, false, DOUBLE_P, "double"}, { 6, false, EXISTS_P, "exists"}, + { 6, false, NUMBER_P, "number"}, { 6, false, STARTS_P, "starts"}, { 6, false, STRICT_P, "strict"}, + { 6, false, STRINGFUNC_P, "string"}, + { 7, false, BOOLEAN_P, "boolean"}, { 7, false, CEILING_P, "ceiling"}, + { 7, false, DECIMAL_P, "decimal"}, + { 7, false, INTEGER_P, "integer"}, + { 7, false, TIME_TZ_P, "time_tz"}, { 7, false, UNKNOWN_P, "unknown"}, { 8, false, DATETIME_P, "datetime"}, { 8, false, KEYVALUE_P, "keyvalue"}, + { 9, false, TIMESTAMP_P, "timestamp"}, { 10,false, LIKE_REGEX_P, "like_regex"}, + { 12,false, TIMESTAMP_TZ_P, "timestamp_tz"}, }; /* Check if current scanstring value is a keyword */ diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index 9d55c25ebc5..0f0e126e03e 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -102,6 +102,17 @@ typedef enum JsonPathItemType jpiLast, /* LAST array subscript */ jpiStartsWith, /* STARTS WITH predicate */ jpiLikeRegex, /* LIKE_REGEX predicate */ + jpiBigint, /* .bigint() item method */ + jpiBoolean, /* .boolean() item method */ + jpiDate, /* .date() item method */ + jpiDecimal, /* .decimal() item method */ + jpiInteger, /* .integer() item method */ + jpiNumber, /* .number() item method */ + jpiStringFunc, /* .string() item method */ + jpiTime, /* .time() item method */ + jpiTimeTz, /* .time_tz() item method */ + jpiTimestamp, /* .timestamp() item method */ + jpiTimestampTz, /* .timestamp_tz() item method */ } JsonPathItemType; /* XQuery regex mode flags for LIKE_REGEX predicate */ diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out index 6659bc9091a..e758d729f43 100644 --- a/src/test/regress/expected/jsonb_jsonpath.out +++ b/src/test/regress/expected/jsonb_jsonpath.out @@ -1732,7 +1732,1246 @@ select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH2 ERROR: unmatched format character "T" select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); ERROR: unmatched format character "T" +-- Test .bigint() +select jsonb_path_query('null', '$.bigint()'); +ERROR: jsonpath item method .bigint() can only be applied to a string or numeric value +select jsonb_path_query('true', '$.bigint()'); +ERROR: jsonpath item method .bigint() can only be applied to a string or numeric value +select jsonb_path_query('null', '$.bigint()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('true', '$.bigint()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', '$.bigint()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.bigint()'); +ERROR: jsonpath item method .bigint() can only be applied to a string or numeric value +select jsonb_path_query('{}', '$.bigint()'); +ERROR: jsonpath item method .bigint() can only be applied to a string or numeric value +select jsonb_path_query('[]', 'strict $.bigint()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('{}', '$.bigint()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"1.23"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('"1.23aaa"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('1e1000', '$.bigint()'); +ERROR: numeric argument of jsonpath item method .bigint() is out of range for type bigint +select jsonb_path_query('"nan"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('"NaN"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('"inf"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('"-inf"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('"inf"', '$.bigint()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"-inf"', '$.bigint()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('123', '$.bigint()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('"123"', '$.bigint()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('1.23', '$.bigint()'); + jsonb_path_query +------------------ + 1 +(1 row) + +select jsonb_path_query('1.83', '$.bigint()'); + jsonb_path_query +------------------ + 2 +(1 row) + +select jsonb_path_query('1234567890123', '$.bigint()'); + jsonb_path_query +------------------ + 1234567890123 +(1 row) + +select jsonb_path_query('"1234567890123"', '$.bigint()'); + jsonb_path_query +------------------ + 1234567890123 +(1 row) + +select jsonb_path_query('12345678901234567890', '$.bigint()'); +ERROR: numeric argument of jsonpath item method .bigint() is out of range for type bigint +select jsonb_path_query('"12345678901234567890"', '$.bigint()'); +ERROR: string argument of jsonpath item method .bigint() is not a valid representation of a big integer +select jsonb_path_query('"+123"', '$.bigint()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('-123', '$.bigint()'); + jsonb_path_query +------------------ + -123 +(1 row) + +select jsonb_path_query('"-123"', '$.bigint()'); + jsonb_path_query +------------------ + -123 +(1 row) + +select jsonb_path_query('123', '$.bigint() * 2'); + jsonb_path_query +------------------ + 246 +(1 row) + +-- Test .boolean() +select jsonb_path_query('null', '$.boolean()'); +ERROR: jsonpath item method .boolean() can only be applied to a bool, string, or numeric value +select jsonb_path_query('null', '$.boolean()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', '$.boolean()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.boolean()'); +ERROR: jsonpath item method .boolean() can only be applied to a bool, string, or numeric value +select jsonb_path_query('{}', '$.boolean()'); +ERROR: jsonpath item method .boolean() can only be applied to a bool, string, or numeric value +select jsonb_path_query('[]', 'strict $.boolean()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('{}', '$.boolean()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('1.23', '$.boolean()'); +ERROR: numeric argument of jsonpath item method .boolean() is out of range for type boolean +select jsonb_path_query('"1.23"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('"1.23aaa"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('1e1000', '$.boolean()'); +ERROR: numeric argument of jsonpath item method .boolean() is out of range for type boolean +select jsonb_path_query('"nan"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('"NaN"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('"inf"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('"-inf"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('"inf"', '$.boolean()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"-inf"', '$.boolean()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"100"', '$.boolean()'); +ERROR: string argument of jsonpath item method .boolean() is not a valid representation of a boolean +select jsonb_path_query('true', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('false', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('1', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('0', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('-1', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('100', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"1"', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"0"', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('"true"', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"false"', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('"TRUE"', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"FALSE"', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('"yes"', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"NO"', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('"T"', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"f"', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('"y"', '$.boolean()'); + jsonb_path_query +------------------ + true +(1 row) + +select jsonb_path_query('"N"', '$.boolean()'); + jsonb_path_query +------------------ + false +(1 row) + +select jsonb_path_query('true', '$.boolean().type()'); + jsonb_path_query +------------------ + "boolean" +(1 row) + +select jsonb_path_query('123', '$.boolean().type()'); + jsonb_path_query +------------------ + "boolean" +(1 row) + +select jsonb_path_query('"Yes"', '$.boolean().type()'); + jsonb_path_query +------------------ + "boolean" +(1 row) + +select jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()'); + jsonb_path_query_array +------------------------ + [true, true, false] +(1 row) + +-- Test .date() +select jsonb_path_query('null', '$.date()'); +ERROR: jsonpath item method .date() can only be applied to a string +select jsonb_path_query('true', '$.date()'); +ERROR: jsonpath item method .date() can only be applied to a string +select jsonb_path_query('1', '$.date()'); +ERROR: jsonpath item method .date() can only be applied to a string +select jsonb_path_query('[]', '$.date()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.date()'); +ERROR: jsonpath item method .date() can only be applied to a string +select jsonb_path_query('{}', '$.date()'); +ERROR: jsonpath item method .date() can only be applied to a string +select jsonb_path_query('"bogus"', '$.date()'); +ERROR: date format is not recognized: "bogus" +select jsonb '"2023-08-15"' @? '$.date()'; + ?column? +---------- + t +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.date()'); + jsonb_path_query +------------------ + "2023-08-15" +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.date().type()'); + jsonb_path_query +------------------ + "date" +(1 row) + +select jsonb_path_query('"12:34:56"', '$.date()'); +ERROR: date format is not recognized: "12:34:56" +select jsonb_path_query('"12:34:56 +05:30"', '$.date()'); +ERROR: date format is not recognized: "12:34:56 +05:30" +select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()'); + jsonb_path_query +------------------ + "2023-08-15" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()'); + jsonb_path_query +------------------ + "2023-08-15" +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.date(2)'); +ERROR: syntax error at or near "2" of jsonpath input +LINE 1: select jsonb_path_query('"2023-08-15"', '$.date(2)'); + ^ +-- Test .decimal() +select jsonb_path_query('null', '$.decimal()'); +ERROR: jsonpath item method .decimal() can only be applied to a string or numeric value +select jsonb_path_query('true', '$.decimal()'); +ERROR: jsonpath item method .decimal() can only be applied to a string or numeric value +select jsonb_path_query('null', '$.decimal()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('true', '$.decimal()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', '$.decimal()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.decimal()'); +ERROR: jsonpath item method .decimal() can only be applied to a string or numeric value +select jsonb_path_query('{}', '$.decimal()'); +ERROR: jsonpath item method .decimal() can only be applied to a string or numeric value +select jsonb_path_query('[]', 'strict $.decimal()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('{}', '$.decimal()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('1.23', '$.decimal()'); + jsonb_path_query +------------------ + 1.23 +(1 row) + +select jsonb_path_query('"1.23"', '$.decimal()'); + jsonb_path_query +------------------ + 1.23 +(1 row) + +select jsonb_path_query('"1.23aaa"', '$.decimal()'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('1e1000', '$.decimal()'); + jsonb_path_query +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +(1 row) + +select jsonb_path_query('"nan"', '$.decimal()'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('"NaN"', '$.decimal()'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('"inf"', '$.decimal()'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('"-inf"', '$.decimal()'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('"inf"', '$.decimal()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"-inf"', '$.decimal()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('123', '$.decimal()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('"123"', '$.decimal()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('12345678901234567890', '$.decimal()'); + jsonb_path_query +---------------------- + 12345678901234567890 +(1 row) + +select jsonb_path_query('"12345678901234567890"', '$.decimal()'); + jsonb_path_query +---------------------- + 12345678901234567890 +(1 row) + +select jsonb_path_query('"+12.3"', '$.decimal()'); + jsonb_path_query +------------------ + 12.3 +(1 row) + +select jsonb_path_query('-12.3', '$.decimal()'); + jsonb_path_query +------------------ + -12.3 +(1 row) + +select jsonb_path_query('"-12.3"', '$.decimal()'); + jsonb_path_query +------------------ + -12.3 +(1 row) + +select jsonb_path_query('12.3', '$.decimal() * 2'); + jsonb_path_query +------------------ + 24.6 +(1 row) + +select jsonb_path_query('12345.678', '$.decimal(6, 1)'); + jsonb_path_query +------------------ + 12345.7 +(1 row) + +select jsonb_path_query('12345.678', '$.decimal(6, 2)'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('1234.5678', '$.decimal(6, 2)'); + jsonb_path_query +------------------ + 1234.57 +(1 row) + +select jsonb_path_query('12345.678', '$.decimal(4, 6)'); +ERROR: string argument of jsonpath item method .decimal() is not a valid representation of a decimal or number +select jsonb_path_query('12345.678', '$.decimal(0, 6)'); +ERROR: NUMERIC precision 0 must be between 1 and 1000 +select jsonb_path_query('12345.678', '$.decimal(1001, 6)'); +ERROR: NUMERIC precision 1001 must be between 1 and 1000 +select jsonb_path_query('1234.5678', '$.decimal(+6, +2)'); + jsonb_path_query +------------------ + 1234.57 +(1 row) + +select jsonb_path_query('1234.5678', '$.decimal(+6, -2)'); + jsonb_path_query +------------------ + 1200 +(1 row) + +select jsonb_path_query('1234.5678', '$.decimal(-6, +2)'); +ERROR: NUMERIC precision -6 must be between 1 and 1000 +select jsonb_path_query('1234.5678', '$.decimal(6, -1001)'); +ERROR: NUMERIC scale -1001 must be between -1000 and 1000 +select jsonb_path_query('1234.5678', '$.decimal(6, 1001)'); +ERROR: NUMERIC scale 1001 must be between -1000 and 1000 +select jsonb_path_query('-1234.5678', '$.decimal(+6, -2)'); + jsonb_path_query +------------------ + -1200 +(1 row) + +select jsonb_path_query('0.0123456', '$.decimal(1,2)'); + jsonb_path_query +------------------ + 0.01 +(1 row) + +select jsonb_path_query('0.0012345', '$.decimal(2,4)'); + jsonb_path_query +------------------ + 0.0012 +(1 row) + +select jsonb_path_query('-0.00123456', '$.decimal(2,-4)'); + jsonb_path_query +------------------ + 0 +(1 row) + +select jsonb_path_query('12.3', '$.decimal(12345678901,1)'); +ERROR: precision of jsonpath item method .decimal() is out of range for type integer +select jsonb_path_query('12.3', '$.decimal(1,12345678901)'); +ERROR: scale of jsonpath item method .decimal() is out of range for type integer +-- Test .integer() +select jsonb_path_query('null', '$.integer()'); +ERROR: jsonpath item method .integer() can only be applied to a string or numeric value +select jsonb_path_query('true', '$.integer()'); +ERROR: jsonpath item method .integer() can only be applied to a string or numeric value +select jsonb_path_query('null', '$.integer()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('true', '$.integer()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', '$.integer()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.integer()'); +ERROR: jsonpath item method .integer() can only be applied to a string or numeric value +select jsonb_path_query('{}', '$.integer()'); +ERROR: jsonpath item method .integer() can only be applied to a string or numeric value +select jsonb_path_query('[]', 'strict $.integer()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('{}', '$.integer()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"1.23"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('"1.23aaa"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('1e1000', '$.integer()'); +ERROR: numeric argument of jsonpath item method .integer() is out of range for type integer +select jsonb_path_query('"nan"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('"NaN"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('"inf"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('"-inf"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('"inf"', '$.integer()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"-inf"', '$.integer()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('123', '$.integer()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('"123"', '$.integer()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('1.23', '$.integer()'); + jsonb_path_query +------------------ + 1 +(1 row) + +select jsonb_path_query('1.83', '$.integer()'); + jsonb_path_query +------------------ + 2 +(1 row) + +select jsonb_path_query('12345678901', '$.integer()'); +ERROR: numeric argument of jsonpath item method .integer() is out of range for type integer +select jsonb_path_query('"12345678901"', '$.integer()'); +ERROR: string argument of jsonpath item method .integer() is not a valid representation of an integer +select jsonb_path_query('"+123"', '$.integer()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('-123', '$.integer()'); + jsonb_path_query +------------------ + -123 +(1 row) + +select jsonb_path_query('"-123"', '$.integer()'); + jsonb_path_query +------------------ + -123 +(1 row) + +select jsonb_path_query('123', '$.integer() * 2'); + jsonb_path_query +------------------ + 246 +(1 row) + +-- Test .number() +select jsonb_path_query('null', '$.number()'); +ERROR: jsonpath item method .number() can only be applied to a string or numeric value +select jsonb_path_query('true', '$.number()'); +ERROR: jsonpath item method .number() can only be applied to a string or numeric value +select jsonb_path_query('null', '$.number()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('true', '$.number()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', '$.number()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.number()'); +ERROR: jsonpath item method .number() can only be applied to a string or numeric value +select jsonb_path_query('{}', '$.number()'); +ERROR: jsonpath item method .number() can only be applied to a string or numeric value +select jsonb_path_query('[]', 'strict $.number()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('{}', '$.number()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('1.23', '$.number()'); + jsonb_path_query +------------------ + 1.23 +(1 row) + +select jsonb_path_query('"1.23"', '$.number()'); + jsonb_path_query +------------------ + 1.23 +(1 row) + +select jsonb_path_query('"1.23aaa"', '$.number()'); +ERROR: string argument of jsonpath item method .number() is not a valid representation of a decimal or number +select jsonb_path_query('1e1000', '$.number()'); + jsonb_path_query +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +(1 row) + +select jsonb_path_query('"nan"', '$.number()'); +ERROR: string argument of jsonpath item method .number() is not a valid representation of a decimal or number +select jsonb_path_query('"NaN"', '$.number()'); +ERROR: string argument of jsonpath item method .number() is not a valid representation of a decimal or number +select jsonb_path_query('"inf"', '$.number()'); +ERROR: string argument of jsonpath item method .number() is not a valid representation of a decimal or number +select jsonb_path_query('"-inf"', '$.number()'); +ERROR: string argument of jsonpath item method .number() is not a valid representation of a decimal or number +select jsonb_path_query('"inf"', '$.number()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('"-inf"', '$.number()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('123', '$.number()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('"123"', '$.number()'); + jsonb_path_query +------------------ + 123 +(1 row) + +select jsonb_path_query('12345678901234567890', '$.number()'); + jsonb_path_query +---------------------- + 12345678901234567890 +(1 row) + +select jsonb_path_query('"12345678901234567890"', '$.number()'); + jsonb_path_query +---------------------- + 12345678901234567890 +(1 row) + +select jsonb_path_query('"+12.3"', '$.number()'); + jsonb_path_query +------------------ + 12.3 +(1 row) + +select jsonb_path_query('-12.3', '$.number()'); + jsonb_path_query +------------------ + -12.3 +(1 row) + +select jsonb_path_query('"-12.3"', '$.number()'); + jsonb_path_query +------------------ + -12.3 +(1 row) + +select jsonb_path_query('12.3', '$.number() * 2'); + jsonb_path_query +------------------ + 24.6 +(1 row) + +-- Test .string() +select jsonb_path_query('null', '$.string()'); +ERROR: jsonpath item method .string() can only be applied to a bool, string, numeric, or datetime value +select jsonb_path_query('null', '$.string()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', '$.string()'); +ERROR: jsonpath item method .string() can only be applied to a bool, string, numeric, or datetime value +select jsonb_path_query('[]', 'strict $.string()'); +ERROR: jsonpath item method .string() can only be applied to a bool, string, numeric, or datetime value +select jsonb_path_query('{}', '$.string()'); +ERROR: jsonpath item method .string() can only be applied to a bool, string, numeric, or datetime value +select jsonb_path_query('[]', 'strict $.string()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('{}', '$.string()', silent => true); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('1.23', '$.string()'); + jsonb_path_query +------------------ + "1.23" +(1 row) + +select jsonb_path_query('"1.23"', '$.string()'); + jsonb_path_query +------------------ + "1.23" +(1 row) + +select jsonb_path_query('"1.23aaa"', '$.string()'); + jsonb_path_query +------------------ + "1.23aaa" +(1 row) + +select jsonb_path_query('1234', '$.string()'); + jsonb_path_query +------------------ + "1234" +(1 row) + +select jsonb_path_query('true', '$.string()'); + jsonb_path_query +------------------ + "true" +(1 row) + +select jsonb_path_query('1234', '$.string().type()'); + jsonb_path_query +------------------ + "string" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); + jsonb_path_query +---------------------------- + "Tue Aug 15 00:04:56 2023" +(1 row) + +select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()'); + jsonb_path_query_array +-------------------------- + ["1.23", "yes", "false"] +(1 row) + +select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()'); + jsonb_path_query_array +-------------------------------- + ["string", "string", "string"] +(1 row) + +-- Test .time() +select jsonb_path_query('null', '$.time()'); +ERROR: jsonpath item method .time() can only be applied to a string +select jsonb_path_query('true', '$.time()'); +ERROR: jsonpath item method .time() can only be applied to a string +select jsonb_path_query('1', '$.time()'); +ERROR: jsonpath item method .time() can only be applied to a string +select jsonb_path_query('[]', '$.time()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.time()'); +ERROR: jsonpath item method .time() can only be applied to a string +select jsonb_path_query('{}', '$.time()'); +ERROR: jsonpath item method .time() can only be applied to a string +select jsonb_path_query('"bogus"', '$.time()'); +ERROR: time format is not recognized: "bogus" +select jsonb '"12:34:56"' @? '$.time()'; + ?column? +---------- + t +(1 row) + +select jsonb_path_query('"12:34:56"', '$.time()'); + jsonb_path_query +------------------ + "12:34:56" +(1 row) + +select jsonb_path_query('"12:34:56"', '$.time().type()'); + jsonb_path_query +-------------------------- + "time without time zone" +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.time()'); +ERROR: time format is not recognized: "2023-08-15" +select jsonb_path_query('"12:34:56 +05:30"', '$.time()'); + jsonb_path_query +------------------ + "12:34:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56"', '$.time()'); + jsonb_path_query +------------------ + "12:34:56" +(1 row) + +select jsonb_path_query('"12:34:56.789"', '$.time(-1)'); +ERROR: syntax error at or near "-" of jsonpath input +LINE 1: select jsonb_path_query('"12:34:56.789"', '$.time(-1)'); + ^ +select jsonb_path_query('"12:34:56.789"', '$.time(2.0)'); +ERROR: syntax error at or near "2.0" of jsonpath input +LINE 1: select jsonb_path_query('"12:34:56.789"', '$.time(2.0)'); + ^ +select jsonb_path_query('"12:34:56.789"', '$.time(12345678901)'); +ERROR: time precision of jsonpath item method .time() is out of range for type integer +select jsonb_path_query('"12:34:56.789"', '$.time(0)'); + jsonb_path_query +------------------ + "12:34:57" +(1 row) + +select jsonb_path_query('"12:34:56.789"', '$.time(2)'); + jsonb_path_query +------------------ + "12:34:56.79" +(1 row) + +select jsonb_path_query('"12:34:56.789"', '$.time(5)'); + jsonb_path_query +------------------ + "12:34:56.789" +(1 row) + +select jsonb_path_query('"12:34:56.789"', '$.time(10)'); +WARNING: TIME(10) precision reduced to maximum allowed, 6 + jsonb_path_query +------------------ + "12:34:56.789" +(1 row) + +select jsonb_path_query('"12:34:56.789012"', '$.time(8)'); +WARNING: TIME(8) precision reduced to maximum allowed, 6 + jsonb_path_query +------------------- + "12:34:56.789012" +(1 row) + +-- Test .time_tz() +select jsonb_path_query('null', '$.time_tz()'); +ERROR: jsonpath item method .time_tz() can only be applied to a string +select jsonb_path_query('true', '$.time_tz()'); +ERROR: jsonpath item method .time_tz() can only be applied to a string +select jsonb_path_query('1', '$.time_tz()'); +ERROR: jsonpath item method .time_tz() can only be applied to a string +select jsonb_path_query('[]', '$.time_tz()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.time_tz()'); +ERROR: jsonpath item method .time_tz() can only be applied to a string +select jsonb_path_query('{}', '$.time_tz()'); +ERROR: jsonpath item method .time_tz() can only be applied to a string +select jsonb_path_query('"bogus"', '$.time_tz()'); +ERROR: time_tz format is not recognized: "bogus" +select jsonb '"12:34:56 +05:30"' @? '$.time_tz()'; + ?column? +---------- + t +(1 row) + +select jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()'); + jsonb_path_query +------------------ + "12:34:56+05:30" +(1 row) + +select jsonb_path_query('"12:34:56 +05:30"', '$.time_tz().type()'); + jsonb_path_query +----------------------- + "time with time zone" +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.time_tz()'); +ERROR: time_tz format is not recognized: "2023-08-15" +select jsonb_path_query('"2023-08-15 12:34:56"', '$.time_tz()'); +ERROR: time_tz format is not recognized: "2023-08-15 12:34:56" +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(-1)'); +ERROR: syntax error at or near "-" of jsonpath input +LINE 1: select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(... + ^ +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2.0)'); +ERROR: syntax error at or near "2.0" of jsonpath input +LINE 1: select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(... + ^ +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(12345678901)'); +ERROR: time precision of jsonpath item method .time_tz() is out of range for type integer +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(0)'); + jsonb_path_query +------------------ + "12:34:57+05:30" +(1 row) + +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)'); + jsonb_path_query +--------------------- + "12:34:56.79+05:30" +(1 row) + +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(5)'); + jsonb_path_query +---------------------- + "12:34:56.789+05:30" +(1 row) + +select jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(10)'); +WARNING: TIME(10) WITH TIME ZONE precision reduced to maximum allowed, 6 + jsonb_path_query +---------------------- + "12:34:56.789+05:30" +(1 row) + +select jsonb_path_query('"12:34:56.789012 +05:30"', '$.time_tz(8)'); +WARNING: TIME(8) WITH TIME ZONE precision reduced to maximum allowed, 6 + jsonb_path_query +------------------------- + "12:34:56.789012+05:30" +(1 row) + +-- Test .timestamp() +select jsonb_path_query('null', '$.timestamp()'); +ERROR: jsonpath item method .timestamp() can only be applied to a string +select jsonb_path_query('true', '$.timestamp()'); +ERROR: jsonpath item method .timestamp() can only be applied to a string +select jsonb_path_query('1', '$.timestamp()'); +ERROR: jsonpath item method .timestamp() can only be applied to a string +select jsonb_path_query('[]', '$.timestamp()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.timestamp()'); +ERROR: jsonpath item method .timestamp() can only be applied to a string +select jsonb_path_query('{}', '$.timestamp()'); +ERROR: jsonpath item method .timestamp() can only be applied to a string +select jsonb_path_query('"bogus"', '$.timestamp()'); +ERROR: timestamp format is not recognized: "bogus" +select jsonb '"2023-08-15 12:34:56"' @? '$.timestamp()'; + ?column? +---------- + t +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()'); + jsonb_path_query +----------------------- + "2023-08-15T12:34:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().type()'); + jsonb_path_query +------------------------------- + "timestamp without time zone" +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.timestamp()'); + jsonb_path_query +----------------------- + "2023-08-15T00:00:00" +(1 row) + +select jsonb_path_query('"12:34:56"', '$.timestamp()'); +ERROR: timestamp format is not recognized: "12:34:56" +select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp()'); +ERROR: timestamp format is not recognized: "12:34:56 +05:30" +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(-1)'); +ERROR: syntax error at or near "-" of jsonpath input +LINE 1: ...ect jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timesta... + ^ +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2.0)'); +ERROR: syntax error at or near "2.0" of jsonpath input +LINE 1: ...ect jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timesta... + ^ +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(12345678901)'); +ERROR: time precision of jsonpath item method .timestamp() is out of range for type integer +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(0)'); + jsonb_path_query +----------------------- + "2023-08-15T12:34:57" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)'); + jsonb_path_query +-------------------------- + "2023-08-15T12:34:56.79" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(5)'); + jsonb_path_query +--------------------------- + "2023-08-15T12:34:56.789" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(10)'); +WARNING: TIMESTAMP(10) precision reduced to maximum allowed, 6 + jsonb_path_query +--------------------------- + "2023-08-15T12:34:56.789" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789012"', '$.timestamp(8)'); +WARNING: TIMESTAMP(8) precision reduced to maximum allowed, 6 + jsonb_path_query +------------------------------ + "2023-08-15T12:34:56.789012" +(1 row) + +-- Test .timestamp_tz() +select jsonb_path_query('null', '$.timestamp_tz()'); +ERROR: jsonpath item method .timestamp_tz() can only be applied to a string +select jsonb_path_query('true', '$.timestamp_tz()'); +ERROR: jsonpath item method .timestamp_tz() can only be applied to a string +select jsonb_path_query('1', '$.timestamp_tz()'); +ERROR: jsonpath item method .timestamp_tz() can only be applied to a string +select jsonb_path_query('[]', '$.timestamp_tz()'); + jsonb_path_query +------------------ +(0 rows) + +select jsonb_path_query('[]', 'strict $.timestamp_tz()'); +ERROR: jsonpath item method .timestamp_tz() can only be applied to a string +select jsonb_path_query('{}', '$.timestamp_tz()'); +ERROR: jsonpath item method .timestamp_tz() can only be applied to a string +select jsonb_path_query('"bogus"', '$.timestamp_tz()'); +ERROR: timestamp_tz format is not recognized: "bogus" +select jsonb '"2023-08-15 12:34:56 +05:30"' @? '$.timestamp_tz()'; + ?column? +---------- + t +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()'); + jsonb_path_query +----------------------------- + "2023-08-15T12:34:56+05:30" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()'); + jsonb_path_query +---------------------------- + "timestamp with time zone" +(1 row) + +select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()'); + jsonb_path_query +----------------------------- + "2023-08-15T07:00:00+00:00" +(1 row) + +select jsonb_path_query('"12:34:56"', '$.timestamp_tz()'); +ERROR: timestamp_tz format is not recognized: "12:34:56" +select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp_tz()'); +ERROR: timestamp_tz format is not recognized: "12:34:56 +05:30" +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(-1)'); +ERROR: syntax error at or near "-" of jsonpath input +LINE 1: ...nb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timesta... + ^ +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2.0)'); +ERROR: syntax error at or near "2.0" of jsonpath input +LINE 1: ...nb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timesta... + ^ +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(12345678901)'); +ERROR: time precision of jsonpath item method .timestamp_tz() is out of range for type integer +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(0)'); + jsonb_path_query +----------------------------- + "2023-08-15T12:34:57+05:30" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)'); + jsonb_path_query +-------------------------------- + "2023-08-15T12:34:56.79+05:30" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(5)'); + jsonb_path_query +--------------------------------- + "2023-08-15T12:34:56.789+05:30" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(10)'); +WARNING: TIMESTAMP(10) WITH TIME ZONE precision reduced to maximum allowed, 6 + jsonb_path_query +--------------------------------- + "2023-08-15T12:34:56.789+05:30" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56.789012 +05:30"', '$.timestamp_tz(8)'); +WARNING: TIMESTAMP(8) WITH TIME ZONE precision reduced to maximum allowed, 6 + jsonb_path_query +------------------------------------ + "2023-08-15T12:34:56.789012+05:30" +(1 row) + set time zone '+00'; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()'); + jsonb_path_query +------------------ + "07:04:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()'); + jsonb_path_query +------------------ + "07:04:56+00:00" +(1 row) + +select jsonb_path_query('"12:34:56"', '$.time_tz()'); + jsonb_path_query +------------------ + "12:34:56+00:00" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); + jsonb_path_query +----------------------- + "2023-08-15T07:04:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()'); + jsonb_path_query +----------------------------- + "2023-08-15T12:34:56+00:00" +(1 row) + select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); jsonb_path_query ----------------------- @@ -1798,6 +3037,36 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); (1 row) set time zone '+10'; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()'); + jsonb_path_query +------------------ + "17:04:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()'); + jsonb_path_query +------------------ + "17:04:56+10:00" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); + jsonb_path_query +----------------------- + "2023-08-15T17:04:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()'); + jsonb_path_query +----------------------------- + "2023-08-15T02:34:56+00:00" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()'); + jsonb_path_query +----------------------------- + "2023-08-15T12:34:56+05:30" +(1 row) + select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")'); jsonb_path_query ----------------------- @@ -1863,6 +3132,30 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")'); (1 row) set time zone default; +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()'); + jsonb_path_query +------------------ + "00:04:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()'); + jsonb_path_query +------------------ + "00:04:56-07:00" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); + jsonb_path_query +----------------------- + "2023-08-15T00:04:56" +(1 row) + +select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()'); + jsonb_path_query +----------------------------- + "2023-08-15T12:34:56+05:30" +(1 row) + select jsonb_path_query('"2017-03-10"', '$.datetime().type()'); jsonb_path_query ------------------ @@ -2019,6 +3312,101 @@ select jsonb_path_query_tz( "2017-03-10T01:02:03+04:00" (2 rows) +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10" + "2017-03-10T00:00:00" + "2017-03-10T03:00:00+03:00" +(3 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10" + "2017-03-11" + "2017-03-10T00:00:00" + "2017-03-10T12:34:56" + "2017-03-10T03:00:00+03:00" +(5 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-09" + "2017-03-10T01:02:03+04:00" +(2 rows) + +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())'); + jsonb_path_query +------------------ + "2017-03-10" + "2017-03-10" + "2017-03-10" + "2017-03-10" +(4 rows) + +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())'); + jsonb_path_query +------------------ + "2017-03-10" + "2017-03-11" + "2017-03-10" + "2017-03-10" + "2017-03-10" +(5 rows) + +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())'); + jsonb_path_query +------------------ + "2017-03-09" + "2017-03-09" +(2 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "2017-03-10" + "2017-03-10" + "2017-03-10" + "2017-03-10" +(4 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "2017-03-10" + "2017-03-11" + "2017-03-10" + "2017-03-10" + "2017-03-10" +(5 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "2017-03-09" + "2017-03-09" +(2 rows) + -- 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"]', @@ -2064,6 +3452,112 @@ select jsonb_path_query_tz( "13:35:00+01:00" (3 rows) +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00" + "12:35:00+00:00" +(2 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00" + "12:36:00" + "12:35:00+00:00" +(3 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:34:00" + "12:35:00+01:00" + "13:35:00+01:00" +(3 rows) + +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())'); + jsonb_path_query +------------------ + "12:35:00" + "12:35:00" + "12:35:00" + "12:35:00" +(4 rows) + +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())'); + jsonb_path_query +------------------ + "12:35:00" + "12:36:00" + "12:35:00" + "12:35:00" + "13:35:00" + "12:35:00" +(6 rows) + +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())'); + jsonb_path_query +------------------ + "12:34:00" + "11:35:00" +(2 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00" + "12:35:00" + "12:35:00" + "12:35:00" +(4 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00" + "12:36:00" + "12:35:00" + "12:35:00" + "13:35:00" + "12:35:00" +(6 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:34:00" + "11:35:00" +(2 rows) + +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))'); + jsonb_path_query +------------------ + "12:35:00.12" + "12:36:00.11" + "12:35:00.12" + "13:35:00.12" +(4 rows) + -- 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"]', @@ -2110,6 +3604,110 @@ select jsonb_path_query_tz( "10:35:00" (3 rows) +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00+01:00" +(1 row) + +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00+01:00" + "12:36:00+01:00" + "12:35:00-02:00" + "11:35:00" + "12:35:00" +(5 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:34:00+01:00" + "12:35:00+02:00" + "10:35:00" +(3 rows) + +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())'); + jsonb_path_query +------------------ + "12:35:00+01:00" +(1 row) + +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())'); + jsonb_path_query +------------------ + "12:35:00+01:00" + "12:36:00+01:00" + "12:35:00-02:00" + "11:35:00+00:00" + "12:35:00+00:00" + "11:35:00+00:00" +(6 rows) + +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())'); + jsonb_path_query +------------------ + "12:34:00+01:00" + "12:35:00+02:00" + "10:35:00+00:00" +(3 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00+01:00" +(1 row) + +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())'); + jsonb_path_query_tz +--------------------- + "12:35:00+01:00" + "12:36:00+01:00" + "12:35:00-02:00" + "11:35:00+00:00" + "12:35:00+00:00" + "11:35:00+00:00" +(6 rows) + +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())'); + jsonb_path_query_tz +--------------------- + "12:34:00+01:00" + "12:35:00+02:00" + "10:35:00+00:00" +(3 rows) + +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))'); + jsonb_path_query +--------------------- + "12:35:00.12+01:00" + "12:36:00.11+01:00" + "12:35:00.12-02:00" + "12:35:00.12+00:00" + "11:35:00.12+00:00" +(5 rows) + -- 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"]', @@ -2157,6 +3755,111 @@ select jsonb_path_query_tz( "2017-03-10" (3 rows) +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:35:00" + "2017-03-10T13:35:00+01:00" +(2 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:36:00" + "2017-03-10T13:35:00+01:00" + "2017-03-10T12:35:00-01:00" + "2017-03-11" +(5 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:34:00" + "2017-03-10T12:35:00+01:00" + "2017-03-10" +(3 rows) + +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())'); + jsonb_path_query +----------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:35:00" +(2 rows) + +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())'); + jsonb_path_query +----------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:36:00" + "2017-03-10T12:35:00" + "2017-03-10T13:35:00" + "2017-03-11T00:00:00" +(5 rows) + +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())'); + jsonb_path_query +----------------------- + "2017-03-10T12:34:00" + "2017-03-10T11:35:00" + "2017-03-10T00:00:00" +(3 rows) + +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())'); + jsonb_path_query_tz +----------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:35:00" +(2 rows) + +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())'); + jsonb_path_query_tz +----------------------- + "2017-03-10T12:35:00" + "2017-03-10T12:36:00" + "2017-03-10T12:35:00" + "2017-03-10T13:35:00" + "2017-03-11T00:00:00" +(5 rows) + +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())'); + jsonb_path_query_tz +----------------------- + "2017-03-10T12:34:00" + "2017-03-10T11:35:00" + "2017-03-10T00:00:00" +(3 rows) + +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))'); + jsonb_path_query +-------------------------- + "2017-03-10T12:35:00.12" + "2017-03-10T12:36:00.11" + "2017-03-10T12:35:00.12" + "2017-03-10T13:35:00.1" + "2017-03-11T00:00:00" +(5 rows) + -- 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"]', @@ -2206,6 +3909,117 @@ select jsonb_path_query_tz( "2017-03-10" (4 rows) +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T11:35:00" +(2 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T12:36:00+01:00" + "2017-03-10T12:35:00-02:00" + "2017-03-10T11:35:00" + "2017-03-10T12:35:00" + "2017-03-11" +(6 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:34:00+01:00" + "2017-03-10T12:35:00+02:00" + "2017-03-10T10:35:00" + "2017-03-10" +(4 rows) + +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())'); + jsonb_path_query +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T11:35:00+00:00" +(2 rows) + +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())'); + jsonb_path_query +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T12:36:00+01:00" + "2017-03-10T12:35:00-02:00" + "2017-03-10T11:35:00+00:00" + "2017-03-10T12:35:00+00:00" + "2017-03-11T00:00:00+00:00" +(6 rows) + +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())'); + jsonb_path_query +----------------------------- + "2017-03-10T12:34:00+01:00" + "2017-03-10T12:35:00+02:00" + "2017-03-10T10:35:00+00:00" + "2017-03-10T00:00:00+00:00" +(4 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T11:35:00+00:00" +(2 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:35:00+01:00" + "2017-03-10T12:36:00+01:00" + "2017-03-10T12:35:00-02:00" + "2017-03-10T11:35:00+00:00" + "2017-03-10T12:35:00+00:00" + "2017-03-11T00:00:00+00:00" +(6 rows) + +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())'); + jsonb_path_query_tz +----------------------------- + "2017-03-10T12:34:00+01:00" + "2017-03-10T12:35:00+02:00" + "2017-03-10T10:35:00+00:00" + "2017-03-10T00:00:00+00:00" +(4 rows) + +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))'); + jsonb_path_query +-------------------------------- + "2017-03-10T12:35:00.12+01:00" + "2017-03-10T12:36:00.11+01:00" + "2017-03-10T12:35:00.12-02:00" + "2017-03-10T12:35:00.12+00:00" + "2017-03-11T00:00:00+00:00" +(5 rows) + -- overflow during comparison select jsonb_path_query('"1000000-01-01"', '$.datetime() > "2020-01-01 12:00:00".datetime()'::jsonpath); jsonb_path_query diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out index eeffb38c1b6..fd9bd755f52 100644 --- a/src/test/regress/expected/jsonpath.out +++ b/src/test/regress/expected/jsonpath.out @@ -405,6 +405,84 @@ select '$.datetime("datetime template")'::jsonpath; $.datetime("datetime template") (1 row) +select '$.bigint().integer().number().decimal()'::jsonpath; + jsonpath +----------------------------------------- + $.bigint().integer().number().decimal() +(1 row) + +select '$.boolean()'::jsonpath; + jsonpath +------------- + $.boolean() +(1 row) + +select '$.date()'::jsonpath; + jsonpath +---------- + $.date() +(1 row) + +select '$.decimal(4,2)'::jsonpath; + jsonpath +---------------- + $.decimal(4,2) +(1 row) + +select '$.string()'::jsonpath; + jsonpath +------------ + $.string() +(1 row) + +select '$.time()'::jsonpath; + jsonpath +---------- + $.time() +(1 row) + +select '$.time(6)'::jsonpath; + jsonpath +----------- + $.time(6) +(1 row) + +select '$.time_tz()'::jsonpath; + jsonpath +------------- + $.time_tz() +(1 row) + +select '$.time_tz(4)'::jsonpath; + jsonpath +-------------- + $.time_tz(4) +(1 row) + +select '$.timestamp()'::jsonpath; + jsonpath +--------------- + $.timestamp() +(1 row) + +select '$.timestamp(2)'::jsonpath; + jsonpath +---------------- + $.timestamp(2) +(1 row) + +select '$.timestamp_tz()'::jsonpath; + jsonpath +------------------ + $.timestamp_tz() +(1 row) + +select '$.timestamp_tz(0)'::jsonpath; + jsonpath +------------------- + $.timestamp_tz(0) +(1 row) + select '$ ? (@ starts with "abc")'::jsonpath; jsonpath ------------------------- 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); diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql index 56e0bef57fd..61a5270d4e8 100644 --- a/src/test/regress/sql/jsonpath.sql +++ b/src/test/regress/sql/jsonpath.sql @@ -73,6 +73,19 @@ select '$.double().floor().ceiling().abs()'::jsonpath; select '$.keyvalue().key'::jsonpath; select '$.datetime()'::jsonpath; select '$.datetime("datetime template")'::jsonpath; +select '$.bigint().integer().number().decimal()'::jsonpath; +select '$.boolean()'::jsonpath; +select '$.date()'::jsonpath; +select '$.decimal(4,2)'::jsonpath; +select '$.string()'::jsonpath; +select '$.time()'::jsonpath; +select '$.time(6)'::jsonpath; +select '$.time_tz()'::jsonpath; +select '$.time_tz(4)'::jsonpath; +select '$.timestamp()'::jsonpath; +select '$.timestamp(2)'::jsonpath; +select '$.timestamp_tz()'::jsonpath; +select '$.timestamp_tz(0)'::jsonpath; select '$ ? (@ starts with "abc")'::jsonpath; select '$ ? (@ starts with $var)'::jsonpath; |