v10-0001-optimize-casting-jsonb-to-a-given-type.patch

From c4b1ae13a0f4ba28972835ffa4c9850e2e0dbda6 Mon Sep 17 00:00:00 2001
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Wed, 16 Aug 2023 14:04:27 +0800
Subject: [PATCH v10 1/3] optimize casting jsonb to a given type.

Previously after we get a JsonbValue, we need to convert it to
Jsonb first then cast the Jsonb to the given type. In this patch,
we covert the JsonbValue to the desired type directly.
---
 src/backend/nodes/makefuncs.c       |  30 +++++
 src/backend/utils/adt/jsonb.c       | 177 +++++++++++++++++++++++++++
 src/backend/utils/adt/jsonfuncs.c   | 115 +++++++++++-------
 src/include/catalog/catversion.h    |   2 +-
 src/include/catalog/pg_proc.dat     |  32 +++--
 src/include/nodes/makefuncs.h       |   2 +
 src/include/utils/jsonb.h           |   1 +
 src/test/regress/expected/jsonb.out | 178 +++++++++++++++++-----------
 src/test/regress/sql/jsonb.sql      |  56 ++++++---
 9 files changed, 462 insertions(+), 131 deletions(-)

diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 0e7e6e46d94..9cb9178f01a 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -20,6 +20,7 @@
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "utils/errcodes.h"
+#include "utils/fmgrprotos.h"
 #include "utils/lsyscache.h"
 
 
@@ -352,6 +353,35 @@ makeNullConst(Oid consttype, int32 consttypmod, Oid constcollid)
 					 typByVal);
 }
 
