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

From 33c9395574cfa0e8040e56661aabec40d5a8aa6b Mon Sep 17 00:00:00 2001
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Thu, 31 Aug 2023 16:48:35 +0800
Subject: [PATCH v11] 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/utils/adt/jsonb.c       | 169 ++++++++++++++++++++++++++++
 src/backend/utils/adt/jsonfuncs.c   | 114 ++++++++++++-------
 src/include/catalog/catversion.h    |   3 +-
 src/include/catalog/pg_proc.dat     |  38 +++++--
 src/test/regress/expected/jsonb.out |  78 ++++++++++++-
 src/test/regress/sql/jsonb.sql      |  48 +++++++-
 6 files changed, 398 insertions(+), 52 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..449cb4a0523 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,11 +17,15 @@
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
+#include "nodes/makefuncs.h"
+#include "nodes/supportnodes.h"
+#include "parser/parse_coerce.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 +2042,171 @@ 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 = palloc0(sizeof(FuncExpr));
+		FuncExpr	*jsonb_start_func = NULL, *jsonb_finish_func = NULL, *final_func = NULL;
+		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_START;
+		else if (opexpr->opfuncid == F_JSONB_ARRAY_ELEMENT)
+			new_func_id = F_JSONB_ARRAY_ELEMENT_START;
+		else if (opexpr->opfuncid == F_JSONB_EXTRACT_PATH)
+			new_func_id = F_JSONB_EXTRACT_PATH_START;
+
+		if (!OidIsValid(new_func_id))
+			PG_RETURN_POINTER(fexpr);
+
+		jsonb_start_func = makeFuncExpr(new_func_id, INTERNALOID, opexpr->args,
+										opexpr->opcollid, opexpr->inputcollid,
+										COERCE_EXPLICIT_CALL);
+
+		/* relabel the first arguments as 'internal'. */
+		linitial(jsonb_start_func->args) = makeRelabelType(linitial(jsonb_start_func->args),
+														   INTERNALOID, 0,
+														   InvalidOid,
+														   COERCE_IMPLICIT_CAST);
+		switch (fexpr->funcresulttype)
+		{
+			case INT2OID:
+			case INT4OID:
+			case INT8OID:
+			case FLOAT4OID:
+			case FLOAT8OID:
+			case NUMERICOID:
+				jsonb_finish_func = makeFuncExpr(F_JSONB_FINISH_NUMERIC, NUMERICOID,
+												 list_make1(jsonb_start_func), opexpr->opcollid,
+												 opexpr->inputcollid, COERCE_EXPLICIT_CALL);
+
+				if (fexpr->funcresulttype != NUMERICOID)
+				{
+					final_func = (FuncExpr *)coerce_type(NULL, (Node *)jsonb_finish_func, NUMERICOID,
+														 fexpr->funcresulttype, 0, COERCION_EXPLICIT,
+														 COERCE_EXPLICIT_CAST, fexpr->location);
+				}
+				else
+					final_func = jsonb_finish_func;
+
+				PG_RETURN_POINTER(final_func);
+			case BOOLOID:
+				final_func = makeFuncExpr(F_JSONB_FINISH_BOOL, BOOLOID,
+											   list_make1(jsonb_start_func), opexpr->opcollid,
+										  opexpr->inputcollid, COERCE_EXPLICIT_CALL);
+				PG_RETURN_POINTER(final_func);
+			default:
+				PG_RETURN_POINTER(fexpr);
+		}
+	}
+
+	PG_RETURN_POINTER(NULL);
+}
+
+
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
+	text	   *key = PG_GETARG_TEXT_PP(1);
+	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();
+
+	PG_RETURN_POINTER(v);
+}
+
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
+	int			element = PG_GETARG_INT32(1);
+	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();
+
+	PG_RETURN_POINTER(v);
+}
+
+Datum
+jsonb_finish_numeric(PG_FUNCTION_ARGS)
+{
+	JsonbValue	*v = (JsonbValue *)PG_GETARG_POINTER(0);
+	if (v->type != jbvNumeric)
+		cannotCastJsonbValue(v->type, "numeric");
+	PG_RETURN_NUMERIC(v->val.numeric);
+}
+
+Datum
+jsonb_finish_bool(PG_FUNCTION_ARGS)
+{
+	JsonbValue	*v = (JsonbValue *)PG_GETARG_POINTER(0);
+	if (v->type != jbvBool)
+		cannotCastJsonbValue(v->type, "boolean");
+	PG_RETURN_BOOL(v->val.boolean);
+}
+
 Datum
 jsonb_bool(PG_FUNCTION_ARGS)
 {
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..f6042ea442c 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,39 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
 	return JSON_SUCCESS;
 }
 
