public inbox for [email protected]  

help / color / mirror / Atom feed

From: Haibo Yan <[email protected]>
To: Andy Fan <[email protected]>
Cc: Dmitry Dolgov <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: jian he <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Fri, 10 Apr 2026 14:48:32 -0700
Message-ID: <CABXr29G35c6Qo-Ab4EpH24gHS+ejjJGfhmW3nQ1eEK+Rgt_9Wg@mail.gmail.com> (raw)
In-Reply-To: <CABXr29ETPyi1Tt+X29oUa6Q9AmT3j7RZSdsp12H_6EzGw7RqnQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
	<[email protected]>
	<mmilfltxeqgajpi4zok2v2moudot6bhdmwsqgjgnymz3nvkvng@k3caignaldcp>
	<[email protected]>
	<cudr6wao4hz2otlycx2z2equkigbjxslxvzqb3ejfc4amroheo@frqbvffcgqnu>
	<[email protected]>
	<[email protected]>
	<CABXr29GhnWxrGXD2YijYSDxwObGLqOsf8gvfzrFu=UcYU71XYA@mail.gmail.com>
	<[email protected]>
	<CABXr29HN04CqHBGZ_k6_jQmCL5FO59tDE4PUL9U56Rpu=d4=0A@mail.gmail.com>
	<CABXr29ETPyi1Tt+X29oUa6Q9AmT3j7RZSdsp12H_6EzGw7RqnQ@mail.gmail.com>

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

> On Tue, Apr 7, 2026 at 6:21 PM Haibo Yan <[email protected]> wrote:
>
>> On Tue, Apr 7, 2026 at 5:00 PM Andy Fan <[email protected]> wrote:
>>
>>> Haibo Yan <[email protected]> writes:
>>>
>>> Hi Haibo,
>>>
>>> > I agree that if this approach is extended to the full matrix naively,
>>> > 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
>>>
>> 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
>>
>>
>>    -
>>
>>    jsonb_object_field / -> / equivalent subscripting form
>>    -
>>
>>    casts to numeric and bool
>>    -
>>
>>    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
>>
>> Hi all,
>
> 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
>

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


Attachments:

  [application/octet-stream] v5-0001-jsonb-optimize-object-field-casts-to-scalar-types.patch (43.6K, 3-v5-0001-jsonb-optimize-object-field-casts-to-scalar-types.patch)
  download | inline diff:
From 2735e9da9b07dd14b0c984b98c317c30e0bff8bc Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Thu, 2 Apr 2026 21:22:54 -0700
Subject: [PATCH v5 1/5] jsonb: optimize object-field casts to scalar types

Extend the existing support-function rewrite for jsonb object-field
extraction, including jsonb_object_field(), ->, and key subscripting.

This keeps ordinary SQL syntax unchanged and rewrites supported casts
directly to explicit typed extractor functions for numeric, bool,
int2, int4, int8, float4, and float8.

Co-authored-by: Andy Fan <[email protected]>
---
 src/backend/utils/adt/jsonb.c       | 154 ++++++++-
 src/backend/utils/adt/jsonfuncs.c   | 195 ++++++++++-
 src/include/catalog/pg_proc.dat     |  55 +++-
 src/test/regress/expected/jsonb.out | 485 +++++++++++++++++++++++++++-
 src/test/regress/sql/jsonb.sql      | 120 ++++++-
 5 files changed, 995 insertions(+), 14 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 864c5ac1c85..c85d4882f22 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,6 +17,9 @@
 #include "funcapi.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
 #include "utils/json.h"
@@ -1816,6 +1819,155 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
 	return (Datum) 0;
 }
 