+/*
+ * makeDummyConst
+ *	 create a Const node with the specified type/typmod.
+ *
+ * This is a convenience routine to create a Const which only the
+ * type is interesting but make sure the value is accessible.
+ */
+Const *
+makeDummyConst(Oid consttype, int32 consttypmod, Oid constcollid)
+{
+	int16		typLen;
+	bool		typByVal;
+	Const		*c;
+	Datum		val = 0;
+
+
+	get_typlenbyval(consttype, &typLen, &typByVal);
+
+	if (consttype == NUMERICOID)
+		val = DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
+	else if (!typByVal)
+		elog(ERROR, "create dummy const for type %u is not supported.", consttype);
+
+	/* XXX: here I assume constvalue=0 is accessible for constbyval.*/
+	c = makeConst(consttype, consttypmod, 0, (int) typLen, val, false, typByVal);
+
+	return c;
+}
+
 /*
  * makeBoolConst -
  *	  creates a Const node representing a boolean value (can be NULL too)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..148c1e2e195 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,11 +17,14 @@
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
+#include "nodes/makefuncs.h"
+#include "nodes/supportnodes.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
 #include "utils/builtins.h"
 #include "utils/date.h"
 #include "utils/datetime.h"
+#include "utils/fmgroids.h"
 #include "utils/json.h"
 #include "utils/jsonb.h"
 #include "utils/jsonfuncs.h"
@@ -2038,6 +2041,180 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
 	elog(ERROR, "unknown jsonb type: %d", (int) type);
 }
 
+static bool
+jsonb_cast_is_optimized(Oid target_type)
+{
+	switch(target_type)
+	{
+		case NUMERICOID:
+		case BOOLOID:
+		case INT2OID:
+		case INT4OID:
+		case INT8OID:
+		case FLOAT4OID:
+		case FLOAT8OID:
+			return true;
+		default:
+			return false;
+	}
+}
+
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+	Node	   *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+	if (IsA(rawreq, SupportRequestSimplify))
+	{
+		SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+		FuncExpr	*fexpr = palloc(sizeof(FuncExpr));
+		OpExpr		*opexpr;
+		Oid			new_func_id = InvalidOid;
+
+		memcpy(fexpr, req->fcall, sizeof(FuncExpr));
+
+		opexpr = (OpExpr *) linitial(fexpr->args);
+
+		if (!IsA(opexpr, OpExpr) ||
+			!jsonb_cast_is_optimized(fexpr->funcresulttype))
+		{
+			/* not the desired pattern. */
+			PG_RETURN_POINTER(fexpr);
+		}
+
+		if (opexpr->opfuncid  == F_JSONB_OBJECT_FIELD)
+			new_func_id = F_JSONB_OBJECT_FIELD_TYPE;
+		else if (opexpr->opfuncid == F_JSONB_ARRAY_ELEMENT)
+			new_func_id = F_JSONB_ARRAY_ELEMENT_TYPE;
+		else if (opexpr->opfuncid == F_JSONB_EXTRACT_PATH)
+			new_func_id = F_JSONB_EXTRACT_PATH_TYPE;
+
+		if (OidIsValid(new_func_id))
+		{
+			Const	*target =  makeDummyConst(fexpr->funcresulttype, 0, InvalidOid);
+			fexpr->funcid = new_func_id;
+			fexpr->args = opexpr->args;
+			fexpr->args = list_insert_nth(fexpr->args, 0, target);
+		}
+
+		PG_RETURN_POINTER(fexpr);
+	}
+
+	PG_RETURN_POINTER(NULL);
+}
+
+Datum
+cast_jsonbvalue_to_type(JsonbValue *v, Oid targetOid)
+{
+	switch(targetOid)
+	{
+		Datum	retValue;
+
+		case BOOLOID:
+			if (v->type != jbvBool)
+				cannotCastJsonbValue(v->type, "bool");
+			PG_RETURN_BOOL(v->val.boolean);
+
+		case NUMERICOID:
+			if (v->type != jbvNumeric)
+				cannotCastJsonbValue(v->type, "numeric");
+			PG_RETURN_NUMERIC(v->val.numeric);
+		case INT2OID:
+			if (v->type != jbvNumeric)
+				cannotCastJsonbValue(v->type, "smallint");
+			retValue = DirectFunctionCall1(numeric_int2,
+										   NumericGetDatum(v->val.numeric));
+			PG_RETURN_DATUM(retValue);
+		case INT4OID:
+			if (v->type != jbvNumeric)
+				cannotCastJsonbValue(v->type, "integer");
+			retValue = DirectFunctionCall1(numeric_int4,
+										   NumericGetDatum(v->val.numeric));
+			PG_RETURN_DATUM(retValue);
+
+		case INT8OID:
+			if (v->type != jbvNumeric)
+				cannotCastJsonbValue(v->type, "bigint");
+			retValue = DirectFunctionCall1(numeric_int8,
+										   NumericGetDatum(v->val.numeric));
+			PG_RETURN_DATUM(retValue);
+
+		case FLOAT4OID:
+			if (v->type != jbvNumeric)
+				cannotCastJsonbValue(v->type, "real");
+			retValue = DirectFunctionCall1(numeric_float4,
+										   NumericGetDatum(v->val.numeric));
+			PG_RETURN_DATUM(retValue);
+
+		case FLOAT8OID:
+			if (v->type != jbvNumeric)
+				cannotCastJsonbValue(v->type, "double precision");
+			retValue = DirectFunctionCall1(numeric_float8,
+										   NumericGetDatum(v->val.numeric));
+			PG_RETURN_DATUM(retValue);
+
+		default:
+			elog(ERROR, "cast jsonb to type %u is not allowed", targetOid);
+			break;
+	}
+
+	PG_RETURN_POINTER(NULL);
+}
+
+Datum
+jsonb_object_field_type(PG_FUNCTION_ARGS)
+{
+	Oid			targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+	Jsonb	   *jb = PG_GETARG_JSONB_P(1);
+	text	   *key = PG_GETARG_TEXT_PP(2);
+
+	JsonbValue *v;
+	JsonbValue	vbuf;
+
+	if (!JB_ROOT_IS_OBJECT(jb))
+		PG_RETURN_NULL();
+
+	v = getKeyJsonValueFromContainer(&jb->root,
+									 VARDATA_ANY(key),
+									 VARSIZE_ANY_EXHDR(key),
+									 &vbuf);
+
+	if (v == NULL)
+		PG_RETURN_NULL();
+
+	return cast_jsonbvalue_to_type(v, targetOid);
+}
+
+Datum
+jsonb_array_element_type(PG_FUNCTION_ARGS)
+{
+	Oid			targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+	Jsonb	   *jb = PG_GETARG_JSONB_P(1);
+	int			element = PG_GETARG_INT32(2);
+
+	JsonbValue *v;
+
+	if (!JB_ROOT_IS_ARRAY(jb))
+		PG_RETURN_NULL();
+
+	/* Handle negative subscript */
+	if (element < 0)
+	{
+		uint32		nelements = JB_ROOT_COUNT(jb);
+
+		if (-element > nelements)
+			PG_RETURN_NULL();
+		else
+			element += nelements;
+	}
+
+	v = getIthJsonbValueFromContainer(&jb->root, element);
+	if (v == NULL)
+		PG_RETURN_NULL();
+
+	return cast_jsonbvalue_to_type(v, targetOid);
+}
+
 Datum
 jsonb_bool(PG_FUNCTION_ARGS)
 {
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..bb4ca807d74 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -492,6 +492,7 @@ static JsonParseErrorType transform_string_values_object_field_start(void *state
 static JsonParseErrorType transform_string_values_array_element_start(void *state, bool isnull);
 static JsonParseErrorType transform_string_values_scalar(void *state, char *token, JsonTokenType tokentype);
 
+static JsonbValue *jsonb_get_jsonbvalue(Jsonb *jb, Datum *path, int npath, bool *isnull);
 
 /*
  * pg_parse_json_or_errsave
@@ -1473,6 +1474,40 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
 	return JSON_SUCCESS;
 }
 
+Datum
+jsonb_extract_path_type(PG_FUNCTION_ARGS)
+{
+	Oid			targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+	Jsonb	   *jb = PG_GETARG_JSONB_P(1);
+	ArrayType  *path = PG_GETARG_ARRAYTYPE_P(2);
+
+	JsonbValue *v;
+
+	Datum	   *pathtext;
+	bool	   *pathnulls;
+	bool		isnull = false;
+	int			npath;
+
+	/*
+	 * If the array contains any null elements, return NULL, on the grounds
+	 * that you'd have gotten NULL if any RHS value were NULL in a nested
+	 * series of applications of the -> operator.  (Note: because we also
+	 * return NULL for error cases such as no-such-field, this is true
+	 * regardless of the contents of the rest of the array.)
+	 */
+	if (array_contains_nulls(path))
+		PG_RETURN_NULL();
+
+	deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath);
+
+	v = jsonb_get_jsonbvalue(jb, pathtext, npath, &isnull);
+
+	if (isnull)
+		PG_RETURN_NULL();
+
+	return cast_jsonbvalue_to_type(v, targetOid);
+}
+
 Datum
 jsonb_extract_path(PG_FUNCTION_ARGS)
 {
@@ -1516,52 +1551,36 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
 		PG_RETURN_DATUM(res);
 }
 
