v5-0003-jsonb-optimize-extract-path-casts-to-scalar-types.patch

From 04e738b46adda4f9edb948cf5729091f85ac9830 Mon Sep 17 00:00:00 2001
From: Haibo Yan <haibo.yan@apple.com>
Date: Fri, 10 Apr 2026 12:08:25 -0700
Subject: [PATCH v5 3/5] jsonb: optimize extract-path casts to scalar types

Extend the existing support-function rewrite to jsonb extract-path
operations, including both #> and direct jsonb_extract_path(...)
calls.

Supported casts are rewritten directly to explicit typed extractor
functions for numeric, bool, int2, int4, int8, float4, and float8.
---
 src/backend/utils/adt/jsonb.c       |  43 +++++-
 src/backend/utils/adt/jsonfuncs.c   | 188 ++++++++++++++++++++++-
 src/include/catalog/pg_proc.dat     |  31 +++-
 src/test/regress/expected/jsonb.out | 227 ++++++++++++++++++++++++++++
 src/test/regress/sql/jsonb.sql      |  61 ++++++++
 5 files changed, 546 insertions(+), 4 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 5abb51d47da..7eba437b8a4 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1834,6 +1834,7 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
  * Supported extraction families:
  *   - jsonb_object_field(j, 'key')  /  j -> 'key'  /  j['key']
  *   - jsonb_array_element(j, idx)   /  j -> idx    /  j[idx]
+ *   - jsonb_extract_path(j, ...)    /  j #> '{a,b}'
  */
 Datum
 jsonb_cast_support(PG_FUNCTION_ARGS)
@@ -2010,6 +2011,46 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
 			else
 				PG_RETURN_POINTER(NULL);
 		}
+		else if (inner_funcid == F_JSONB_EXTRACT_PATH)
+		{
+			if (fexpr->funcid == F_NUMERIC_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_NUMERIC;
+				replacement_rettype = NUMERICOID;
+			}
+			else if (fexpr->funcid == F_BOOL_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_BOOL;
+				replacement_rettype = BOOLOID;
+			}
+			else if (fexpr->funcid == F_INT4_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_INT4;
+				replacement_rettype = INT4OID;
+			}
+			else if (fexpr->funcid == F_INT8_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_INT8;
+				replacement_rettype = INT8OID;
+			}
+			else if (fexpr->funcid == F_FLOAT8_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_FLOAT8;
+				replacement_rettype = FLOAT8OID;
+			}
+			else if (fexpr->funcid == F_INT2_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_INT2;
+				replacement_rettype = INT2OID;
+			}
+			else if (fexpr->funcid == F_FLOAT4_JSONB)
+			{
+				replacement_funcid = F_JSONB_EXTRACT_PATH_FLOAT4;
+				replacement_rettype = FLOAT4OID;
+			}
+			else
+				PG_RETURN_POINTER(NULL);
+		}
 		else
 			PG_RETURN_POINTER(NULL);
 
@@ -2241,4 +2282,4 @@ JsonbUnquote(Jsonb *jb)
 	}
 	else
 		return JsonbToCString(NULL, &jb->root, VARSIZE(jb));
-}
\ No newline at end of file
+}
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index cc9c51c5cbf..b3fd1dfb631 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -1060,7 +1060,6 @@ jsonb_value_to_float8_datum(JsonbValue *v)
 
 	return DirectFunctionCall1(numeric_float8, NumericGetDatum(v->val.numeric));
 }
-
 static Datum
 jsonb_value_to_int2_datum(JsonbValue *v)
 {
@@ -1181,6 +1180,191 @@ DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_float8, jsonb_value_to_floa
 DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_int2, jsonb_value_to_int2_datum)
 DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_float4, jsonb_value_to_float4_datum)
 
