v1-0004-Implement-jasonpath-.decimal-precision-scale-meth.patch

From 775f3a4be3420378cb619f5a8716995f8eaa8421 Mon Sep 17 00:00:00 2001
From: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Date: Mon, 28 Aug 2023 18:46:32 +0530
Subject: [PATCH v1 4/4] Implement jasonpath .decimal([precision [, scale]])
 method

This commit implements jsonpath .decimal() method with optional
precision and scale.  If precision and scale are provided, then
it is converted to the equivalent numerictypmod and applied to the
numeric number.

Jeevan Chalke.
---
 doc/src/sgml/func.sgml                       |  14 ++
 src/backend/utils/adt/jsonpath.c             |  22 +++
 src/backend/utils/adt/jsonpath_exec.c        |  93 ++++++++++++-
 src/backend/utils/adt/jsonpath_gram.y        |  40 +++++-
 src/backend/utils/adt/jsonpath_scan.l        |   1 +
 src/include/utils/jsonpath.h                 |   1 +
 src/test/regress/expected/jsonb_jsonpath.out | 191 ++++++++++++++++++++++++++-
 src/test/regress/sql/jsonb_jsonpath.sql      |  43 ++++++
 8 files changed, 391 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 32ca050..41a3525 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17567,6 +17567,20 @@ strict $.**.HR
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
+        <replaceable>value</replaceable> <literal>.</literal> <literal>decimal( [ <replaceable>precision</replaceable> [ , <replaceable>scale</replaceable> ] ] )</literal>
+        <returnvalue><replaceable>decimal</replaceable></returnvalue>
+       </para>
+       <para>
+        Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values.
+       </para>
+       <para>
+        <literal>jsonb_path_query('1234.5678', '$.decimal(6, 2)')</literal>
+        <returnvalue>1234.57</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
         <replaceable>value</replaceable> <literal>.</literal> <literal>bigint()</literal>
         <returnvalue><replaceable>bigint</replaceable></returnvalue>
        </para>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index de7adf5..42f325b 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -294,6 +294,7 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
 		case jpiMul:
 		case jpiDiv:
 		case jpiMod:
+		case jpiDecimal:
 		case jpiStartsWith:
 			{
 				/*
@@ -747,6 +748,21 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
 		case jpiDouble:
 			appendStringInfoString(buf, ".double()");
 			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 jpiBigint:
 			appendStringInfoString(buf, ".bigint()");
 			break;
@@ -840,6 +856,8 @@ jspOperationName(JsonPathItemType type)
 			return "keyvalue";
 		case jpiDouble:
 			return "double";
+		case jpiDecimal:
+			return "decimal";
 		case jpiBigint:
 			return "bigint";
 		case jpiInteger:
@@ -988,6 +1006,7 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
 		case jpiGreater:
 		case jpiLessOrEqual:
 		case jpiGreaterOrEqual:
+		case jpiDecimal:
 		case jpiStartsWith:
 			read_int32(v->content.args.left, base, pos);
 			read_int32(v->content.args.right, base, pos);
@@ -1086,6 +1105,7 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
 			   v->type == jpiFloor ||
 			   v->type == jpiCeiling ||
 			   v->type == jpiDouble ||
+			   v->type == jpiDecimal ||
 			   v->type == jpiBigint ||
 			   v->type == jpiInteger ||
 			   v->type == jpiNumber ||
@@ -1123,6 +1143,7 @@ jspGetLeftArg(JsonPathItem *v, JsonPathItem *a)
 		   v->type == jpiMul ||
 		   v->type == jpiDiv ||
 		   v->type == jpiMod ||
+		   v->type == jpiDecimal ||
 		   v->type == jpiStartsWith);
 
 	jspInitByBuffer(a, v->base, v->content.args.left);
@@ -1144,6 +1165,7 @@ jspGetRightArg(JsonPathItem *v, JsonPathItem *a)
 		   v->type == jpiMul ||
 		   v->type == jpiDiv ||
 		   v->type == jpiMod ||
+		   v->type == jpiDecimal ||
 		   v->type == jpiStartsWith);
 
 	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 f52663b..94f1052 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1371,9 +1371,11 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 			break;
 
 		case jpiNumber:
+		case jpiDecimal:
 			{
 				JsonbValue	jbv;
 				Numeric		num;
+				char	   *numstr = NULL;
 
 				if (unwrap && JsonbType(jb) == jbvArray)
 					return executeItemUnwrapTargetArray(cxt, jsp, jb, found,
@@ -1385,9 +1387,12 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 					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",
+											  errmsg("numeric argument of jsonpath item method .%s() is not a valid representation of a decimal or number",
 													 jspOperationName(jsp->type)))));
 
+					if (jsp->type == jpiDecimal)
+						numstr = DatumGetCString(DirectFunctionCall1(numeric_out,
+																	 NumericGetDatum(num)));
 					res = jperOk;
 				}
 				else if (jb->type == jbvString)
@@ -1395,11 +1400,11 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 					/* cast string as number */
 					Datum		datum;
 					bool		noerr;
-					char	   *tmp = pnstrdup(jb->val.string.val,
-											   jb->val.string.len);
+					char	   *numstr = pnstrdup(jb->val.string.val,
+												  jb->val.string.len);
 					ErrorSaveContext escontext = {T_ErrorSaveContext};
 
-					noerr = DirectInputFunctionCallSafe(numeric_in, tmp,
+					noerr = DirectInputFunctionCallSafe(numeric_in, numstr,
 														InvalidOid, -1,
 														(Node *) &escontext,
 														&datum);
@@ -1407,14 +1412,14 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 					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",
+											  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 number",
+											  errmsg("string argument of jsonpath item method .%s() is not a valid representation of a decimal or number",
 													 jspOperationName(jsp->type)))));
 
 					res = jperOk;
