aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/catalog/sql_features.txt28
-rw-r--r--src/backend/utils/adt/jsonpath.c138
-rw-r--r--src/backend/utils/adt/jsonpath_exec.c718
-rw-r--r--src/backend/utils/adt/jsonpath_gram.y78
-rw-r--r--src/backend/utils/adt/jsonpath_scan.l11
-rw-r--r--src/include/utils/jsonpath.h11
-rw-r--r--src/test/regress/expected/jsonb_jsonpath.out1814
-rw-r--r--src/test/regress/expected/jsonpath.out78
-rw-r--r--src/test/regress/sql/jsonb_jsonpath.sql492
-rw-r--r--src/test/regress/sql/jsonpath.sql13
10 files changed, 3351 insertions, 30 deletions
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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 22:24:37 +0000