-Datum
-jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+
+static JsonbValue *
+jsonb_get_jsonbvalue(Jsonb *jb, Datum *path, int npath, bool *isnull)
 {
+	bool have_object = false, have_array = false;
 	JsonbContainer *container = &jb->root;
+	int i;
 	JsonbValue *jbvp = NULL;
-	int			i;
-	bool		have_object = false,
-				have_array = false;
 
-	*isnull = false;
+	/*
+	 * If the array is empty, return the entire LHS object, on the grounds
+	 * that we should do zero field or element extractions.
+	 */
+	if (npath <= 0)
+	{
+		JsonbValue *res = NULL;
+		if (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb))
+			return getIthJsonbValueFromContainer(container, 0);
+
+		/* NB: res is a jbvBinary JsonbValue */
+		res = palloc0(sizeof(JsonbValue));
+		JsonbToJsonbValue(jb, res);
+		return res;
+	}
 
 	/* Identify whether we have object, array, or scalar at top-level */
 	if (JB_ROOT_IS_OBJECT(jb))
 		have_object = true;
 	else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
 		have_array = true;
-	else
-	{
-		Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb));
-		/* Extract the scalar value, if it is what we'll return */
-		if (npath <= 0)
-			jbvp = getIthJsonbValueFromContainer(container, 0);
-	}
-
-	/*
-	 * If the array is empty, return the entire LHS object, on the grounds
-	 * that we should do zero field or element extractions.  For the
-	 * non-scalar case we can just hand back the object without much work. For
-	 * the scalar case, fall through and deal with the value below the loop.
-	 * (This inconsistency arises because there's no easy way to generate a
-	 * JsonbValue directly for root-level containers.)
-	 */
-	if (npath <= 0 && jbvp == NULL)
-	{
-		if (as_text)
-		{
-			return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
-																  container,
-																  VARSIZE(jb))));
-		}
-		else
-		{
-			/* not text mode - just hand back the jsonb */
-			PG_RETURN_JSONB_P(jb);
-		}
-	}
 
 	for (i = 0; i < npath; i++)
 	{
@@ -1586,7 +1605,7 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 			if (endptr == indextext || *endptr != '\0' || errno != 0)
 			{
 				*isnull = true;
-				return PointerGetDatum(NULL);
+				return NULL;
 			}
 
 			if (lindex >= 0)
@@ -1607,7 +1626,7 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 				if (lindex == INT_MIN || -lindex > nelements)
 				{
 					*isnull = true;
-					return PointerGetDatum(NULL);
+					return NULL;
 				}
 				else
 					index = nelements + lindex;
@@ -1619,13 +1638,13 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 		{
 			/* scalar, extraction yields a null */
 			*isnull = true;
-			return PointerGetDatum(NULL);
+			return NULL;
 		}
 
 		if (jbvp == NULL)
 		{
 			*isnull = true;
-			return PointerGetDatum(NULL);
+			return NULL;
 		}
 		else if (i == npath - 1)
 			break;
@@ -1644,6 +1663,22 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 			have_array = false;
 		}
 	}
