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 11:35:32 -0700
Message-ID: <CABXr29Fk2-32d7JhgbJLpnBTJKX51s0a-Ae75ZcPxLCEuUydZg@mail.gmail.com> (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]>
<[email protected]>
On Mon, Apr 6, 2026 at 10:57 AM Haibo Yan <[email protected]> wrote:
>
>
> 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
>
>
> This is quite embarrassing — it looks like my attachment got lost
somewhere in transit. I’m not sure what happened. I did confirm locally
that the attachment was included, but I can’t see it in the mail thread.
I’m now trying to resend it using a different mail client. Hopefully this
won’t cause too much disruption for everyone.
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: <CABXr29Fk2-32d7JhgbJLpnBTJKX51s0a-Ae75ZcPxLCEuUydZg@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