v5-0002-jsonb-optimize-array-element-casts-to-scalar-type.patch

From 9c8ab19b7cdceb70a642013acc866887ce6ccb0d Mon Sep 17 00:00:00 2001
From: Haibo Yan <haibo.yan@apple.com>
Date: Wed, 8 Apr 2026 00:49:15 -0700
Subject: [PATCH v5 2/5] jsonb: optimize array-element casts to scalar types

Extend the existing support-function rewrite to jsonb array-element
extraction, including both -> integer and single-index subscripting.

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       | 154 +++++++++-----
 src/backend/utils/adt/jsonfuncs.c   |  67 ++++++
 src/include/catalog/pg_proc.dat     |  28 +++
 src/test/regress/expected/jsonb.out | 308 ++++++++++++++++++++++++++++
 src/test/regress/sql/jsonb.sql      |  85 ++++++++
 5 files changed, 593 insertions(+), 49 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index c85d4882f22..5abb51d47da 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1826,15 +1826,14 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
  * prosupport on the jsonb_numeric, jsonb_bool, jsonb_int4, jsonb_int8, and
  * jsonb_float8 catalog entries.
  *
- * When the sole argument to the cast is a jsonb_object_field() call (the ->
- * operator), we replace the two-step cast(extract(...)) expression with a
- * single typed extractor that reads the scalar directly from the in-memory
- * JsonbValue, avoiding a round-trip through JsonbValueToJsonb.
+ * When the sole argument to the cast is a jsonb extraction call, we replace
+ * the two-step cast(extract(...)) expression with a single typed extractor
+ * that reads the scalar directly from the in-memory JsonbValue, avoiding a
+ * round-trip through JsonbValueToJsonb.
  *
- * For example, (j -> 'a')::numeric is parsed as:
- *   jsonb_numeric(jsonb_object_field(j, 'a'))
- * and is rewritten to:
- *   jsonb_object_field_numeric(j, 'a')
+ * Supported extraction families:
+ *   - jsonb_object_field(j, 'key')  /  j -> 'key'  /  j['key']
+ *   - jsonb_array_element(j, idx)   /  j -> idx    /  j[idx]
  */
 Datum
 jsonb_cast_support(PG_FUNCTION_ARGS)
@@ -1886,11 +1885,12 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
 		{
 			SubscriptingRef *sbsref = (SubscriptingRef *) arg;
 			Node	   *subscript;
+			Oid			subscript_type;
 
 			/*
-			 * Only handle the narrow case equivalent to object-field
-			 * extraction: a single text-typed subscript on a jsonb
-			 * container, with no slice and no assignment.
+			 * Handle single-subscript jsonb access with no slice and no
+			 * assignment.  Text subscripts map to object-field extraction;
+			 * int4 subscripts map to array-element extraction.
 			 */
 			if (sbsref->refcontainertype != JSONBOID)
 				PG_RETURN_POINTER(NULL);
@@ -1902,57 +1902,113 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
 				PG_RETURN_POINTER(NULL);
 
 			subscript = (Node *) linitial(sbsref->refupperindexpr);
-			if (exprType(subscript) != TEXTOID)
+			subscript_type = exprType(subscript);
+
+			if (subscript_type == TEXTOID)
+			{
+				inner_funcid = F_JSONB_OBJECT_FIELD;
+				inner_args = list_make2(sbsref->refexpr, subscript);
+			}
+			else if (subscript_type == INT4OID)
+			{
+				inner_funcid = F_JSONB_ARRAY_ELEMENT;
+				inner_args = list_make2(sbsref->refexpr, subscript);
+			}
+			else
 				PG_RETURN_POINTER(NULL);
 
-			inner_funcid = F_JSONB_OBJECT_FIELD;
-			inner_args = list_make2(sbsref->refexpr, subscript);
 			location = exprLocation(arg);
 		}
 		else
 			PG_RETURN_POINTER(NULL);
 
