Subject: Re: Extract numeric filed in JSONB more effectively

From: Andy Fan <[email-masked]>
Date: Sun, 10 Mar 2024 07:16:40 +0800

>> But I have a different question about this patch set.  This has some
>> overlap with the JSON_VALUE function that is being discussed at
>> [0][1]. For example, if I apply the patch
>> v39-0001-Add-SQL-JSON-query-functions.patch from that thread, I can run
>>
>> select count(*) from tb where json_value(a, '$.a' returning numeric) = 2;
>>
>> and I get a noticeable performance boost over
>>
>> select count(*) from tb where cast (a->'a' as numeric) = 2;
>
> Here is my test and profile about the above 2 queries.
>
..
> As we can see the patch here has the best performance (this result looks
> be different from yours?).
>
> After I check the code, I am sure both patches *don't* have the problem
> in master where it get a jsonbvalue first and convert it to jsonb and
> then cast to numeric.
>
> Then I perf the result, and find the below stuff:
>
..

> JSONB_VALUE has a much longer way to get getKeyJsonValueFromContainer,
> then I think JSON_VALUE probably is designed for some more complex path 
> which need to pay extra effort which bring the above performance
> difference. 

Hello Peter,

Thanks for highlight the JSON_VALUE patch! Here is the sistuation in my
mind now. 

My patch is desigined to *not* introducing any new user-faced functions, 
but let some existing functions run faster. JSON_VALUE patch is designed
to following more on SQL standard so introuduced one new function which
has more flexibility on ERROR handling [1].  

Both patches are helpful on the subject here, but my patch here has a
better performance per my testing, I don't think I did anything better
here, just because JSON_VALUE function is designed for some more generic
purpose which has to pay some extra effort, and even if we have some
chance to improve JSON_VALUE, I don't think it shoud not block the patch
here (I'd like to learn more about this, it may takes some time!)

So I think the my patch here can be go ahead again, what do you think? 

[1]
https://www.postgresql.org/message-id/CACJufxGtetrn34Hwnb9D2if5D_HOPAh235MtEZ1meVYx-BiNtg%40mail.gmail.com 

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Mon, 01 Apr 2024 09:42:12 +0800

Here is latest version, nothing changed besides the rebase to the latest
master. The most recent 3 questions should be addressed.

- The error message compatible issue [1] and the Peter's answer at [2].
- Peter's new question at [2] and my answer at [3].

Any effrot to move this patch ahead is welcome and thanks all the people
who provided high quaility feedback so far, especially chapman!

[1] https://www.postgresql.org/message-id/[email-masked]
[2]
https://www.postgresql.org/message-id/8102ff5b-b156-409e-a48f-e53e63a39b36%40eisentraut.org
[3] https://www.postgresql.org/message-id/8734t6c5rh.fsf%40163.com

-- 
Best Regards
Andy Fan

>From fb38be5addb93d7c0b8c1a3e8376751c9b3be5f5 Mon Sep 17 00:00:00 2001
From: "yizhi.fzh" <[email-masked]>
Date: Mon, 1 Apr 2024 09:36:08 +0800
Subject: [PATCH v17 1/1] Improve the performance of Jsonb numeric/bool 
 extraction.

JSONB object uses a binary compatible numeric format with the numeric
data type in SQL. However in the past, extracting a numeric value from a
JSONB field still needs to find the corresponding JsonbValue first,
then convert the JsonbValue to Jsonb, and finally use the cast system to
convert the Jsonb to a Numeric data type. This approach was very
inefficient in terms of performance.

In the current patch, It is handled that the JsonbValue is converted to
numeric data type directly.  This is done by the planner support
function which detects the above case and simplify the expression.
Because the boolean type and numeric type share certain similarities in
their attributes, we have implemented the same optimization approach for
both.  In the ideal test case, the performance can be 2x than before.

The optimized functions and operators includes:
1. jsonb_object_field / ->
2. jsonb_array_element / ->
3. jsonb_extract_path / #>
4. jsonb_path_query
5. jsonb_path_query_first
---
 src/backend/utils/adt/jsonb.c         | 206 ++++++++++++++++++++++++++
 src/backend/utils/adt/jsonbsubs.c     |   4 +-
 src/backend/utils/adt/jsonfuncs.c     | 123 ++++++++++-----
 src/backend/utils/adt/jsonpath_exec.c |  32 +++-
 src/include/catalog/pg_proc.dat       |  46 +++++-
 src/include/utils/jsonb.h             |  11 +-
 src/test/regress/expected/jsonb.out   | 112 +++++++++++++-
 src/test/regress/sql/jsonb.sql        |  66 ++++++++-
 src/tools/pgindent/typedefs.list      |   1 +
 9 files changed, 542 insertions(+), 59 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index a5e48744ac..6e93b34fd6 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -16,9 +16,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"
@@ -2035,6 +2041,206 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
 	elog(ERROR, "unknown jsonb type: %d", (int) type);
 }
 