+	return jbvp;
+}
+
+/*
+ * Return jsonb datum or jsonb-as-text datum.
+ */
+Datum
+jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+{
+	JsonbValue *jbvp = NULL;
+	*isnull = false;
+
+	jbvp = jsonb_get_jsonbvalue(jb, path, npath, isnull);
+
+	if (*isnull)
+		return PointerGetDatum(NULL);
 
 	if (as_text)
 	{
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index f507b49bb28..8a896f9aad2 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202307261
+#define CATALOG_VERSION_NO	202308171
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989a..b6844537529 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4575,25 +4575,26 @@
   proname => 'pg_lsn', prorettype => 'pg_lsn', proargtypes => 'numeric',
   prosrc => 'numeric_pg_lsn' },
 
-{ oid => '3556', descr => 'convert jsonb to boolean',
+{ oid => '3556', descr => 'convert jsonb to boolean', prosupport => 'jsonb_cast_support',
   proname => 'bool', prorettype => 'bool', proargtypes => 'jsonb',
   prosrc => 'jsonb_bool' },
 { oid => '3449', descr => 'convert jsonb to numeric',
-  proname => 'numeric', prorettype => 'numeric', proargtypes => 'jsonb',
+  proname => 'numeric', prosupport => 'jsonb_cast_support',
+  prorettype => 'numeric', proargtypes => 'jsonb',
   prosrc => 'jsonb_numeric' },
-{ oid => '3450', descr => 'convert jsonb to int2',
+{ oid => '3450', descr => 'convert jsonb to int2', prosupport => 'jsonb_cast_support',
   proname => 'int2', prorettype => 'int2', proargtypes => 'jsonb',
   prosrc => 'jsonb_int2' },
-{ oid => '3451', descr => 'convert jsonb to int4',
+{ oid => '3451', descr => 'convert jsonb to int4', prosupport => 'jsonb_cast_support',
   proname => 'int4', prorettype => 'int4', proargtypes => 'jsonb',
   prosrc => 'jsonb_int4' },
-{ oid => '3452', descr => 'convert jsonb to int8',
+{ oid => '3452', descr => 'convert jsonb to int8', prosupport => 'jsonb_cast_support',
   proname => 'int8', prorettype => 'int8', proargtypes => 'jsonb',
   prosrc => 'jsonb_int8' },
-{ oid => '3453', descr => 'convert jsonb to float4',
+{ oid => '3453', descr => 'convert jsonb to float4', prosupport => 'jsonb_cast_support',
   proname => 'float4', prorettype => 'float4', proargtypes => 'jsonb',
   prosrc => 'jsonb_float4' },
-{ oid => '2580', descr => 'convert jsonb to float8',
+{ oid => '2580', descr => 'convert jsonb to float8', prosupport => 'jsonb_cast_support',
   proname => 'float8', prorettype => 'float8', proargtypes => 'jsonb',
   prosrc => 'jsonb_float8' },
 
@@ -9928,6 +9929,13 @@
   proname => 'jsonb_object_field_text', prorettype => 'text',
   proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
   prosrc => 'jsonb_object_field_text' },
+{ oid => '3813', descr => 'return a given type specified in desired_type from jsonb field',
+  proname => 'jsonb_object_field_type', prorettype => 'anyelement',
+  proargtypes => 'anyelement jsonb text', proargnames => '{target_type, from_json, field_name}',
+  prosrc => 'jsonb_object_field_type'},
+{ oid => '3814', descr => 'planner support for numeric(jsonb)',
+  proname => 'jsonb_cast_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'jsonb_cast_support' },
 { oid => '3215',
   proname => 'jsonb_array_element', prorettype => 'jsonb',
   proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
@@ -9936,6 +9944,10 @@
   proname => 'jsonb_array_element_text', prorettype => 'text',
   proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
   prosrc => 'jsonb_array_element_text' },
+{ oid => '4549', descr => 'cast an array element to given type',
+  proname => 'jsonb_array_element_type', prorettype => 'anyelement',
+  proargtypes => 'anyelement jsonb int4', proargnames => '{target_type, from_json, element_index}',
+  prosrc => 'jsonb_array_element_type' },
 { oid => '3217', descr => 'get value from jsonb with path elements',
   proname => 'jsonb_extract_path', provariadic => 'text', prorettype => 'jsonb',
   proargtypes => 'jsonb _text', proallargtypes => '{jsonb,_text}',
@@ -9947,6 +9959,12 @@
   proallargtypes => '{jsonb,_text}', proargmodes => '{i,v}',
   proargnames => '{from_json,path_elems}',
   prosrc => 'jsonb_extract_path_text' },
+{ oid => '4551', descr => 'cast value from jsonb as text with path elements to given type',
+  proname => 'jsonb_extract_path_type', provariadic => 'text',
+  prorettype => 'anyelement', proargtypes => 'anyelement jsonb _text',
+  proallargtypes => '{anyelement,jsonb,_text}', proargmodes => '{i,i,v}',
+  proargnames => '{target_type,from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_type' },
 { oid => '3219', descr => 'elements of a jsonb array',
   proname => 'jsonb_array_elements', prorows => '100', proretset => 't',
   prorettype => 'jsonb', proargtypes => 'jsonb',
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 31807030055..cfbe5b26196 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -58,6 +58,8 @@ extern Const *makeConst(Oid consttype,
 
 extern Const *makeNullConst(Oid consttype, int32 consttypmod, Oid constcollid);
 
+extern Const *makeDummyConst(Oid consttype, int32 consttypmod, Oid constcollid);
+
 extern Node *makeBoolConst(bool value, bool isnull);
 
 extern Expr *makeBoolExpr(BoolExprType boolop, List *args, int location);
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 649a1644f24..532225314a9 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -435,5 +435,6 @@ extern Datum jsonb_build_object_worker(int nargs, Datum *args, bool *nulls,
 									   bool unique_keys);
 extern Datum jsonb_build_array_worker(int nargs, Datum *args, bool *nulls,
 									  Oid *types, bool absent_on_null);
+extern Datum cast_jsonbvalue_to_type(JsonbValue *v, Oid target_oid);
 
 #endif							/* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4a16d0dbafb..12daacb3b80 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,6 +457,7 @@ CREATE TEMP TABLE test_jsonb (
 );
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
+('scalarint','2'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
 ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
@@ -501,10 +502,25 @@ SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object';
  val2
 (1 row)
 
-SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar';
+SELECT test_json -> 2, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalar';
+ ?column? |  ?column?  
+----------+------------
+          | "a scalar"
+(1 row)
+
+explain (verbose, costs off)
+SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_array_element_type(0, test_json, 0), (test_json -> 0)
+   Filter: (test_jsonb.json_type = 'scalarint'::text)
+(3 rows)
+
+SELECT test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
  ?column? 
 ----------
- 
+ 2
 (1 row)
 
 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array';
@@ -1786,6 +1802,12 @@ select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
  {"a": {"b": {"c": "foo"}}}
 (1 row)
 
+select ('2'::jsonb #> '{}')::int2, ('{"a":2}'::jsonb #> '{"b"}'), ('{"a":2}'::jsonb #> '{"b"}')::int2;
+ int2 | ?column? | int2 
+------+----------+------
+    2 |          |     
+(1 row)
+
 select '[1,2,3]'::jsonb #> '{}';
  ?column?  
 -----------
@@ -3537,6 +3559,24 @@ SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
 
 RESET enable_seqscan;
 DROP INDEX jidx;
+-- test the supported function for jsonb cast.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (j->'a')::numeric,
+(j->'a')::int2,
+(j->'a')::int4,
+(j->'a')::int8,
+(j->'a')::float4,
+(j->'a')::float8,
+(j->'a')::bool,
+(j #> '{"a"}')::numeric,
+(j->0)::numeric
+FROM testjsonb;
+                                                                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                                                                             
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on public.testjsonb
+   Output: jsonb_object_field_type('0'::numeric, j, 'a'::text), jsonb_object_field_type('0'::smallint, j, 'a'::text), jsonb_object_field_type(0, j, 'a'::text), jsonb_object_field_type('0'::bigint, j, 'a'::text), jsonb_object_field_type('0'::real, j, 'a'::text), jsonb_object_field_type('0'::double precision, j, 'a'::text), jsonb_object_field_type(false, j, 'a'::text), pg_catalog.jsonb_extract_path_type('0'::numeric, j, '{a}'::text[]), jsonb_array_element_type('0'::numeric, j, 0)
+(2 rows)
+
 -- nested tests
 SELECT '{"ff":{"a":12,"b":16}}'::jsonb;
            jsonb            
@@ -5471,107 +5511,113 @@ select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
 (1 row)
 
 -- casts
-select 'true'::jsonb::bool;
- bool 
-------
- t
+select 'true'::jsonb::bool, ('{"a": true}'::jsonb->'a')::bool;
+ bool | bool 
+------+------
+ t    | t
 (1 row)
 
 select '[]'::jsonb::bool;
 ERROR:  cannot cast jsonb array to type boolean
-select '1.0'::jsonb::float;
- float8 
---------
-      1
+select ('{"a": []}'::jsonb->'a')::bool;
+ERROR:  cannot cast jsonb array to type boolean
+select '1.0'::jsonb::float, ('{"a": 1.0}'::jsonb->'a')::float;
+ float8 | float8 
+--------+--------
+      1 |      1
 (1 row)
 
 select '[1.0]'::jsonb::float;
 ERROR:  cannot cast jsonb array to type double precision
-select '12345'::jsonb::int4;
- int4  
--------
- 12345
+select ('{"a": [1.0]}'::jsonb->'a')::float;
+ERROR:  cannot cast jsonb array to type double precision
+select '12345'::jsonb::int4,  ('{"a": 12345}'::jsonb->'a')::int4;
+ int4  | int4  
+-------+-------
+ 12345 | 12345
 (1 row)
 
 select '"hello"'::jsonb::int4;
 ERROR:  cannot cast jsonb string to type integer
-select '12345'::jsonb::numeric;
- numeric 
----------
-   12345
+select ('{"a": "hello"}'::jsonb->'a')::int4;
+ERROR:  cannot cast jsonb string to type integer
+select '12345'::jsonb::numeric, ('{"a": 12345}'::jsonb->'a')::numeric;
+ numeric | numeric 
+---------+---------
+   12345 |   12345
 (1 row)
 
 select '{}'::jsonb::numeric;
 ERROR:  cannot cast jsonb object to type numeric
-select '12345.05'::jsonb::numeric;
- numeric  
-----------
- 12345.05
+select '12345.05'::jsonb::numeric, ('{"a": 12345.05}'::jsonb->'a')::numeric;
+ numeric  | numeric  
+----------+----------
+ 12345.05 | 12345.05
 (1 row)
 
-select '12345.05'::jsonb::float4;
-  float4  
-----------
- 12345.05
+select '12345.05'::jsonb::float4, ('{"a": 12345.05}'::jsonb->'a')::float4;
+  float4  |  float4  
+----------+----------
+ 12345.05 | 12345.05
 (1 row)
 
-select '12345.05'::jsonb::float8;
-  float8  
-----------
- 12345.05
+select '12345.05'::jsonb::float8, ('{"a": 12345.05}'::jsonb->'a')::float8;
+  float8  |  float8  
+----------+----------
+ 12345.05 | 12345.05
 (1 row)
 
-select '12345.05'::jsonb::int2;
- int2  
--------
- 12345
+select '12345.05'::jsonb::int2, ('{"a": 12345.05}'::jsonb->'a')::int2;
+ int2  | int2  
+-------+-------
+ 12345 | 12345
 (1 row)
 
-select '12345.05'::jsonb::int4;
- int4  
--------
- 12345
+select '12345.05'::jsonb::int4, ('{"a": 12345.05}'::jsonb->'a')::int4;
+ int4  | int4  
+-------+-------
+ 12345 | 12345
 (1 row)
 
-select '12345.05'::jsonb::int8;
- int8  
--------
- 12345
+select '12345.05'::jsonb::int8, ('{"a": 12345.05}'::jsonb->'a')::int8;
+ int8  | int8  
+-------+-------
+ 12345 | 12345
 (1 row)
 
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
-                       numeric                        
-------------------------------------------------------
- 12345.0000000000000000000000000000000000000000000005
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::numeric;
+                       numeric                        |                       numeric                        
+------------------------------------------------------+------------------------------------------------------
+ 12345.0000000000000000000000000000000000000000000005 | 12345.0000000000000000000000000000000000000000000005
 (1 row)
 
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
- float4 
---------
-  12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4,  ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float4;
+ float4 | float4 
+--------+--------
+  12345 |  12345
 (1 row)
 
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
- float8 
---------
-  12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float8;
+ float8 | float8 
+--------+--------
+  12345 |  12345
 (1 row)
 
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
- int2  
--------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int2;
+ int2  | int2  
+-------+-------
+ 12345 | 12345
 (1 row)
 
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
- int4  
--------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int4;
+ int4  | int4  
+-------+-------
+ 12345 | 12345
 (1 row)
 
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
- int8  
--------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int8;
+ int8  | int8  
+-------+-------
+ 12345 | 12345
 (1 row)
 
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index e4b7cdf703d..8634d154efe 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,6 +154,7 @@ CREATE TEMP TABLE test_jsonb (
 
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
+('scalarint','2'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
 ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
 
@@ -166,7 +167,10 @@ SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array';
 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object';
 
-SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar';
+SELECT test_json -> 2, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalar';
+explain (verbose, costs off)
+SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
+SELECT test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array';
 SELECT test_json -> 9 FROM test_jsonb WHERE json_type = 'array';
 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object';
@@ -491,6 +495,7 @@ SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1'];
 
 -- corner cases for same
 select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
+select ('2'::jsonb #> '{}')::int2, ('{"a":2}'::jsonb #> '{"b"}'), ('{"a":2}'::jsonb #> '{"b"}')::int2;
 select '[1,2,3]'::jsonb #> '{}';
 select '"foo"'::jsonb #> '{}';
 select '42'::jsonb #> '{}';
@@ -939,6 +944,19 @@ SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
 RESET enable_seqscan;
 DROP INDEX jidx;
 
+-- test the supported function for jsonb cast.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (j->'a')::numeric,
+(j->'a')::int2,
+(j->'a')::int4,
+(j->'a')::int8,
+(j->'a')::float4,
+(j->'a')::float8,
+(j->'a')::bool,
+(j #> '{"a"}')::numeric,
+(j->0)::numeric
+FROM testjsonb;
+
 -- nested tests
 SELECT '{"ff":{"a":12,"b":16}}'::jsonb;
 SELECT '{"ff":{"a":12,"b":16},"qq":123}'::jsonb;
@@ -1496,23 +1514,27 @@ select ts_headline('{}'::jsonb, tsquery('aaa & bbb'));
 select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
 
 -- casts
-select 'true'::jsonb::bool;
+select 'true'::jsonb::bool, ('{"a": true}'::jsonb->'a')::bool;
 select '[]'::jsonb::bool;
-select '1.0'::jsonb::float;
+select ('{"a": []}'::jsonb->'a')::bool;
+select '1.0'::jsonb::float, ('{"a": 1.0}'::jsonb->'a')::float;
 select '[1.0]'::jsonb::float;
-select '12345'::jsonb::int4;
+select ('{"a": [1.0]}'::jsonb->'a')::float;
+select '12345'::jsonb::int4,  ('{"a": 12345}'::jsonb->'a')::int4;
 select '"hello"'::jsonb::int4;
-select '12345'::jsonb::numeric;
+select ('{"a": "hello"}'::jsonb->'a')::int4;
+
+select '12345'::jsonb::numeric, ('{"a": 12345}'::jsonb->'a')::numeric;
 select '{}'::jsonb::numeric;
-select '12345.05'::jsonb::numeric;
-select '12345.05'::jsonb::float4;
-select '12345.05'::jsonb::float8;
-select '12345.05'::jsonb::int2;
-select '12345.05'::jsonb::int4;
-select '12345.05'::jsonb::int8;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+select '12345.05'::jsonb::numeric, ('{"a": 12345.05}'::jsonb->'a')::numeric;
+select '12345.05'::jsonb::float4, ('{"a": 12345.05}'::jsonb->'a')::float4;
+select '12345.05'::jsonb::float8, ('{"a": 12345.05}'::jsonb->'a')::float8;
+select '12345.05'::jsonb::int2, ('{"a": 12345.05}'::jsonb->'a')::int2;
+select '12345.05'::jsonb::int4, ('{"a": 12345.05}'::jsonb->'a')::int4;
+select '12345.05'::jsonb::int8, ('{"a": 12345.05}'::jsonb->'a')::int8;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::numeric;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4,  ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float4;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float8;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int2;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int4;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int8;
-- 
2.21.0