-		/* Only rewrite jsonb_object_field(jsonb, text); verify arity too */
-		if (inner_funcid != F_JSONB_OBJECT_FIELD)
-			PG_RETURN_POINTER(NULL);
+		/*
+		 * Verify the inner extraction function and map the outer cast to the
+		 * corresponding typed extractor.  Each supported extraction family
+		 * has its own set of typed rewrite targets.
+		 */
 		if (list_length(inner_args) != 2)
 			PG_RETURN_POINTER(NULL);
 
-		/* Map the outer cast to the corresponding typed extractor */
-		if (fexpr->funcid == F_NUMERIC_JSONB)
-		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_NUMERIC;
-			replacement_rettype = NUMERICOID;
-		}
-		else if (fexpr->funcid == F_BOOL_JSONB)
-		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_BOOL;
-			replacement_rettype = BOOLOID;
-		}
-		else if (fexpr->funcid == F_INT4_JSONB)
+		if (inner_funcid == F_JSONB_OBJECT_FIELD)
 		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_INT4;
-			replacement_rettype = INT4OID;
-		}
-		else if (fexpr->funcid == F_INT8_JSONB)
-		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_INT8;
-			replacement_rettype = INT8OID;
-		}
-		else if (fexpr->funcid == F_FLOAT8_JSONB)
-		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_FLOAT8;
-			replacement_rettype = FLOAT8OID;
-		}
-		else if (fexpr->funcid == F_INT2_JSONB)
-		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_INT2;
-			replacement_rettype = INT2OID;
+			if (fexpr->funcid == F_NUMERIC_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_NUMERIC;
+				replacement_rettype = NUMERICOID;
+			}
+			else if (fexpr->funcid == F_BOOL_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_BOOL;
+				replacement_rettype = BOOLOID;
+			}
+			else if (fexpr->funcid == F_INT4_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_INT4;
+				replacement_rettype = INT4OID;
+			}
+			else if (fexpr->funcid == F_INT8_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_INT8;
+				replacement_rettype = INT8OID;
+			}
+			else if (fexpr->funcid == F_FLOAT8_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_FLOAT8;
+				replacement_rettype = FLOAT8OID;
+			}
+			else if (fexpr->funcid == F_INT2_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_INT2;
+				replacement_rettype = INT2OID;
+			}
+			else if (fexpr->funcid == F_FLOAT4_JSONB)
+			{
+				replacement_funcid = F_JSONB_OBJECT_FIELD_FLOAT4;
+				replacement_rettype = FLOAT4OID;
+			}
+			else
+				PG_RETURN_POINTER(NULL);
 		}
-		else if (fexpr->funcid == F_FLOAT4_JSONB)
+		else if (inner_funcid == F_JSONB_ARRAY_ELEMENT)
 		{
-			replacement_funcid = F_JSONB_OBJECT_FIELD_FLOAT4;
-			replacement_rettype = FLOAT4OID;
+			if (fexpr->funcid == F_NUMERIC_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_NUMERIC;
+				replacement_rettype = NUMERICOID;
+			}
+			else if (fexpr->funcid == F_BOOL_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_BOOL;
+				replacement_rettype = BOOLOID;
+			}
+			else if (fexpr->funcid == F_INT4_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_INT4;
+				replacement_rettype = INT4OID;
+			}
+			else if (fexpr->funcid == F_INT8_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_INT8;
+				replacement_rettype = INT8OID;
+			}
+			else if (fexpr->funcid == F_FLOAT8_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_FLOAT8;
+				replacement_rettype = FLOAT8OID;
+			}
+			else if (fexpr->funcid == F_INT2_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_INT2;
+				replacement_rettype = INT2OID;
+			}
+			else if (fexpr->funcid == F_FLOAT4_JSONB)
+			{
+				replacement_funcid = F_JSONB_ARRAY_ELEMENT_FLOAT4;
+				replacement_rettype = FLOAT4OID;
+			}
+			else
+				PG_RETURN_POINTER(NULL);
 		}
 		else
 			PG_RETURN_POINTER(NULL);
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index c260577d895..cc9c51c5cbf 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -1114,6 +1114,73 @@ DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_float8, jsonb_value_to_float8
 DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_int2, jsonb_value_to_int2_datum)
 DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_float4, jsonb_value_to_float4_datum)
 