+
+/*
+ * jsonb_cast_support()
+ *
+ * Planner support function for extracting numeric or bool data type more
+ * effectively. After finding out the corresponding JsonbValue, instead of
+ * casting it to Jsonb as an intermediate type, we covert it to the desired
+ * data type directly.
+ */
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+	Node	   *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+	if (IsA(rawreq, SupportRequestSimplify))
+	{
+		SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+		FuncExpr   *fexpr = req->fcall;
+		FuncExpr   *jsonb_start_func = NULL,
+				   *jsonb_finish_func = NULL,
+				   *final_func = NULL;
+		Node	   *input;
+		Oid			new_func_id = InvalidOid;
+		List	   *args;
+		Oid			input_func_id,
+					collid,
+					inputcollid;
+		bool		retset = false,
+					variadic = false;
+
+		Assert(list_length(fexpr->args) == 1);
+		input = (Node *) linitial(fexpr->args);
+
+		if (IsA(input, OpExpr))
+		{
+			OpExpr	   *opExpr = castNode(OpExpr, input);
+
+			input_func_id = opExpr->opfuncid;
+			collid = opExpr->opcollid;
+			inputcollid = opExpr->inputcollid;
+			args = opExpr->args;
+		}
+		else if (IsA(input, FuncExpr))
+		{
+			FuncExpr   *funcExpr = castNode(FuncExpr, input);
+
+			input_func_id = funcExpr->funcid;
+			collid = funcExpr->funccollid;
+			inputcollid = funcExpr->inputcollid;
+			args = funcExpr->args;
+		}
+		else
+			/* not the desired pattern. */
+			PG_RETURN_POINTER(NULL);
+
+		/* build a function to return the JsonbValue directly. */
+		switch (input_func_id)
+		{
+			case F_JSONB_OBJECT_FIELD:
+				new_func_id = F_JSONB_OBJECT_FIELD_START;
+				break;
+			case F_JSONB_ARRAY_ELEMENT:
+				new_func_id = F_JSONB_ARRAY_ELEMENT_START;
+				break;
+			case F_JSONB_EXTRACT_PATH:
+				new_func_id = F_JSONB_EXTRACT_PATH_START;
+				variadic = true;
+				break;
+			case F_JSONB_PATH_QUERY:
+				new_func_id = F_JSONB_PATH_QUERY_START;
+				retset = true;
+				break;
+			case F_JSONB_PATH_QUERY_FIRST:
+				new_func_id = F_JSONB_PATH_QUERY_FIRST_START;
+				break;
+			default:
+				new_func_id = InvalidOid;
+				break;
+		}
+
+		if (!OidIsValid(new_func_id))
+			PG_RETURN_POINTER(NULL);
+
+		/*
+		 * All the simplified functions have the same arguments as the
+		 * original one and return an internal object (actually a JsonbValue)
+		 * which will be casted desired type in the later function call.
+		 */
+		jsonb_start_func = makeFuncExpr(new_func_id, INTERNALOID, args,
+										collid, inputcollid,
+										COERCE_EXPLICIT_CALL);
+		jsonb_start_func->funcretset = retset;
+		jsonb_start_func->funcvariadic = variadic;
+
+		/*
+		 * relabel the first argument as 'internal' to follow our current
+		 * function signature checking system where if a function returns a
+		 * internal type, one of its arguments must be an internal type.
+		 */
+		linitial(jsonb_start_func->args) = makeRelabelType(linitial(jsonb_start_func->args),
+														   INTERNALOID, -1,
+														   InvalidOid,
+														   COERCE_IMPLICIT_CAST);
+
+		switch (fexpr->funcresulttype)
+		{
+			case INT2OID:
+			case INT4OID:
+			case INT8OID:
+			case FLOAT4OID:
+			case FLOAT8OID:
+			case NUMERICOID:
+				/* build another function to cast the JsonbValue into numeric */
+				jsonb_finish_func = makeFuncExpr(F_JSONB_FINISH_NUMERIC,
+												 NUMERICOID,
+												 list_make2(jsonb_start_func,
+															makeConst(OIDOID,
+																	  -1,
+																	  InvalidOid,
+																	  sizeof(Oid),
+																	  ObjectIdGetDatum(fexpr->funcresulttype),
+																	  false,
+																	  true)),
+												 collid,
+												 inputcollid,
+												 COERCE_EXPLICIT_CALL);
+
+				if (fexpr->funcresulttype != NUMERICOID)
+				{
+					/*
+					 * Leverage the casting system to cast the numeric to the
+					 * desired type.
+					 */
+					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), collid,
+										  inputcollid, COERCE_EXPLICIT_CALL);
+				PG_RETURN_POINTER(final_func);
+			default:
+				PG_RETURN_POINTER(NULL);
+		}
+	}
+
+	PG_RETURN_POINTER(NULL);
+}
+
+
+/*
+ * jsonb_finish_numeric
+ *		convert the JsonbValue to a numeric datum and raise error if
+ * necessary.
+ */
+Datum
+jsonb_finish_numeric(PG_FUNCTION_ARGS)
+{
+	JsonbValue *v = (JsonbValue *) PG_GETARG_POINTER(0);
+
+	/*
+	 * XXX: when converting a non jbvNumeric JsonbValue to numeric, some error
+	 * like "cannot cast jsonb xxx to type yyy" should be raised, here the xxx
+	 * is the real type of jsonbvalue, yyy is the desired type. if we just
+	 * want to say yyy is "numeric", arg(1) is not needed, but if we have to
+	 * insist on the error message compatible, we have to input this extra
+	 * argument.
+	 */
+	Oid			final_oid = PG_GETARG_OID(1);
+
+	if (v->type != jbvNumeric)
+		cannotCastJsonbValue(v->type, format_type_be(final_oid));
+	PG_RETURN_NUMERIC(v->val.numeric);
+}
+
+/*
+ * jsonb_finish_numeric
+ *		convert the JsonbValue to a bool datum and raise error if
+ * necessary.
+ */
+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/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index d3edb010ed..1c86cca6e4 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -248,7 +248,7 @@ jsonb_subscript_fetch(ExprState *state,
 									  workspace->index,
 									  sbsrefstate->numupper,
 									  op->resnull,
-									  false);
+									  JsonbValue_AsJsonb);
 }
 
 /*
@@ -340,7 +340,7 @@ jsonb_subscript_fetch_old(ExprState *state,
 												   sbsrefstate->upperindex,
 												   sbsrefstate->numupper,
 												   &sbsrefstate->prevnull,
-												   false);
+												   JsonbValue_AsJsonb);
 	}
 }
 
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 1b0f494329..5a89989b38 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -358,7 +358,7 @@ static JsonParseErrorType get_scalar(void *state, char *token, JsonTokenType tok
 static Datum get_path_all(FunctionCallInfo fcinfo, bool as_text);
 static text *get_worker(text *json, char **tpath, int *ipath, int npath,
 						bool normalize_results);
-static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text);
+static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target);
 static text *JsonbValueAsText(JsonbValue *v);
 
 /* semantic action functions for json_array_length */
@@ -500,6 +500,24 @@ 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);
 
+/*
+ * convert_jsonbvalue
+ * 		convert the JsonbValue to Text, Jsonb or just a pointer datum.
+ */
+Datum
+convert_jsonbvalue(JsonbValue *jbv, JsonbValueTarget target)
+{
+	switch (target)
+	{
+		case JsonbValue_AsJsonbValue:
+			PG_RETURN_POINTER(jbv);
+		case JsonbValue_AsJsonb:
+			PG_RETURN_JSONB_P(JsonbValueToJsonb(jbv));
+		case JsonbValue_AsText:
+			PG_RETURN_TEXT_P(JsonbValueAsText(jbv));
+	}
+	PG_RETURN_POINTER(NULL);
+}
 
 /*
  * pg_parse_json_or_errsave
@@ -855,13 +873,12 @@ json_object_field(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
-Datum
-jsonb_object_field(PG_FUNCTION_ARGS)
+static Datum
+jsonb_object_field_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
 {
 	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();
@@ -869,14 +886,26 @@ jsonb_object_field(PG_FUNCTION_ARGS)
 	v = getKeyJsonValueFromContainer(&jb->root,
 									 VARDATA_ANY(key),
 									 VARSIZE_ANY_EXHDR(key),
-									 &vbuf);
+									 NULL);
 
 	if (v != NULL)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+		return convert_jsonbvalue(v, target);
 
 	PG_RETURN_NULL();
 }
 
+Datum
+jsonb_object_field(PG_FUNCTION_ARGS)
+{
+	return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonbValue);
+}
+
 Datum
 json_object_field_text(PG_FUNCTION_ARGS)
 {
@@ -930,8 +959,8 @@ json_array_element(PG_FUNCTION_ARGS)
 		PG_RETURN_NULL();
 }
 
-Datum
-jsonb_array_element(PG_FUNCTION_ARGS)
+static Datum
+jsonb_array_element_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	int			element = PG_GETARG_INT32(1);
@@ -953,11 +982,23 @@ jsonb_array_element(PG_FUNCTION_ARGS)
 
 	v = getIthJsonbValueFromContainer(&jb->root, element);
 	if (v != NULL)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+		return convert_jsonbvalue(v, target);
 
 	PG_RETURN_NULL();
 }
 
+Datum
+jsonb_array_element(PG_FUNCTION_ARGS)
+{
+	return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonbValue);
+}
+
 Datum
 json_array_element_text(PG_FUNCTION_ARGS)
 {
@@ -1484,17 +1525,23 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
 Datum
 jsonb_extract_path(PG_FUNCTION_ARGS)
 {
-	return get_jsonb_path_all(fcinfo, false);
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonb);
 }
 
 Datum
 jsonb_extract_path_text(PG_FUNCTION_ARGS)
 {
-	return get_jsonb_path_all(fcinfo, true);
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsText);
+}
+
+Datum
+jsonb_extract_path_start(PG_FUNCTION_ARGS)
+{
+	return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonbValue);
 }
 
 static Datum
-get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
+get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	ArrayType  *path = PG_GETARG_ARRAYTYPE_P(1);
@@ -1516,7 +1563,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
 
 	deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath);
 
-	res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text);
+	res = jsonb_get_element(jb, pathtext, npath, &isnull, target);
 
 	if (isnull)
 		PG_RETURN_NULL();
@@ -1525,7 +1572,8 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
 }
 
 Datum
-jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull,
+				  JsonbValueTarget target)
 {
 	JsonbContainer *container = &jb->root;
 	JsonbValue *jbvp = NULL;
@@ -1558,16 +1606,26 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 	 */
 	if (npath <= 0 && jbvp == NULL)
 	{
-		if (as_text)
+		switch (target)
 		{
-			return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
-																  container,
-																  VARSIZE(jb))));
-		}
-		else
-		{
-			/* not text mode - just hand back the jsonb */
-			PG_RETURN_JSONB_P(jb);
+			case JsonbValue_AsText:
+				return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
+																	  container,
+																	  VARSIZE(jb))));
+				/* not text mode - just hand back the jsonb */
+			case JsonbValue_AsJsonb:
+				PG_RETURN_JSONB_P(jb);
+			case JsonbValue_AsJsonbValue:
+				{
+					JsonbValue *jbv = NULL;
+
+					if (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb))
+						PG_RETURN_POINTER(getIthJsonbValueFromContainer(container, 0));
+
+					jbv = palloc0(sizeof(JsonbValue));
+					JsonbToJsonbValue(jb, jbv);
+					PG_RETURN_POINTER(jbv);
+				}
 		}
 	}
 