+/*
+ * Walk a text[] path through a jsonb value and return the leaf JsonbValue,
+ * or NULL.  Returns NULL when a path element is missing, the leaf is JSON
+ * null, or the path array contains null elements.
+ *
+ * For an empty path, returns the root value itself (as a jbvBinary for
+ * containers, or the unwrapped scalar for scalar jsonb).  The caller's
+ * conversion helper then decides whether to accept or reject the type.
+ *
+ * For arrays, path elements are parsed as integers (matching the existing
+ * jsonb_extract_path / jsonb_get_element traversal semantics).
+ */
+static JsonbValue *
+jsonb_extract_path_lookup(Jsonb *jb, ArrayType *path, JsonbValue *vbuf)
+{
+	Datum	   *pathtext;
+	int			npath;
+	JsonbContainer *container = &jb->root;
+	JsonbValue *jbvp = NULL;
+	bool		have_object,
+				have_array;
+	int			i;
+
+	/* If the path array contains any nulls, return NULL */
+	if (array_contains_nulls(path))
+		return NULL;
+
+	deconstruct_array_builtin(path, TEXTOID, &pathtext, NULL, &npath);
+
+	/* Identify top-level container type */
+	if (JB_ROOT_IS_OBJECT(jb))
+	{
+		have_object = true;
+		have_array = false;
+	}
+	else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
+	{
+		have_object = false;
+		have_array = true;
+	}
+	else
+	{
+		Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb));
+
+		/* For a scalar root with an empty path, return the scalar itself */
+		if (npath <= 0)
+		{
+			jbvp = getIthJsonbValueFromContainer(container, 0);
+			if (jbvp == NULL || jbvp->type == jbvNull)
+				return NULL;
+			return jbvp;
+		}
+
+		/* Non-empty path into a scalar yields NULL */
+		return NULL;
+	}
+
+	/*
+	 * For an empty path on a container root, return the container as a
+	 * jbvBinary value.  The conversion helper will reject it with the
+	 * usual "cannot cast jsonb array or object" error.
+	 */
+	if (npath <= 0)
+	{
+		vbuf->type = jbvBinary;
+		vbuf->val.binary.data = container;
+		vbuf->val.binary.len = VARSIZE(jb) - VARHDRSZ;
+		return vbuf;
+	}
+
+	for (i = 0; i < npath; i++)
+	{
+		if (have_object)
+		{
+			text	   *subscr = DatumGetTextPP(pathtext[i]);
+
+			jbvp = getKeyJsonValueFromContainer(container,
+												VARDATA_ANY(subscr),
+												VARSIZE_ANY_EXHDR(subscr),
+												NULL);
+		}
+		else if (have_array)
+		{
+			int			lindex;
+			uint32		index;
+			char	   *indextext = TextDatumGetCString(pathtext[i]);
+			char	   *endptr;
+
+			errno = 0;
+			lindex = strtoint(indextext, &endptr, 10);
+			if (endptr == indextext || *endptr != '\0' || errno != 0)
+				return NULL;
+
+			if (lindex >= 0)
+			{
+				index = (uint32) lindex;
+			}
+			else
+			{
+				uint32		nelements;
+
+				if (!JsonContainerIsArray(container))
+					elog(ERROR, "not a jsonb array");
+
+				nelements = JsonContainerSize(container);
+
+				if (lindex == INT_MIN || -lindex > nelements)
+					return NULL;
+				else
+					index = nelements + lindex;
+			}
+
+			jbvp = getIthJsonbValueFromContainer(container, index);
+		}
+		else
+		{
+			/* scalar: cannot traverse further */
+			return NULL;
+		}
+
+		if (jbvp == NULL)
+			return NULL;
+
+		/* If this is the last path element, we have our leaf */
+		if (i == npath - 1)
+			break;
+
+		/* Descend into the next container level */
+		if (jbvp->type == jbvBinary)
+		{
+			container = jbvp->val.binary.data;
+			have_object = JsonContainerIsObject(container);
+			have_array = JsonContainerIsArray(container);
+		}
+		else
+		{
+			/* scalar at intermediate step: no further traversal */
+			have_object = false;
+			have_array = false;
+		}
+	}
+
+	/* Missing value or JSON null both map to SQL NULL */
+	if (jbvp == NULL || jbvp->type == jbvNull)
+		return NULL;
+
+	return jbvp;
+}
+
+/*
+ * Thin-wrapper macro for the jsonb_extract_path_<type> extractor family.
+ * Same pattern as the object-field and array-element wrapper macros.
+ */
+#define DEFINE_JSONB_EXTRACT_PATH_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0); \
+	ArrayType  *path = PG_GETARG_ARRAYTYPE_P(1); \
+	JsonbValue	vbuf; \
+	JsonbValue *v; \
+	Datum		result; \
+\
+	v = jsonb_extract_path_lookup(jb, path, &vbuf); \
+	if (v == NULL) \
+	{ \
+		PG_FREE_IF_COPY(path, 1); \
+		PG_FREE_IF_COPY(jb, 0); \
+		PG_RETURN_NULL(); \
+	} \
+\
+	result = convfn(v); \
+	PG_FREE_IF_COPY(path, 1); \
+	PG_FREE_IF_COPY(jb, 0); \
+	return result; \
+}
+
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_float8, jsonb_value_to_float8_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_int2, jsonb_value_to_int2_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_float4, jsonb_value_to_float4_datum)
+
 Datum
 json_array_element(PG_FUNCTION_ARGS)
 {
@@ -6427,4 +6611,4 @@ json_check_mutability(Oid typoid, bool is_jsonb, bool *has_mutable)
 				*has_mutable = true;
 			break;
 	}