+/*
+ * Look up an element by index in a jsonb array and return the JsonbValue,
+ * or NULL.  Returns NULL (without error) when the input is not an array,
+ * the index is out of range, or the value is JSON null.  Handles negative
+ * indices the same way as jsonb_array_element().
+ */
+static JsonbValue *
+jsonb_array_element_lookup(Jsonb *jb, int32 element)
+{
+	JsonbValue *v;
+
+	if (!JB_ROOT_IS_ARRAY(jb))
+		return NULL;
+
+	/* Handle negative subscript */
+	if (element < 0)
+	{
+		uint32		nelements = JB_ROOT_COUNT(jb);
+
+		if (pg_abs_s32(element) > nelements)
+			return NULL;
+		else
+			element += nelements;
+	}
+
+	v = getIthJsonbValueFromContainer(&jb->root, element);
+
+	/* Missing index or JSON null both map to SQL NULL */
+	if (v == NULL || v->type == jbvNull)
+		return NULL;
+
+	return v;
+}
+
+/*
+ * Thin-wrapper macro for the jsonb_array_element_<type> extractor family.
+ * Same pattern as DEFINE_JSONB_OBJECT_FIELD_TYPED but for array elements.
+ */
+#define DEFINE_JSONB_ARRAY_ELEMENT_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0); \
+	int32		element = PG_GETARG_INT32(1); \
+	JsonbValue *v; \
+	Datum		result; \
+\
+	v = jsonb_array_element_lookup(jb, element); \
+	if (v == NULL) \
+	{ \
+		PG_FREE_IF_COPY(jb, 0); \
+		PG_RETURN_NULL(); \
+	} \
+\
+	result = convfn(v); \
+	PG_FREE_IF_COPY(jb, 0); \
+	return result; \
+}
+
+DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_float8, jsonb_value_to_float8_datum)
+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)
+
 Datum
 json_array_element(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 406e08dffdd..ad752a0a9e0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12807,5 +12807,33 @@
   proname => 'jsonb_object_field_float4', prorettype => 'float4',
   proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
   prosrc => 'jsonb_object_field_float4' },
+{ oid => '9960', descr => 'extract numeric from jsonb array by element index',
+  proname => 'jsonb_array_element_numeric', prorettype => 'numeric',
+  proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
+  prosrc => 'jsonb_array_element_numeric' },
+{ oid => '9961', descr => 'extract boolean from jsonb array by element index',
+  proname => 'jsonb_array_element_bool', prorettype => 'bool',
+  proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
+  prosrc => 'jsonb_array_element_bool' },
+{ oid => '9962', descr => 'extract int4 from jsonb array by element index',
+  proname => 'jsonb_array_element_int4', prorettype => 'int4',
+  proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
+  prosrc => 'jsonb_array_element_int4' },
+{ oid => '9963', descr => 'extract int8 from jsonb array by element index',
+  proname => 'jsonb_array_element_int8', prorettype => 'int8',
+  proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
+  prosrc => 'jsonb_array_element_int8' },
+{ oid => '9964', descr => 'extract float8 from jsonb array by element index',
+  proname => 'jsonb_array_element_float8', prorettype => 'float8',
+  proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
+  prosrc => 'jsonb_array_element_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}',
+  prosrc => 'jsonb_array_element_int2' },
+{ oid => '9983', descr => 'extract float4 from jsonb array by element index',
+  proname => 'jsonb_array_element_float4', prorettype => 'float4',
+  proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
+  prosrc => 'jsonb_array_element_float4' },
 
 ]
\ No newline at end of file
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index c31fb120dd8..36bc82095be 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -939,6 +939,314 @@ ERROR:  integer out of range
 -- direct calls: int2 overflow
 SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
 ERROR:  smallint out of range