@@ -1653,23 +1711,14 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
 		}
 	}
 
-	if (as_text)
-	{
-		if (jbvp->type == jbvNull)
-		{
-			*isnull = true;
-			return PointerGetDatum(NULL);
-		}
 
-		return PointerGetDatum(JsonbValueAsText(jbvp));
-	}
-	else
+	if (target == JsonbValue_AsText && jbvp->type == jbvNull)
 	{
-		Jsonb	   *res = JsonbValueToJsonb(jbvp);
-
-		/* not text mode - just hand back the jsonb */
-		PG_RETURN_JSONB_P(res);
+		*isnull = true;
+		return PointerGetDatum(NULL);
 	}
+
+	return convert_jsonbvalue(jbvp, target);
 }
 
 Datum
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 6c8bd57503..c806bdf1ac 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -408,7 +408,7 @@ jsonb_path_match_opr(PG_FUNCTION_ARGS)
  *		rowset.
  */
 static Datum
-jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz, JsonbValueTarget target)
 {
 	FuncCallContext *funcctx;
 	List	   *found;
@@ -452,19 +452,25 @@ jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
 	v = lfirst(c);
 	funcctx->user_fctx = list_delete_first(found);
 
-	SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v)));
+	SRF_RETURN_NEXT(funcctx, convert_jsonbvalue(v, target));
 }
 
 Datum
 jsonb_path_query(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_internal(fcinfo, false);
+	return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonb);
 }
 
 Datum
 jsonb_path_query_tz(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_internal(fcinfo, true);
+	return jsonb_path_query_internal(fcinfo, true, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_path_query_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonbValue);
 }
 
 /*
@@ -506,7 +512,7 @@ jsonb_path_query_array_tz(PG_FUNCTION_ARGS)
  *		item.  If there are no items, NULL returned.
  */
 static Datum
-jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	JsonPath   *jp = PG_GETARG_JSONPATH_P(1);
@@ -519,7 +525,11 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
 						   jb, !silent, &found, tz);
 
 	if (JsonValueListLength(&found) >= 1)
-		PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found)));
+	{
+		JsonbValue *jbv = JsonValueListHead(&found);
+
+		return convert_jsonbvalue(jbv, target);
+	}
 	else
 		PG_RETURN_NULL();
 }
@@ -527,13 +537,19 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
 Datum
 jsonb_path_query_first(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_first_internal(fcinfo, false);
+	return jsonb_path_query_first_internal(fcinfo, false, JsonbValue_AsJsonb);
 }
 
 Datum
 jsonb_path_query_first_tz(PG_FUNCTION_ARGS)
 {
-	return jsonb_path_query_first_internal(fcinfo, true);
+	return jsonb_path_query_first_internal(fcinfo, true, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_path_query_first_start(PG_FUNCTION_ARGS)
+{
+	return jsonb_path_query_first_internal(fcinfo, false, JsonbValue_AsJsonbValue);
 }
 
 /********************Execute functions for JsonPath**************************/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 291ed876fc..c786edaeaf 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4590,25 +4590,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' },
 
@@ -9976,6 +9976,30 @@
   proname => 'jsonb_object_field_text', prorettype => 'text',
   proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
   prosrc => 'jsonb_object_field_text' },
+{ oid => '4552', descr => 'extract jsonbvalue from jsonb for the given field',
+  proname => 'jsonb_object_field_start', prorettype => 'internal',
+  proargtypes => 'internal text', proargnames => '{from_json, field_name}',
+  prosrc => 'jsonb_object_field_start' },
+{ oid => '8688', descr => 'extract josnbvalue from jsonb array for the given index',
+  proname => 'jsonb_array_element_start', prorettype => 'internal',
+  proargtypes => 'internal int4', proargnames => '{from_jsonb, element_index}',
+  prosrc => 'jsonb_array_element_start' },
+{ oid => '4551', descr => 'extract jsonbvalue from jsonb for the given paths',
+  proname => 'jsonb_extract_path_start', provariadic => 'text', prorettype => 'internal',
+  proargtypes => 'internal _text', proallargtypes => '{internal,_text}',
+  proargmodes => '{i,v}', proargnames => '{from_jsonb,path_elems}',
+  prosrc => 'jsonb_extract_path_start'},
+{ oid => '4553', descr => 'convert a jsonbvalue to numeric',
+  proname => 'jsonb_finish_numeric', prorettype => 'numeric',
+  proargtypes => 'internal oid', proargnames => '{from_jsonvalue,target_oid}',
+  prosrc => 'jsonb_finish_numeric' },
+{ oid => '4554', descr => 'convert a jsonbvalue to boolean',
+  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}',
@@ -10172,6 +10196,10 @@
   proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
   prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
   prosrc => 'jsonb_path_query' },
+{ oid => '4557', descr => 'jsonpath query as jsonbvalue',
+  proname => 'jsonb_path_query_start', prorows => '1000', proretset => 't',
+  prorettype => 'internal', proargtypes => 'internal jsonpath jsonb bool',
+  prosrc => 'jsonb_path_query_start' },
 { oid => '4007', descr => 'jsonpath query wrapped into array',
   proname => 'jsonb_path_query_array', prorettype => 'jsonb',
   proargtypes => 'jsonb jsonpath jsonb bool',
@@ -10180,6 +10208,10 @@
   proname => 'jsonb_path_query_first', prorettype => 'jsonb',
   proargtypes => 'jsonb jsonpath jsonb bool',
   prosrc => 'jsonb_path_query_first' },
