public inbox for [email protected]
help / color / mirror / Atom feedFrom: Haibo Yan <[email protected]>
To: Dmitry Dolgov <[email protected]>
Cc: Andy Fan <[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: Thu, 2 Apr 2026 21:46:22 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <cudr6wao4hz2otlycx2z2equkigbjxslxvzqb3ejfc4amroheo@frqbvffcgqnu>
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>
On Nov 22, 2024, at 10:14 AM, Dmitry Dolgov <[email protected]> wrote:
>
>> On Mon, Nov 18, 2024 at 08:23:52AM GMT, Andy Fan wrote:
>>
>>>>> I imagined you'd the patch should create a SupportRequestSimplify
>>>>> support function for jsonb_numeric() that checks if the input
>>>>> expression is an OpExpr with funcid of jsonb_object_field(). All you
>>>>> do then is ditch the cast and change the OpExpr to call a new function
>>>>> named jsonb_object_field_numeric() which returns the val.numeric
>>>>> directly. Likely the same support function could handle jsonb casts
>>>>> to other types too, in which case you'd just call some other function,
>>>>> e.g jsonb_object_field_timestamp() or jsonb_object_field_boolean().
>>>>
>>>> Basically yes. The reason complexity comes when we many operators we
>>>> want to optimize AND my patch I want to reduce the number of function
>>>> created.
>>>>
>>>> [...]
>>>>
>>>> Within the start / finish function, we need to create *7* functions.
>>>
>>> Any particular reason you want to keep number of functions minimal? Is
>>> it just to make the patch smaller? I might be missing something without
>>> looking at the implementation in details, but the difference between 10
>>> and 7 functions doesn't seem to be significant.
>>
>> Another reason is for reducing code duplication, writting too many
>> similar function looks not good to me. Chapman expressed this idea
>> first at [1]. Search "it would make me happy to further reduce some
>> of the code" in the message.
>>
>> Acutally this doesn't make the patch complexer too much.
>>
>> [1]
>> https://www.postgresql.org/message-id/5138c6b5fd239e7ce4e1a4e63826ac27%40anastigmatix.net
>
> It might not make everything too much complex, but e.g. relabeling of
> the first argument for a "finish" function into an internal one sounds
> strange to me. Maybe there is a way to avoid duplication of the code,
> but keep all needed functions in pg_proc?
>
> Btw, sorry to complain about small details, but I find start / finish
> naming pattern not quite fitting here. Their main purpose is to extract
> / convert a value, the order in which they are happening is less
> relevant.
>
>
>
>
Hi all,
I’d like to continue pushing this patch forward.
Based on the earlier discussion, I reworked the patch into a smaller stage-1 version with a narrower scope and a simpler rewrite strategy. The current patch keeps the normal SQL syntax unchanged and uses support-function simplification to rewrite only the following patterns:
(jsonb_object_field(...))::numeric
jsonb_object_field(...))::bool
into explicit typed extractor calls.
So at this stage it intentionally covers only:
jsonb_object_field(...) / ->
casts to numeric
casts to bool
and does not yet try to cover array/path extraction or integer/float typed extractors.
I also ran a small microbenchmark to isolate the cast-over-object-field path. On my setup, the current patch shows the following gains:
Query Before After Speedup
--------------------------------------------------------------------------------
SELECT sum((j->'n')::numeric) FROM t 118.028 ms 56.082 ms 2.10x
SELECT count(*) FROM t WHERE (j->'b')::bool 115.665 ms 51.945 ms 2.23x
--------------------------------------------------------------------------------
These are microbenchmark numbers rather than end-to-end workload results, but they suggest that the simplified rewrite path is worth pursuing.
My goal with this version is not to solve the full matrix at once, but to first land a reviewer-friendly subset that:
1. does not introduce new user-visible operators,
2. keeps ordinary cast syntax unchanged,
3. avoids the more abstract internal/start-finish style machinery,
4. and uses explicit rewrite targets that are easier to review.
If this direction looks reasonable, I’d appreciate another round of review on the updated patch. If people think the stage-1 scope is acceptable, I can continue with follow-up patches for additional extractor families and target types.
Thanks,
Haibo
Attachments:
[application/octet-stream] 0001-jsonb-optimize-object-field-casts-to-numeric-and-boo.patch (19.8K, 2-0001-jsonb-optimize-object-field-casts-to-numeric-and-boo.patch)
download | inline diff:
From 077780e4e91b3cb4a4f37a298615f01f5e4122c2 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Thu, 2 Apr 2026 21:22:54 -0700
Subject: [PATCH] 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()/-> expressions 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 | 96 +++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 136 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 18 +++-
src/test/regress/expected/jsonb.out | 159 +++++++++++++++++++++++++++-
src/test/regress/sql/jsonb.sql | 48 ++++++++-
5 files changed, 452 insertions(+), 5 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 864c5ac1c85..fccbb2fbab6 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,6 +17,8 @@
#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/supportnodes.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/json.h"
@@ -1816,6 +1818,100 @@ 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
+ * 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);
+
+ /* 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..81091ea9038 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -458,7 +458,161 @@ 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)
+
+-- 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)
+
+-- 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 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 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 +740,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..5cb16b7fe2b 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -155,7 +155,53 @@ 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';
+
+-- 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';
+
+-- 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 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 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