@@ -1426,6 +1431,82 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 										  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)
+				{
+					JsonPathItem elem;
+					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_INVALID_SQL_JSON_SUBSCRIPT),
+											  errmsg("precision is out of integer range"))));
+
+					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_INVALID_SQL_JSON_SUBSCRIPT),
+												  errmsg("scale is out of integer range"))));
+					}
+
+					/*
+					 * 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;
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 8c03f9b..da031e3 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -83,16 +83,16 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
 %token	<str>		ABS_P SIZE_P TYPE_P FLOOR_P DOUBLE_P CEILING_P KEYVALUE_P
 %token	<str>		DATETIME_P
 %token	<str>		DATE_P TIME_P TIME_TZ_P TIMESTAMP_P TIMESTAMP_TZ_P
-%token	<str>		BOOLEAN_P STRINGFUNC_P BIGINT_P INTEGER_P NUMBER_P
+%token	<str>		BOOLEAN_P STRINGFUNC_P BIGINT_P INTEGER_P NUMBER_P DECIMAL_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
 
-%type	<elems>		accessor_expr
+%type	<elems>		accessor_expr csv_list opt_csv_list
 
 %type	<indexs>	index_list
 
@@ -250,11 +250,44 @@ accessor_op:
 	| array_accessor				{ $$ = $1; }
 	| '.' any_path					{ $$ = $2; }
 	| '.' method '(' ')'			{ $$ = makeItemType($2); }
+	| '.' 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); }
 	| '?' '(' predicate ')'			{ $$ = makeItemUnary(jpiFilter, $3); }
 	;
 
+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_template:
 	STRING_P						{ $$ = makeItemString(&$1); }
 	;
@@ -287,6 +320,7 @@ key_name:
 	| STRINGFUNC_P
 	| FLOOR_P
 	| DOUBLE_P
+	| DECIMAL_P
 	| BIGINT_P
 	| INTEGER_P
 	| NUMBER_P
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index 1e4fabc..d87da14 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -421,6 +421,7 @@ static const JsonPathKeyword keywords[] = {
 	{ 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"},
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 5af6a44..7058563 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -91,6 +91,7 @@ typedef enum JsonPathItemType
 	jpiFloor,					/* .floor() item method */
 	jpiCeiling,					/* .ceiling() item method */
 	jpiDouble,					/* .double() item method */
+	jpiDecimal,					/* .decimal() item method */
 	jpiBigint,					/* .bigint() item method */
 	jpiInteger,					/* .integer() item method */
 	jpiNumber,					/* .number() item method */
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index cc81787..4185d2a 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2036,7 +2036,7 @@ select jsonb_path_query('"1.23"', '$.number()');
 (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
+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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@@ -2044,13 +2044,13 @@ select jsonb_path_query('1e1000', '$.number()');
 (1 row)
 
 select jsonb_path_query('"nan"', '$.number()');
-ERROR:  string argument of jsonpath item method .number() is not a valid representation of a 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 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 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 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 
 ------------------
@@ -2109,6 +2109,187 @@ select jsonb_path_query('12.3', '$.number() * 2');
  24.6
 (1 row)
 
+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('{}', '$.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 260cba9..c5416c1 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -464,6 +464,49 @@ 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('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('{}', '$.abs()');
 select jsonb_path_query('true', '$.floor()');
 select jsonb_path_query('"1.2"', '$.ceiling()');
-- 
1.8.3.1