+{ oid => '4555', descr => 'jsonpath query first item as jsonbvalue',
+  proname => 'jsonb_path_query_first_start', prorettype => 'internal',
+  proargtypes => 'internal jsonpath jsonb bool',
+  prosrc => 'jsonb_path_query_first_start' },
 { oid => '4009', descr => 'jsonpath match',
   proname => 'jsonb_path_match', prorettype => 'bool',
   proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index e38dfd4901..1bcf72e351 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -294,6 +294,13 @@ struct JsonbValue
 	}			val;
 };
 
+typedef enum JsonbValueTarget
+{
+	JsonbValue_AsJsonbValue,
+	JsonbValue_AsJsonb,
+	JsonbValue_AsText
+} JsonbValueTarget;
+
 #define IsAJsonbScalar(jsonbval)	(((jsonbval)->type >= jbvNull && \
 									  (jsonbval)->type <= jbvBool) || \
 									  (jsonbval)->type == jbvDatetime)
@@ -428,12 +435,12 @@ extern const char *JsonbTypeName(JsonbValue *val);
 extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
 							   JsonbValue *newval);
 extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
-							   bool *isnull, bool as_text);
+							   bool *isnull, JsonbValueTarget target);
 extern bool to_jsonb_is_immutable(Oid typoid);
 extern Datum jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls,
 									   const Oid *types, bool absent_on_null,
 									   bool unique_keys);
 extern Datum jsonb_build_array_worker(int nargs, const Datum *args, const bool *nulls,
 									  const Oid *types, bool absent_on_null);
-
+extern Datum convert_jsonbvalue(JsonbValue *jbv, JsonbValueTarget target);
 #endif							/* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 66bee5162b..44f2db574d 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,8 +457,114 @@ 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, "field8": [1,2,3,4,5]}');
+\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, 'field4'::text), '1700'::oid), (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '23'::oid))::integer, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '20'::oid))::bigint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '700'::oid))::real, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '701'::oid))::double precision, jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 0), '1700'::oid), jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 10), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f1}'::text[]), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f2}'::text[]), '1700'::oid), jsonb_finish_bool(jsonb_extract_path_start((test_json)::internal, VARIADIC '{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
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+                                                                                                                                                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                                                                                             
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Result
+   Output: (jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), jsonb_path_query_first(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), (jsonb_finish_numeric((jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_path_query_first_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), '21'::oid))::smallint
+   ->  ProjectSet
+         Output: jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), test_json
+         ->  Seq Scan on pg_temp.test_jsonb
+               Output: json_type, test_json
+               Filter: (test_jsonb.json_type = 'object'::text)
+(7 rows)
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+ jsonb_path_query | jsonb_path_query_first | jsonb_path_query | jsonb_path_query_first 
+------------------+------------------------+------------------+------------------------
+ 2                | 2                      |                2 |                      2
+ 3                | 2                      |                3 |                      2
+ 4                | 2                      |                4 |                      2
+(3 rows)
+
+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, VARIADIC '{}'::text[]), '1700'::oid)
+   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, 'field1'::text), '23'::oid))::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 integer
+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 +692,9 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
  field4
  field5
  field6
-(6 rows)
+ field7
+ field8
+(8 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 97bc2242a1..9886044c82 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,8 +154,72 @@ 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, "field8": [1,2,3,4,5]}');
+
+\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
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+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';
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 95ae7845d8..9ea3f11a7b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1273,6 +1273,7 @@ JsonArrayAgg
 JsonArrayConstructor
 JsonArrayQueryConstructor
 JsonBaseObjectInfo
+JsonbValueTarget
 JsonConstructorExpr
 JsonConstructorExprState
 JsonConstructorType
-- 
2.34.1

---

From: Andy Fan <[email-masked]>
Date: Wed, 17 Apr 2024 13:13:34 +0800

> [3] https://www.postgresql.org/message-id/8734t6c5rh.fsf%40163.com

rebase to the latest master again.

commit bc990b983136ef658cd3be03cdb07f2eadc4cd5c (HEAD -> jsonb_numeric)
Author: yizhi.fzh <[email-masked]>
Date:   Mon Apr 1 09:36:08 2024 +0800

    Improve the performance of Jsonb numeric/bool extraction.

    

-- 
Best Regards
Andy Fan

>From bc990b983136ef658cd3be03cdb07f2eadc4cd5c Mon Sep 17 00:00:00 2001
From: "yizhi.fzh" <[email-masked]>
Date: Mon, 1 Apr 2024 09:36:08 +0800
Subject: [PATCH v18 1/1] Improve the performance of Jsonb numeric/bool
 extraction.

index e4562b3c6c..e05b5b35f1 100644
@@ -2034,6 +2040,206 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
 
+
+/*
+ * jsonb_cast_support()
+ *
index 2b037131c9..1bb9c25826 100644
 
 /*
 
index 83125b06a4..6585f6730d 100644

+/*
+ * convert_jsonbvalue
 
 /*
 
-Datum

 	if (v != NULL)
 
 	PG_RETURN_NULL();
 }
 
+Datum
 
-Datum

 	PG_RETURN_NULL();
 }
 
+Datum
 
 Datum
 
 static Datum

 
 	if (isnull)
 		PG_RETURN_NULL();
 
 Datum

-	if (as_text)
-	{

 Datum
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 103572ed93..1a016f5b9d 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -523,7 +523,7 @@ jsonb_path_match_opr(PG_FUNCTION_ARGS)
  *		rowset.
  */
 static Datum
-jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz, JsonbValueTarget target)
 {
 	FuncCallContext *funcctx;
 	List	   *found;
@@ -567,19 +567,25 @@ jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
 	v = lfirst(c);
 	funcctx->user_fctx = list_delete_first(found);

 Datum
 
 Datum
 
 /*
@@ -621,7 +627,7 @@ jsonb_path_query_array_tz(PG_FUNCTION_ARGS)
  *		item.  If there are no items, NULL returned.
  */
 static Datum
-jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz, JsonbValueTarget target)
 {
 	Jsonb	   *jb = PG_GETARG_JSONB_P(0);
 	JsonPath   *jp = PG_GETARG_JSONPATH_P(1);
@@ -634,7 +640,11 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
 						   jb, !silent, &found, tz);
 
@@ -642,13 +652,19 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
 
 Datum
 
index 134e3b22fd..dd0e45954e 100644
@@ -4605,25 +4605,25 @@

@@ -10004,6 +10004,30 @@
@@ -10200,6 +10224,10 @@
@@ -10208,6 +10236,10 @@
index d589ace5a2..a177d37c8d 100644
 
@@ -429,12 +436,12 @@ extern const char *JsonbTypeName(JsonbValue *val);
 
 -- nulls

 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d551ada325..dbcc3fd15c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1290,6 +1290,7 @@ JsonArrayQueryConstructor
 JsonBaseObjectInfo
 JsonBehavior
 JsonBehaviorType
+JsonbValueTarget
 JsonConstructorExpr
 JsonConstructorExprState
 JsonConstructorType
-- 
2.34.1

---

From: David Rowley <[email-masked]>
Date: Thu, 12 Sep 2024 09:00:41 +1200

> rebase to the latest master again.

There's a lot of complexity in the v18 patch that I don't understand
the need for.

I imagined you'd the patch should create a SupportRequestSimplify
support function for jsonb_numeric() that checks if the input
expression is an OpExpr with funcid of jsonb_object_field().  All you
do then is ditch the cast and change the OpExpr to call a new function
named jsonb_object_field_numeric() which returns the val.numeric
directly.  Likely the same support function could handle jsonb casts
to other types too, in which case you'd just call some other function,
e.g jsonb_object_field_timestamp() or jsonb_object_field_boolean().