+/*
+ * jsonb_cast_support()
+ *
+ * Planner support function for jsonb-to-scalar cast functions, attached via
+ * 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.
+ *
+ * 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')
+ */
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+	Node	   *rawreq = (Node *) PG_GETARG_POINTER(0);
+	Node	   *ret = NULL;
+
+	if (IsA(rawreq, SupportRequestSimplify))
+	{
+		SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+		FuncExpr   *fexpr = req->fcall;
+		Node	   *arg;
+		Oid			inner_funcid;
+		List	   *inner_args;
+		int			location;
+		Oid			replacement_funcid;
+		Oid			replacement_rettype;
+		FuncExpr   *newfexpr;
+
+		/* The cast function must have exactly one argument */
+		if (list_length(fexpr->args) != 1)
+			PG_RETURN_POINTER(NULL);
+
+		arg = (Node *) linitial(fexpr->args);
+
+		/*
+		 * Identify the inner extraction expression.  It may appear as a
+		 * FuncExpr, an OpExpr, or a SubscriptingRef, depending on how the
+		 * expression is represented at this point.  Accept the supported
+		 * 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 if (IsA(arg, SubscriptingRef))
+		{
+			SubscriptingRef *sbsref = (SubscriptingRef *) arg;
+			Node	   *subscript;
+
+			/*
+			 * 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.
+			 */
+			if (sbsref->refcontainertype != JSONBOID)
+				PG_RETURN_POINTER(NULL);
+			if (list_length(sbsref->refupperindexpr) != 1)
+				PG_RETURN_POINTER(NULL);
+			if (sbsref->reflowerindexpr != NIL)
+				PG_RETURN_POINTER(NULL);
+			if (sbsref->refassgnexpr != NULL)
+				PG_RETURN_POINTER(NULL);
+
+			subscript = (Node *) linitial(sbsref->refupperindexpr);
+			if (exprType(subscript) != TEXTOID)
+				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);
+		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)
+		{
+			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);
+
+		/* Build the replacement function call */
+		newfexpr = makeFuncExpr(replacement_funcid, replacement_rettype,
+								inner_args, InvalidOid, InvalidOid,
+								COERCE_EXPLICIT_CALL);
+		newfexpr->location = location;
+		ret = (Node *) newfexpr;
+	}
+
+	PG_RETURN_POINTER(ret);
+}
+
 Datum
 jsonb_bool(PG_FUNCTION_ARGS)
 {
@@ -2033,4 +2185,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 97cc3d60340..c260577d895 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -921,6 +921,199 @@ jsonb_object_field_text(PG_FUNCTION_ARGS)
 	PG_RETURN_NULL();
 }
 
