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: Wed, 8 Apr 2026 12:50:50 -0700
Message-ID: <CABXr29ETPyi1Tt+X29oUa6Q9AmT3j7RZSdsp12H_6EzGw7RqnQ@mail.gmail.com> (raw)
In-Reply-To: <CABXr29HN04CqHBGZ_k6_jQmCL5FO59tDE4PUL9U56Rpu=d4=0A@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>
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
Attachments:
[application/octet-stream] v4-0001-jsonb-optimize-object-field-casts-to-scalar-types.patch (35.5K, 3-v4-0001-jsonb-optimize-object-field-casts-to-scalar-types.patch)
download | inline diff:
From 73883d06cc20117205160fc23420c047d4dd87c9 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Thu, 2 Apr 2026 21:22:54 -0700
Subject: [PATCH v4 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,
int4, int8, and float8.
Co-authored-by: Andy Fan <[email protected]>
---
src/backend/utils/adt/jsonb.c | 142 +++++++++++
src/backend/utils/adt/jsonfuncs.c | 173 +++++++++++++
src/include/catalog/pg_proc.dat | 39 ++-
src/test/regress/expected/jsonb.out | 368 +++++++++++++++++++++++++++-
src/test/regress/sql/jsonb.sql | 96 +++++++-
5 files changed, 810 insertions(+), 8 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 864c5ac1c85..25495007076 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,145 @@ 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
+ 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)
{
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 97cc3d60340..ffc5b856e13 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -921,6 +921,179 @@ 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));
+}
+
+/*
+ * 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)
+
Datum
json_array_element(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fa9ae79082b..e0bcff46441 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4798,25 +4798,28 @@
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',
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',
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 +12772,30 @@
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' },
+
]
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4e2467852db..7f4c6673760 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -458,7 +458,370 @@ 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, int4, int8, 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)
+
+-- 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)
+
+-- Verify that unsupported cast targets are NOT rewritten by this patch.
+-- int2 and float4 casts remain on the original jsonb_object_field + cast path.
+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: ((test_json -> 'field4'::text))::smallint
+ 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: ((test_json -> 'field4'::text))::real
+ 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)
+
+-- 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)
+
+-- 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';
+ float4
+--------
+ 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)
+
+-- 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
+-- 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)
+
+-- 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)
+
+-- 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
+-- direct calls: integer overflow
+SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
+ERROR: integer out of range
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
?column?
----------
@@ -586,7 +949,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..7e4d86b0bdd 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -155,7 +155,101 @@ 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, int4, int8, 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';
+
+-- 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';
+
+-- Verify that unsupported cast targets are NOT rewritten by this patch.
+-- int2 and float4 casts remain on the original jsonb_object_field + cast path.
+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';
+
+-- 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';
+
+-- 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
+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
+
+-- 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
+
+-- 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');
+-- 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');
+-- 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
+-- direct calls: integer overflow
+SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
--
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: <CABXr29ETPyi1Tt+X29oUa6Q9AmT3j7RZSdsp12H_6EzGw7RqnQ@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