Can you explain why the additional complexity is needed over what's in
the attached patch?

David
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 928552d551..7b60b36189 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -18,7 +18,9 @@
 #include "funcapi.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
+#include "nodes/supportnodes.h"
 #include "utils/builtins.h"
+#include "utils/fmgroids.h"
 #include "utils/json.h"
 #include "utils/jsonb.h"
 #include "utils/jsonfuncs.h"
@@ -2069,6 +2071,64 @@ jsonb_numeric(PG_FUNCTION_ARGS)
 	PG_RETURN_NUMERIC(retValue);
 }
 
+Datum
+jsonb_object_field_numeric(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)
+	{
+		if (v->type == jbvNumeric)
+			PG_RETURN_NUMERIC(v->val.numeric);
+
+		cannotCastJsonbValue(v->type, "numeric");
+	}
+
+	PG_RETURN_NULL();
+}
+
+Datum
+jsonb_numeric_support(PG_FUNCTION_ARGS)
+{
+	Node	   *rawreq = (Node *) PG_GETARG_POINTER(0);
+	Node *ret = NULL;
+
+	if (IsA(rawreq, SupportRequestSimplify))
+	{
+		SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+		FuncExpr *func = req->fcall;
+		OpExpr *opexpr = list_nth(func->args, 0);
+
+		/*
+		 * Transform jsonb_object_field calls that directly cast to numeric
+		 * into a direct call to jsonb_object_field_numeric.  This allows us
+		 * to directly access the numeric field and return it directly thus
+		 * saving casting from jsonb to numeric.
+		 */
+		if (IsA(opexpr, OpExpr) && opexpr->opfuncid == F_JSONB_OBJECT_FIELD)
+		{
+			opexpr->opfuncid = F_JSONB_OBJECT_FIELD_NUMERIC;
+			PG_RETURN_POINTER(opexpr);
+		}
+
+		PG_RETURN_POINTER(ret);
+	}
+
+	PG_RETURN_POINTER(ret);
+}
+
+
 Datum
 jsonb_int2(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ff5436acac..10aed0b0b1 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4642,8 +4642,12 @@
   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_numeric_support',
+  prorettype => 'numeric', proargtypes => 'jsonb',
   prosrc => 'jsonb_numeric' },
+{ oid => '8394', descr => 'planner support for jsonb casting support',
+  proname => 'jsonb_numeric_support',  prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'jsonb_numeric_support' },
 { oid => '3450', descr => 'convert jsonb to int2',
   proname => 'int2', prorettype => 'int2', proargtypes => 'jsonb',
   prosrc => 'jsonb_int2' },
@@ -10083,6 +10087,10 @@
   proargtypes => 'jsonb _text', proallargtypes => '{jsonb,_text}',
   proargmodes => '{i,v}', proargnames => '{from_json,path_elems}',
   prosrc => 'jsonb_extract_path' },
+{ oid => '8395',
+  proname => 'jsonb_object_field_numeric', prorettype => 'numeric',
+  proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
+  prosrc => 'jsonb_object_field_numeric' },
 { oid => '3940', descr => 'get value from jsonb as text with path elements',
   proname => 'jsonb_extract_path_text', provariadic => 'text',
   prorettype => 'text', proargtypes => 'jsonb _text',

---

From: Andy Fan <[email-masked]>
Date: Thu, 12 Sep 2024 03:03:18 +0000

Hello David,

  Thanks for checking this!
  
> e.g jsonb_object_field_timestamp() or jsonb_object_field_boolean().

Basically yes. The reason complexity comes when we many operators we
want to optimize AND my patch I want to reduce the number of function
created. 

> ..., in which case you'd just call some other function,

This works, but We need to create 2 functions for each operator. In the
patched case, we have 5 operators, so we need to create 10 functions.

op[1,2,3,4,5]_bool
op[1,2,3,4,5]_numeric.

Within the start / finish function, we need to create *7* functions.

op[1,2,3,4,5]_start :  return the "JsonbVaue".

jsonb_finish_numeric:  convert jsonbvalue to numeric
jsonb_finish_bool   :  convert jsonbvalue to bool.

I think the above is the major factor for the additional complexity. 

Some other factors contribute to complexity a bit.

1. we also have jsonb_int{2,4,8}/float{4,8} in pg_proc for '->'
   operator, not only numeric.
2. user may use OpExpr, like (jb->'x')::numeric, user may also use
   FuncExpr, like (jsonb_object_field(a, 'x'))::numeric. 

-- 
Best Regards
Andy Fan

---

From: Dmitry Dolgov <[email-masked]>
Date: Fri, 15 Nov 2024 17:30:09 +0100

> On Thu, Sep 12, 2024 at 03:03:18AM GMT, Andy Fan wrote:
>

Any particular reason you want to keep number of functions minimal? Is
it just to make the patch smaller? I might be missing something without
looking at the implementation in details, but the difference between 10
and 7 functions doesn't seem to be significant.

---

From: Andy Fan <[email-masked]>
Date: Mon, 18 Nov 2024 08:23:52 +0800

Hi Dmitry,

> and 7 functions doesn't seem to be significant.

Another reason is for reducing code duplication, writting too many
similar function looks not good to me. Chapman expressed this idea
first at [1]. Search "it would make me happy to further reduce some
of the code" in the message.

Acutally this doesn't make the patch complexer too much.

[1]
https://www.postgresql.org/message-id/5138c6b5fd239e7ce4e1a4e63826ac27%40anastigmatix.net 

-- 
Best Regards
Andy Fan

---

From: Dmitry Dolgov <[email-masked]>
Date: Fri, 22 Nov 2024 19:14:25 +0100

> On Mon, Nov 18, 2024 at 08:23:52AM GMT, Andy Fan wrote:
>
> >> [...]

It might not make everything too much complex, but e.g. relabeling of
the first argument for a "finish" function into an internal one sounds
strange to me. Maybe there is a way to avoid duplication of the code,
but keep all needed functions in pg_proc?

Btw, sorry to complain about small details, but I find start / finish
naming pattern not quite fitting here. Their main purpose is to extract
/ convert a value, the order in which they are happening is less
relevant.

---

From: Haibo Yan <[email-masked]>
Date: Thu, 2 Apr 2026 21:46:22 -0700

> / convert a value, the order in which they are happening is less

Hi all,
I’d like to continue pushing this patch forward.
Based on the earlier discussion, I reworked the patch into a smaller stage-1 version with a narrower scope and a simpler rewrite strategy. The current patch keeps the normal SQL syntax unchanged and uses support-function simplification to rewrite only the following patterns:
	(jsonb_object_field(...))::numeric
	jsonb_object_field(...))::bool
into explicit typed extractor calls.
So at this stage it intentionally covers only:
	jsonb_object_field(...) / ->
	casts to numeric
	casts to bool