-}
\ No newline at end of file
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ad752a0a9e0..d8ac9cbf053 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12827,6 +12827,26 @@
   proname => 'jsonb_array_element_float8', prorettype => 'float8',
   proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
   prosrc => 'jsonb_array_element_float8' },
+{ oid => '9965', descr => 'extract numeric from jsonb by path',
+  proname => 'jsonb_extract_path_numeric', prorettype => 'numeric',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_numeric' },
+{ oid => '9966', descr => 'extract boolean from jsonb by path',
+  proname => 'jsonb_extract_path_bool', prorettype => 'bool',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_bool' },
+{ oid => '9967', descr => 'extract int4 from jsonb by path',
+  proname => 'jsonb_extract_path_int4', prorettype => 'int4',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_int4' },
+{ oid => '9968', descr => 'extract int8 from jsonb by path',
+  proname => 'jsonb_extract_path_int8', prorettype => 'int8',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_int8' },
+{ oid => '9969', descr => 'extract float8 from jsonb by path',
+  proname => 'jsonb_extract_path_float8', prorettype => 'float8',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_float8' },
 { oid => '9982', descr => 'extract int2 from jsonb array by element index',
   proname => 'jsonb_array_element_int2', prorettype => 'int2',
   proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
@@ -12836,4 +12856,13 @@
   proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
   prosrc => 'jsonb_array_element_float4' },
 
-]
\ No newline at end of file
+{ oid => '9984', descr => 'extract int2 from jsonb by path',
+  proname => 'jsonb_extract_path_int2', prorettype => 'int2',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_int2' },
+{ oid => '9985', descr => 'extract float4 from jsonb by path',
+  proname => 'jsonb_extract_path_float4', prorettype => 'float4',
+  proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+  prosrc => 'jsonb_extract_path_float4' },
+
+]
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 36bc82095be..b96c095525c 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -1247,6 +1247,233 @@ SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
                         2.5
 (1 row)
 
+-- Optimized typed extraction: extract-path family
+-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
+-- into direct typed extractor calls for the same target types.
+-- Section P1: planner rewrite verification (operator form #>)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_numeric(test_json, '{field4}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_int4(test_json, '{field4}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_float8(test_json, '{field4}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_int8(test_json, '{field6,f1}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_bool(test_json, '{field7}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_int2(test_json, '{field4}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_float4(test_json, '{field4}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section P1b: planner rewrite verification (direct function-call form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_float8(test_json, '{field4}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field6', 'f1'))::int4 FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_int4(test_json, '{field6,f1}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section P1c: planner rewrite verification (VARIADIC ARRAY form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, VARIADIC ARRAY['field6','f1']))::int4 FROM test_jsonb WHERE json_type = 'object';
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_extract_path_int4(test_json, '{field6,f1}'::text[])
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section P2: correct execution through rewritten path
+SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+ int4 
+------
+    4
+(1 row)
+
+SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+ numeric 
+---------
+       4
+(1 row)
+
+SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+ float8 
+--------
+      4
+(1 row)
+
+SELECT (test_json #> '{field6,f1}')::int4 FROM test_jsonb WHERE json_type = 'object';
+ int4 
+------
+    9
+(1 row)
+
+SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+ int8 
+------
+    9
+(1 row)
+
+SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+ bool 
+------
+ t
+(1 row)
+
+-- array index in path
+SELECT ('{"a":[10,20,30]}'::jsonb #> '{a,1}')::int4;
+ int4 
+------
+   20
+(1 row)
+
+-- Section P3: NULL semantics
+SELECT (test_json #> '{nonexistent}')::int4 FROM test_jsonb WHERE json_type = 'object';  -- missing key
+ int4 
+------
+     
+(1 row)
+
+SELECT (test_json #> '{field3}')::numeric FROM test_jsonb WHERE json_type = 'object';  -- JSON null leaf
+ numeric 
+---------
+        
+(1 row)
+
+SELECT (test_json #> '{field6,missing}')::int4 FROM test_jsonb WHERE json_type = 'object';  -- missing nested key
+ int4 
+------
+     
+(1 row)
+
+-- null in path array
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY[NULL]::text[]);
+ jsonb_extract_path_int4 
+-------------------------
+                        
+(1 row)
+
+-- empty path on container root produces cast error
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, '{}'::text[]);
+ERROR:  cannot cast jsonb array or object to type integer
+-- Section P4: type-mismatch errors
+SELECT (test_json #> '{field1}')::int4 FROM test_jsonb WHERE json_type = 'object';  -- string to int4
+ERROR:  cannot cast jsonb string to type integer
+SELECT (test_json #> '{field5}')::float8 FROM test_jsonb WHERE json_type = 'object';  -- array to float8
+ERROR:  cannot cast jsonb array or object to type double precision
+SELECT (test_json #> '{field6}')::int8 FROM test_jsonb WHERE json_type = 'object';  -- object to int8
+ERROR:  cannot cast jsonb array or object to type bigint
+-- Section P5: direct calls to extract-path typed extractor builtins
+SELECT jsonb_extract_path_int4('{"a":{"b":42}}'::jsonb, ARRAY['a','b']);
+ jsonb_extract_path_int4 
+-------------------------
+                      42
+(1 row)
+
+SELECT jsonb_extract_path_int8('{"x":99}'::jsonb, ARRAY['x']);
+ jsonb_extract_path_int8 
+-------------------------
+                      99
+(1 row)
+
+SELECT jsonb_extract_path_float8('{"a":3.14}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_float8 
+---------------------------
+                      3.14
+(1 row)
+
+SELECT jsonb_extract_path_numeric('{"a":1.23}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_numeric 
+----------------------------
+                       1.23
+(1 row)
+
+SELECT jsonb_extract_path_bool('{"a":true}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_bool 
+-------------------------
+ t
+(1 row)
+
+-- direct calls: missing path
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY['b']);
+ jsonb_extract_path_int4 
+-------------------------
+                        
+(1 row)
+
+-- direct calls: type-mismatch
+SELECT jsonb_extract_path_int4('{"a":"text"}'::jsonb, ARRAY['a']);
+ERROR:  cannot cast jsonb string to type integer
+-- direct calls: array index in path
+SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
+ jsonb_extract_path_int4 
+-------------------------
+                      20
+(1 row)
+
+-- direct calls: int2 and float4
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+ jsonb_extract_path_int2 
+-------------------------
+                       7
+(1 row)
+
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_float4 
+---------------------------
+                      3.14
+(1 row)
+
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
  ?column? 
 ----------
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 69c21776f84..11191fd0f82 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -358,6 +358,67 @@ SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
 SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
 
 
+-- Optimized typed extraction: extract-path family
+-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
+-- into direct typed extractor calls for the same target types.
+
+-- Section P1: planner rewrite verification (operator form #>)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+-- Section P1b: planner rewrite verification (direct function-call form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field6', 'f1'))::int4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section P1c: planner rewrite verification (VARIADIC ARRAY form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, VARIADIC ARRAY['field6','f1']))::int4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section P2: correct execution through rewritten path
+SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field6,f1}')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+-- array index in path
+SELECT ('{"a":[10,20,30]}'::jsonb #> '{a,1}')::int4;
+
+-- Section P3: NULL semantics
+SELECT (test_json #> '{nonexistent}')::int4 FROM test_jsonb WHERE json_type = 'object';  -- missing key
+SELECT (test_json #> '{field3}')::numeric FROM test_jsonb WHERE json_type = 'object';  -- JSON null leaf
+SELECT (test_json #> '{field6,missing}')::int4 FROM test_jsonb WHERE json_type = 'object';  -- missing nested key
+-- null in path array
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY[NULL]::text[]);
+-- empty path on container root produces cast error
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, '{}'::text[]);
+
+-- Section P4: type-mismatch errors
+SELECT (test_json #> '{field1}')::int4 FROM test_jsonb WHERE json_type = 'object';  -- string to int4
+SELECT (test_json #> '{field5}')::float8 FROM test_jsonb WHERE json_type = 'object';  -- array to float8
+SELECT (test_json #> '{field6}')::int8 FROM test_jsonb WHERE json_type = 'object';  -- object to int8
+
+-- Section P5: direct calls to extract-path typed extractor builtins
+SELECT jsonb_extract_path_int4('{"a":{"b":42}}'::jsonb, ARRAY['a','b']);
+SELECT jsonb_extract_path_int8('{"x":99}'::jsonb, ARRAY['x']);
+SELECT jsonb_extract_path_float8('{"a":3.14}'::jsonb, ARRAY['a']);
+SELECT jsonb_extract_path_numeric('{"a":1.23}'::jsonb, ARRAY['a']);
+SELECT jsonb_extract_path_bool('{"a":true}'::jsonb, ARRAY['a']);
+-- direct calls: missing path
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY['b']);
+-- direct calls: type-mismatch
+SELECT jsonb_extract_path_int4('{"a":"text"}'::jsonb, ARRAY['a']);
+-- direct calls: array index in path
+SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
+-- direct calls: int2 and float4
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+
+
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object';
-- 
2.52.0