+/*
+ * Typed scalar extraction from jsonb object fields.
+ *
+ * These functions extract a typed scalar directly from the in-memory
+ * JsonbValue found by key lookup, skipping the intermediate Jsonb
+ * serialization that occurs with the unoptimized cast-over-extraction path.
+ *
+ * They live here alongside jsonb_object_field() because they share the
+ * same key-lookup logic (getKeyJsonValueFromContainer).
+ *
+ * Registered in pg_proc as ordinary SQL-callable builtins; also serve as
+ * planner rewrite targets for jsonb_cast_support() in jsonb.c.
+ *
+ * NULL semantics match the existing cast path: a missing key or a JSON
+ * null value both produce SQL NULL; a type mismatch raises ERROR.
+ */
+
+/*
+ * Look up a key in a jsonb object and return the JsonbValue, or NULL.
+ * Returns NULL (without error) when the input is not an object, the key
+ * is absent, or the value is JSON null.
+ */
+static JsonbValue *
+jsonb_object_field_lookup(Jsonb *jb, text *key, JsonbValue *vbuf)
+{
+	JsonbValue *v;
+
+	if (!JB_ROOT_IS_OBJECT(jb))
+		return NULL;
+
+	v = getKeyJsonValueFromContainer(&jb->root,
+									 VARDATA_ANY(key),
+									 VARSIZE_ANY_EXHDR(key),
+									 vbuf);
+
+	/* Missing key or JSON null both map to SQL NULL */
+	if (v == NULL || v->type == jbvNull)
+		return NULL;
+
+	return v;
+}
+
+/*
+ * Raise a type-mismatch error for typed field extraction.
+ *
+ * The message wording matches cannotCastJsonbValue() in jsonb.c so that
+ * the optimized and unoptimized paths produce identical errors.
+ */
+static void
+jsonb_field_cast_error(JsonbValue *v, const char *sqltype)
+{
+	const char *jsontype;
+
+	switch (v->type)
+	{
+		case jbvNull:
+			jsontype = "null";
+			break;
+		case jbvString:
+			jsontype = "string";
+			break;
+		case jbvNumeric:
+			jsontype = "numeric";
+			break;
+		case jbvBool:
+			jsontype = "boolean";
+			break;
+		case jbvArray:
+			jsontype = "array";
+			break;
+		case jbvObject:
+			jsontype = "object";
+			break;
+		case jbvBinary:
+			jsontype = "array or object";
+			break;
+		default:
+			elog(ERROR, "unknown jsonb type: %d", (int) v->type);
+			jsontype = NULL;	/* keep compiler quiet */
+	}
+
+	ereport(ERROR,
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			 errmsg("cannot cast jsonb %s to type %s",
+					jsontype, sqltype)));
+}
+
+/*
+ * Per-type conversion helpers for typed field extraction.
+ *
+ * Each validates the expected JsonbValue type, raises a type-mismatch
+ * error (via jsonb_field_cast_error) if wrong, and returns the converted
+ * value as Datum.  These are the only place where conversion semantics
+ * live; the wrapper macro below is intentionally kept thin.
+ */
+static Datum
+jsonb_value_to_numeric_datum(JsonbValue *v)
+{
+	if (v->type != jbvNumeric)
+		jsonb_field_cast_error(v, "numeric");
+
+	return NumericGetDatum(DatumGetNumericCopy(NumericGetDatum(v->val.numeric)));
+}
+
+static Datum
+jsonb_value_to_bool_datum(JsonbValue *v)
+{
+	if (v->type != jbvBool)
+		jsonb_field_cast_error(v, "boolean");
+
+	return BoolGetDatum(v->val.boolean);
+}
+
+static Datum
+jsonb_value_to_int4_datum(JsonbValue *v)
+{
+	if (v->type != jbvNumeric)
+		jsonb_field_cast_error(v, "integer");
+
+	return DirectFunctionCall1(numeric_int4, NumericGetDatum(v->val.numeric));
+}
+
+static Datum
+jsonb_value_to_int8_datum(JsonbValue *v)
+{
+	if (v->type != jbvNumeric)
+		jsonb_field_cast_error(v, "bigint");
+
+	return DirectFunctionCall1(numeric_int8, NumericGetDatum(v->val.numeric));
+}
+
+static Datum
+jsonb_value_to_float8_datum(JsonbValue *v)
+{
+	if (v->type != jbvNumeric)
+		jsonb_field_cast_error(v, "double precision");
+
+	return DirectFunctionCall1(numeric_float8, NumericGetDatum(v->val.numeric));
+}
+
+static Datum
+jsonb_value_to_int2_datum(JsonbValue *v)
+{
+	if (v->type != jbvNumeric)
+		jsonb_field_cast_error(v, "smallint");
+
+	return DirectFunctionCall1(numeric_int2, NumericGetDatum(v->val.numeric));
+}
+
+static Datum
+jsonb_value_to_float4_datum(JsonbValue *v)
+{
+	if (v->type != jbvNumeric)
+		jsonb_field_cast_error(v, "real");
+
+	return DirectFunctionCall1(numeric_float4, NumericGetDatum(v->val.numeric));
+}
+
+/*
+ * Thin-wrapper macro for the jsonb_object_field_<type> extractor family.
+ * Reduces repetition: each wrapper does lookup, NULL handling, and delegates
+ * to a type-specific conversion helper that holds the actual semantics.
+ */
+#define DEFINE_JSONB_OBJECT_FIELD_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+	Jsonb	   *jb = PG_GETARG_JSONB_P(0); \
+	text	   *key = PG_GETARG_TEXT_PP(1); \
+	JsonbValue	vbuf; \
+	JsonbValue *v; \
+	Datum		result; \
+\
+	v = jsonb_object_field_lookup(jb, key, &vbuf); \
+	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_OBJECT_FIELD_TYPED(jsonb_object_field_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_OBJECT_FIELD_TYPED(jsonb_object_field_float8, jsonb_value_to_float8_datum)
+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)
+
 Datum
 json_array_element(PG_FUNCTION_ARGS)
 {
@@ -6167,4 +6360,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 fa9ae79082b..406e08dffdd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4798,25 +4798,30 @@
   prosrc => 'numeric_pg_lsn' },
 
 { oid => '3556', descr => 'convert jsonb to boolean',
-  proname => 'bool', prorettype => 'bool', proargtypes => 'jsonb',
+  proname => 'bool', prosupport => 'jsonb_cast_support', prorettype => 'bool', proargtypes => 'jsonb',
   prosrc => 'jsonb_bool' },
 { oid => '3449', descr => 'convert jsonb to numeric',
-  proname => 'numeric', prorettype => 'numeric', proargtypes => 'jsonb',
+  proname => 'numeric', prosupport => 'jsonb_cast_support', prorettype => 'numeric', proargtypes => 'jsonb',
   prosrc => 'jsonb_numeric' },
 { oid => '3450', descr => 'convert jsonb to int2',
-  proname => 'int2', prorettype => 'int2', proargtypes => 'jsonb',
+  proname => 'int2', prosupport => 'jsonb_cast_support',
+  prorettype => 'int2', proargtypes => 'jsonb',
   prosrc => 'jsonb_int2' },
 { oid => '3451', descr => 'convert jsonb to int4',
-  proname => 'int4', prorettype => 'int4', proargtypes => 'jsonb',
+  proname => 'int4', prosupport => 'jsonb_cast_support',
+  prorettype => 'int4', proargtypes => 'jsonb',
   prosrc => 'jsonb_int4' },
 { oid => '3452', descr => 'convert jsonb to int8',
-  proname => 'int8', prorettype => 'int8', proargtypes => 'jsonb',
+  proname => 'int8', prosupport => 'jsonb_cast_support',
+  prorettype => 'int8', proargtypes => 'jsonb',
   prosrc => 'jsonb_int8' },
 { oid => '3453', descr => 'convert jsonb to float4',
-  proname => 'float4', prorettype => 'float4', proargtypes => 'jsonb',
+  proname => 'float4', prosupport => 'jsonb_cast_support',
+  prorettype => 'float4', proargtypes => 'jsonb',
   prosrc => 'jsonb_float4' },
 { oid => '2580', descr => 'convert jsonb to float8',
-  proname => 'float8', prorettype => 'float8', proargtypes => 'jsonb',
+  proname => 'float8', prosupport => 'jsonb_cast_support',
+  prorettype => 'float8', proargtypes => 'jsonb',
   prosrc => 'jsonb_float8' },
 
 # formatting
