v1-0001-Implement-jsonpath-.bigint-.integer-and-.number-m.patch
application/x-patch
Filename: v1-0001-Implement-jsonpath-.bigint-.integer-and-.number-m.patch
Type: application/x-patch
Part: 1
Message:
More new SQL/JSON item methods
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v1-0001
Subject: Implement jsonpath .bigint(), .integer(), and .number() methods
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 42 | 0 |
| src/backend/utils/adt/jsonpath.c | 24 | 0 |
| src/backend/utils/adt/jsonpath_exec.c | 190 | 0 |
| src/backend/utils/adt/jsonpath_gram.y | 7 | 0 |
| src/backend/utils/adt/jsonpath_scan.l | 3 | 0 |
| src/include/utils/jsonpath.h | 3 | 0 |
| src/test/regress/expected/jsonb_jsonpath.out | 329 | 0 |
| src/test/regress/sql/jsonb_jsonpath.sql | 86 | 0 |
From 109c9a2da73d72674f18857a843130211127ee40 Mon Sep 17 00:00:00 2001
From: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Date: Mon, 28 Aug 2023 18:34:31 +0530
Subject: [PATCH v1 1/4] Implement jsonpath .bigint(), .integer(), and
.number() methods
This commit implements jsonpath .bigint(), .integer(), and .number()
methods. The JSON string or a numeric value is converted to the
bigint, int4, and numeric type representation.
Jeevan Chalke.
---
doc/src/sgml/func.sgml | 42 ++++
src/backend/utils/adt/jsonpath.c | 24 ++
src/backend/utils/adt/jsonpath_exec.c | 190 ++++++++++++++++
src/backend/utils/adt/jsonpath_gram.y | 7 +
src/backend/utils/adt/jsonpath_scan.l | 3 +
src/include/utils/jsonpath.h | 3 +
src/test/regress/expected/jsonb_jsonpath.out | 329 +++++++++++++++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 86 +++++++
8 files changed, 684 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7a0d4b9..ca9899f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17535,6 +17535,48 @@ strict $.**.HR
<row>
<entry role="func_table_entry"><para role="func_signature">
+ <replaceable>value</replaceable> <literal>.</literal> <literal>bigint()</literal>
+ <returnvalue><replaceable>bigint</replaceable></returnvalue>
+ </para>
+ <para>
+ Big integer value converted from a JSON number or string
+ </para>
+ <para>
+ <literal>jsonb_path_query('{"len": "9876543219"}', '$.len.bigint()')</literal>
+ <returnvalue>9876543219</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>value</replaceable> <literal>.</literal> <literal>integer()</literal>
+ <returnvalue><replaceable>integer</replaceable></returnvalue>
+ </para>
+ <para>
+ Integer value converted from a JSON number or string
+ </para>
+ <para>
+ <literal>jsonb_path_query('{"len": "12345"}', '$.len.integer()')</literal>
+ <returnvalue>12345</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>value</replaceable> <literal>.</literal> <literal>number()</literal>
+ <returnvalue><replaceable>numeric</replaceable></returnvalue>
+ </para>
+ <para>
+ Numeric value converted from a JSON number or string
+ </para>
+ <para>
+ <literal>jsonb_path_query('{"len": "123.45"}', '$.len.number()')</literal>
+ <returnvalue>123.45</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
<replaceable>number</replaceable> <literal>.</literal> <literal>ceiling()</literal>
<returnvalue><replaceable>number</replaceable></returnvalue>
</para>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index c5ba3b7..f45f919 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -444,6 +444,9 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
case jpiCeiling:
case jpiDouble:
case jpiKeyValue:
+ case jpiBigint:
+ case jpiInteger:
+ case jpiNumber:
break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", item->type);
@@ -730,6 +733,15 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
case jpiDouble:
appendStringInfoString(buf, ".double()");
break;
+ case jpiBigint:
+ appendStringInfoString(buf, ".bigint()");
+ break;
+ case jpiInteger:
+ appendStringInfoString(buf, ".integer()");
+ break;
+ case jpiNumber:
+ appendStringInfoString(buf, ".number()");
+ break;
case jpiDatetime:
appendStringInfoString(buf, ".datetime(");
if (v->content.arg)
@@ -795,6 +807,12 @@ jspOperationName(JsonPathItemType type)
return "keyvalue";
case jpiDouble:
return "double";
+ case jpiBigint:
+ return "bigint";
+ case jpiInteger:
+ return "integer";
+ case jpiNumber:
+ return "number";
case jpiAbs:
return "abs";
case jpiFloor:
@@ -897,6 +915,9 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
case jpiFloor:
case jpiCeiling:
case jpiDouble:
+ case jpiBigint:
+ case jpiInteger:
+ case jpiNumber:
case jpiKeyValue:
case jpiLast:
break;
@@ -1012,6 +1033,9 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiFloor ||
v->type == jpiCeiling ||
v->type == jpiDouble ||
+ v->type == jpiBigint ||
+ v->type == jpiInteger ||
+ v->type == jpiNumber ||
v->type == jpiDatetime ||
v->type == jpiKeyValue ||
v->type == jpiStartsWith ||
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 2d0599b..ff7fdaf 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1098,6 +1098,196 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
break;
+ case jpiBigint:
+ {
+ JsonbValue jbv;
+ Datum datum;
+ bool noerr;
+
+ if (unwrap && JsonbType(jb) == jbvArray)
+ return executeItemUnwrapTargetArray(cxt, jsp, jb, found,
+ false);
+
+ if (jb->type == jbvNumeric)
+ {
+ char *tmp = DatumGetCString(DirectFunctionCall1(numeric_out,
+ NumericGetDatum(jb->val.numeric)));
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+ 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("numeric argument of jsonpath item method .%s() is out of range for type bigint",
+ jspOperationName(jsp->type)))));
+ 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};
+
+ 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 jpiInteger:
+ {
+ JsonbValue jbv;
+ Datum datum;
+ bool noerr;
+
+ if (unwrap && JsonbType(jb) == jbvArray)
+ return executeItemUnwrapTargetArray(cxt, jsp, jb, found,
+ false);
+
+ if (jb->type == jbvNumeric)
+ {
+ 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 integer",
+ jspOperationName(jsp->type)))));
+ 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};
+
+ 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 jpiNumber:
+ {
+ JsonbValue jbv;
+ Numeric num;
+
+ 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 not a valid representation of a number",
+ jspOperationName(jsp->type)))));
+
+ res = jperOk;
+ }
+ else if (jb->type == jbvString)
+ {
+ /* cast string as number */
+ Datum datum;
+ bool noerr;
+ char *tmp = pnstrdup(jb->val.string.val,
+ jb->val.string.len);
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+ noerr = DirectInputFunctionCallSafe(numeric_in, 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 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 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)))));
+
+ jb = &jbv;
+ jb->type = jbvNumeric;
+ jb->val.numeric = num;
+
+ res = executeNextItem(cxt, jsp, NULL, jb, found, true);
+ }
+ break;
+
case jpiDatetime:
if (unwrap && JsonbType(jb) == jbvArray)
return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false);
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index adc259d..340caa9 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -82,6 +82,7 @@ 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 INTEGER_P NUMBER_P
%type <result> result
@@ -283,6 +284,9 @@ key_name:
| TYPE_P
| FLOOR_P
| DOUBLE_P
+ | BIGINT_P
+ | INTEGER_P
+ | NUMBER_P
| CEILING_P
| DATETIME_P
| KEYVALUE_P
@@ -299,6 +303,9 @@ method:
| TYPE_P { $$ = jpiType; }
| FLOOR_P { $$ = jpiFloor; }
| DOUBLE_P { $$ = jpiDouble; }
+ | BIGINT_P { $$ = jpiBigint; }
+ | INTEGER_P { $$ = jpiInteger; }
+ | NUMBER_P { $$ = jpiNumber; }
| CEILING_P { $$ = jpiCeiling; }
| KEYVALUE_P { $$ = jpiKeyValue; }
;
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index 29c26af..1abcea3 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -410,11 +410,14 @@ static const JsonPathKeyword keywords[] = {
{ 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"},
{ 7, false, CEILING_P, "ceiling"},
+ { 7, false, INTEGER_P, "integer"},
{ 7, false, UNKNOWN_P, "unknown"},
{ 8, false, DATETIME_P, "datetime"},
{ 8, false, KEYVALUE_P, "keyvalue"},
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index f0181e0..9fe161f 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -89,6 +89,9 @@ typedef enum JsonPathItemType
jpiFloor, /* .floor() item method */
jpiCeiling, /* .ceiling() item method */
jpiDouble, /* .double() item method */
+ jpiBigint, /* .bigint() item method */
+ jpiInteger, /* .integer() item method */
+ jpiNumber, /* .number() item method */
jpiDatetime, /* .datetime() item method */
jpiKeyValue, /* .keyvalue() item method */
jpiSubscript, /* array subscript: 'expr' or 'expr TO expr' */
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 6659bc9..c7d1a4e 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1517,6 +1517,335 @@ select jsonb_path_query('"-inf"', '$.double()', silent => true);
------------------
(0 rows)
+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: numeric argument of jsonpath item method .bigint() is out of range for type bigint
+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('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)
+
+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: numeric argument of jsonpath item method .integer() is out of range for type integer
+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('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)
+
+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 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 number
+select jsonb_path_query('"NaN"', '$.number()');
+ERROR: string argument of jsonpath item method .number() is not a valid representation of a number
+select jsonb_path_query('"inf"', '$.number()');
+ERROR: string argument of jsonpath item method .number() is not a valid representation of a number
+select jsonb_path_query('"-inf"', '$.number()');
+ERROR: string argument of jsonpath item method .number() is not a valid representation of a 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)
+
select jsonb_path_query('{}', '$.abs()');
ERROR: jsonpath item method .abs() can only be applied to a numeric value
select jsonb_path_query('true', '$.floor()');
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index e0ce509..a9240c2 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -320,6 +320,92 @@ select jsonb_path_query('"-inf"', '$.double()');
select jsonb_path_query('"inf"', '$.double()', silent => true);
select jsonb_path_query('"-inf"', '$.double()', silent => true);
+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.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('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');
+
+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.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('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');
+
+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');
+
select jsonb_path_query('{}', '$.abs()');
select jsonb_path_query('true', '$.floor()');
select jsonb_path_query('"1.2"', '$.ceiling()');
--
1.8.3.1