and does not yet try to cover array/path extraction or integer/float typed extractors.
I also ran a small microbenchmark to isolate the cast-over-object-field path. On my setup, the current patch shows the following gains:
Query                                        Before      After       Speedup
--------------------------------------------------------------------------------
SELECT sum((j->'n')::numeric) FROM t         118.028 ms  56.082 ms   2.10x
SELECT count(*) FROM t WHERE (j->'b')::bool  115.665 ms  51.945 ms   2.23x
--------------------------------------------------------------------------------
These are microbenchmark numbers rather than end-to-end workload results, but they suggest that the simplified rewrite path is worth pursuing.
My goal with this version is not to solve the full matrix at once, but to first land a reviewer-friendly subset that:
1. does not introduce new user-visible operators,
2. keeps ordinary cast syntax unchanged,
3. avoids the more abstract internal/start-finish style machinery,
4. and uses explicit rewrite targets that are easier to review.
If this direction looks reasonable, I’d appreciate another round of review on the updated patch. If people think the stage-1 scope is acceptable, I can continue with follow-up patches for additional extractor families and target types.
Thanks,
Haibo

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 3 Apr 2026 06:50:27 +0200

pá 3. 4. 2026 v 6:46 odesílatel Haibo Yan <[email-masked]> napsal:

> Based on the earlier discussion, I reworked the patch into a smaller
> stage-1 version with a narrower scope and a simpler rewrite strategy. The
> current patch keeps the normal SQL syntax unchanged and uses
> support-function simplification to rewrite only the following patterns:
> I also ran a small microbenchmark to isolate the cast-over-object-field
> path. On my setup, the current patch shows the following gains:
> Query                                        Before      After
>  Speedup
>
> These are microbenchmark numbers rather than end-to-end workload results,
> but they suggest that the simplified rewrite path is worth pursuing.
> My goal with this version is not to solve the full matrix at once, but to
> first land a reviewer-friendly subset that:
> If this direction looks reasonable, I’d appreciate another round of review
> on the updated patch. If people think the stage-1 scope is acceptable, I
> can continue with follow-up patches for additional extractor families and
> target types.
> Thanks,
> Haibo
>

+1

Pavel

---

From: David Rowley <[email-masked]>
Date: Fri, 3 Apr 2026 19:41:21 +1300

> I’d like to continue pushing this patch forward.

Please can you add it to the PG20-1 commitfest @ [1].

David

[1] https://commitfest.postgresql.org/59/

---

From: Haibo Yan <[email-masked]>
Date: Fri, 3 Apr 2026 09:37:22 -0700

On Apr 2, 2026, at 11:41 PM, David Rowley <[email-masked]> wrote:
> 
> On Fri, 3 Apr 2026 at 17:46, Haibo Yan <[email-masked] <mailto:[email-masked]>> wrote:

Done — I’ve added it to the PG20-1 CommitFest:

https://commitfest.postgresql.org/patch/6644/

Thanks for the reminder.

Regards,

Haibo

---

From: Haibo Yan <[email-masked]>
Date: Fri, 3 Apr 2026 09:42:20 -0700

> [1] https://commitfest.postgresql.org/59/

Hi David,

One small thing: the UI looks a bit odd on my side. It does not seem to have picked my attachment, and is instead showing your older attachment there. But cfbot appears to have picked up mine, so I think it may just be a UI issue.

Thanks for the reminder.

Regards,

Haibo

---

From: Dmitry Dolgov <[email-masked]>
Date: Mon, 6 Apr 2026 15:05:37 +0200

> On Fri, Apr 03, 2026 at 09:42:20AM -0700, Haibo Yan wrote:

Thanks for picking it up, the patch looks good.

> One small thing: the UI looks a bit odd on my side. It does not seem to have picked my attachment, and is instead showing your older attachment there. But cfbot appears to have picked up mine, so I…

It takes some time, as far as I can see currently the correct patch is shown.

> +		/*
> +		 * Identify the inner extraction expression.  It may appear as
> +		 * either a FuncExpr or an OpExpr; accept both forms.
> +		 */
> +		if (IsA(arg, FuncExpr))
> +		{
> +			FuncExpr   *inner = (FuncExpr *) arg;
> +
> +			inner_funcid = inner->funcid;
> +			inner_args = inner->args;
> +			location = inner->location;
> +		}
> +		else if (IsA(arg, OpExpr))
> +		{
> +			OpExpr	   *inner = (OpExpr *) arg;
> +
> +			inner_funcid = inner->opfuncid;
> +			inner_args = inner->args;
> +			location = inner->location;
> +		}
> +		else

It may also appear as a SubscriptingRef expression if we use subscription over
jsonb.

	SELECT test_json['field7']::bool FROM test_jsonb WHERE json_type = 'object';

Seems to be worth handling this case as well, since it doesn't lead to an
interface explosion.

---

From: Haibo Yan <[email-masked]>
Date: Mon, 6 Apr 2026 10:51:43 -0700

> On Apr 6, 2026, at 6:05 AM, Dmitry Dolgov <[email-masked]> wrote:
> 

Thank you Dmitry
I agree that handling SubscriptingRef here is the right thing to do.

It stays within the same stage-1 scope, covers an equivalent object-field extraction form, and does not introduce any additional interface surface. Based on your suggestion, I updated the patch to recognize the SubscriptingRefrepresentation as well, so cases like:

----------------------------------------

test_json['field7']::bool

test_json['field4']::numeric

----------------------------------------

can follow the same rewrite path as the existing jsonb_object_field(...) / -> cases.

Please see the updated patch:

￼
Thanks again for the review and suggestion.

Regards,

Haibo

---

From: Haibo Yan <[email-masked]>
Date: Mon, 6 Apr 2026 10:57:38 -0700

> On Apr 6, 2026, at 10:51 AM, Haibo Yan <[email-masked]> wrote:
> 
> 
> <v2-0001-jsonb-optimize-object-field-casts-to-numeric-and-.patch>
> 

It looks like the attachment may have been dropped in transit. I’m reattaching it here.

Regards,

Haibo

￼

---

From: Haibo Yan <[email-masked]>
Date: Mon, 6 Apr 2026 11:35:32 -0700

On Mon, Apr 6, 2026 at 10:57 AM Haibo Yan <[email-masked]> wrote:

> One small thing: the UI looks a bit odd on my side. It does not seem to
> have picked my attachment, and is instead showing your older attachment
> there. But cfbot appears to have picked up mine, so I think it may just be
> a UI issue.
>
>
> It stays within the same stage-1 scope, covers an equivalent object-field
> extraction form, and does not introduce any additional interface surface.
> Based on your suggestion, I updated the patch to recognize the
> SubscriptingRefrepresentation as well, so cases like:
>
> This is quite embarrassing — it looks like my attachment got lost
somewhere in transit. I’m not sure what happened. I did confirm locally
that the attachment was included, but I can’t see it in the mail thread.
I’m now trying to resend it using a different mail client. Hopefully this
won’t cause too much disruption for everyone.

Haibo

---

From: Haibo Yan <[email-masked]>
Date: Mon, 6 Apr 2026 12:55:29 -0700

> won’t cause too much disruption for everyone.

Sorry — the previous version failed to build because of a duplicate objid.
I’ve reuploaded a fixed version here.

Regards,
Haibo

---

From: Haibo Yan <[email-masked]>
Date: Mon, 6 Apr 2026 20:12:04 -0700

On Mon, Apr 6, 2026 at 12:55 PM Haibo Yan <[email-masked]> wrote:

>>> It stays within the same stage-1 scope, covers an equivalent
>>> object-field extraction form, and does not introduce any additional
>>> interface surface. Based on your suggestion, I updated the patch to
>>> recognize the SubscriptingRefrepresentation as well, so cases like:
>>>

