public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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: Sun, 26 Apr 2026 22:01:58 -0700
Message-ID: <CABXr29EZNEgjJVNRX3gLOjRZVjXT6tda2_m8DdxQTfr8s8mkEQ@mail.gmail.com> (raw)
In-Reply-To: <CABXr29G35c6Qo-Ab4EpH24gHS+ejjJGfhmW3nQ1eEK+Rgt_9Wg@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>
<CABXr29G35c6Qo-Ab4EpH24gHS+ejjJGfhmW3nQ1eEK+Rgt_9Wg@mail.gmail.com>
On Fri, Apr 10, 2026 at 2:48 PM Haibo Yan <[email protected]> wrote:
> 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
>
Hi all,
I spent some time reworking this patch series into a shape that I think is
easier to review and easier to reason about patch-by-patch.
The goal of the series is still the same: optimize casts over
scalar-returning jsonb extraction functions by using the cast function’s
support hook to recognize:
- cast(extract(...))
and rewrite that directly to explicit typed extractor calls.
That keeps ordinary SQL syntax unchanged, but avoids the extra jsonb scalar
wrapping/unwrapping on the optimized path.
At this point the series is organized as 5 patches:
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
The supported target types are:
- numeric
- bool
- int2
- int4
- int8
- float4
- float8
The covered scalar-returning extraction families/forms are:
- jsonb_object_field
- -> with text key
- equivalent key subscripting
- jsonb_array_element
- -> with integer RHS
- single-index array subscripting
- jsonb_extract_path
- #>
- direct jsonb_extract_path(...)
- multi-subscript jsonb subscripting (lowered to extract-path)
- jsonb_path_query_first
- jsonb_path_query_first_tz
A few points about the current shape of the series:
- I intentionally stayed with the support-function rewrite model and did
not introduce new user-visible operators.
- I also did not go back to the earlier start/finish/internal pipeline
approach. The current version rewrites directly to explicit typed extractor
functions.
- I folded int2 and float4 into the same family-based design, since they
are the remaining natural numeric-family targets that fit the same
conversion model.
- I kept the multi-subscript patch conservative. It lowers through the
existing extract-path family, but it does not try to turn this into a
broader executor-side subscripting redesign.
One explicit boundary of the series is that it does *not* try to optimize
jsonb_path_query.
After looking at that more carefully, I do not think it belongs in the same
patch line. This series is about scalar-returning extraction functions,
where the cast prosupport hook can see and rewrite a scalar expression
pair. jsonb_path_query is set-returning, so optimizing casts over it would
likely require a different mechanism, probably at planner or executor
level, rather than one more patch in this prosupport-based series.
Another explicit boundary is that the series stops at the current
numeric/bool scalar targets. I did not try to extend it to
text/date/uuid-like conversions, because those would require different
semantics such as full jsonb serialization or string parsing, rather than
the direct scalar conversion pattern used here.
I expect one likely question will be the catalog footprint, since this
approach adds a noticeable number of typed extractor builtins. I think that
is the cleanest tradeoff in this case: SQL is statically typed, and
explicit typed extractors keep the rewrite path simple, predictable, and
reviewable. The implementation-side boilerplate is kept under control with
shared helpers and thin family-specific macros.
Thanks in advance for any review and feedback.
Regards,
Haibo
Attachments:
[application/octet-stream] v5-0002-jsonb-optimize-array-element-casts-to-scalar-type.patch (27.4K, 3-v5-0002-jsonb-optimize-array-element-casts-to-scalar-type.patch)
download | inline diff:
From 9c8ab19b7cdceb70a642013acc866887ce6ccb0d 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
[application/octet-stream] v5-0003-jsonb-optimize-extract-path-casts-to-scalar-types.patch (24.4K, 4-v5-0003-jsonb-optimize-extract-path-casts-to-scalar-types.patch)
download | inline diff:
From 04e738b46adda4f9edb948cf5729091f85ac9830 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Fri, 10 Apr 2026 12:08:25 -0700
Subject: [PATCH v5 3/5] jsonb: optimize extract-path casts to scalar types
Extend the existing support-function rewrite to jsonb extract-path
operations, including both #> and direct jsonb_extract_path(...)
calls.
Supported casts are rewritten directly to explicit typed extractor
functions for numeric, bool, int2, int4, int8, float4, and float8.
---
src/backend/utils/adt/jsonb.c | 43 +++++-
src/backend/utils/adt/jsonfuncs.c | 188 ++++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 31 +++-
src/test/regress/expected/jsonb.out | 227 ++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 61 ++++++++
5 files changed, 546 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 5abb51d47da..7eba437b8a4 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1834,6 +1834,7 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
* Supported extraction families:
* - jsonb_object_field(j, 'key') / j -> 'key' / j['key']
* - jsonb_array_element(j, idx) / j -> idx / j[idx]
+ * - jsonb_extract_path(j, ...) / j #> '{a,b}'
*/
Datum
jsonb_cast_support(PG_FUNCTION_ARGS)
@@ -2010,6 +2011,46 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
else
PG_RETURN_POINTER(NULL);
}
+ else if (inner_funcid == F_JSONB_EXTRACT_PATH)
+ {
+ if (fexpr->funcid == F_NUMERIC_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_NUMERIC;
+ replacement_rettype = NUMERICOID;
+ }
+ else if (fexpr->funcid == F_BOOL_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_BOOL;
+ replacement_rettype = BOOLOID;
+ }
+ else if (fexpr->funcid == F_INT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_INT4;
+ replacement_rettype = INT4OID;
+ }
+ else if (fexpr->funcid == F_INT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_INT8;
+ replacement_rettype = INT8OID;
+ }
+ else if (fexpr->funcid == F_FLOAT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_FLOAT8;
+ replacement_rettype = FLOAT8OID;
+ }
+ else if (fexpr->funcid == F_INT2_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_INT2;
+ replacement_rettype = INT2OID;
+ }
+ else if (fexpr->funcid == F_FLOAT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_EXTRACT_PATH_FLOAT4;
+ replacement_rettype = FLOAT4OID;
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+ }
else
PG_RETURN_POINTER(NULL);
@@ -2241,4 +2282,4 @@ JsonbUnquote(Jsonb *jb)
}
else
return JsonbToCString(NULL, &jb->root, VARSIZE(jb));
-}
\ No newline at end of file
+}
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index cc9c51c5cbf..b3fd1dfb631 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -1060,7 +1060,6 @@ jsonb_value_to_float8_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_float8, NumericGetDatum(v->val.numeric));
}
-
static Datum
jsonb_value_to_int2_datum(JsonbValue *v)
{
@@ -1181,6 +1180,191 @@ DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_float8, jsonb_value_to_floa
DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_int2, jsonb_value_to_int2_datum)
DEFINE_JSONB_ARRAY_ELEMENT_TYPED(jsonb_array_element_float4, jsonb_value_to_float4_datum)
+/*
+ * Walk a text[] path through a jsonb value and return the leaf JsonbValue,
+ * or NULL. Returns NULL when a path element is missing, the leaf is JSON
+ * null, or the path array contains null elements.
+ *
+ * For an empty path, returns the root value itself (as a jbvBinary for
+ * containers, or the unwrapped scalar for scalar jsonb). The caller's
+ * conversion helper then decides whether to accept or reject the type.
+ *
+ * For arrays, path elements are parsed as integers (matching the existing
+ * jsonb_extract_path / jsonb_get_element traversal semantics).
+ */
+static JsonbValue *
+jsonb_extract_path_lookup(Jsonb *jb, ArrayType *path, JsonbValue *vbuf)
+{
+ Datum *pathtext;
+ int npath;
+ JsonbContainer *container = &jb->root;
+ JsonbValue *jbvp = NULL;
+ bool have_object,
+ have_array;
+ int i;
+
+ /* If the path array contains any nulls, return NULL */
+ if (array_contains_nulls(path))
+ return NULL;
+
+ deconstruct_array_builtin(path, TEXTOID, &pathtext, NULL, &npath);
+
+ /* Identify top-level container type */
+ if (JB_ROOT_IS_OBJECT(jb))
+ {
+ have_object = true;
+ have_array = false;
+ }
+ else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
+ {
+ have_object = false;
+ have_array = true;
+ }
+ else
+ {
+ Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb));
+
+ /* For a scalar root with an empty path, return the scalar itself */
+ if (npath <= 0)
+ {
+ jbvp = getIthJsonbValueFromContainer(container, 0);
+ if (jbvp == NULL || jbvp->type == jbvNull)
+ return NULL;
+ return jbvp;
+ }
+
+ /* Non-empty path into a scalar yields NULL */
+ return NULL;
+ }
+
+ /*
+ * For an empty path on a container root, return the container as a
+ * jbvBinary value. The conversion helper will reject it with the
+ * usual "cannot cast jsonb array or object" error.
+ */
+ if (npath <= 0)
+ {
+ vbuf->type = jbvBinary;
+ vbuf->val.binary.data = container;
+ vbuf->val.binary.len = VARSIZE(jb) - VARHDRSZ;
+ return vbuf;
+ }
+
+ for (i = 0; i < npath; i++)
+ {
+ if (have_object)
+ {
+ text *subscr = DatumGetTextPP(pathtext[i]);
+
+ jbvp = getKeyJsonValueFromContainer(container,
+ VARDATA_ANY(subscr),
+ VARSIZE_ANY_EXHDR(subscr),
+ NULL);
+ }
+ else if (have_array)
+ {
+ int lindex;
+ uint32 index;
+ char *indextext = TextDatumGetCString(pathtext[i]);
+ char *endptr;
+
+ errno = 0;
+ lindex = strtoint(indextext, &endptr, 10);
+ if (endptr == indextext || *endptr != '\0' || errno != 0)
+ return NULL;
+
+ if (lindex >= 0)
+ {
+ index = (uint32) lindex;
+ }
+ else
+ {
+ uint32 nelements;
+
+ if (!JsonContainerIsArray(container))
+ elog(ERROR, "not a jsonb array");
+
+ nelements = JsonContainerSize(container);
+
+ if (lindex == INT_MIN || -lindex > nelements)
+ return NULL;
+ else
+ index = nelements + lindex;
+ }
+
+ jbvp = getIthJsonbValueFromContainer(container, index);
+ }
+ else
+ {
+ /* scalar: cannot traverse further */
+ return NULL;
+ }
+
+ if (jbvp == NULL)
+ return NULL;
+
+ /* If this is the last path element, we have our leaf */
+ if (i == npath - 1)
+ break;
+
+ /* Descend into the next container level */
+ if (jbvp->type == jbvBinary)
+ {
+ container = jbvp->val.binary.data;
+ have_object = JsonContainerIsObject(container);
+ have_array = JsonContainerIsArray(container);
+ }
+ else
+ {
+ /* scalar at intermediate step: no further traversal */
+ have_object = false;
+ have_array = false;
+ }
+ }
+
+ /* Missing value or JSON null both map to SQL NULL */
+ if (jbvp == NULL || jbvp->type == jbvNull)
+ return NULL;
+
+ return jbvp;
+}
+
+/*
+ * Thin-wrapper macro for the jsonb_extract_path_<type> extractor family.
+ * Same pattern as the object-field and array-element wrapper macros.
+ */
+#define DEFINE_JSONB_EXTRACT_PATH_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+ Jsonb *jb = PG_GETARG_JSONB_P(0); \
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); \
+ JsonbValue vbuf; \
+ JsonbValue *v; \
+ Datum result; \
+\
+ v = jsonb_extract_path_lookup(jb, path, &vbuf); \
+ if (v == NULL) \
+ { \
+ PG_FREE_IF_COPY(path, 1); \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ result = convfn(v); \
+ PG_FREE_IF_COPY(path, 1); \
+ PG_FREE_IF_COPY(jb, 0); \
+ return result; \
+}
+
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_float8, jsonb_value_to_float8_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_int2, jsonb_value_to_int2_datum)
+DEFINE_JSONB_EXTRACT_PATH_TYPED(jsonb_extract_path_float4, jsonb_value_to_float4_datum)
+
Datum
json_array_element(PG_FUNCTION_ARGS)
{
@@ -6427,4 +6611,4 @@ json_check_mutability(Oid typoid, bool is_jsonb, bool *has_mutable)
*has_mutable = true;
break;
}
-}
\ No newline at end of file
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ad752a0a9e0..d8ac9cbf053 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12827,6 +12827,26 @@
proname => 'jsonb_array_element_float8', prorettype => 'float8',
proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
prosrc => 'jsonb_array_element_float8' },
+{ oid => '9965', descr => 'extract numeric from jsonb by path',
+ proname => 'jsonb_extract_path_numeric', prorettype => 'numeric',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_numeric' },
+{ oid => '9966', descr => 'extract boolean from jsonb by path',
+ proname => 'jsonb_extract_path_bool', prorettype => 'bool',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_bool' },
+{ oid => '9967', descr => 'extract int4 from jsonb by path',
+ proname => 'jsonb_extract_path_int4', prorettype => 'int4',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_int4' },
+{ oid => '9968', descr => 'extract int8 from jsonb by path',
+ proname => 'jsonb_extract_path_int8', prorettype => 'int8',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_int8' },
+{ oid => '9969', descr => 'extract float8 from jsonb by path',
+ proname => 'jsonb_extract_path_float8', prorettype => 'float8',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_float8' },
{ oid => '9982', descr => 'extract int2 from jsonb array by element index',
proname => 'jsonb_array_element_int2', prorettype => 'int2',
proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
@@ -12836,4 +12856,13 @@
proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
prosrc => 'jsonb_array_element_float4' },
-]
\ No newline at end of file
+{ oid => '9984', descr => 'extract int2 from jsonb by path',
+ proname => 'jsonb_extract_path_int2', prorettype => 'int2',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_int2' },
+{ oid => '9985', descr => 'extract float4 from jsonb by path',
+ proname => 'jsonb_extract_path_float4', prorettype => 'float4',
+ proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
+ prosrc => 'jsonb_extract_path_float4' },
+
+]
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 36bc82095be..b96c095525c 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -1247,6 +1247,233 @@ SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
2.5
(1 row)
+-- Optimized typed extraction: extract-path family
+-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
+-- into direct typed extractor calls for the same target types.
+-- Section P1: planner rewrite verification (operator form #>)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_numeric(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int4(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_float8(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int8(test_json, '{field6,f1}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_bool(test_json, '{field7}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int2(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_float4(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section P1b: planner rewrite verification (direct function-call form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_float8(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field6', 'f1'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int4(test_json, '{field6,f1}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section P1c: planner rewrite verification (VARIADIC ARRAY form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, VARIADIC ARRAY['field6','f1']))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int4(test_json, '{field6,f1}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section P2: correct execution through rewritten path
+SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+ int4
+------
+ 4
+(1 row)
+
+SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+ numeric
+---------
+ 4
+(1 row)
+
+SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+ float8
+--------
+ 4
+(1 row)
+
+SELECT (test_json #> '{field6,f1}')::int4 FROM test_jsonb WHERE json_type = 'object';
+ int4
+------
+ 9
+(1 row)
+
+SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+ int8
+------
+ 9
+(1 row)
+
+SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+ bool
+------
+ t
+(1 row)
+
+-- array index in path
+SELECT ('{"a":[10,20,30]}'::jsonb #> '{a,1}')::int4;
+ int4
+------
+ 20
+(1 row)
+
+-- Section P3: NULL semantics
+SELECT (test_json #> '{nonexistent}')::int4 FROM test_jsonb WHERE json_type = 'object'; -- missing key
+ int4
+------
+
+(1 row)
+
+SELECT (test_json #> '{field3}')::numeric FROM test_jsonb WHERE json_type = 'object'; -- JSON null leaf
+ numeric
+---------
+
+(1 row)
+
+SELECT (test_json #> '{field6,missing}')::int4 FROM test_jsonb WHERE json_type = 'object'; -- missing nested key
+ int4
+------
+
+(1 row)
+
+-- null in path array
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY[NULL]::text[]);
+ jsonb_extract_path_int4
+-------------------------
+
+(1 row)
+
+-- empty path on container root produces cast error
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, '{}'::text[]);
+ERROR: cannot cast jsonb array or object to type integer
+-- Section P4: type-mismatch errors
+SELECT (test_json #> '{field1}')::int4 FROM test_jsonb WHERE json_type = 'object'; -- string to int4
+ERROR: cannot cast jsonb string to type integer
+SELECT (test_json #> '{field5}')::float8 FROM test_jsonb WHERE json_type = 'object'; -- array to float8
+ERROR: cannot cast jsonb array or object to type double precision
+SELECT (test_json #> '{field6}')::int8 FROM test_jsonb WHERE json_type = 'object'; -- object to int8
+ERROR: cannot cast jsonb array or object to type bigint
+-- Section P5: direct calls to extract-path typed extractor builtins
+SELECT jsonb_extract_path_int4('{"a":{"b":42}}'::jsonb, ARRAY['a','b']);
+ jsonb_extract_path_int4
+-------------------------
+ 42
+(1 row)
+
+SELECT jsonb_extract_path_int8('{"x":99}'::jsonb, ARRAY['x']);
+ jsonb_extract_path_int8
+-------------------------
+ 99
+(1 row)
+
+SELECT jsonb_extract_path_float8('{"a":3.14}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_float8
+---------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_extract_path_numeric('{"a":1.23}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_numeric
+----------------------------
+ 1.23
+(1 row)
+
+SELECT jsonb_extract_path_bool('{"a":true}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_bool
+-------------------------
+ t
+(1 row)
+
+-- direct calls: missing path
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY['b']);
+ jsonb_extract_path_int4
+-------------------------
+
+(1 row)
+
+-- direct calls: type-mismatch
+SELECT jsonb_extract_path_int4('{"a":"text"}'::jsonb, ARRAY['a']);
+ERROR: cannot cast jsonb string to type integer
+-- direct calls: array index in path
+SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
+ jsonb_extract_path_int4
+-------------------------
+ 20
+(1 row)
+
+-- direct calls: int2 and float4
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+ jsonb_extract_path_int2
+-------------------------
+ 7
+(1 row)
+
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_float4
+---------------------------
+ 3.14
+(1 row)
+
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
?column?
----------
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 69c21776f84..11191fd0f82 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -358,6 +358,67 @@ SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
+-- Optimized typed extraction: extract-path family
+-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
+-- into direct typed extractor calls for the same target types.
+
+-- Section P1: planner rewrite verification (operator form #>)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+-- Section P1b: planner rewrite verification (direct function-call form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field6', 'f1'))::int4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section P1c: planner rewrite verification (VARIADIC ARRAY form)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, VARIADIC ARRAY['field6','f1']))::int4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section P2: correct execution through rewritten path
+SELECT (test_json #> '{field4}')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field4}')::numeric FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field4}')::float8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field6,f1}')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
+-- array index in path
+SELECT ('{"a":[10,20,30]}'::jsonb #> '{a,1}')::int4;
+
+-- Section P3: NULL semantics
+SELECT (test_json #> '{nonexistent}')::int4 FROM test_jsonb WHERE json_type = 'object'; -- missing key
+SELECT (test_json #> '{field3}')::numeric FROM test_jsonb WHERE json_type = 'object'; -- JSON null leaf
+SELECT (test_json #> '{field6,missing}')::int4 FROM test_jsonb WHERE json_type = 'object'; -- missing nested key
+-- null in path array
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY[NULL]::text[]);
+-- empty path on container root produces cast error
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, '{}'::text[]);
+
+-- Section P4: type-mismatch errors
+SELECT (test_json #> '{field1}')::int4 FROM test_jsonb WHERE json_type = 'object'; -- string to int4
+SELECT (test_json #> '{field5}')::float8 FROM test_jsonb WHERE json_type = 'object'; -- array to float8
+SELECT (test_json #> '{field6}')::int8 FROM test_jsonb WHERE json_type = 'object'; -- object to int8
+
+-- Section P5: direct calls to extract-path typed extractor builtins
+SELECT jsonb_extract_path_int4('{"a":{"b":42}}'::jsonb, ARRAY['a','b']);
+SELECT jsonb_extract_path_int8('{"x":99}'::jsonb, ARRAY['x']);
+SELECT jsonb_extract_path_float8('{"a":3.14}'::jsonb, ARRAY['a']);
+SELECT jsonb_extract_path_numeric('{"a":1.23}'::jsonb, ARRAY['a']);
+SELECT jsonb_extract_path_bool('{"a":true}'::jsonb, ARRAY['a']);
+-- direct calls: missing path
+SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY['b']);
+-- direct calls: type-mismatch
+SELECT jsonb_extract_path_int4('{"a":"text"}'::jsonb, ARRAY['a']);
+-- direct calls: array index in path
+SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
+-- direct calls: int2 and float4
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+
+
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object';
--
2.52.0
[application/octet-stream] v5-0001-jsonb-optimize-object-field-casts-to-scalar-types.patch (43.6K, 5-v5-0001-jsonb-optimize-object-field-casts-to-scalar-types.patch)
download | inline diff:
From 8a06567bdc249ccf6bee91c51cd1fa734170f758 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-0005-jsonb-optimize-jsonpath-first-casts-to-scalar-typ.patch (82.6K, 6-v5-0005-jsonb-optimize-jsonpath-first-casts-to-scalar-typ.patch)
download | inline diff:
From be43cda8d05767b4fee6a61f148c550cf8f78cd0 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Fri, 10 Apr 2026 14:34:24 -0700
Subject: [PATCH v5 5/5] jsonb: optimize jsonpath-first casts to scalar types
Extend the existing support-function rewrite to jsonb_path_query_first
and jsonb_path_query_first_tz so casts are rewritten directly to
explicit typed extractor functions.
This patch supports numeric, bool, int2, int4, int8, float4, and
float8, and leaves jsonb_path_query out of scope.
---
src/backend/utils/adt/jsonb.c | 102 +++-
src/backend/utils/adt/jsonfuncs.c | 17 +-
src/backend/utils/adt/jsonpath_exec.c | 136 +++++
src/include/catalog/pg_proc.dat | 73 +++
src/include/utils/jsonfuncs.h | 10 +
src/test/regress/expected/jsonb.out | 846 +++++++++++++++++++-------
src/test/regress/sql/jsonb.sql | 229 +++++--
7 files changed, 1114 insertions(+), 299 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index d5c0871a213..2985777f1c9 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1836,6 +1836,8 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
* - jsonb_array_element(j, idx) / j -> idx / j[idx]
* - jsonb_extract_path(j, ...) / j #> '{a,b}'
* - multi-subscript chains j['a']['b'], j['a'][0], etc.
+ * - jsonb_path_query_first(j, path [, vars [, silent]])
+ * - jsonb_path_query_first_tz(j, path [, vars [, silent]])
*/
Datum
jsonb_cast_support(PG_FUNCTION_ARGS)
@@ -2012,13 +2014,13 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
/*
* 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.
+ * has its own set of typed rewrite targets and expected arg count.
*/
- if (list_length(inner_args) != 2)
- PG_RETURN_POINTER(NULL);
-
if (inner_funcid == F_JSONB_OBJECT_FIELD)
{
+ if (list_length(inner_args) != 2)
+ PG_RETURN_POINTER(NULL);
+
if (fexpr->funcid == F_NUMERIC_JSONB)
{
replacement_funcid = F_JSONB_OBJECT_FIELD_NUMERIC;
@@ -2059,6 +2061,9 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
}
else if (inner_funcid == F_JSONB_ARRAY_ELEMENT)
{
+ if (list_length(inner_args) != 2)
+ PG_RETURN_POINTER(NULL);
+
if (fexpr->funcid == F_NUMERIC_JSONB)
{
replacement_funcid = F_JSONB_ARRAY_ELEMENT_NUMERIC;
@@ -2099,6 +2104,9 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
}
else if (inner_funcid == F_JSONB_EXTRACT_PATH)
{
+ if (list_length(inner_args) != 2)
+ PG_RETURN_POINTER(NULL);
+
if (fexpr->funcid == F_NUMERIC_JSONB)
{
replacement_funcid = F_JSONB_EXTRACT_PATH_NUMERIC;
@@ -2137,6 +2145,92 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
else
PG_RETURN_POINTER(NULL);
}
+ else if (inner_funcid == F_JSONB_PATH_QUERY_FIRST)
+ {
+ if (list_length(inner_args) != 4)
+ PG_RETURN_POINTER(NULL);
+
+ if (fexpr->funcid == F_NUMERIC_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_NUMERIC;
+ replacement_rettype = NUMERICOID;
+ }
+ else if (fexpr->funcid == F_BOOL_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_BOOL;
+ replacement_rettype = BOOLOID;
+ }
+ else if (fexpr->funcid == F_INT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_INT4;
+ replacement_rettype = INT4OID;
+ }
+ else if (fexpr->funcid == F_INT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_INT8;
+ replacement_rettype = INT8OID;
+ }
+ else if (fexpr->funcid == F_FLOAT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_FLOAT8;
+ replacement_rettype = FLOAT8OID;
+ }
+ else if (fexpr->funcid == F_INT2_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_INT2;
+ replacement_rettype = INT2OID;
+ }
+ else if (fexpr->funcid == F_FLOAT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_FLOAT4;
+ replacement_rettype = FLOAT4OID;
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+ }
+ else if (inner_funcid == F_JSONB_PATH_QUERY_FIRST_TZ)
+ {
+ if (list_length(inner_args) != 4)
+ PG_RETURN_POINTER(NULL);
+
+ if (fexpr->funcid == F_NUMERIC_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_NUMERIC;
+ replacement_rettype = NUMERICOID;
+ }
+ else if (fexpr->funcid == F_BOOL_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_BOOL;
+ replacement_rettype = BOOLOID;
+ }
+ else if (fexpr->funcid == F_INT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_INT4;
+ replacement_rettype = INT4OID;
+ }
+ else if (fexpr->funcid == F_INT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_INT8;
+ replacement_rettype = INT8OID;
+ }
+ else if (fexpr->funcid == F_FLOAT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_FLOAT8;
+ replacement_rettype = FLOAT8OID;
+ }
+ else if (fexpr->funcid == F_INT2_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_INT2;
+ replacement_rettype = INT2OID;
+ }
+ else if (fexpr->funcid == F_FLOAT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_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 b3fd1dfb631..32cdb5c61b8 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -969,7 +969,7 @@ jsonb_object_field_lookup(Jsonb *jb, text *key, JsonbValue *vbuf)
* The message wording matches cannotCastJsonbValue() in jsonb.c so that
* the optimized and unoptimized paths produce identical errors.
*/
-static void
+void
jsonb_field_cast_error(JsonbValue *v, const char *sqltype)
{
const char *jsontype;
@@ -1016,7 +1016,7 @@ jsonb_field_cast_error(JsonbValue *v, const char *sqltype)
* value as Datum. These are the only place where conversion semantics
* live; the wrapper macro below is intentionally kept thin.
*/
-static Datum
+Datum
jsonb_value_to_numeric_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1025,7 +1025,7 @@ jsonb_value_to_numeric_datum(JsonbValue *v)
return NumericGetDatum(DatumGetNumericCopy(NumericGetDatum(v->val.numeric)));
}
-static Datum
+Datum
jsonb_value_to_bool_datum(JsonbValue *v)
{
if (v->type != jbvBool)
@@ -1034,7 +1034,7 @@ jsonb_value_to_bool_datum(JsonbValue *v)
return BoolGetDatum(v->val.boolean);
}
-static Datum
+Datum
jsonb_value_to_int4_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1043,7 +1043,7 @@ jsonb_value_to_int4_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_int4, NumericGetDatum(v->val.numeric));
}
-static Datum
+Datum
jsonb_value_to_int8_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1052,7 +1052,7 @@ jsonb_value_to_int8_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_int8, NumericGetDatum(v->val.numeric));
}
-static Datum
+Datum
jsonb_value_to_float8_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1060,7 +1060,8 @@ jsonb_value_to_float8_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_float8, NumericGetDatum(v->val.numeric));
}
-static Datum
+
+Datum
jsonb_value_to_int2_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1069,7 +1070,7 @@ jsonb_value_to_int2_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_int2, NumericGetDatum(v->val.numeric));
}
-static Datum
+Datum
jsonb_value_to_float4_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 0ec9b4df2ef..2048696248b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -72,6 +72,7 @@
#include "utils/float.h"
#include "utils/formatting.h"
#include "utils/json.h"
+#include "utils/jsonfuncs.h"
#include "utils/jsonpath.h"
#include "utils/memutils.h"
#include "utils/timestamp.h"
@@ -687,6 +688,141 @@ jsonb_path_query_first_tz(PG_FUNCTION_ARGS)
return jsonb_path_query_first_internal(fcinfo, true);
}
+/*
+ * Lookup helper for the jsonb_path_query_first typed extractor family.
+ *
+ * Invokes the jsonpath execution machinery and returns the first result as
+ * an in-memory JsonbValue, or NULL if there is no result or the result is
+ * JSON null. This avoids the JsonbValueToJsonb round-trip that the
+ * unoptimized cast(jsonb_path_query_first(...)) path would perform.
+ */
+static JsonbValue *
+jsonb_path_query_first_lookup(Jsonb *jb, JsonPath *jp, Jsonb *vars,
+ bool silent)
+{
+ JsonValueList found;
+ JsonbValue *v;
+
+ JsonValueListInit(&found);
+
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ countVariablesFromJsonb,
+ jb, !silent, &found, false);
+
+ if (JsonValueListIsEmpty(&found))
+ return NULL;
+
+ v = JsonValueListHead(&found);
+ if (v->type == jbvNull)
+ return NULL;
+
+ return v;
+}
+
+/*
+ * Thin-wrapper macro for the jsonb_path_query_first_<type> extractor family.
+ * Same pattern as the other typed extractor families in jsonfuncs.c.
+ */
+#define DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+ Jsonb *jb = PG_GETARG_JSONB_P(0); \
+ JsonPath *jp = PG_GETARG_JSONPATH_P(1); \
+ Jsonb *vars = PG_GETARG_JSONB_P(2); \
+ bool silent = PG_GETARG_BOOL(3); \
+ JsonbValue *v; \
+ Datum result; \
+\
+ v = jsonb_path_query_first_lookup(jb, jp, vars, silent); \
+ if (v == NULL) \
+ { \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ result = convfn(v); \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ return result; \
+}
+
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_float8, jsonb_value_to_float8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_int2, jsonb_value_to_int2_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_float4, jsonb_value_to_float4_datum)
+
+/*
+ * Lookup helper for the jsonb_path_query_first_tz typed extractor family.
+ * Same as jsonb_path_query_first_lookup but with timezone-aware evaluation.
+ */
+static JsonbValue *
+jsonb_path_query_first_tz_lookup(Jsonb *jb, JsonPath *jp, Jsonb *vars,
+ bool silent)
+{
+ JsonValueList found;
+ JsonbValue *v;
+
+ JsonValueListInit(&found);
+
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb,
+ countVariablesFromJsonb,
+ jb, !silent, &found, true);
+
+ if (JsonValueListIsEmpty(&found))
+ return NULL;
+
+ v = JsonValueListHead(&found);
+ if (v->type == jbvNull)
+ return NULL;
+
+ return v;
+}
+
+/*
+ * Thin-wrapper macro for the jsonb_path_query_first_tz_<type> extractor family.
+ */
+#define DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+ Jsonb *jb = PG_GETARG_JSONB_P(0); \
+ JsonPath *jp = PG_GETARG_JSONPATH_P(1); \
+ Jsonb *vars = PG_GETARG_JSONB_P(2); \
+ bool silent = PG_GETARG_BOOL(3); \
+ JsonbValue *v; \
+ Datum result; \
+\
+ v = jsonb_path_query_first_tz_lookup(jb, jp, vars, silent); \
+ if (v == NULL) \
+ { \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ result = convfn(v); \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ return result; \
+}
+
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_float8, jsonb_value_to_float8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_int2, jsonb_value_to_int2_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_float4, jsonb_value_to_float4_datum)
+
/********************Execute functions for JsonPath**************************/
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d8ac9cbf053..921e278a045 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12847,6 +12847,56 @@
proname => 'jsonb_extract_path_float8', prorettype => 'float8',
proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_extract_path_float8' },
+{ oid => '9970', descr => 'jsonpath query first item as numeric',
+ proname => 'jsonb_path_query_first_numeric', prorettype => 'numeric',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_numeric' },
+{ oid => '9971', descr => 'jsonpath query first item as bool',
+ proname => 'jsonb_path_query_first_bool', prorettype => 'bool',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_bool' },
+{ oid => '9972', descr => 'jsonpath query first item as int4',
+ proname => 'jsonb_path_query_first_int4', prorettype => 'int4',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_int4' },
+{ oid => '9973', descr => 'jsonpath query first item as int8',
+ proname => 'jsonb_path_query_first_int8', prorettype => 'int8',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_int8' },
+{ oid => '9974', descr => 'jsonpath query first item as float8',
+ proname => 'jsonb_path_query_first_float8', prorettype => 'float8',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_float8' },
+{ oid => '9975', descr => 'jsonpath query first item as numeric with timezone',
+ proname => 'jsonb_path_query_first_tz_numeric', provolatile => 's',
+ prorettype => 'numeric', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_numeric' },
+{ oid => '9976', descr => 'jsonpath query first item as bool with timezone',
+ proname => 'jsonb_path_query_first_tz_bool', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_bool' },
+{ oid => '9977', descr => 'jsonpath query first item as int4 with timezone',
+ proname => 'jsonb_path_query_first_tz_int4', provolatile => 's',
+ prorettype => 'int4', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_int4' },
+{ oid => '9978', descr => 'jsonpath query first item as int8 with timezone',
+ proname => 'jsonb_path_query_first_tz_int8', provolatile => 's',
+ prorettype => 'int8', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_int8' },
+{ oid => '9979', descr => 'jsonpath query first item as float8 with timezone',
+ proname => 'jsonb_path_query_first_tz_float8', provolatile => 's',
+ prorettype => 'float8', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_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}',
@@ -12865,4 +12915,27 @@
proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_extract_path_float4' },
+# jsonb_path_query_first family: int2 / float4
+{ oid => '9986', descr => 'jsonpath query first item as int2',
+ proname => 'jsonb_path_query_first_int2', prorettype => 'int2',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_int2' },
+{ oid => '9987', descr => 'jsonpath query first item as float4',
+ proname => 'jsonb_path_query_first_float4', prorettype => 'float4',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_float4' },
+
+# jsonb_path_query_first_tz family: int2 / float4
+{ oid => '9988', descr => 'jsonpath query first item as int2 with timezone',
+ proname => 'jsonb_path_query_first_tz_int2', provolatile => 's',
+ prorettype => 'int2', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_int2' },
+{ oid => '9989', descr => 'jsonpath query first item as float4 with timezone',
+ proname => 'jsonb_path_query_first_tz_float4', provolatile => 's',
+ prorettype => 'float4', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_float4' },
]
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 27713be3aeb..ce55fe1b1ce 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -91,6 +91,16 @@ extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
extern Datum jsonb_from_text(text *js, bool unique_keys);
+/* Typed field extraction helpers (shared by jsonfuncs.c and jsonpath_exec.c) */
+extern void jsonb_field_cast_error(JsonbValue *v, const char *sqltype);
+extern Datum jsonb_value_to_numeric_datum(JsonbValue *v);
+extern Datum jsonb_value_to_bool_datum(JsonbValue *v);
+extern Datum jsonb_value_to_int4_datum(JsonbValue *v);
+extern Datum jsonb_value_to_int8_datum(JsonbValue *v);
+extern Datum jsonb_value_to_float8_datum(JsonbValue *v);
+extern Datum jsonb_value_to_int2_datum(JsonbValue *v);
+extern Datum jsonb_value_to_float4_datum(JsonbValue *v);
+
extern Datum json_populate_type(Datum json_val, Oid json_type,
Oid typid, int32 typmod,
void **cache, MemoryContext mcxt,
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 3198442352c..7de2bcea3f7 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -503,22 +503,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float8 FROM test_js
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
@@ -560,7 +544,8 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::float8 FROM test_json
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';
+-- Verify that int2 and float4 are also rewritten through the object-field family.
+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
@@ -568,7 +553,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int2 FROM test_jsonb
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';
+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
@@ -607,18 +592,6 @@ SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object
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
@@ -650,16 +623,17 @@ SELECT (test_json['field4'])::float8 FROM test_jsonb WHERE json_type = 'object';
4
(1 row)
-SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
- test_json
------------
- 4
+-- Unsupported targets still work correctly through the original path
+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';
- test_json
------------
- 4
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4
+--------
+ 4
(1 row)
-- Section 3: NULL semantics (missing key, JSON null, non-object input)
@@ -711,30 +685,6 @@ SELECT (test_json -> 'nonexistent')::float8 FROM test_jsonb WHERE json_type = 'o
(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
@@ -771,14 +721,6 @@ SELECT (test_json -> 'field5')::int4 FROM test_jsonb WHERE json_type = 'object';
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
@@ -827,30 +769,6 @@ SELECT jsonb_object_field_float8('{"a": 3.14}'::jsonb, 'a');
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
@@ -888,30 +806,6 @@ SELECT jsonb_object_field_float8('{"a": 1.0}'::jsonb, 'missing');
(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
@@ -925,20 +819,9 @@ 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
-- 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.
@@ -981,20 +864,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int8 FROM 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
@@ -1031,13 +900,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int8 FROM 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
@@ -1077,18 +939,6 @@ SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
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
@@ -1135,18 +985,6 @@ SELECT ('{"k":1}'::jsonb -> 0)::int4; -- non-array input
(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
@@ -1171,10 +1009,6 @@ 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
@@ -1235,18 +1069,6 @@ 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)
-
-- Optimized typed extraction: extract-path family
-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
-- into direct typed extractor calls for the same target types.
@@ -1291,22 +1113,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_js
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
-------------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_extract_path_int2(test_json, '{field4}'::text[])
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
---------------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_extract_path_float4(test_json, '{field4}'::text[])
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-- Section P1b: planner rewrite verification (direct function-call form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
QUERY PLAN
@@ -1461,19 +1267,6 @@ SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
20
(1 row)
--- direct calls: int2 and float4
-SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
- jsonb_extract_path_int2
--------------------------
- 7
-(1 row)
-
-SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
- jsonb_extract_path_float4
----------------------------
- 3.14
-(1 row)
-
-- Optimized typed extraction: multi-subscript chains
-- The planner lowers j['a']['b'], j['a'][0], etc. to the extract-path typed
-- extractor family, reusing the same functions as the #> operator path.
@@ -1526,21 +1319,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int4 FROM test_jsonb
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
--- Section M1b: multi-subscript int2/float4
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
----------------------------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_extract_path_int2(test_json, ARRAY['field6'::text, 'f1'::text])
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
- int2
-------
- 7
-(1 row)
-
-- Section M2: execution through multi-subscript rewrite
SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int4;
int4
@@ -1631,6 +1409,606 @@ SELECT (('{"a":[[10,20],[30,40]]}'::jsonb)['a'][i][0])::int4 FROM generate_serie
Function Call: generate_series(0, 1)
(3 rows)
+-- Optimized typed extraction: jsonb_path_query_first family
+-- The planner rewrites (jsonb_path_query_first(j, path))::type into a direct
+-- typed extractor call for the same target types as the other families.
+-- Section Q1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_bool(test_json, '$."field7"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_numeric(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, true)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section Q2: execution through rewrite
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int4;
+ jsonb_path_query_first
+------------------------
+ 42
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":3.14}'::jsonb, '$.a'))::float8;
+ jsonb_path_query_first
+------------------------
+ 3.14
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":true}'::jsonb, '$.a'))::bool;
+ jsonb_path_query_first
+------------------------
+ t
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::numeric;
+ jsonb_path_query_first
+------------------------
+ 99
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::int8;
+ jsonb_path_query_first
+------------------------
+ 99
+(1 row)
+
+-- nested object path
+SELECT (jsonb_path_query_first('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+ jsonb_path_query_first
+------------------------
+ 42
+(1 row)
+
+-- array access in jsonpath
+SELECT (jsonb_path_query_first('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+ jsonb_path_query_first
+------------------------
+ 20
+(1 row)
+
+-- Section Q3: NULL semantics
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+ jsonb_path_query_first
+------------------------
+
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+ jsonb_path_query_first
+------------------------
+
+(1 row)
+
+-- Section Q4: silent semantics
+-- silent=true suppresses jsonpath execution errors, NOT cast errors
+SELECT (jsonb_path_query_first('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+ERROR: cannot cast jsonb string to type integer
+-- silent=false (default): jsonpath strict error
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict missing key error
+ERROR: JSON object does not contain key "b"
+-- silent=true: jsonpath strict error suppressed
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, returns NULL
+ jsonb_path_query_first
+------------------------
+
+(1 row)
+
+-- Section Q5: type-mismatch errors
+SELECT (jsonb_path_query_first('{"a":"hello"}'::jsonb, '$.a'))::int4; -- string to int4
+ERROR: cannot cast jsonb string to type integer
+SELECT (jsonb_path_query_first('{"a":[1,2]}'::jsonb, '$.a'))::int4; -- container to int4
+ERROR: cannot cast jsonb array to type integer
+-- Section Q6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int4
+-----------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int8
+-----------------------------
+ 99
+(1 row)
+
+SELECT jsonb_path_query_first_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_float8
+-------------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_numeric
+--------------------------------
+ 1.23
+(1 row)
+
+SELECT jsonb_path_query_first_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_bool
+-----------------------------
+ t
+(1 row)
+
+-- direct call: missing path
+SELECT jsonb_path_query_first_int4('{"a":1}'::jsonb, '$.b'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int4
+-----------------------------
+
+(1 row)
+
+-- direct call: with vars
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a ? (@ > $x)'::jsonpath, '{"x":10}'::jsonb, false);
+ jsonb_path_query_first_int4
+-----------------------------
+ 42
+(1 row)
+
+-- Optimized typed extraction: jsonb_path_query_first_tz family
+-- Same pattern as the non-tz family, but with timezone-aware evaluation.
+-- Section T1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_bool(test_json, '$."field7"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_numeric(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, true)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section T2: execution through rewrite
+SELECT (jsonb_path_query_first_tz('{"a":42}'::jsonb, '$.a'))::int4;
+ jsonb_path_query_first_tz
+---------------------------
+ 42
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float8;
+ jsonb_path_query_first_tz
+---------------------------
+ 3.14
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":true}'::jsonb, '$.a'))::bool;
+ jsonb_path_query_first_tz
+---------------------------
+ t
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::numeric;
+ jsonb_path_query_first_tz
+---------------------------
+ 99
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::int8;
+ jsonb_path_query_first_tz
+---------------------------
+ 99
+(1 row)
+
+-- nested path
+SELECT (jsonb_path_query_first_tz('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+ jsonb_path_query_first_tz
+---------------------------
+ 42
+(1 row)
+
+-- array access
+SELECT (jsonb_path_query_first_tz('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+ jsonb_path_query_first_tz
+---------------------------
+ 20
+(1 row)
+
+-- Section T3: NULL semantics
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+ jsonb_path_query_first_tz
+---------------------------
+
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+ jsonb_path_query_first_tz
+---------------------------
+
+(1 row)
+
+-- Section T4: silent semantics
+SELECT (jsonb_path_query_first_tz('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+ERROR: cannot cast jsonb string to type integer
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict error
+ERROR: JSON object does not contain key "b"
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, NULL
+ jsonb_path_query_first_tz
+---------------------------
+
+(1 row)
+
+-- Section T5: type-mismatch errors
+SELECT (jsonb_path_query_first_tz('{"a":"hello"}'::jsonb, '$.a'))::int4;
+ERROR: cannot cast jsonb string to type integer
+-- Section T6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_tz_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_int4
+--------------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_tz_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_int8
+--------------------------------
+ 99
+(1 row)
+
+SELECT jsonb_path_query_first_tz_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_float8
+----------------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_tz_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_numeric
+-----------------------------------
+ 1.23
+(1 row)
+
+SELECT jsonb_path_query_first_tz_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_bool
+--------------------------------
+ t
+(1 row)
+
+-- Optimized typed extraction: int2 / float4 matrix completion
+-- Extends the existing typed extractor families to cover int2 (smallint)
+-- and float4 (real) target types.
+-- Section I1: direct calls to int2 / float4 typed extractor builtins (object-field)
+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)
+
+-- NULL semantics
+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)
+
+-- type-mismatch errors
+SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a');
+ERROR: cannot cast jsonb string to type smallint
+SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a');
+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
+-- int2 overflow
+SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
+ERROR: smallint out of range
+-- Section I2: planner rewrite verification — one per family (using int2)
+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_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_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int2(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int2(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int2(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- float4 rewrite (one representative)
+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 I3: multi-subscript automatic coverage
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int2(test_json, ARRAY['field6'::text, 'f1'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
+ int2
+------
+ 7
+(1 row)
+
+-- Section I4: representative execution through rewrite (distributed across families)
+-- object-field: int2
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+ int2
+------
+ 4
+(1 row)
+
+-- object-field: float4
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4
+--------
+ 4
+(1 row)
+
+-- array-element: int2
+SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+ int2
+------
+ 10
+(1 row)
+
+-- array-element: float4
+SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+ float4
+--------
+ 2.5
+(1 row)
+
+-- extract-path: float4
+SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4
+--------
+ 4
+(1 row)
+
+-- jsonb_path_query_first: int2
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int2;
+ jsonb_path_query_first
+------------------------
+ 42
+(1 row)
+
+-- jsonb_path_query_first_tz: float4
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float4;
+ jsonb_path_query_first_tz
+---------------------------
+ 3.14
+(1 row)
+
+-- Section I5: NULL through rewrite
+SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object';
+ int2
+------
+
+(1 row)
+
+SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr; -- JSON null element
+ float4
+--------
+
+(1 row)
+
+SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr; -- out of range
+ int2
+------
+
+(1 row)
+
+-- Section I6: type-mismatch through rewrite
+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
+-- Section I7: direct calls to other family builtins (representative)
+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 jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+ jsonb_extract_path_int2
+-------------------------
+ 7
+(1 row)
+
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_float4
+---------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int2
+-----------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_float4
+-------------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_tz_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_int2
+--------------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_tz_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_float4
+----------------------------------
+ 3.14
+(1 row)
+
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
?column?
----------
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index ed70c4d29ee..c3e1c7ae735 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -167,8 +167,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field7')::bool FROM test_json
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';
@@ -176,8 +174,10 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field7'])::bool FROM test_jsonb
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';
+
+-- Verify that int2 and float4 are also rewritten through the object-field family.
+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';
@@ -185,8 +185,6 @@ 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';
@@ -194,8 +192,10 @@ 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';
+
+-- Unsupported targets still work correctly through the original path
+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
@@ -206,10 +206,6 @@ SELECT (test_json -> 'field3')::int4 FROM test_jsonb WHERE json_type = 'object';
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
@@ -225,10 +221,6 @@ SELECT (test_json -> 'field1')::int8 FROM test_jsonb WHERE json_type = 'object';
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
@@ -242,10 +234,6 @@ 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');
@@ -253,10 +241,6 @@ 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');
@@ -264,14 +248,8 @@ SELECT jsonb_object_field_numeric('{"a": {"x":1}}'::jsonb, 'a'); -- container t
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');
-- Optimized typed extraction: array-element family
-- The planner rewrites (j->idx)::type and (j[idx])::type into direct
@@ -287,8 +265,6 @@ 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;
@@ -296,7 +272,6 @@ 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;
@@ -307,8 +282,6 @@ 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;
@@ -321,8 +294,6 @@ 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
@@ -334,8 +305,6 @@ 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
@@ -354,9 +323,6 @@ SELECT jsonb_array_element_float8('{"a":1}'::jsonb, 0); -- non-array
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);
-
-- Optimized typed extraction: extract-path family
-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
@@ -369,8 +335,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float8 FROM test_
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
-- Section P1b: planner rewrite verification (direct function-call form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field6', 'f1'))::int4 FROM test_jsonb WHERE json_type = 'object';
@@ -414,10 +378,6 @@ SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY['b']);
SELECT jsonb_extract_path_int4('{"a":"text"}'::jsonb, ARRAY['a']);
-- direct calls: array index in path
SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
--- direct calls: int2 and float4
-SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
-SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
-
-- Optimized typed extraction: multi-subscript chains
-- The planner lowers j['a']['b'], j['a'][0], etc. to the extract-path typed
@@ -433,10 +393,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::numeric FROM te
-- Verify single subscript still uses the existing object-field family, not extract-path
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
-
--- Section M1b: multi-subscript int2/float4
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
-SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
-- Section M2: execution through multi-subscript rewrite
SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int4;
SELECT (('{"a":[10,20,30]}'::jsonb)['a'][1])::int4;
@@ -462,6 +418,173 @@ SELECT (('{"a":{"b":[1,2]}}'::jsonb)['a']['b'])::int4; -- container to int4
EXPLAIN (VERBOSE, COSTS OFF)
SELECT (('{"a":[[10,20],[30,40]]}'::jsonb)['a'][i][0])::int4 FROM generate_series(0,1) AS i;
+-- Optimized typed extraction: jsonb_path_query_first family
+-- The planner rewrites (jsonb_path_query_first(j, path))::type into a direct
+-- typed extractor call for the same target types as the other families.
+
+-- Section Q1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section Q2: execution through rewrite
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int4;
+SELECT (jsonb_path_query_first('{"a":3.14}'::jsonb, '$.a'))::float8;
+SELECT (jsonb_path_query_first('{"a":true}'::jsonb, '$.a'))::bool;
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::numeric;
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::int8;
+-- nested object path
+SELECT (jsonb_path_query_first('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+-- array access in jsonpath
+SELECT (jsonb_path_query_first('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+
+-- Section Q3: NULL semantics
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+SELECT (jsonb_path_query_first('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+
+-- Section Q4: silent semantics
+-- silent=true suppresses jsonpath execution errors, NOT cast errors
+SELECT (jsonb_path_query_first('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+-- silent=false (default): jsonpath strict error
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict missing key error
+-- silent=true: jsonpath strict error suppressed
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, returns NULL
+
+-- Section Q5: type-mismatch errors
+SELECT (jsonb_path_query_first('{"a":"hello"}'::jsonb, '$.a'))::int4; -- string to int4
+SELECT (jsonb_path_query_first('{"a":[1,2]}'::jsonb, '$.a'))::int4; -- container to int4
+
+-- Section Q6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+-- direct call: missing path
+SELECT jsonb_path_query_first_int4('{"a":1}'::jsonb, '$.b'::jsonpath, '{}'::jsonb, false);
+-- direct call: with vars
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a ? (@ > $x)'::jsonpath, '{"x":10}'::jsonb, false);
+
+-- Optimized typed extraction: jsonb_path_query_first_tz family
+-- Same pattern as the non-tz family, but with timezone-aware evaluation.
+
+-- Section T1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section T2: execution through rewrite
+SELECT (jsonb_path_query_first_tz('{"a":42}'::jsonb, '$.a'))::int4;
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float8;
+SELECT (jsonb_path_query_first_tz('{"a":true}'::jsonb, '$.a'))::bool;
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::numeric;
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::int8;
+-- nested path
+SELECT (jsonb_path_query_first_tz('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+-- array access
+SELECT (jsonb_path_query_first_tz('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+
+-- Section T3: NULL semantics
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+SELECT (jsonb_path_query_first_tz('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+
+-- Section T4: silent semantics
+SELECT (jsonb_path_query_first_tz('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict error
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, NULL
+
+-- Section T5: type-mismatch errors
+SELECT (jsonb_path_query_first_tz('{"a":"hello"}'::jsonb, '$.a'))::int4;
+
+-- Section T6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_tz_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+
+-- Optimized typed extraction: int2 / float4 matrix completion
+-- Extends the existing typed extractor families to cover int2 (smallint)
+-- and float4 (real) target types.
+
+-- Section I1: direct calls to int2 / float4 typed extractor builtins (object-field)
+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');
+-- NULL semantics
+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');
+-- type-mismatch errors
+SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a');
+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
+-- int2 overflow
+SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
+
+-- Section I2: planner rewrite verification — one per family (using int2)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+-- float4 rewrite (one representative)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section I3: multi-subscript automatic coverage
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
+
+-- Section I4: representative execution through rewrite (distributed across families)
+-- object-field: int2
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+-- object-field: float4
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+-- array-element: int2
+SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+-- array-element: float4
+SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+-- extract-path: float4
+SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+-- jsonb_path_query_first: int2
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int2;
+-- jsonb_path_query_first_tz: float4
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float4;
+
+-- Section I5: NULL through rewrite
+SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr; -- JSON null element
+SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr; -- out of range
+
+-- Section I6: type-mismatch through rewrite
+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
+
+-- Section I7: direct calls to other family builtins (representative)
+SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
+SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+SELECT jsonb_path_query_first_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+
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';
@@ -1919,4 +2042,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;
\ No newline at end of file
+select ('{"text": "hello"}'::jsonb).text;
--
2.52.0
[application/octet-stream] v5-0004-jsonb-optimize-multi-subscript-casts-via-extract-.patch (14.6K, 7-v5-0004-jsonb-optimize-multi-subscript-casts-via-extract-.patch)
download | inline diff:
From 1c5bc428388e6a4f97c1e684b7705b98e5d0baf9 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Sun, 26 Apr 2026 18:49:33 -0700
Subject: [PATCH v5 4/5] jsonb: optimize multi-subscript casts via extract-path
rewrite
Extend the existing support-function rewrite to multi-subscript jsonb
subscripting chains, lowering them to the extract-path typed extractor
family already introduced by the series.
Only chains whose integer subscripts are all constants are eligible for
the rewrite; non-constant integer subscripts (which would require a
runtime CoerceViaIO conversion) cause the entire chain to be left in
its original form.
This keeps single-subscript behavior unchanged and supports casts to
numeric, bool, int2, int4, int8, float4, and float8.
---
src/backend/utils/adt/jsonb.c | 116 +++++++++++++++++---
src/test/regress/expected/jsonb.out | 157 ++++++++++++++++++++++++++++
src/test/regress/sql/jsonb.sql | 43 ++++++++
3 files changed, 301 insertions(+), 15 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 7eba437b8a4..d5c0871a213 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1835,6 +1835,7 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
* - jsonb_object_field(j, 'key') / j -> 'key' / j['key']
* - jsonb_array_element(j, idx) / j -> idx / j[idx]
* - jsonb_extract_path(j, ...) / j #> '{a,b}'
+ * - multi-subscript chains j['a']['b'], j['a'][0], etc.
*/
Datum
jsonb_cast_support(PG_FUNCTION_ARGS)
@@ -1885,35 +1886,120 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
else if (IsA(arg, SubscriptingRef))
{
SubscriptingRef *sbsref = (SubscriptingRef *) arg;
- Node *subscript;
- Oid subscript_type;
+ int nsubscripts;
/*
- * 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.
+ * Handle jsonb subscript access with no slice and no assignment.
+ * Single subscripts map to object-field or array-element
+ * extraction; multi-subscript chains lower to the extract-path
+ * family.
*/
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);
- subscript_type = exprType(subscript);
+ nsubscripts = list_length(sbsref->refupperindexpr);
- if (subscript_type == TEXTOID)
+ if (nsubscripts == 1)
{
- inner_funcid = F_JSONB_OBJECT_FIELD;
- inner_args = list_make2(sbsref->refexpr, subscript);
+ /*
+ * Single subscript: text maps to object-field, int4 maps to
+ * array-element.
+ */
+ Node *subscript;
+ Oid subscript_type;
+
+ subscript = (Node *) linitial(sbsref->refupperindexpr);
+ 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);
}
- else if (subscript_type == INT4OID)
+ else if (nsubscripts >= 2)
{
- inner_funcid = F_JSONB_ARRAY_ELEMENT;
- inner_args = list_make2(sbsref->refexpr, subscript);
+ /*
+ * Multi-subscript chain: build a text[] path and lower to
+ * the extract-path family. Each subscript must be text or
+ * a constant int4; non-constant integer subscripts cause
+ * the entire chain to be left unoptimized.
+ */
+ List *path_elems = NIL;
+ ListCell *lc;
+ ArrayExpr *aexpr;
+
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *subscript = (Node *) lfirst(lc);
+ Oid subscript_type = exprType(subscript);
+
+ if (subscript_type == TEXTOID)
+ {
+ path_elems = lappend(path_elems, subscript);
+ }
+ else if (subscript_type == INT4OID)
+ {
+ Const *con;
+
+ /*
+ * Only constant integer subscripts can be safely
+ * converted to text at plan time. Non-constant
+ * ones would require a runtime CoerceViaIO node;
+ * decline the rewrite for the entire chain.
+ */
+ if (!IsA(subscript, Const))
+ PG_RETURN_POINTER(NULL);
+
+ con = (Const *) subscript;
+
+ if (con->constisnull)
+ {
+ path_elems = lappend(path_elems,
+ makeNullConst(TEXTOID,
+ -1,
+ InvalidOid));
+ }
+ else
+ {
+ char *str;
+
+ str = DatumGetCString(
+ DirectFunctionCall1(int4out,
+ con->constvalue));
+ path_elems = lappend(path_elems,
+ makeConst(TEXTOID, -1, InvalidOid, -1,
+ CStringGetTextDatum(str),
+ false, false));
+ }
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+ }
+
+ aexpr = makeNode(ArrayExpr);
+ aexpr->array_typeid = TEXTARRAYOID;
+ aexpr->array_collid = InvalidOid;
+ aexpr->element_typeid = TEXTOID;
+ aexpr->elements = path_elems;
+ aexpr->multidims = false;
+ aexpr->list_start = -1;
+ aexpr->list_end = -1;
+ aexpr->location = -1;
+
+ inner_funcid = F_JSONB_EXTRACT_PATH;
+ inner_args = list_make2(sbsref->refexpr, aexpr);
}
else
PG_RETURN_POINTER(NULL);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index b96c095525c..3198442352c 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -1474,6 +1474,163 @@ SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
3.14
(1 row)
+-- Optimized typed extraction: multi-subscript chains
+-- The planner lowers j['a']['b'], j['a'][0], etc. to the extract-path typed
+-- extractor family, reusing the same functions as the #> operator path.
+-- Section M1: planner rewrite verification for multi-subscript chains
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int4(test_json, ARRAY['field6'::text, 'f1'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field5'][0])::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int4(test_json, ARRAY['field5'::text, '0'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[6]['k'])::int4 FROM test_jsonb_arr;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+ Output: jsonb_extract_path_int4(test_arr, ARRAY['6'::text, 'k'::text])
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_float8(test_json, ARRAY['field6'::text, 'f1'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::numeric FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_numeric(test_json, ARRAY['field6'::text, 'f1'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Verify single subscript still uses the existing object-field family, not extract-path
+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)
+
+-- Section M1b: multi-subscript int2/float4
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int2(test_json, ARRAY['field6'::text, 'f1'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
+ int2
+------
+ 7
+(1 row)
+
+-- Section M2: execution through multi-subscript rewrite
+SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int4;
+ int4
+------
+ 42
+(1 row)
+
+SELECT (('{"a":[10,20,30]}'::jsonb)['a'][1])::int4;
+ int4
+------
+ 20
+(1 row)
+
+SELECT (('[{"a":true}]'::jsonb)[0]['a'])::bool;
+ bool
+------
+ t
+(1 row)
+
+SELECT (('[[1,2],[3,4]]'::jsonb)[0][1])::int4;
+ int4
+------
+ 2
+(1 row)
+
+SELECT (('{"a":[{"b":3.14}]}'::jsonb)['a'][0]['b'])::float8;
+ float8
+--------
+ 3.14
+(1 row)
+
+SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::numeric;
+ numeric
+---------
+ 42
+(1 row)
+
+SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int8;
+ int8
+------
+ 42
+(1 row)
+
+-- negative index in nested chain
+SELECT (('{"a":[10,20,30]}'::jsonb)['a'][-1])::int4;
+ int4
+------
+ 30
+(1 row)
+
+-- Section M3: NULL semantics for multi-subscript chains
+SELECT (('{"a":1}'::jsonb)['x']['b'])::int4; -- missing intermediate key
+ int4
+------
+
+(1 row)
+
+SELECT (('{"a":{"c":1}}'::jsonb)['a']['b'])::int4; -- missing final key
+ int4
+------
+
+(1 row)
+
+SELECT (('{"a":{"b":null}}'::jsonb)['a']['b'])::int4; -- JSON null leaf
+ int4
+------
+
+(1 row)
+
+SELECT (('{"a":[1]}'::jsonb)['a'][5])::int4; -- out-of-range nested index
+ int4
+------
+
+(1 row)
+
+-- Section M4: type-mismatch errors for multi-subscript chains
+SELECT (('{"a":{"b":"hello"}}'::jsonb)['a']['b'])::int4; -- string to int4
+ERROR: cannot cast jsonb string to type integer
+SELECT (('{"a":{"b":[1,2]}}'::jsonb)['a']['b'])::int4; -- container to int4
+ERROR: cannot cast jsonb array to type integer
+-- Section M5: non-constant int4 subscript declines rewrite
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT (('{"a":[[10,20],[30,40]]}'::jsonb)['a'][i][0])::int4 FROM generate_series(0,1) AS i;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Function Scan on pg_catalog.generate_series i
+ Output: (('{"a": [[10, 20], [30, 40]]}'::jsonb)['a'::text][i][0])::integer
+ Function Call: generate_series(0, 1)
+(3 rows)
+
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 11191fd0f82..ed70c4d29ee 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -419,6 +419,49 @@ SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+-- Optimized typed extraction: multi-subscript chains
+-- The planner lowers j['a']['b'], j['a'][0], etc. to the extract-path typed
+-- extractor family, reusing the same functions as the #> operator path.
+
+-- Section M1: planner rewrite verification for multi-subscript chains
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field5'][0])::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[6]['k'])::int4 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::float8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::numeric FROM test_jsonb WHERE json_type = 'object';
+
+-- Verify single subscript still uses the existing object-field family, not extract-path
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
+
+
+-- Section M1b: multi-subscript int2/float4
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
+-- Section M2: execution through multi-subscript rewrite
+SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int4;
+SELECT (('{"a":[10,20,30]}'::jsonb)['a'][1])::int4;
+SELECT (('[{"a":true}]'::jsonb)[0]['a'])::bool;
+SELECT (('[[1,2],[3,4]]'::jsonb)[0][1])::int4;
+SELECT (('{"a":[{"b":3.14}]}'::jsonb)['a'][0]['b'])::float8;
+SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::numeric;
+SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int8;
+-- negative index in nested chain
+SELECT (('{"a":[10,20,30]}'::jsonb)['a'][-1])::int4;
+
+-- Section M3: NULL semantics for multi-subscript chains
+SELECT (('{"a":1}'::jsonb)['x']['b'])::int4; -- missing intermediate key
+SELECT (('{"a":{"c":1}}'::jsonb)['a']['b'])::int4; -- missing final key
+SELECT (('{"a":{"b":null}}'::jsonb)['a']['b'])::int4; -- JSON null leaf
+SELECT (('{"a":[1]}'::jsonb)['a'][5])::int4; -- out-of-range nested index
+
+-- Section M4: type-mismatch errors for multi-subscript chains
+SELECT (('{"a":{"b":"hello"}}'::jsonb)['a']['b'])::int4; -- string to int4
+SELECT (('{"a":{"b":[1,2]}}'::jsonb)['a']['b'])::int4; -- container to int4
+
+-- Section M5: non-constant int4 subscript declines rewrite
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT (('{"a":[[10,20],[30,40]]}'::jsonb)['a'][i][0])::int4 FROM generate_series(0,1) AS i;
+
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: <CABXr29EZNEgjJVNRX3gLOjRZVjXT6tda2_m8DdxQTfr8s8mkEQ@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