+-- Optimized typed extraction: array-element family
+-- The planner rewrites (j->idx)::type and (j[idx])::type into direct
+-- typed extractor calls for the same target types as the object-field family.
+-- Create a small fixture with typed array elements for testing
+CREATE TEMP TABLE test_jsonb_arr (test_arr jsonb);
+INSERT INTO test_jsonb_arr VALUES ('[10, 2.5, true, null, "hello", [1,2], {"k":1}]');
+-- Section A1: planner rewrite verification (array element, operator form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::numeric FROM test_jsonb_arr;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_numeric(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int4 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int4(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float8 FROM test_jsonb_arr;
+                    QUERY PLAN                     
+---------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_float8(test_arr, 1)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_bool(test_arr, 2)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int8(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int2(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+                    QUERY PLAN                     
+---------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_float4(test_arr, 1)
+(2 rows)
+
+-- Section A1b: planner rewrite verification (array subscripting form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::numeric FROM test_jsonb_arr;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_numeric(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int4(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[1])::float8 FROM test_jsonb_arr;
+                    QUERY PLAN                     
+---------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_float8(test_arr, 1)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[2])::bool FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_bool(test_arr, 2)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int8 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int8(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int2 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int2(test_arr, 0)
+(2 rows)
+
+-- Section A1c: planner rewrite verification (direct function call form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_array_element(test_arr, 0))::int4 FROM test_jsonb_arr;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+   Output: jsonb_array_element_int4(test_arr, 0)
+(2 rows)
+
+-- Section A2: correct execution through rewritten path
+SELECT (test_arr -> 0)::int4 FROM test_jsonb_arr;
+ int4 
+------
+   10
+(1 row)
+
+SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
+ int8 
+------
+   10
+(1 row)
+
+SELECT (test_arr -> 0)::numeric FROM test_jsonb_arr;
+ numeric 
+---------
+      10
+(1 row)
+
+SELECT (test_arr -> 1)::float8 FROM test_jsonb_arr;
+ float8 
+--------
+    2.5
+(1 row)
+
+SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
+ bool 
+------
+ t
+(1 row)
+
+SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+ int2 
+------
+   10
+(1 row)
+
+SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+ float4 
+--------
+    2.5
+(1 row)
+
+-- Section A2b: correct execution through subscripting
+SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
+ test_arr 
+----------
+       10
+(1 row)
+
+SELECT (test_arr[1])::float8 FROM test_jsonb_arr;
+ test_arr 
+----------
+      2.5
+(1 row)
+
+SELECT (test_arr[2])::bool FROM test_jsonb_arr;
+ test_arr 
+----------
+ t
+(1 row)
+
+-- Section A3: NULL semantics
+SELECT (test_arr -> 99)::int4 FROM test_jsonb_arr;  -- out of range
+ int4 
+------
+     
+(1 row)
+
+SELECT (test_arr -> 3)::numeric FROM test_jsonb_arr;  -- JSON null element
+ numeric 
+---------
+        
+(1 row)
+
+SELECT (test_arr -> -1)::int4 FROM test_jsonb_arr;  -- negative: last element is object, wrong type would error; use -4 for null
+ERROR:  cannot cast jsonb array or object to type integer
+SELECT (test_arr -> -4)::numeric FROM test_jsonb_arr;  -- negative index pointing to null element
+ numeric 
+---------
+        
+(1 row)
+
+SELECT ('{"k":1}'::jsonb -> 0)::int4;  -- non-array input
+ int4 
+------
+     
+(1 row)
+
+SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr;  -- out of range, int2
+ int2 
+------
+     
+(1 row)
+
+SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr;  -- JSON null element, float4
+ float4 
+--------
+       
+(1 row)
+
+-- Section A3b: NULL through subscripting
+SELECT (test_arr[99])::float8 FROM test_jsonb_arr;  -- out of range
+ test_arr 
+----------
+         
+(1 row)
+
+SELECT (test_arr[3])::int8 FROM test_jsonb_arr;  -- JSON null element
+ test_arr 
+----------
+         
+(1 row)
+
+-- Section A4: type-mismatch errors
+SELECT (test_arr -> 4)::int4 FROM test_jsonb_arr;  -- string to int4
+ERROR:  cannot cast jsonb string to type integer
+SELECT (test_arr -> 4)::float8 FROM test_jsonb_arr;  -- string to float8
+ERROR:  cannot cast jsonb string to type double precision
+SELECT (test_arr -> 5)::numeric FROM test_jsonb_arr;  -- array container to numeric
+ERROR:  cannot cast jsonb array or object to type numeric
+SELECT (test_arr -> 6)::int8 FROM test_jsonb_arr;  -- object container to int8
+ERROR:  cannot cast jsonb array or object to type bigint
+SELECT (test_arr -> 2)::int4 FROM test_jsonb_arr;  -- bool to int4
+ERROR:  cannot cast jsonb boolean to type integer
+SELECT (test_arr -> 4)::int2 FROM test_jsonb_arr;  -- string to int2
+ERROR:  cannot cast jsonb string to type smallint
+SELECT (test_arr -> 4)::float4 FROM test_jsonb_arr;  -- string to float4
+ERROR:  cannot cast jsonb string to type real
+-- Section A4b: error through subscripting
+SELECT (test_arr[4])::int8 FROM test_jsonb_arr;  -- string to int8
+ERROR:  cannot cast jsonb string to type bigint
+-- Section A5: direct calls to array-element typed extractor builtins
+SELECT jsonb_array_element_int4('[10, 20, 30]'::jsonb, 0);
+ jsonb_array_element_int4 
+--------------------------
+                       10
+(1 row)
+
+SELECT jsonb_array_element_int8('[10, 20, 30]'::jsonb, 1);
+ jsonb_array_element_int8 
+--------------------------
+                       20
+(1 row)
+
+SELECT jsonb_array_element_float8('[1.5, 2.5]'::jsonb, 0);
+ jsonb_array_element_float8 
+----------------------------
+                        1.5
+(1 row)
+
+SELECT jsonb_array_element_numeric('[3.14]'::jsonb, 0);
+ jsonb_array_element_numeric 
+-----------------------------
+                        3.14
+(1 row)
+
+SELECT jsonb_array_element_bool('[true, false]'::jsonb, 1);
+ jsonb_array_element_bool 
+--------------------------
+ f
+(1 row)
+
+-- direct calls: NULL semantics
+SELECT jsonb_array_element_int4('[1, 2]'::jsonb, 5);  -- out of range
+ jsonb_array_element_int4 
+--------------------------
+                         
+(1 row)
+
+SELECT jsonb_array_element_int4('[1, null, 3]'::jsonb, 1);  -- JSON null
+ jsonb_array_element_int4 
+--------------------------
+                         
+(1 row)
+
+SELECT jsonb_array_element_float8('{"a":1}'::jsonb, 0);  -- non-array
+ jsonb_array_element_float8 
+----------------------------
+                           
+(1 row)
+
+-- direct calls: type-mismatch errors
+SELECT jsonb_array_element_int4('["text"]'::jsonb, 0);
+ERROR:  cannot cast jsonb string to type integer
+SELECT jsonb_array_element_int8('[true]'::jsonb, 0);
+ERROR:  cannot cast jsonb boolean to type bigint
+SELECT jsonb_array_element_float8('[[1,2]]'::jsonb, 0);  -- container to float8
+ERROR:  cannot cast jsonb array or object to type double precision
+SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
+ jsonb_array_element_int2 
+--------------------------
+                       10
+(1 row)
+
+SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
+ jsonb_array_element_float4 
+----------------------------
+                        2.5
+(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 61c567110fc..69c21776f84 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -273,6 +273,91 @@ SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
 -- direct calls: int2 overflow
 SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
 
+-- Optimized typed extraction: array-element family
+-- The planner rewrites (j->idx)::type and (j[idx])::type into direct
+-- typed extractor calls for the same target types as the object-field family.
+
+-- Create a small fixture with typed array elements for testing
+CREATE TEMP TABLE test_jsonb_arr (test_arr jsonb);
+INSERT INTO test_jsonb_arr VALUES ('[10, 2.5, true, null, "hello", [1,2], {"k":1}]');
+
+-- Section A1: planner rewrite verification (array element, operator form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::numeric FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int4 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float8 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+
+-- Section A1b: planner rewrite verification (array subscripting form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::numeric FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[1])::float8 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[2])::bool FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int8 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int2 FROM test_jsonb_arr;
+
+-- Section A1c: planner rewrite verification (direct function call form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_array_element(test_arr, 0))::int4 FROM test_jsonb_arr;
+
+-- Section A2: correct execution through rewritten path
+SELECT (test_arr -> 0)::int4 FROM test_jsonb_arr;
+SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
+SELECT (test_arr -> 0)::numeric FROM test_jsonb_arr;
+SELECT (test_arr -> 1)::float8 FROM test_jsonb_arr;
+SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
+SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+
+-- Section A2b: correct execution through subscripting
+SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
+SELECT (test_arr[1])::float8 FROM test_jsonb_arr;
+SELECT (test_arr[2])::bool FROM test_jsonb_arr;
+
+-- Section A3: NULL semantics
+SELECT (test_arr -> 99)::int4 FROM test_jsonb_arr;  -- out of range
+SELECT (test_arr -> 3)::numeric FROM test_jsonb_arr;  -- JSON null element
+SELECT (test_arr -> -1)::int4 FROM test_jsonb_arr;  -- negative: last element is object, wrong type would error; use -4 for null
+SELECT (test_arr -> -4)::numeric FROM test_jsonb_arr;  -- negative index pointing to null element
+SELECT ('{"k":1}'::jsonb -> 0)::int4;  -- non-array input
+SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr;  -- out of range, int2
+SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr;  -- JSON null element, float4
+
+-- Section A3b: NULL through subscripting
+SELECT (test_arr[99])::float8 FROM test_jsonb_arr;  -- out of range
+SELECT (test_arr[3])::int8 FROM test_jsonb_arr;  -- JSON null element
+
+-- Section A4: type-mismatch errors
+SELECT (test_arr -> 4)::int4 FROM test_jsonb_arr;  -- string to int4
+SELECT (test_arr -> 4)::float8 FROM test_jsonb_arr;  -- string to float8
+SELECT (test_arr -> 5)::numeric FROM test_jsonb_arr;  -- array container to numeric
+SELECT (test_arr -> 6)::int8 FROM test_jsonb_arr;  -- object container to int8
+SELECT (test_arr -> 2)::int4 FROM test_jsonb_arr;  -- bool to int4
+SELECT (test_arr -> 4)::int2 FROM test_jsonb_arr;  -- string to int2
+SELECT (test_arr -> 4)::float4 FROM test_jsonb_arr;  -- string to float4
+
+-- Section A4b: error through subscripting
+SELECT (test_arr[4])::int8 FROM test_jsonb_arr;  -- string to int8
+
+-- Section A5: direct calls to array-element typed extractor builtins
+SELECT jsonb_array_element_int4('[10, 20, 30]'::jsonb, 0);
+SELECT jsonb_array_element_int8('[10, 20, 30]'::jsonb, 1);
+SELECT jsonb_array_element_float8('[1.5, 2.5]'::jsonb, 0);
+SELECT jsonb_array_element_numeric('[3.14]'::jsonb, 0);
+SELECT jsonb_array_element_bool('[true, false]'::jsonb, 1);
+-- direct calls: NULL semantics
+SELECT jsonb_array_element_int4('[1, 2]'::jsonb, 5);  -- out of range
+SELECT jsonb_array_element_int4('[1, null, 3]'::jsonb, 1);  -- JSON null
+SELECT jsonb_array_element_float8('{"a":1}'::jsonb, 0);  -- non-array
+-- direct calls: type-mismatch errors
+SELECT jsonb_array_element_int4('["text"]'::jsonb, 0);
+SELECT jsonb_array_element_int8('[true]'::jsonb, 0);
+SELECT jsonb_array_element_float8('[[1,2]]'::jsonb, 0);  -- container to float8
+SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
+SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
+
+
 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