Since this is my first patch submission, I forgot to include the original
author.
Thanks to Andy Fan for the suggestion — I’ve now added him as a co-author.
Regards
Haibo

---

From: Andy Fan <[email-masked]>
Date: Tue, 07 Apr 2026 14:13:35 +0800

Hi Haibo,

> stage-1 version with a narrower scope and a simpler rewrite
> strategy. ..

Thanks for working on this. I did a quick comparison between this
version and my last patch v18 [1],  here is the difference.

My previous v18[1]: 542 insertions(+), 59 deletions(-)
Your patch: 543 insertions(+), 5 deletions(-)

However as what you have realized, your current patch still lack of many 
optimizations, Not only the integer/float stuff, but also many
functions, e.g. jsonb_array_element, jsonb_extract_path,
jsonb_path_query and jsonb_path_query_first. After considering this,
what patch will look like in your approach? I guess you can see many
duplications. Less code doesn't always mean better, but I think this
still be a key consideration to address/check.  

> Query                                        Before      After       Speedup

Thanks for running the test. 

[1] https://www.postgresql.org/message-id/87ttk0lgcx.fsf%40163.com

-- 
Best Regards
Andy Fan

---

From: Haibo Yan <[email-masked]>
Date: Tue, 7 Apr 2026 01:31:16 -0700

> still be a key consideration to address/check.

Yes, I agree the current patch is still missing a large part of the full
matrix from v18 — not only int4/int8/float8, but also other extractor
families such as jsonb_array_element, jsonb_extract_path, jsonb_path_query,
and jsonb_path_query_first.

My intent with this version was not to cover the whole space at once, but
to first reduce it to a smaller stage-1 subset that is easier to review and
easier to reason about. In particular, I wanted to validate the simpler
rewrite shape first: keep the support-function-based approach, but rewrite
directly to explicit typed extractor functions, without the previous
start/finish/internal pipeline.

I agree that if this approach is extended to the full matrix naively,
duplication will become a real issue. So I am not claiming that the current
patch shape should simply be copied mechanically across every
extractor/type combination. My thought was to first see whether this
narrower subset is acceptable in principle, and then decide how best to
extend it without reintroducing too much abstraction or too much
duplication.

So for now I would prefer to keep the current scope intentionally narrow:

   -

   jsonb_object_field / -> / equivalent subscripting form
   -

   casts to numeric and bool

and treat the rest as follow-up work, rather than trying to solve the
entire matrix in one patch.

Thanks,
Haibo

---

From: Andy Fan <[email-masked]>
Date: Wed, 08 Apr 2026 07:59:26 +0800

Haibo Yan <[email-masked]> writes:

Hi Haibo,

> duplication will become a real issue.

Could you summary how it would be? I think it would be helpful for
others to review.  Otherwise every reviewer needs to count them many
times. 

-- 
Best Regards
Andy Fan

---

From: Haibo Yan <[email-masked]>
Date: Tue, 7 Apr 2026 18:21:45 -0700

> others to review.  Otherwise every reviewer needs to count them many
Hi Andy,
Sure.

My current thought is to extend it in stages, rather than trying to solve
the full matrix in a single patch.

A rough plan would be:

1. Keep the current stage-1 patch small and validate the basic approach
first

   -

   support-function rewrite directly to explicit typed extractor functions

2. Extend target types before extending extractor families

   -

   add int4 / int8 / float8 for the same object-field family first
   -

   keep the SQL-visible rewrite targets explicit, e.g.

   -

      jsonb_object_field_int4
      -

      jsonb_object_field_int8
      -

      jsonb_object_field_float8

   -

   avoid the previous numeric-intermediate rewrite shape

3. Then extend to other extractor families with the same overall pattern

   -

   likely starting with jsonb_array_element and jsonb_extract_path
   -

   and possibly jsonb_path_query_first later
   -

   each family would still rewrite to explicit typed extractor entry
   points, e.g.

   -

      jsonb_array_element_numeric
      -

      jsonb_extract_path_bool
      -

      jsonb_path_query_first_int4

4. Keep duplication manageable by sharing the implementation underneath

   -

   keep the SQL/catalog-level rewrite targets explicit for readability and
   reviewability
   -

   but factor the C implementation into:

   -

      extractor-family lookup helpers
      -

      target-type conversion helpers
      -

      thin wrappers, possibly generated with small macros

So the idea would be: explicit rewrite targets at the SQL/catalog level,
but shared lookup/conversion code underneath, instead of going back to the
earlier start/finish/internal pipeline.

I agree that if this is extended naively across the full matrix,
duplication will become a real issue. My reason for keeping the current
patch narrow is that I wanted to first validate this simpler rewrite shape
on a small subset before deciding how best to scale it further.

Regards,

Haibo

---

From: Haibo Yan <[email-masked]>
Date: Wed, 8 Apr 2026 12:50:50 -0700

On Tue, Apr 7, 2026 at 6:21 PM Haibo Yan <[email-masked]> wrote:

>    keep the SQL/catalog-level rewrite targets explicit for readability
>    and reviewability
>    -
>

Following up on our previous discussion, I want to clarify the current
patch plan together with the updated first patch.

Earlier I described this work roughly as a 4-patch line. After iterating on
the implementation and trying to keep each step reviewable, I now think the
cleaner split is a 5-patch series:

   1.

   object-field casts to scalar types
   2.

   array-element casts to scalar types
   3.

   extract-path casts to scalar types
   4.

   multi-subscript casts via extract-path lowering
   5.

   jsonpath-first casts to scalar types (jsonb_path_query_first and _tz)

The overall design is unchanged: use the cast function’s support hook to
recognize cast(extract(...)) over scalar-returning jsonb extraction
functions, and rewrite that directly to explicit typed extractor calls.

Supported target types remain:

   - numeric
   - bool
   - int4
   - int8
   - float8

One point I also want to make explicit is that I do not plan to include
jsonb_path_query in this series.

After looking at it more carefully, I do not think it fits the same model
as the rest of the series. The patches here are all about scalar-returning
extraction functions, where the cast prosupport hook can see and rewrite a
scalar expression pair. jsonb_path_query is set-returning, so optimizing
casts over it would likely need a different mechanism, probably at planner
or executor level, rather than one more patch in this prosupport-based
series.

Attached here is the updated first patch in the current plan.

This patch covers object-field extraction only:

   - jsonb_object_field
   - -> with text key
   - key subscripting

and rewrites casts to:

   - numeric
   - bool
   - int4
   - int8
   - float8

to direct typed extractor calls.

Thanks again for the earlier comments. I plan to send the remaining patches
in follow-up emails in the order above.

Regards,
Haibo

---

From: Haibo Yan <[email-masked]>
Date: Fri, 10 Apr 2026 14:48:32 -0700

On Wed, Apr 8, 2026 at 12:50 PM Haibo Yan <[email-masked]> wrote:

> Earlier I described this work roughly as a 4-patch line. After iterating
> on the implementation and trying to keep each step reviewable, I now think
> the cleaner split is a 5-patch series:
>
>
>    1.
>
> Thanks again for the earlier comments. I plan to send the remaining
> patches in follow-up emails in the order above.
>
> Regards,
> Haibo
>

Hi all,
Following up on the earlier thread, I am continuing with the same overall
plan and sending the next two patches in the series together.
The design is still the same as before: use the cast function’s support
hook to recognize cast(extract(...)) over scalar-returning jsonb extraction
functions, and rewrite that directly to explicit typed extractor calls,
without changing normal SQL syntax.
At this point, I have also folded int2 and float4 into the per-family
patches, so each patch now carries the full target-type coverage for the
functionality it introduces.
The supported target types are now:
numeric

   - bool
   - int2
   - int4
   - int8
   - float4
   - float8

I am still keeping jsonb_path_query out of scope for this series. The
series is focused on scalar-returning extraction functions, where the cast
prosupport hook can see and rewrite a scalar expression pair.
jsonb_path_query is set-returning, so optimizing casts over it looks like a
different planner/executor problem rather than one more patch in this
prosupport-based line.

With that in mind, the current 5-patch plan is:

   1. object-field casts to scalar types
   2. array-element casts to scalar types
   3. extract-path casts to scalar types
   4. multi-subscript casts via extract-path lowering
   5. jsonpath-first casts to scalar types (jsonb_path_query_first and _tz)

In this email I am sending the first two patches:

   - patch 1: object-field casts to scalar types
   - patch 2: array-element casts to scalar types

Patch 1 covers:

   - jsonb_object_field
   - -> with text key
   - key subscripting

Patch 2 covers:

   - jsonb_array_element
   - -> with integer RHS
   - single-index array subscripting

Both patches now support the full target-type set listed above.
Thanks again for the earlier comments. I plan to continue with the
remaining patches in follow-up emails in the same order.

Regards,
Haibo

---

From: Alvaro Herrera <[email-masked]>
Date: Sun, 12 Apr 2026 17:06:20 +0200

> won’t cause too much disruption for everyone.

Yeah, we've seen Apple Mail being broken in this way before.  The
attachment is not lost -- it's just that Apple Mail uses a weird MIME
structure, so the attachment doesn't show in many other clients; in
particular, our archives parser doesn't see it.  I also don't see it in
the default Mutt view, but if I open the MIME-browser, it shows up
there.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

---

From: Haibo Yan <[email-masked]>
Date: Sun, 26 Apr 2026 22:01:58 -0700

On Fri, Apr 10, 2026 at 2:48 PM Haibo Yan <[email-masked]> wrote:

>>> My current thought is to extend it in stages, rather than trying to
>>> solve the full matrix in a single patch.
>>>
>>> A rough plan would be:
>>>
Hi all,

I spent some time reworking this patch series into a shape that I think is
easier to review and easier to reason about patch-by-patch.

The goal of the series is still the same: optimize casts over
scalar-returning jsonb extraction functions by using the cast function’s
support hook to recognize:

   - cast(extract(...))

and rewrite that directly to explicit typed extractor calls.

That keeps ordinary SQL syntax unchanged, but avoids the extra jsonb scalar
wrapping/unwrapping on the optimized path.

At this point the series is organized as 5 patches:

   5. jsonpath-first casts to scalar types

The supported target types are:

   - numeric
   - bool
   - int2
   - int4
   - int8
   - float4
   - float8

The covered scalar-returning extraction families/forms are:

   - jsonb_object_field
   - -> with text key
   - equivalent key subscripting
   - jsonb_array_element
   - -> with integer RHS
   - single-index array subscripting
   - jsonb_extract_path
   - #>
   - direct jsonb_extract_path(...)
   - multi-subscript jsonb subscripting (lowered to extract-path)
   - jsonb_path_query_first
   - jsonb_path_query_first_tz

A few points about the current shape of the series:

   - I intentionally stayed with the support-function rewrite model and did
   not introduce new user-visible operators.
   - I also did not go back to the earlier start/finish/internal pipeline
   approach. The current version rewrites directly to explicit typed extractor
   functions.
   - I folded int2 and float4 into the same family-based design, since they
   are the remaining natural numeric-family targets that fit the same
   conversion model.
   - I kept the multi-subscript patch conservative. It lowers through the
   existing extract-path family, but it does not try to turn this into a
   broader executor-side subscripting redesign.

One explicit boundary of the series is that it does *not* try to optimize
jsonb_path_query.

After looking at that more carefully, I do not think it belongs in the same
patch line. This series is about scalar-returning extraction functions,
where the cast prosupport hook can see and rewrite a scalar expression
pair. jsonb_path_query is set-returning, so optimizing casts over it would
likely require a different mechanism, probably at planner or executor
level, rather than one more patch in this prosupport-based series.

Another explicit boundary is that the series stops at the current
numeric/bool scalar targets. I did not try to extend it to
text/date/uuid-like conversions, because those would require different
semantics such as full jsonb serialization or string parsing, rather than
the direct scalar conversion pattern used here.

I expect one likely question will be the catalog footprint, since this
approach adds a noticeable number of typed extractor builtins. I think that
is the cleanest tradeoff in this case: SQL is statically typed, and
explicit typed extractors keep the rewrite path simple, predictable, and
reviewable. The implementation-side boilerplate is kept under control with
shared helpers and thin family-specific macros.

Thanks in advance for any review and feedback.

Regards,
Haibo

---

From: Haibo Yan <[email-masked]>
Date: Mon, 27 Apr 2026 13:35:57 -0700

On Sun, Apr 26, 2026 at 10:01 PM Haibo Yan <[email-masked]> wrote:

>>>> So the idea would be: explicit rewrite targets at the SQL/catalog
>>>> level, but shared lookup/conversion code underneath, instead of going back
>>>> to the earlier start/finish/internal pipeline.
>>>>
>>> After looking at it more carefully, I do not think it fits the same
>>> model as the rest of the series. The patches here are all about
>>> scalar-returning extraction functions, where the cast prosupport hook can
>>> see and rewrite a scalar expression pair. jsonb_path_query is
>>> set-returning, so optimizing casts over it would likely need a different
>>> mechanism, probably at planner or executor level, rather than one more
>>> patch in this prosupport-based series.
>>>
> That keeps ordinary SQL syntax unchanged, but avoids the extra jsonb
> scalar wrapping/unwrapping on the optimized path.
>
>    - I intentionally stayed with the support-function rewrite model and
>    did not introduce new user-visible operators.
>    - I folded int2 and float4 into the same family-based design, since
>    they are the remaining natural numeric-family targets that fit the same
>    conversion model.
> After looking at that more carefully, I do not think it belongs in the
> same patch line. This series is about scalar-returning extraction
> functions, where the cast prosupport hook can see and rewrite a scalar
> expression pair. jsonb_path_query is set-returning, so optimizing casts
> over it would likely require a different mechanism, probably at planner or
> executor level, rather than one more patch in this prosupport-based series.
>
Hi all,

I saw that CI failed on the latest version of the series, in the
jsonb_path_query_first / jsonb_path_query_first_tz typed-extractor path.

The failure does not look like an expected-output issue. The more likely
problem is in how the typed jsonpath-first helper is obtaining or returning
the first JsonbValue from the jsonpath execution result.

At the moment my working suspicion is one of these:

   1. the code is returning a dangling / no-longer-valid JsonbValue *, for
   example a pointer into a temporary JsonValueList or other local
   container whose contents are no longer stable by the time the conversion
   helper inspects it; or
   2. the code is not actually passing the correct result JsonbValue to the
   conversion helper, and is instead interpreting some other internal
   structure as a JsonbValue, which would explain the bogus unknown jsonb
   type failure seen in CI.

 Sending the new patches.

Regards,
Haibo