+Datum
+jsonb_extract_path_start(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
+	ArrayType  *path = PG_GETARG_ARRAYTYPE_P(1);
+
+	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();
+
+	PG_RETURN_POINTER(v);
+}
+
 Datum
 jsonb_extract_path(PG_FUNCTION_ARGS)
 {
@@ -1516,52 +1550,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 +1604,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 +1625,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 +1637,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 +1662,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 ab9a7ac1f79..2ce85b42d8f 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,5 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202308251
-
+#define CATALOG_VERSION_NO	202308311
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9805bc61180..583032707e4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4587,25 +4587,25 @@
   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', prorettype => 'numeric', proargtypes => 'jsonb', prosupport => 'jsonb_cast_support',
   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' },
 
@@ -9947,6 +9947,30 @@
   proname => 'jsonb_object_field_text', prorettype => 'text',
   proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
   prosrc => 'jsonb_object_field_text' },
+{ oid => '4552',
+  proname => 'jsonb_object_field_start', prorettype => 'internal',
+  proargtypes => 'internal text', proargnames => '{from_json, field_name}',
+  prosrc => 'jsonb_object_field_start' },
+{ oid => '3813',
+  proname => 'jsonb_array_element_start', prorettype => 'internal',
+  proargtypes => 'internal int4', proargnames => '{from_jsonb, element_index}',
+  prosrc => 'jsonb_array_element_start' },
+{ oid => '4549',
+  proname => 'jsonb_extract_path_start', prorettype => 'internal',
+  proargtypes => 'internal _text', proallargtypes => '{jsonb,_text}',
+  proargmodes => '{i,v}', proargnames => '{from_jsonb,path_elems}',
+  prosrc => 'jsonb_extract_path_start'},
+{ oid => '4553',
+  proname => 'jsonb_finish_numeric', prorettype => 'numeric',
+  proargtypes => 'internal', proargnames => '{from_jsonvalue}',
+  prosrc => 'jsonb_finish_numeric' },
+{ oid => '4554',
+  proname => 'jsonb_finish_bool', prorettype => 'bool',
+  proargtypes => 'internal', proargnames => '{jsonvalue}',
+  prosrc => 'jsonb_finish_bool' },
+{ 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}',
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4a16d0dbafb..c166eb003f1 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,8 +457,81 @@ 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}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}, "field7": true}');
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json->'field5' -> 0)::numeric,
+(test_json->'field5' -> 10)::numeric,
+(test_json#>'{"field6", "f1"}')::numeric,
+(test_json#>'{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text)), (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text)))::smallint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text)))::integer, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text)))::bigint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text)))::real, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text)))::double precision, jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal(0), 0)), jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal(0), 10)), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal(0), '{field6,f1}'::text[])), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal(0), '{field6,f2}'::text[])), jsonb_finish_bool(jsonb_extract_path_start((test_json)::internal(0), '{field7}'::text[]))
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+ numeric | int2 | int4 | int8 | float4 | float8 | numeric | numeric | numeric | numeric | bool 
+---------+------+------+------+--------+--------+---------+---------+---------+---------+------
+       4 |    4 |    4 |    4 |      4 |      4 |       1 |    NULL |       9 |    NULL | t
+(1 row)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+                                            QUERY PLAN                                            
+--------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal(0), '{}'::text[]))
+   Filter: (test_jsonb.json_type = 'scalarint'::text)
+(3 rows)
+
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+ numeric 
+---------
+       2
+(1 row)
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+                                                  QUERY PLAN                                                   
+---------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field1'::text)))::integer
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+ERROR:  cannot cast jsonb string to type numeric
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+ERROR:  cannot cast jsonb string to type boolean
+\pset null ''
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
  ?column? 
 ----------
@@ -586,7 +659,8 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
  field4
  field5
  field6
-(6 rows)
+ field7
+(7 rows)
 
 -- nulls
 SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object';
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index e4b7cdf703d..9df88866671 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,8 +154,54 @@ 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}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}, "field7": true}');
+
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json->'field5' -> 0)::numeric,
+(test_json->'field5' -> 10)::numeric,
+(test_json#>'{"field6", "f1"}')::numeric,
+(test_json#>'{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+
+\pset null ''
 
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
-- 
2.21.0