public inbox for [email protected]
help / color / mirror / Atom feedFrom: Haibo Yan <[email protected]>
To: Dmitry Dolgov <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Andy Fan <[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: Mon, 6 Apr 2026 10:57:38 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
<[email protected]>
<mmilfltxeqgajpi4zok2v2moudot6bhdmwsqgjgnymz3nvkvng@k3caignaldcp>
<[email protected]>
<cudr6wao4hz2otlycx2z2equkigbjxslxvzqb3ejfc4amroheo@frqbvffcgqnu>
<[email protected]>
<CAApHDvozyjc8Qety7_aWeLaiNWGs59vBSoDNrNzXGgXbjL85NQ@mail.gmail.com>
<[email protected]>
<l6mh4jmhbmdxgoqbpcjuxa5x27age7h5xtrqzpe5sjg7sjmrsa@4g7wv4zw5o5i>
<[email protected]>
> On Apr 6, 2026, at 10:51 AM, Haibo Yan <[email protected]> wrote:
>
>
>> On Apr 6, 2026, at 6:05 AM, Dmitry Dolgov <[email protected]> wrote:
>>
>>> On Fri, Apr 03, 2026 at 09:42:20AM -0700, Haibo Yan wrote:
>>
>> Thanks for picking it up, the patch looks good.
>>
>>> One small thing: the UI looks a bit odd on my side. It does not seem to have picked my attachment, and is instead showing your older attachment there. But cfbot appears to have picked up mine, so I think it may just be a UI issue.
>>
>> It takes some time, as far as I can see currently the correct patch is shown.
>>
>>> + /*
>>> + * Identify the inner extraction expression. It may appear as
>>> + * either a FuncExpr or an OpExpr; accept both 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
>>> + PG_RETURN_POINTER(NULL);
>>
>> It may also appear as a SubscriptingRef expression if we use subscription over
>> jsonb.
>>
>> SELECT test_json['field7']::bool FROM test_jsonb WHERE json_type = 'object';
>>
>> Seems to be worth handling this case as well, since it doesn't lead to an
>> interface explosion.
>
>
>
> Thank you Dmitry
> I agree that handling SubscriptingRef here is the right thing to do.
>
> It stays within the same stage-1 scope, covers an equivalent object-field extraction form, and does not introduce any additional interface surface. Based on your suggestion, I updated the patch to recognize the SubscriptingRefrepresentation as well, so cases like:
>
> ----------------------------------------
>
> test_json['field7']::bool
>
> test_json['field4']::numeric
>
> ----------------------------------------
>
> can follow the same rewrite path as the existing jsonb_object_field(...) / -> cases.
>
> Please see the updated patch:
>
>
> <v2-0001-jsonb-optimize-object-field-casts-to-numeric-and-.patch>
>
> Thanks again for the review and suggestion.
>
> Regards,
>
> Haibo
>
It looks like the attachment may have been dropped in transit. I’m reattaching it here.
Regards,
Haibo

Attachments:
[application/octet-stream] v2-0001-jsonb-optimize-object-field-casts-to-numeric-and-bool.patch (23.5K, 3-v2-0001-jsonb-optimize-object-field-casts-to-numeric-and-bool.patch)
download | inline diff:
From 327358c08d803bf0c00f7fe897f8c2eb475b97a7 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Thu, 2 Apr 2026 21:22:54 -0700
Subject: [PATCH v2] jsonb: optimize object-field casts to numeric and bool
Add a support function for jsonb-to-numeric/bool casts that
recognizes casts over jsonb object-field extraction, including
jsonb_object_field(), ->, and subscripting by key, and rewrites
them to explicit typed extractor calls.
This keeps ordinary SQL syntax unchanged while avoiding extra
jsonb scalar wrapping/unwrapping on the optimized path.
Stage 1 only: covers object-field extraction to numeric/bool.
---
src/backend/utils/adt/jsonb.c | 126 +++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 136 ++++++++++++++++++
src/include/catalog/pg_proc.dat | 18 ++-
src/test/regress/expected/jsonb.out | 205 +++++++++++++++++++++++++++-
src/test/regress/sql/jsonb.sql | 63 ++++++++-
5 files changed, 543 insertions(+), 5 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 864c5ac1c85..1ee15679733 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,129 @@ 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 and jsonb_bool 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
+ 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..fe11f5edd55 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -921,6 +921,142 @@ 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)));
+}
+
+/*
+ * jsonb_object_field_numeric - extract a numeric value from a jsonb object
+ * by field name. Returns NULL for missing keys and JSON nulls.
+ */
+Datum
+jsonb_object_field_numeric(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ JsonbValue vbuf;
+ JsonbValue *v;
+ Numeric retValue;
+
+ v = jsonb_object_field_lookup(jb, key, &vbuf);
+ if (v == NULL)
+ PG_RETURN_NULL();
+
+ if (v->type != jbvNumeric)
+ jsonb_field_cast_error(v, "numeric");
+
+ retValue = DatumGetNumericCopy(NumericGetDatum(v->val.numeric));
+
+ PG_FREE_IF_COPY(jb, 0);
+ PG_RETURN_NUMERIC(retValue);
+}
+
+/*
+ * jsonb_object_field_bool - extract a boolean value from a jsonb object
+ * by field name. Returns NULL for missing keys and JSON nulls.
+ */
+Datum
+jsonb_object_field_bool(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ JsonbValue vbuf;
+ JsonbValue *v;
+
+ v = jsonb_object_field_lookup(jb, key, &vbuf);
+ if (v == NULL)
+ PG_RETURN_NULL();
+
+ if (v->type != jbvBool)
+ jsonb_field_cast_error(v, "boolean");
+
+ PG_FREE_IF_COPY(jb, 0);
+ PG_RETURN_BOOL(v->val.boolean);
+}
+
Datum
json_array_element(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3579cec5744..9a698ea3104 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4792,10 +4792,10 @@
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',
@@ -12851,4 +12851,18 @@
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 => '9951', 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 => '9952', 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' },
+
]
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4e2467852db..11f9cccb649 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -458,7 +458,207 @@ 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 and bool only.
+-- 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)
+
+-- 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)
+
+-- Verify that unsupported cast targets are NOT rewritten by this patch.
+-- Integer and float casts remain on the original jsonb_object_field + cast 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: ((test_json -> 'field4'::text))::integer
+ 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: ((test_json -> 'field4'::text))::bigint
+ 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: ((test_json -> 'field4'::text))::double precision
+ 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)
+
+-- 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)
+
+-- Unsupported targets still work correctly through the original path
+SELECT (test_json -> 'field4')::int4 FROM test_jsonb WHERE json_type = 'object';
+ int4
+------
+ 4
+(1 row)
+
+SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object';
+ float8
+--------
+ 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)
+
+-- 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)
+
+-- 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
+-- 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
+-- 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)
+
+-- 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)
+
+-- 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 test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
?column?
----------
@@ -586,7 +786,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..09836bca0a9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -155,7 +155,68 @@ 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 and bool only.
+
+-- 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';
+
+-- 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';
+
+-- Verify that unsupported cast targets are NOT rewritten by this patch.
+-- Integer and float casts remain on the original jsonb_object_field + cast path.
+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 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';
+
+-- 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';
+
+-- Unsupported targets still work correctly through the original path
+SELECT (test_json -> 'field4')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::float8 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
+
+-- 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
+
+-- 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
+
+-- Section 4b: type-mismatch error through subscripting syntax
+SELECT (test_json['field1'])::numeric FROM test_jsonb WHERE json_type = 'object'; -- string to numeric
+
+-- 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');
+-- 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');
+-- 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 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: <[email protected]>
* 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