@@ -12769,4 +12774,38 @@
   proname => 'hashoid8extended', prorettype => 'int8',
   proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
 
-]
+
+# jsonb cast optimization support functions
+{ oid => '9950', descr => 'planner support for jsonb casts',
+  proname => 'jsonb_cast_support', prorettype => 'internal',
+  proargtypes => 'internal', prosrc => 'jsonb_cast_support' },
+{ oid => '9953', descr => 'extract numeric from jsonb object by field name',
+  proname => 'jsonb_object_field_numeric', prorettype => 'numeric',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_numeric' },
+{ oid => '9954', descr => 'extract boolean from jsonb object by field name',
+  proname => 'jsonb_object_field_bool', prorettype => 'bool',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_bool' },
+{ oid => '9955', descr => 'extract int4 from jsonb object by field name',
+  proname => 'jsonb_object_field_int4', prorettype => 'int4',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_int4' },
+{ oid => '9956', descr => 'extract int8 from jsonb object by field name',
+  proname => 'jsonb_object_field_int8', prorettype => 'int8',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_int8' },
+{ oid => '9957', descr => 'extract float8 from jsonb object by field name',
+  proname => 'jsonb_object_field_float8', prorettype => 'float8',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_float8' },
+{ oid => '9980', descr => 'extract int2 from jsonb object by field name',
+  proname => 'jsonb_object_field_int2', prorettype => 'int2',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_int2' },
+{ oid => '9981', descr => 'extract float4 from jsonb object by field name',
+  proname => 'jsonb_object_field_float4', prorettype => 'float4',
+  proargtypes => 'jsonb text', proargnames => '{from_json,field_name}',
+  prosrc => 'jsonb_object_field_float4' },
+
+]
\ No newline at end of file
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4e2467852db..c31fb120dd8 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -458,7 +458,487 @@ CREATE TEMP TABLE test_jsonb (
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
 ('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}');
+-- Optimized typed extraction: the planner rewrites (j->'key')::type into a
+-- direct typed extractor call, currently for numeric, bool, int2, int4, int8,
+-- float4, float8.
+-- Section 1: planner rewrite verification (rewritten targets)
+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_object_field_numeric(test_json, 'field4'::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_object_field_bool(test_json, 'field7'::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_object_field_int4(test_json, 'field4'::text)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int8 FROM test_jsonb WHERE json_type = 'object';
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_object_field_int8(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_object_field_float8(test_json, 'field4'::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_object_field_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_object_field_float4(test_json, 'field4'::text)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section 1b: planner rewrite verification for subscripting syntax
+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_object_field_numeric(test_json, 'field4'::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_object_field_bool(test_json, 'field7'::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_object_field_int4(test_json, 'field4'::text)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int8 FROM test_jsonb WHERE json_type = 'object';
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+   Output: jsonb_object_field_int8(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_object_field_float8(test_json, 'field4'::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_object_field_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_object_field_float4(test_json, 'field4'::text)
+   Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section 2: correct execution through the rewritten path
+SELECT (test_json -> 'field4')::numeric FROM test_jsonb WHERE json_type = 'object';
+ numeric 
+---------
+       4
+(1 row)
+
+SELECT (test_json -> 'field7')::bool FROM test_jsonb WHERE json_type = 'object';
+ bool 
+------
+ t
+(1 row)
+
+SELECT (test_json -> 'field4')::int4 FROM test_jsonb WHERE json_type = 'object';
+ int4 
+------
+    4
+(1 row)
+
+SELECT (test_json -> 'field4')::int8 FROM test_jsonb WHERE json_type = 'object';
+ int8 
+------
+    4
+(1 row)
+
+SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object';
+ float8 
+--------
+      4
+(1 row)
+
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+ int2 
+------
+    4
+(1 row)
+
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4 
+--------
+      4
+(1 row)
+
+-- Section 2b: correct execution through subscripting syntax
+SELECT (test_json['field4'])::numeric FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+         4
+(1 row)
+
+SELECT (test_json['field7'])::bool FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+ t
+(1 row)
+
+SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+         4
+(1 row)
+
+SELECT (test_json['field4'])::int8 FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+         4
+(1 row)
+
+SELECT (test_json['field4'])::float8 FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+         4
+(1 row)
+
+SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+         4
+(1 row)
+
+SELECT (test_json['field4'])::float4 FROM test_jsonb WHERE json_type = 'object';
+ test_json 
+-----------
+         4
+(1 row)
+
+-- Section 3: NULL semantics (missing key, JSON null, non-object input)
+SELECT (test_json -> 'field3')::numeric FROM test_jsonb WHERE json_type = 'object';  -- JSON null
+ numeric 
+---------
+        
+(1 row)
+
+SELECT (test_json -> 'nonexistent')::numeric FROM test_jsonb WHERE json_type = 'object';  -- missing key
+ numeric 
+---------
+        
+(1 row)
+
+SELECT (test_json -> 'x')::numeric FROM test_jsonb WHERE json_type = 'array';  -- non-object
+ numeric 
+---------
+        
+(1 row)
+
+SELECT (test_json -> 'field3')::bool FROM test_jsonb WHERE json_type = 'object';  -- JSON null, bool path
+ bool 
+------
+ 
+(1 row)
+
+SELECT (test_json -> 'field3')::int4 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, int4 path
+ int4 
+------
+     
+(1 row)
+
+SELECT (test_json -> 'nonexistent')::int4 FROM test_jsonb WHERE json_type = 'object';  -- missing key, int4
+ int4 
+------
+     
+(1 row)
+
+SELECT (test_json -> 'field3')::float8 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, float8 path
+ float8 
+--------
+       
+(1 row)
+
+SELECT (test_json -> 'nonexistent')::float8 FROM test_jsonb WHERE json_type = 'object';  -- missing key, float8
+ float8 
+--------
+       
+(1 row)
+
+SELECT (test_json -> 'field3')::int2 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, int2 path
+ int2 
+------
+     
+(1 row)
+
+SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object';  -- missing key, int2
+ int2 
+------
+     
+(1 row)
+
+SELECT (test_json -> 'field3')::float4 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, float4 path
+ float4 
+--------
+       
+(1 row)
+
+SELECT (test_json -> 'nonexistent')::float4 FROM test_jsonb WHERE json_type = 'object';  -- missing key, float4
+ float4 
+--------
+       
+(1 row)
+
+-- Section 3b: NULL semantics through subscripting syntax
+SELECT (test_json['field3'])::numeric FROM test_jsonb WHERE json_type = 'object';  -- JSON null
+ test_json 
+-----------
+          
+(1 row)
+
+SELECT (test_json['nonexistent'])::numeric FROM test_jsonb WHERE json_type = 'object';  -- missing key
+ test_json 
+-----------
+          
+(1 row)
+
+SELECT (test_json['nonexistent'])::float8 FROM test_jsonb WHERE json_type = 'object';  -- missing key, float8
+ test_json 
+-----------
+          
+(1 row)
+
+-- Section 4: type-mismatch errors (scalar and container types)
+SELECT (test_json -> 'field1')::numeric FROM test_jsonb WHERE json_type = 'object';  -- string to numeric
+ERROR:  cannot cast jsonb string to type numeric
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';  -- string to bool
+ERROR:  cannot cast jsonb string to type boolean
+SELECT (test_json -> 'field5')::numeric FROM test_jsonb WHERE json_type = 'object';  -- array to numeric
+ERROR:  cannot cast jsonb array or object to type numeric
+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 -> 'field1')::int8 FROM test_jsonb WHERE json_type = 'object';  -- string to int8
+ERROR:  cannot cast jsonb string to type bigint
+SELECT (test_json -> 'field1')::float8 FROM test_jsonb WHERE json_type = 'object';  -- string to float8
+ERROR:  cannot cast jsonb string to type double precision
+SELECT (test_json -> 'field5')::int4 FROM test_jsonb WHERE json_type = 'object';  -- array to int4
+ERROR:  cannot cast jsonb array or object 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 -> 'field1')::int2 FROM test_jsonb WHERE json_type = 'object';  -- string to int2
+ERROR:  cannot cast jsonb string to type smallint
+SELECT (test_json -> 'field1')::float4 FROM test_jsonb WHERE json_type = 'object';  -- string to float4
+ERROR:  cannot cast jsonb string to type real
+SELECT (test_json -> 'field5')::int2 FROM test_jsonb WHERE json_type = 'object';  -- array to int2
+ERROR:  cannot cast jsonb array or object to type smallint
+SELECT (test_json -> 'field5')::float4 FROM test_jsonb WHERE json_type = 'object';  -- array to float4
+ERROR:  cannot cast jsonb array or object to type real
+-- Section 4b: type-mismatch error through subscripting syntax
+SELECT (test_json['field1'])::numeric FROM test_jsonb WHERE json_type = 'object';  -- string to numeric
+ERROR:  cannot cast jsonb string to type numeric
+SELECT (test_json['field1'])::int8 FROM test_jsonb WHERE json_type = 'object';  -- string to int8
+ERROR:  cannot cast jsonb string to type bigint
+-- Section 5: direct calls to typed extractor builtins
+SELECT jsonb_object_field_numeric('{"a": 1}'::jsonb, 'a');
+ jsonb_object_field_numeric 
+----------------------------
+                          1
+(1 row)
+
+SELECT jsonb_object_field_numeric('{"a": 3.14}'::jsonb, 'a');
+ jsonb_object_field_numeric 
+----------------------------
+                       3.14
+(1 row)
+
+SELECT jsonb_object_field_bool('{"a": true}'::jsonb, 'a');
+ jsonb_object_field_bool 
+-------------------------
+ t
+(1 row)
+
+SELECT jsonb_object_field_bool('{"a": false}'::jsonb, 'a');
+ jsonb_object_field_bool 
+-------------------------
+ f
+(1 row)
+
+SELECT jsonb_object_field_int4('{"a": 42}'::jsonb, 'a');
+ jsonb_object_field_int4 
+-------------------------
+                      42
+(1 row)
+
+SELECT jsonb_object_field_int8('{"a": 9876543210}'::jsonb, 'a');
+ jsonb_object_field_int8 
+-------------------------
+              9876543210
+(1 row)
+
+SELECT jsonb_object_field_float8('{"a": 3.14}'::jsonb, 'a');
+ jsonb_object_field_float8 
+---------------------------
+                      3.14
+(1 row)
+
+SELECT jsonb_object_field_int2('{"a": 42}'::jsonb, 'a');
+ jsonb_object_field_int2 
+-------------------------
+                      42
+(1 row)
+
+SELECT jsonb_object_field_int2('{"a": 3}'::jsonb, 'a');
+ jsonb_object_field_int2 
+-------------------------
+                       3
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": 3.14}'::jsonb, 'a');
+ jsonb_object_field_float4 
+---------------------------
+                      3.14
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": 1.5}'::jsonb, 'a');
+ jsonb_object_field_float4 
+---------------------------
+                       1.5
+(1 row)
+
+-- direct calls: NULL semantics
+SELECT jsonb_object_field_numeric('{"a": 1}'::jsonb, 'missing');
+ jsonb_object_field_numeric 
+----------------------------
+                           
+(1 row)
+
+SELECT jsonb_object_field_numeric('{"a": null}'::jsonb, 'a');
+ jsonb_object_field_numeric 
+----------------------------
+                           
+(1 row)
+
+SELECT jsonb_object_field_bool('{"a": true}'::jsonb, 'missing');
+ jsonb_object_field_bool 
+-------------------------
+ 
+(1 row)
+
+SELECT jsonb_object_field_int4('{"a": 1}'::jsonb, 'missing');
+ jsonb_object_field_int4 
+-------------------------
+                        
+(1 row)
+
+SELECT jsonb_object_field_int4('{"a": null}'::jsonb, 'a');
+ jsonb_object_field_int4 
+-------------------------
+                        
+(1 row)
+
+SELECT jsonb_object_field_float8('{"a": 1.0}'::jsonb, 'missing');
+ jsonb_object_field_float8 
+---------------------------
+                          
+(1 row)
+
+SELECT jsonb_object_field_int2('{"a": 1}'::jsonb, 'missing');
+ jsonb_object_field_int2 
+-------------------------
+                        
+(1 row)
+
+SELECT jsonb_object_field_int2('{"a": null}'::jsonb, 'a');
+ jsonb_object_field_int2 
+-------------------------
+                        
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": 1.0}'::jsonb, 'missing');
+ jsonb_object_field_float4 
+---------------------------
+                          
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": null}'::jsonb, 'a');
+ jsonb_object_field_float4 
+---------------------------
+                          
+(1 row)
+
+-- direct calls: type-mismatch errors
+SELECT jsonb_object_field_numeric('{"a": "text"}'::jsonb, 'a');
+ERROR:  cannot cast jsonb string to type numeric
+SELECT jsonb_object_field_bool('{"a": 1}'::jsonb, 'a');
+ERROR:  cannot cast jsonb numeric to type boolean
+SELECT jsonb_object_field_numeric('{"a": {"x":1}}'::jsonb, 'a');  -- container to scalar
+ERROR:  cannot cast jsonb array or object to type numeric
+SELECT jsonb_object_field_int4('{"a": "text"}'::jsonb, 'a');
+ERROR:  cannot cast jsonb string to type integer
+SELECT jsonb_object_field_int8('{"a": true}'::jsonb, 'a');
+ERROR:  cannot cast jsonb boolean to type bigint
+SELECT jsonb_object_field_float8('{"a": [1,2]}'::jsonb, 'a');  -- container to float8
+ERROR:  cannot cast jsonb array or object to type double precision
+SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a');  -- string to int2
+ERROR:  cannot cast jsonb string to type smallint
+SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a');  -- bool to float4
+ERROR:  cannot cast jsonb boolean to type real
+SELECT jsonb_object_field_int2('{"a": [1,2]}'::jsonb, 'a');  -- container to int2
+ERROR:  cannot cast jsonb array or object to type smallint
+SELECT jsonb_object_field_float4('{"a": {"x":1}}'::jsonb, 'a');  -- container to float4
+ERROR:  cannot cast jsonb array or object to type real
+-- direct calls: integer overflow
+SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
+ERROR:  integer out of range
+-- direct calls: int2 overflow
+SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
+ERROR:  smallint out of range
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
  ?column? 
 ----------
@@ -586,7 +1066,8 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
  field4
  field5
  field6
-(6 rows)
+ field7
+(7 rows)
 
 -- nulls
 SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object';
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index d28ed1c1e85..61c567110fc 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -155,7 +155,123 @@ CREATE TEMP TABLE test_jsonb (
 INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
 ('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}');
+
+-- Optimized typed extraction: the planner rewrites (j->'key')::type into a
+-- direct typed extractor call, currently for numeric, bool, int2, int4, int8,
+-- float4, float8.
+
+-- Section 1: planner rewrite verification (rewritten targets)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::numeric 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')::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int8 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 -> '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 1b: planner rewrite verification for subscripting syntax
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::numeric 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'])::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int8 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['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 2: correct execution through the rewritten path
+SELECT (test_json -> 'field4')::numeric FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field7')::bool FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::int8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section 2b: correct execution through subscripting syntax
+SELECT (test_json['field4'])::numeric FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json['field7'])::bool FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json['field4'])::int8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json['field4'])::float8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json['field4'])::float4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section 3: NULL semantics (missing key, JSON null, non-object input)
+SELECT (test_json -> 'field3')::numeric FROM test_jsonb WHERE json_type = 'object';  -- JSON null
+SELECT (test_json -> 'nonexistent')::numeric FROM test_jsonb WHERE json_type = 'object';  -- missing key
+SELECT (test_json -> 'x')::numeric FROM test_jsonb WHERE json_type = 'array';  -- non-object
+SELECT (test_json -> 'field3')::bool FROM test_jsonb WHERE json_type = 'object';  -- JSON null, bool path
+SELECT (test_json -> 'field3')::int4 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, int4 path
+SELECT (test_json -> 'nonexistent')::int4 FROM test_jsonb WHERE json_type = 'object';  -- missing key, int4
+SELECT (test_json -> 'field3')::float8 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, float8 path
+SELECT (test_json -> 'nonexistent')::float8 FROM test_jsonb WHERE json_type = 'object';  -- missing key, float8
+SELECT (test_json -> 'field3')::int2 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, int2 path
+SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object';  -- missing key, int2
+SELECT (test_json -> 'field3')::float4 FROM test_jsonb WHERE json_type = 'object';  -- JSON null, float4 path
+SELECT (test_json -> 'nonexistent')::float4 FROM test_jsonb WHERE json_type = 'object';  -- missing key, float4
+
+-- Section 3b: NULL semantics through subscripting syntax
+SELECT (test_json['field3'])::numeric FROM test_jsonb WHERE json_type = 'object';  -- JSON null
+SELECT (test_json['nonexistent'])::numeric FROM test_jsonb WHERE json_type = 'object';  -- missing key
+SELECT (test_json['nonexistent'])::float8 FROM test_jsonb WHERE json_type = 'object';  -- missing key, float8
+
+-- Section 4: type-mismatch errors (scalar and container types)
+SELECT (test_json -> 'field1')::numeric FROM test_jsonb WHERE json_type = 'object';  -- string to numeric
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';  -- string to bool
+SELECT (test_json -> 'field5')::numeric FROM test_jsonb WHERE json_type = 'object';  -- array to numeric
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';  -- string to int4
+SELECT (test_json -> 'field1')::int8 FROM test_jsonb WHERE json_type = 'object';  -- string to int8
+SELECT (test_json -> 'field1')::float8 FROM test_jsonb WHERE json_type = 'object';  -- string to float8
+SELECT (test_json -> 'field5')::int4 FROM test_jsonb WHERE json_type = 'object';  -- array to int4
+SELECT (test_json -> 'field5')::float8 FROM test_jsonb WHERE json_type = 'object';  -- array to float8
+SELECT (test_json -> 'field1')::int2 FROM test_jsonb WHERE json_type = 'object';  -- string to int2
+SELECT (test_json -> 'field1')::float4 FROM test_jsonb WHERE json_type = 'object';  -- string to float4
+SELECT (test_json -> 'field5')::int2 FROM test_jsonb WHERE json_type = 'object';  -- array to int2
+SELECT (test_json -> 'field5')::float4 FROM test_jsonb WHERE json_type = 'object';  -- array to float4
+
+-- Section 4b: type-mismatch error through subscripting syntax
+SELECT (test_json['field1'])::numeric FROM test_jsonb WHERE json_type = 'object';  -- string to numeric
+SELECT (test_json['field1'])::int8 FROM test_jsonb WHERE json_type = 'object';  -- string to int8
+
+-- Section 5: direct calls to typed extractor builtins
+SELECT jsonb_object_field_numeric('{"a": 1}'::jsonb, 'a');
+SELECT jsonb_object_field_numeric('{"a": 3.14}'::jsonb, 'a');
+SELECT jsonb_object_field_bool('{"a": true}'::jsonb, 'a');
+SELECT jsonb_object_field_bool('{"a": false}'::jsonb, 'a');
+SELECT jsonb_object_field_int4('{"a": 42}'::jsonb, 'a');
+SELECT jsonb_object_field_int8('{"a": 9876543210}'::jsonb, 'a');
+SELECT jsonb_object_field_float8('{"a": 3.14}'::jsonb, 'a');
+SELECT jsonb_object_field_int2('{"a": 42}'::jsonb, 'a');
+SELECT jsonb_object_field_int2('{"a": 3}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": 3.14}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": 1.5}'::jsonb, 'a');
+-- direct calls: NULL semantics
+SELECT jsonb_object_field_numeric('{"a": 1}'::jsonb, 'missing');
+SELECT jsonb_object_field_numeric('{"a": null}'::jsonb, 'a');
+SELECT jsonb_object_field_bool('{"a": true}'::jsonb, 'missing');
+SELECT jsonb_object_field_int4('{"a": 1}'::jsonb, 'missing');
+SELECT jsonb_object_field_int4('{"a": null}'::jsonb, 'a');
+SELECT jsonb_object_field_float8('{"a": 1.0}'::jsonb, 'missing');
+SELECT jsonb_object_field_int2('{"a": 1}'::jsonb, 'missing');
+SELECT jsonb_object_field_int2('{"a": null}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": 1.0}'::jsonb, 'missing');
+SELECT jsonb_object_field_float4('{"a": null}'::jsonb, 'a');
+-- direct calls: type-mismatch errors
+SELECT jsonb_object_field_numeric('{"a": "text"}'::jsonb, 'a');
+SELECT jsonb_object_field_bool('{"a": 1}'::jsonb, 'a');
+SELECT jsonb_object_field_numeric('{"a": {"x":1}}'::jsonb, 'a');  -- container to scalar
+SELECT jsonb_object_field_int4('{"a": "text"}'::jsonb, 'a');
+SELECT jsonb_object_field_int8('{"a": true}'::jsonb, 'a');
+SELECT jsonb_object_field_float8('{"a": [1,2]}'::jsonb, 'a');  -- container to float8
+SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a');  -- string to int2
+SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a');  -- bool to float4
+SELECT jsonb_object_field_int2('{"a": [1,2]}'::jsonb, 'a');  -- container to int2
+SELECT jsonb_object_field_float4('{"a": {"x":1}}'::jsonb, 'a');  -- container to float4
+-- direct calls: integer overflow
+SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
+-- direct calls: int2 overflow
+SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
 
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
@@ -1614,4 +1730,4 @@ select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
 select ('true'::jsonb)::bool;
 select ('true'::jsonb).bool;
 select ('{"text": "hello"}'::jsonb)::text;
-select ('{"text": "hello"}'::jsonb).text;
+select ('{"text": "hello"}'::jsonb).text;
\ No newline at end of file
-- 
2.52.0



  [application/octet-stream] v5-0002-jsonb-optimize-array-element-casts-to-scalar-type.patch (27.4K, 4-v5-0002-jsonb-optimize-array-element-casts-to-scalar-type.patch)
  download | inline diff:
From 62eb5cb2268ec468668a231a523b4e5eafebf789 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
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

view thread (28+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Extract numeric filed in JSONB more effectively
  In-Reply-To: <CABXr29G35c6Qo-Ab4EpH24gHS+ejjJGfhmW3nQ1eEK+Rgt_9Wg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox