v6-0005-jsonb-optimize-jsonpath-first-casts-to-scalar-typ.patch
application/octet-stream
Filename: v6-0005-jsonb-optimize-jsonpath-first-casts-to-scalar-typ.patch
Type: application/octet-stream
Part: 4
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v6-0005
Subject: jsonb: optimize jsonpath-first casts to scalar types
| File | + | − |
|---|---|---|
| src/backend/utils/adt/jsonb.c | 98 | 4 |
| src/backend/utils/adt/jsonfuncs.c | 9 | 8 |
| src/backend/utils/adt/jsonpath_exec.c | 113 | 0 |
| src/include/catalog/pg_proc.dat | 73 | 0 |
| src/include/utils/jsonfuncs.h | 10 | 0 |
| src/test/regress/expected/jsonb.out | 612 | 234 |
| src/test/regress/sql/jsonb.sql | 176 | 53 |
From 2b750fc74d2f8fbc1ba070ccf317c112b35d59e5 Mon Sep 17 00:00:00 2001
From: Haibo Yan <haibo.yan@apple.com>
Date: Fri, 10 Apr 2026 14:34:24 -0700
Subject: [PATCH v6 5/5] jsonb: optimize jsonpath-first casts to scalar types
Extend the existing support-function rewrite to jsonb_path_query_first
and jsonb_path_query_first_tz so casts are rewritten directly to
explicit typed extractor functions.
This patch supports numeric, bool, int2, int4, int8, float4, and
float8, and leaves jsonb_path_query out of scope.
---
src/backend/utils/adt/jsonb.c | 102 +++-
src/backend/utils/adt/jsonfuncs.c | 17 +-
src/backend/utils/adt/jsonpath_exec.c | 113 ++++
src/include/catalog/pg_proc.dat | 73 +++
src/include/utils/jsonfuncs.h | 10 +
src/test/regress/expected/jsonb.out | 846 +++++++++++++++++++-------
src/test/regress/sql/jsonb.sql | 229 +++++--
7 files changed, 1091 insertions(+), 299 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index d5c0871a213..2985777f1c9 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1836,6 +1836,8 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
* - jsonb_array_element(j, idx) / j -> idx / j[idx]
* - jsonb_extract_path(j, ...) / j #> '{a,b}'
* - multi-subscript chains j['a']['b'], j['a'][0], etc.
+ * - jsonb_path_query_first(j, path [, vars [, silent]])
+ * - jsonb_path_query_first_tz(j, path [, vars [, silent]])
*/
Datum
jsonb_cast_support(PG_FUNCTION_ARGS)
@@ -2012,13 +2014,13 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
/*
* Verify the inner extraction function and map the outer cast to the
* corresponding typed extractor. Each supported extraction family
- * has its own set of typed rewrite targets.
+ * has its own set of typed rewrite targets and expected arg count.
*/
- if (list_length(inner_args) != 2)
- PG_RETURN_POINTER(NULL);
-
if (inner_funcid == F_JSONB_OBJECT_FIELD)
{
+ if (list_length(inner_args) != 2)
+ PG_RETURN_POINTER(NULL);
+
if (fexpr->funcid == F_NUMERIC_JSONB)
{
replacement_funcid = F_JSONB_OBJECT_FIELD_NUMERIC;
@@ -2059,6 +2061,9 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
}
else if (inner_funcid == F_JSONB_ARRAY_ELEMENT)
{
+ if (list_length(inner_args) != 2)
+ PG_RETURN_POINTER(NULL);
+
if (fexpr->funcid == F_NUMERIC_JSONB)
{
replacement_funcid = F_JSONB_ARRAY_ELEMENT_NUMERIC;
@@ -2099,6 +2104,9 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
}
else if (inner_funcid == F_JSONB_EXTRACT_PATH)
{
+ if (list_length(inner_args) != 2)
+ PG_RETURN_POINTER(NULL);
+
if (fexpr->funcid == F_NUMERIC_JSONB)
{
replacement_funcid = F_JSONB_EXTRACT_PATH_NUMERIC;
@@ -2137,6 +2145,92 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
else
PG_RETURN_POINTER(NULL);
}
+ else if (inner_funcid == F_JSONB_PATH_QUERY_FIRST)
+ {
+ if (list_length(inner_args) != 4)
+ PG_RETURN_POINTER(NULL);
+
+ if (fexpr->funcid == F_NUMERIC_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_NUMERIC;
+ replacement_rettype = NUMERICOID;
+ }
+ else if (fexpr->funcid == F_BOOL_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_BOOL;
+ replacement_rettype = BOOLOID;
+ }
+ else if (fexpr->funcid == F_INT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_INT4;
+ replacement_rettype = INT4OID;
+ }
+ else if (fexpr->funcid == F_INT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_INT8;
+ replacement_rettype = INT8OID;
+ }
+ else if (fexpr->funcid == F_FLOAT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_FLOAT8;
+ replacement_rettype = FLOAT8OID;
+ }
+ else if (fexpr->funcid == F_INT2_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_INT2;
+ replacement_rettype = INT2OID;
+ }
+ else if (fexpr->funcid == F_FLOAT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_FLOAT4;
+ replacement_rettype = FLOAT4OID;
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+ }
+ else if (inner_funcid == F_JSONB_PATH_QUERY_FIRST_TZ)
+ {
+ if (list_length(inner_args) != 4)
+ PG_RETURN_POINTER(NULL);
+
+ if (fexpr->funcid == F_NUMERIC_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_NUMERIC;
+ replacement_rettype = NUMERICOID;
+ }
+ else if (fexpr->funcid == F_BOOL_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_BOOL;
+ replacement_rettype = BOOLOID;
+ }
+ else if (fexpr->funcid == F_INT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_INT4;
+ replacement_rettype = INT4OID;
+ }
+ else if (fexpr->funcid == F_INT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_INT8;
+ replacement_rettype = INT8OID;
+ }
+ else if (fexpr->funcid == F_FLOAT8_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_FLOAT8;
+ replacement_rettype = FLOAT8OID;
+ }
+ else if (fexpr->funcid == F_INT2_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_INT2;
+ replacement_rettype = INT2OID;
+ }
+ else if (fexpr->funcid == F_FLOAT4_JSONB)
+ {
+ replacement_funcid = F_JSONB_PATH_QUERY_FIRST_TZ_FLOAT4;
+ replacement_rettype = FLOAT4OID;
+ }
+ else
+ PG_RETURN_POINTER(NULL);
+ }
else
PG_RETURN_POINTER(NULL);
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index b3fd1dfb631..32cdb5c61b8 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -969,7 +969,7 @@ jsonb_object_field_lookup(Jsonb *jb, text *key, JsonbValue *vbuf)
* The message wording matches cannotCastJsonbValue() in jsonb.c so that
* the optimized and unoptimized paths produce identical errors.
*/
-static void
+void
jsonb_field_cast_error(JsonbValue *v, const char *sqltype)
{
const char *jsontype;
@@ -1016,7 +1016,7 @@ jsonb_field_cast_error(JsonbValue *v, const char *sqltype)
* value as Datum. These are the only place where conversion semantics
* live; the wrapper macro below is intentionally kept thin.
*/
-static Datum
+Datum
jsonb_value_to_numeric_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1025,7 +1025,7 @@ jsonb_value_to_numeric_datum(JsonbValue *v)
return NumericGetDatum(DatumGetNumericCopy(NumericGetDatum(v->val.numeric)));
}
-static Datum
+Datum
jsonb_value_to_bool_datum(JsonbValue *v)
{
if (v->type != jbvBool)
@@ -1034,7 +1034,7 @@ jsonb_value_to_bool_datum(JsonbValue *v)
return BoolGetDatum(v->val.boolean);
}
-static Datum
+Datum
jsonb_value_to_int4_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1043,7 +1043,7 @@ jsonb_value_to_int4_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_int4, NumericGetDatum(v->val.numeric));
}
-static Datum
+Datum
jsonb_value_to_int8_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1052,7 +1052,7 @@ jsonb_value_to_int8_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_int8, NumericGetDatum(v->val.numeric));
}
-static Datum
+Datum
jsonb_value_to_float8_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1060,7 +1060,8 @@ jsonb_value_to_float8_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_float8, NumericGetDatum(v->val.numeric));
}
-static Datum
+
+Datum
jsonb_value_to_int2_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
@@ -1069,7 +1070,7 @@ jsonb_value_to_int2_datum(JsonbValue *v)
return DirectFunctionCall1(numeric_int2, NumericGetDatum(v->val.numeric));
}
-static Datum
+Datum
jsonb_value_to_float4_datum(JsonbValue *v)
{
if (v->type != jbvNumeric)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 0ec9b4df2ef..542c9ac720f 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -72,6 +72,7 @@
#include "utils/float.h"
#include "utils/formatting.h"
#include "utils/json.h"
+#include "utils/jsonfuncs.h"
#include "utils/jsonpath.h"
#include "utils/memutils.h"
#include "utils/timestamp.h"
@@ -687,6 +688,118 @@ jsonb_path_query_first_tz(PG_FUNCTION_ARGS)
return jsonb_path_query_first_internal(fcinfo, true);
}
+/*
+ * Wrapper macro for the jsonb_path_query_first_<type> extractor family.
+ *
+ * Invokes the jsonpath execution machinery and converts the first result
+ * directly to the target scalar type. The JsonValueList is kept on the
+ * caller's stack so that the pointer returned by JsonValueListHead() remains
+ * valid through the conversion call.
+ */
+#define DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+ Jsonb *jb = PG_GETARG_JSONB_P(0); \
+ JsonPath *jp = PG_GETARG_JSONPATH_P(1); \
+ Jsonb *vars = PG_GETARG_JSONB_P(2); \
+ bool silent = PG_GETARG_BOOL(3); \
+ JsonValueList found; \
+ JsonbValue *v; \
+ Datum result; \
+\
+ JsonValueListInit(&found); \
+\
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb, \
+ countVariablesFromJsonb, \
+ jb, !silent, &found, false); \
+\
+ if (JsonValueListIsEmpty(&found)) \
+ { \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ v = JsonValueListHead(&found); \
+ if (v->type == jbvNull) \
+ { \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ result = convfn(v); \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ return result; \
+}
+
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_float8, jsonb_value_to_float8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_int2, jsonb_value_to_int2_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TYPED(jsonb_path_query_first_float4, jsonb_value_to_float4_datum)
+
+/*
+ * Wrapper macro for the jsonb_path_query_first_tz_<type> extractor family.
+ * Same as above but with timezone-aware evaluation.
+ */
+#define DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(fname, convfn) \
+Datum \
+fname(PG_FUNCTION_ARGS) \
+{ \
+ Jsonb *jb = PG_GETARG_JSONB_P(0); \
+ JsonPath *jp = PG_GETARG_JSONPATH_P(1); \
+ Jsonb *vars = PG_GETARG_JSONB_P(2); \
+ bool silent = PG_GETARG_BOOL(3); \
+ JsonValueList found; \
+ JsonbValue *v; \
+ Datum result; \
+\
+ JsonValueListInit(&found); \
+\
+ (void) executeJsonPath(jp, vars, getJsonPathVariableFromJsonb, \
+ countVariablesFromJsonb, \
+ jb, !silent, &found, true); \
+\
+ if (JsonValueListIsEmpty(&found)) \
+ { \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ v = JsonValueListHead(&found); \
+ if (v->type == jbvNull) \
+ { \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ PG_RETURN_NULL(); \
+ } \
+\
+ result = convfn(v); \
+ PG_FREE_IF_COPY(jb, 0); \
+ PG_FREE_IF_COPY(jp, 1); \
+ PG_FREE_IF_COPY(vars, 2); \
+ return result; \
+}
+
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_numeric, jsonb_value_to_numeric_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_bool, jsonb_value_to_bool_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_int4, jsonb_value_to_int4_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_int8, jsonb_value_to_int8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_float8, jsonb_value_to_float8_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_int2, jsonb_value_to_int2_datum)
+DEFINE_JSONB_PATH_QUERY_FIRST_TZ_TYPED(jsonb_path_query_first_tz_float4, jsonb_value_to_float4_datum)
+
/********************Execute functions for JsonPath**************************/
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d8ac9cbf053..921e278a045 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12847,6 +12847,56 @@
proname => 'jsonb_extract_path_float8', prorettype => 'float8',
proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_extract_path_float8' },
+{ oid => '9970', descr => 'jsonpath query first item as numeric',
+ proname => 'jsonb_path_query_first_numeric', prorettype => 'numeric',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_numeric' },
+{ oid => '9971', descr => 'jsonpath query first item as bool',
+ proname => 'jsonb_path_query_first_bool', prorettype => 'bool',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_bool' },
+{ oid => '9972', descr => 'jsonpath query first item as int4',
+ proname => 'jsonb_path_query_first_int4', prorettype => 'int4',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_int4' },
+{ oid => '9973', descr => 'jsonpath query first item as int8',
+ proname => 'jsonb_path_query_first_int8', prorettype => 'int8',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_int8' },
+{ oid => '9974', descr => 'jsonpath query first item as float8',
+ proname => 'jsonb_path_query_first_float8', prorettype => 'float8',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_float8' },
+{ oid => '9975', descr => 'jsonpath query first item as numeric with timezone',
+ proname => 'jsonb_path_query_first_tz_numeric', provolatile => 's',
+ prorettype => 'numeric', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_numeric' },
+{ oid => '9976', descr => 'jsonpath query first item as bool with timezone',
+ proname => 'jsonb_path_query_first_tz_bool', provolatile => 's',
+ prorettype => 'bool', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_bool' },
+{ oid => '9977', descr => 'jsonpath query first item as int4 with timezone',
+ proname => 'jsonb_path_query_first_tz_int4', provolatile => 's',
+ prorettype => 'int4', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_int4' },
+{ oid => '9978', descr => 'jsonpath query first item as int8 with timezone',
+ proname => 'jsonb_path_query_first_tz_int8', provolatile => 's',
+ prorettype => 'int8', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_int8' },
+{ oid => '9979', descr => 'jsonpath query first item as float8 with timezone',
+ proname => 'jsonb_path_query_first_tz_float8', provolatile => 's',
+ prorettype => 'float8', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_float8' },
{ oid => '9982', descr => 'extract int2 from jsonb array by element index',
proname => 'jsonb_array_element_int2', prorettype => 'int2',
proargtypes => 'jsonb int4', proargnames => '{from_json,element_index}',
@@ -12865,4 +12915,27 @@
proargtypes => 'jsonb _text', proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_extract_path_float4' },
+# jsonb_path_query_first family: int2 / float4
+{ oid => '9986', descr => 'jsonpath query first item as int2',
+ proname => 'jsonb_path_query_first_int2', prorettype => 'int2',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_int2' },
+{ oid => '9987', descr => 'jsonpath query first item as float4',
+ proname => 'jsonb_path_query_first_float4', prorettype => 'float4',
+ proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_float4' },
+
+# jsonb_path_query_first_tz family: int2 / float4
+{ oid => '9988', descr => 'jsonpath query first item as int2 with timezone',
+ proname => 'jsonb_path_query_first_tz_int2', provolatile => 's',
+ prorettype => 'int2', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_int2' },
+{ oid => '9989', descr => 'jsonpath query first item as float4 with timezone',
+ proname => 'jsonb_path_query_first_tz_float4', provolatile => 's',
+ prorettype => 'float4', proargtypes => 'jsonb jsonpath jsonb bool',
+ proargnames => '{target,path,vars,silent}',
+ prosrc => 'jsonb_path_query_first_tz_float4' },
]
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 27713be3aeb..ce55fe1b1ce 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -91,6 +91,16 @@ extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
extern Datum jsonb_from_text(text *js, bool unique_keys);
+/* Typed field extraction helpers (shared by jsonfuncs.c and jsonpath_exec.c) */
+extern void jsonb_field_cast_error(JsonbValue *v, const char *sqltype);
+extern Datum jsonb_value_to_numeric_datum(JsonbValue *v);
+extern Datum jsonb_value_to_bool_datum(JsonbValue *v);
+extern Datum jsonb_value_to_int4_datum(JsonbValue *v);
+extern Datum jsonb_value_to_int8_datum(JsonbValue *v);
+extern Datum jsonb_value_to_float8_datum(JsonbValue *v);
+extern Datum jsonb_value_to_int2_datum(JsonbValue *v);
+extern Datum jsonb_value_to_float4_datum(JsonbValue *v);
+
extern Datum json_populate_type(Datum json_val, Oid json_type,
Oid typid, int32 typmod,
void **cache, MemoryContext mcxt,
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 3198442352c..7de2bcea3f7 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -503,22 +503,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float8 FROM test_js
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
---------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_object_field_int2(test_json, 'field4'::text)
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
-----------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_object_field_float4(test_json, 'field4'::text)
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-- Section 1b: planner rewrite verification for subscripting syntax
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::numeric FROM test_jsonb WHERE json_type = 'object';
QUERY PLAN
@@ -560,7 +544,8 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::float8 FROM test_json
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
+-- Verify that int2 and float4 are also rewritten through the object-field family.
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
QUERY PLAN
--------------------------------------------------------------
Seq Scan on pg_temp.test_jsonb
@@ -568,7 +553,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int2 FROM test_jsonb
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::float4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
QUERY PLAN
----------------------------------------------------------------
Seq Scan on pg_temp.test_jsonb
@@ -607,18 +592,6 @@ SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object
4
(1 row)
-SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
- int2
-------
- 4
-(1 row)
-
-SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
- float4
---------
- 4
-(1 row)
-
-- Section 2b: correct execution through subscripting syntax
SELECT (test_json['field4'])::numeric FROM test_jsonb WHERE json_type = 'object';
test_json
@@ -650,16 +623,17 @@ SELECT (test_json['field4'])::float8 FROM test_jsonb WHERE json_type = 'object';
4
(1 row)
-SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
- test_json
------------
- 4
+-- Unsupported targets still work correctly through the original path
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+ int2
+------
+ 4
(1 row)
-SELECT (test_json['field4'])::float4 FROM test_jsonb WHERE json_type = 'object';
- test_json
------------
- 4
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4
+--------
+ 4
(1 row)
-- Section 3: NULL semantics (missing key, JSON null, non-object input)
@@ -711,30 +685,6 @@ SELECT (test_json -> 'nonexistent')::float8 FROM test_jsonb WHERE json_type = 'o
(1 row)
-SELECT (test_json -> 'field3')::int2 FROM test_jsonb WHERE json_type = 'object'; -- JSON null, int2 path
- int2
-------
-
-(1 row)
-
-SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object'; -- missing key, int2
- int2
-------
-
-(1 row)
-
-SELECT (test_json -> 'field3')::float4 FROM test_jsonb WHERE json_type = 'object'; -- JSON null, float4 path
- float4
---------
-
-(1 row)
-
-SELECT (test_json -> 'nonexistent')::float4 FROM test_jsonb WHERE json_type = 'object'; -- missing key, float4
- float4
---------
-
-(1 row)
-
-- Section 3b: NULL semantics through subscripting syntax
SELECT (test_json['field3'])::numeric FROM test_jsonb WHERE json_type = 'object'; -- JSON null
test_json
@@ -771,14 +721,6 @@ SELECT (test_json -> 'field5')::int4 FROM test_jsonb WHERE json_type = 'object';
ERROR: cannot cast jsonb array or object to type integer
SELECT (test_json -> 'field5')::float8 FROM test_jsonb WHERE json_type = 'object'; -- array to float8
ERROR: cannot cast jsonb array or object to type double precision
-SELECT (test_json -> 'field1')::int2 FROM test_jsonb WHERE json_type = 'object'; -- string to int2
-ERROR: cannot cast jsonb string to type smallint
-SELECT (test_json -> 'field1')::float4 FROM test_jsonb WHERE json_type = 'object'; -- string to float4
-ERROR: cannot cast jsonb string to type real
-SELECT (test_json -> 'field5')::int2 FROM test_jsonb WHERE json_type = 'object'; -- array to int2
-ERROR: cannot cast jsonb array or object to type smallint
-SELECT (test_json -> 'field5')::float4 FROM test_jsonb WHERE json_type = 'object'; -- array to float4
-ERROR: cannot cast jsonb array or object to type real
-- Section 4b: type-mismatch error through subscripting syntax
SELECT (test_json['field1'])::numeric FROM test_jsonb WHERE json_type = 'object'; -- string to numeric
ERROR: cannot cast jsonb string to type numeric
@@ -827,30 +769,6 @@ SELECT jsonb_object_field_float8('{"a": 3.14}'::jsonb, 'a');
3.14
(1 row)
-SELECT jsonb_object_field_int2('{"a": 42}'::jsonb, 'a');
- jsonb_object_field_int2
--------------------------
- 42
-(1 row)
-
-SELECT jsonb_object_field_int2('{"a": 3}'::jsonb, 'a');
- jsonb_object_field_int2
--------------------------
- 3
-(1 row)
-
-SELECT jsonb_object_field_float4('{"a": 3.14}'::jsonb, 'a');
- jsonb_object_field_float4
----------------------------
- 3.14
-(1 row)
-
-SELECT jsonb_object_field_float4('{"a": 1.5}'::jsonb, 'a');
- jsonb_object_field_float4
----------------------------
- 1.5
-(1 row)
-
-- direct calls: NULL semantics
SELECT jsonb_object_field_numeric('{"a": 1}'::jsonb, 'missing');
jsonb_object_field_numeric
@@ -888,30 +806,6 @@ SELECT jsonb_object_field_float8('{"a": 1.0}'::jsonb, 'missing');
(1 row)
-SELECT jsonb_object_field_int2('{"a": 1}'::jsonb, 'missing');
- jsonb_object_field_int2
--------------------------
-
-(1 row)
-
-SELECT jsonb_object_field_int2('{"a": null}'::jsonb, 'a');
- jsonb_object_field_int2
--------------------------
-
-(1 row)
-
-SELECT jsonb_object_field_float4('{"a": 1.0}'::jsonb, 'missing');
- jsonb_object_field_float4
----------------------------
-
-(1 row)
-
-SELECT jsonb_object_field_float4('{"a": null}'::jsonb, 'a');
- jsonb_object_field_float4
----------------------------
-
-(1 row)
-
-- direct calls: type-mismatch errors
SELECT jsonb_object_field_numeric('{"a": "text"}'::jsonb, 'a');
ERROR: cannot cast jsonb string to type numeric
@@ -925,20 +819,9 @@ SELECT jsonb_object_field_int8('{"a": true}'::jsonb, 'a');
ERROR: cannot cast jsonb boolean to type bigint
SELECT jsonb_object_field_float8('{"a": [1,2]}'::jsonb, 'a'); -- container to float8
ERROR: cannot cast jsonb array or object to type double precision
-SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a'); -- string to int2
-ERROR: cannot cast jsonb string to type smallint
-SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a'); -- bool to float4
-ERROR: cannot cast jsonb boolean to type real
-SELECT jsonb_object_field_int2('{"a": [1,2]}'::jsonb, 'a'); -- container to int2
-ERROR: cannot cast jsonb array or object to type smallint
-SELECT jsonb_object_field_float4('{"a": {"x":1}}'::jsonb, 'a'); -- container to float4
-ERROR: cannot cast jsonb array or object to type real
-- direct calls: integer overflow
SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
ERROR: integer out of range
--- direct calls: int2 overflow
-SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
-ERROR: smallint out of range
-- Optimized typed extraction: array-element family
-- The planner rewrites (j->idx)::type and (j[idx])::type into direct
-- typed extractor calls for the same target types as the object-field family.
@@ -981,20 +864,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
Output: jsonb_array_element_int8(test_arr, 0)
(2 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
- QUERY PLAN
--------------------------------------------------
- Seq Scan on pg_temp.test_jsonb_arr
- Output: jsonb_array_element_int2(test_arr, 0)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
- QUERY PLAN
----------------------------------------------------
- Seq Scan on pg_temp.test_jsonb_arr
- Output: jsonb_array_element_float4(test_arr, 1)
-(2 rows)
-
-- Section A1b: planner rewrite verification (array subscripting form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::numeric FROM test_jsonb_arr;
QUERY PLAN
@@ -1031,13 +900,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int8 FROM test_jsonb_arr;
Output: jsonb_array_element_int8(test_arr, 0)
(2 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int2 FROM test_jsonb_arr;
- QUERY PLAN
--------------------------------------------------
- Seq Scan on pg_temp.test_jsonb_arr
- Output: jsonb_array_element_int2(test_arr, 0)
-(2 rows)
-
-- Section A1c: planner rewrite verification (direct function call form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_array_element(test_arr, 0))::int4 FROM test_jsonb_arr;
QUERY PLAN
@@ -1077,18 +939,6 @@ SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
t
(1 row)
-SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
- int2
-------
- 10
-(1 row)
-
-SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
- float4
---------
- 2.5
-(1 row)
-
-- Section A2b: correct execution through subscripting
SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
test_arr
@@ -1135,18 +985,6 @@ SELECT ('{"k":1}'::jsonb -> 0)::int4; -- non-array input
(1 row)
-SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr; -- out of range, int2
- int2
-------
-
-(1 row)
-
-SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr; -- JSON null element, float4
- float4
---------
-
-(1 row)
-
-- Section A3b: NULL through subscripting
SELECT (test_arr[99])::float8 FROM test_jsonb_arr; -- out of range
test_arr
@@ -1171,10 +1009,6 @@ SELECT (test_arr -> 6)::int8 FROM test_jsonb_arr; -- object container to int8
ERROR: cannot cast jsonb array or object to type bigint
SELECT (test_arr -> 2)::int4 FROM test_jsonb_arr; -- bool to int4
ERROR: cannot cast jsonb boolean to type integer
-SELECT (test_arr -> 4)::int2 FROM test_jsonb_arr; -- string to int2
-ERROR: cannot cast jsonb string to type smallint
-SELECT (test_arr -> 4)::float4 FROM test_jsonb_arr; -- string to float4
-ERROR: cannot cast jsonb string to type real
-- Section A4b: error through subscripting
SELECT (test_arr[4])::int8 FROM test_jsonb_arr; -- string to int8
ERROR: cannot cast jsonb string to type bigint
@@ -1235,18 +1069,6 @@ SELECT jsonb_array_element_int8('[true]'::jsonb, 0);
ERROR: cannot cast jsonb boolean to type bigint
SELECT jsonb_array_element_float8('[[1,2]]'::jsonb, 0); -- container to float8
ERROR: cannot cast jsonb array or object to type double precision
-SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
- jsonb_array_element_int2
---------------------------
- 10
-(1 row)
-
-SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
- jsonb_array_element_float4
-----------------------------
- 2.5
-(1 row)
-
-- Optimized typed extraction: extract-path family
-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
-- into direct typed extractor calls for the same target types.
@@ -1291,22 +1113,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_js
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
-------------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_extract_path_int2(test_json, '{field4}'::text[])
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
---------------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_extract_path_float4(test_json, '{field4}'::text[])
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-- Section P1b: planner rewrite verification (direct function-call form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
QUERY PLAN
@@ -1461,19 +1267,6 @@ SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
20
(1 row)
--- direct calls: int2 and float4
-SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
- jsonb_extract_path_int2
--------------------------
- 7
-(1 row)
-
-SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
- jsonb_extract_path_float4
----------------------------
- 3.14
-(1 row)
-
-- Optimized typed extraction: multi-subscript chains
-- The planner lowers j['a']['b'], j['a'][0], etc. to the extract-path typed
-- extractor family, reusing the same functions as the #> operator path.
@@ -1526,21 +1319,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int4 FROM test_jsonb
Filter: (test_jsonb.json_type = 'object'::text)
(3 rows)
--- Section M1b: multi-subscript int2/float4
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
- QUERY PLAN
----------------------------------------------------------------------------------
- Seq Scan on pg_temp.test_jsonb
- Output: jsonb_extract_path_int2(test_json, ARRAY['field6'::text, 'f1'::text])
- Filter: (test_jsonb.json_type = 'object'::text)
-(3 rows)
-
-SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
- int2
-------
- 7
-(1 row)
-
-- Section M2: execution through multi-subscript rewrite
SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int4;
int4
@@ -1631,6 +1409,606 @@ SELECT (('{"a":[[10,20],[30,40]]}'::jsonb)['a'][i][0])::int4 FROM generate_serie
Function Call: generate_series(0, 1)
(3 rows)
+-- Optimized typed extraction: jsonb_path_query_first family
+-- The planner rewrites (jsonb_path_query_first(j, path))::type into a direct
+-- typed extractor call for the same target types as the other families.
+-- Section Q1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_bool(test_json, '$."field7"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_numeric(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, true)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section Q2: execution through rewrite
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int4;
+ jsonb_path_query_first
+------------------------
+ 42
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":3.14}'::jsonb, '$.a'))::float8;
+ jsonb_path_query_first
+------------------------
+ 3.14
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":true}'::jsonb, '$.a'))::bool;
+ jsonb_path_query_first
+------------------------
+ t
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::numeric;
+ jsonb_path_query_first
+------------------------
+ 99
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::int8;
+ jsonb_path_query_first
+------------------------
+ 99
+(1 row)
+
+-- nested object path
+SELECT (jsonb_path_query_first('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+ jsonb_path_query_first
+------------------------
+ 42
+(1 row)
+
+-- array access in jsonpath
+SELECT (jsonb_path_query_first('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+ jsonb_path_query_first
+------------------------
+ 20
+(1 row)
+
+-- Section Q3: NULL semantics
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+ jsonb_path_query_first
+------------------------
+
+(1 row)
+
+SELECT (jsonb_path_query_first('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+ jsonb_path_query_first
+------------------------
+
+(1 row)
+
+-- Section Q4: silent semantics
+-- silent=true suppresses jsonpath execution errors, NOT cast errors
+SELECT (jsonb_path_query_first('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+ERROR: cannot cast jsonb string to type integer
+-- silent=false (default): jsonpath strict error
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict missing key error
+ERROR: JSON object does not contain key "b"
+-- silent=true: jsonpath strict error suppressed
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, returns NULL
+ jsonb_path_query_first
+------------------------
+
+(1 row)
+
+-- Section Q5: type-mismatch errors
+SELECT (jsonb_path_query_first('{"a":"hello"}'::jsonb, '$.a'))::int4; -- string to int4
+ERROR: cannot cast jsonb string to type integer
+SELECT (jsonb_path_query_first('{"a":[1,2]}'::jsonb, '$.a'))::int4; -- container to int4
+ERROR: cannot cast jsonb array to type integer
+-- Section Q6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int4
+-----------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int8
+-----------------------------
+ 99
+(1 row)
+
+SELECT jsonb_path_query_first_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_float8
+-------------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_numeric
+--------------------------------
+ 1.23
+(1 row)
+
+SELECT jsonb_path_query_first_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_bool
+-----------------------------
+ t
+(1 row)
+
+-- direct call: missing path
+SELECT jsonb_path_query_first_int4('{"a":1}'::jsonb, '$.b'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int4
+-----------------------------
+
+(1 row)
+
+-- direct call: with vars
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a ? (@ > $x)'::jsonpath, '{"x":10}'::jsonb, false);
+ jsonb_path_query_first_int4
+-----------------------------
+ 42
+(1 row)
+
+-- Optimized typed extraction: jsonb_path_query_first_tz family
+-- Same pattern as the non-tz family, but with timezone-aware evaluation.
+-- Section T1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_bool(test_json, '$."field7"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_numeric(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int4(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_float8(test_json, '$."field4"'::jsonpath, '{}'::jsonb, true)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section T2: execution through rewrite
+SELECT (jsonb_path_query_first_tz('{"a":42}'::jsonb, '$.a'))::int4;
+ jsonb_path_query_first_tz
+---------------------------
+ 42
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float8;
+ jsonb_path_query_first_tz
+---------------------------
+ 3.14
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":true}'::jsonb, '$.a'))::bool;
+ jsonb_path_query_first_tz
+---------------------------
+ t
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::numeric;
+ jsonb_path_query_first_tz
+---------------------------
+ 99
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::int8;
+ jsonb_path_query_first_tz
+---------------------------
+ 99
+(1 row)
+
+-- nested path
+SELECT (jsonb_path_query_first_tz('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+ jsonb_path_query_first_tz
+---------------------------
+ 42
+(1 row)
+
+-- array access
+SELECT (jsonb_path_query_first_tz('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+ jsonb_path_query_first_tz
+---------------------------
+ 20
+(1 row)
+
+-- Section T3: NULL semantics
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+ jsonb_path_query_first_tz
+---------------------------
+
+(1 row)
+
+SELECT (jsonb_path_query_first_tz('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+ jsonb_path_query_first_tz
+---------------------------
+
+(1 row)
+
+-- Section T4: silent semantics
+SELECT (jsonb_path_query_first_tz('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+ERROR: cannot cast jsonb string to type integer
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict error
+ERROR: JSON object does not contain key "b"
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, NULL
+ jsonb_path_query_first_tz
+---------------------------
+
+(1 row)
+
+-- Section T5: type-mismatch errors
+SELECT (jsonb_path_query_first_tz('{"a":"hello"}'::jsonb, '$.a'))::int4;
+ERROR: cannot cast jsonb string to type integer
+-- Section T6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_tz_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_int4
+--------------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_tz_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_int8
+--------------------------------
+ 99
+(1 row)
+
+SELECT jsonb_path_query_first_tz_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_float8
+----------------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_tz_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_numeric
+-----------------------------------
+ 1.23
+(1 row)
+
+SELECT jsonb_path_query_first_tz_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_bool
+--------------------------------
+ t
+(1 row)
+
+-- Optimized typed extraction: int2 / float4 matrix completion
+-- Extends the existing typed extractor families to cover int2 (smallint)
+-- and float4 (real) target types.
+-- Section I1: direct calls to int2 / float4 typed extractor builtins (object-field)
+SELECT jsonb_object_field_int2('{"a": 42}'::jsonb, 'a');
+ jsonb_object_field_int2
+-------------------------
+ 42
+(1 row)
+
+SELECT jsonb_object_field_int2('{"a": 3}'::jsonb, 'a');
+ jsonb_object_field_int2
+-------------------------
+ 3
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": 3.14}'::jsonb, 'a');
+ jsonb_object_field_float4
+---------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": 1.5}'::jsonb, 'a');
+ jsonb_object_field_float4
+---------------------------
+ 1.5
+(1 row)
+
+-- NULL semantics
+SELECT jsonb_object_field_int2('{"a": 1}'::jsonb, 'missing');
+ jsonb_object_field_int2
+-------------------------
+
+(1 row)
+
+SELECT jsonb_object_field_int2('{"a": null}'::jsonb, 'a');
+ jsonb_object_field_int2
+-------------------------
+
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": 1.0}'::jsonb, 'missing');
+ jsonb_object_field_float4
+---------------------------
+
+(1 row)
+
+SELECT jsonb_object_field_float4('{"a": null}'::jsonb, 'a');
+ jsonb_object_field_float4
+---------------------------
+
+(1 row)
+
+-- type-mismatch errors
+SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a');
+ERROR: cannot cast jsonb string to type smallint
+SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a');
+ERROR: cannot cast jsonb boolean to type real
+SELECT jsonb_object_field_int2('{"a": [1,2]}'::jsonb, 'a'); -- container to int2
+ERROR: cannot cast jsonb array or object to type smallint
+SELECT jsonb_object_field_float4('{"a": {"x":1}}'::jsonb, 'a'); -- container to float4
+ERROR: cannot cast jsonb array or object to type real
+-- int2 overflow
+SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
+ERROR: smallint out of range
+-- Section I2: planner rewrite verification — one per family (using int2)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_object_field_int2(test_json, 'field4'::text)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+ QUERY PLAN
+-------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb_arr
+ Output: jsonb_array_element_int2(test_arr, 0)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int2(test_json, '{field4}'::text[])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_int2(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first_tz_int2(test_json, '$."field4"'::jsonpath, '{}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- float4 rewrite (one representative)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_object_field_float4(test_json, 'field4'::text)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+-- Section I3: multi-subscript automatic coverage
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_extract_path_int2(test_json, ARRAY['field6'::text, 'f1'::text])
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
+ int2
+------
+ 7
+(1 row)
+
+-- Section I4: representative execution through rewrite (distributed across families)
+-- object-field: int2
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+ int2
+------
+ 4
+(1 row)
+
+-- object-field: float4
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4
+--------
+ 4
+(1 row)
+
+-- array-element: int2
+SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+ int2
+------
+ 10
+(1 row)
+
+-- array-element: float4
+SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+ float4
+--------
+ 2.5
+(1 row)
+
+-- extract-path: float4
+SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+ float4
+--------
+ 4
+(1 row)
+
+-- jsonb_path_query_first: int2
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int2;
+ jsonb_path_query_first
+------------------------
+ 42
+(1 row)
+
+-- jsonb_path_query_first_tz: float4
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float4;
+ jsonb_path_query_first_tz
+---------------------------
+ 3.14
+(1 row)
+
+-- Section I5: NULL through rewrite
+SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object';
+ int2
+------
+
+(1 row)
+
+SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr; -- JSON null element
+ float4
+--------
+
+(1 row)
+
+SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr; -- out of range
+ int2
+------
+
+(1 row)
+
+-- Section I6: type-mismatch through rewrite
+SELECT (test_json -> 'field1')::int2 FROM test_jsonb WHERE json_type = 'object'; -- string to int2
+ERROR: cannot cast jsonb string to type smallint
+SELECT (test_json -> 'field1')::float4 FROM test_jsonb WHERE json_type = 'object'; -- string to float4
+ERROR: cannot cast jsonb string to type real
+-- Section I7: direct calls to other family builtins (representative)
+SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
+ jsonb_array_element_int2
+--------------------------
+ 10
+(1 row)
+
+SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
+ jsonb_array_element_float4
+----------------------------
+ 2.5
+(1 row)
+
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+ jsonb_extract_path_int2
+-------------------------
+ 7
+(1 row)
+
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+ jsonb_extract_path_float4
+---------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_int2
+-----------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_float4
+-------------------------------
+ 3.14
+(1 row)
+
+SELECT jsonb_path_query_first_tz_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_int2
+--------------------------------
+ 42
+(1 row)
+
+SELECT jsonb_path_query_first_tz_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+ jsonb_path_query_first_tz_float4
+----------------------------------
+ 3.14
+(1 row)
+
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
?column?
----------
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index ed70c4d29ee..c3e1c7ae735 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -167,8 +167,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field7')::bool FROM test_json
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int4 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int8 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
-- Section 1b: planner rewrite verification for subscripting syntax
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::numeric FROM test_jsonb WHERE json_type = 'object';
@@ -176,8 +174,10 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field7'])::bool FROM test_jsonb
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int8 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::float8 FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::float4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Verify that int2 and float4 are also rewritten through the object-field family.
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
-- Section 2: correct execution through the rewritten path
SELECT (test_json -> 'field4')::numeric FROM test_jsonb WHERE json_type = 'object';
@@ -185,8 +185,6 @@ SELECT (test_json -> 'field7')::bool FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json -> 'field4')::int4 FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json -> 'field4')::int8 FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json -> 'field4')::float8 FROM test_jsonb WHERE json_type = 'object';
-SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
-SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
-- Section 2b: correct execution through subscripting syntax
SELECT (test_json['field4'])::numeric FROM test_jsonb WHERE json_type = 'object';
@@ -194,8 +192,10 @@ SELECT (test_json['field7'])::bool FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json['field4'])::int8 FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json['field4'])::float8 FROM test_jsonb WHERE json_type = 'object';
-SELECT (test_json['field4'])::int2 FROM test_jsonb WHERE json_type = 'object';
-SELECT (test_json['field4'])::float4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Unsupported targets still work correctly through the original path
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
-- Section 3: NULL semantics (missing key, JSON null, non-object input)
SELECT (test_json -> 'field3')::numeric FROM test_jsonb WHERE json_type = 'object'; -- JSON null
@@ -206,10 +206,6 @@ SELECT (test_json -> 'field3')::int4 FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json -> 'nonexistent')::int4 FROM test_jsonb WHERE json_type = 'object'; -- missing key, int4
SELECT (test_json -> 'field3')::float8 FROM test_jsonb WHERE json_type = 'object'; -- JSON null, float8 path
SELECT (test_json -> 'nonexistent')::float8 FROM test_jsonb WHERE json_type = 'object'; -- missing key, float8
-SELECT (test_json -> 'field3')::int2 FROM test_jsonb WHERE json_type = 'object'; -- JSON null, int2 path
-SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object'; -- missing key, int2
-SELECT (test_json -> 'field3')::float4 FROM test_jsonb WHERE json_type = 'object'; -- JSON null, float4 path
-SELECT (test_json -> 'nonexistent')::float4 FROM test_jsonb WHERE json_type = 'object'; -- missing key, float4
-- Section 3b: NULL semantics through subscripting syntax
SELECT (test_json['field3'])::numeric FROM test_jsonb WHERE json_type = 'object'; -- JSON null
@@ -225,10 +221,6 @@ SELECT (test_json -> 'field1')::int8 FROM test_jsonb WHERE json_type = 'object';
SELECT (test_json -> 'field1')::float8 FROM test_jsonb WHERE json_type = 'object'; -- string to float8
SELECT (test_json -> 'field5')::int4 FROM test_jsonb WHERE json_type = 'object'; -- array to int4
SELECT (test_json -> 'field5')::float8 FROM test_jsonb WHERE json_type = 'object'; -- array to float8
-SELECT (test_json -> 'field1')::int2 FROM test_jsonb WHERE json_type = 'object'; -- string to int2
-SELECT (test_json -> 'field1')::float4 FROM test_jsonb WHERE json_type = 'object'; -- string to float4
-SELECT (test_json -> 'field5')::int2 FROM test_jsonb WHERE json_type = 'object'; -- array to int2
-SELECT (test_json -> 'field5')::float4 FROM test_jsonb WHERE json_type = 'object'; -- array to float4
-- Section 4b: type-mismatch error through subscripting syntax
SELECT (test_json['field1'])::numeric FROM test_jsonb WHERE json_type = 'object'; -- string to numeric
@@ -242,10 +234,6 @@ SELECT jsonb_object_field_bool('{"a": false}'::jsonb, 'a');
SELECT jsonb_object_field_int4('{"a": 42}'::jsonb, 'a');
SELECT jsonb_object_field_int8('{"a": 9876543210}'::jsonb, 'a');
SELECT jsonb_object_field_float8('{"a": 3.14}'::jsonb, 'a');
-SELECT jsonb_object_field_int2('{"a": 42}'::jsonb, 'a');
-SELECT jsonb_object_field_int2('{"a": 3}'::jsonb, 'a');
-SELECT jsonb_object_field_float4('{"a": 3.14}'::jsonb, 'a');
-SELECT jsonb_object_field_float4('{"a": 1.5}'::jsonb, 'a');
-- direct calls: NULL semantics
SELECT jsonb_object_field_numeric('{"a": 1}'::jsonb, 'missing');
SELECT jsonb_object_field_numeric('{"a": null}'::jsonb, 'a');
@@ -253,10 +241,6 @@ SELECT jsonb_object_field_bool('{"a": true}'::jsonb, 'missing');
SELECT jsonb_object_field_int4('{"a": 1}'::jsonb, 'missing');
SELECT jsonb_object_field_int4('{"a": null}'::jsonb, 'a');
SELECT jsonb_object_field_float8('{"a": 1.0}'::jsonb, 'missing');
-SELECT jsonb_object_field_int2('{"a": 1}'::jsonb, 'missing');
-SELECT jsonb_object_field_int2('{"a": null}'::jsonb, 'a');
-SELECT jsonb_object_field_float4('{"a": 1.0}'::jsonb, 'missing');
-SELECT jsonb_object_field_float4('{"a": null}'::jsonb, 'a');
-- direct calls: type-mismatch errors
SELECT jsonb_object_field_numeric('{"a": "text"}'::jsonb, 'a');
SELECT jsonb_object_field_bool('{"a": 1}'::jsonb, 'a');
@@ -264,14 +248,8 @@ SELECT jsonb_object_field_numeric('{"a": {"x":1}}'::jsonb, 'a'); -- container t
SELECT jsonb_object_field_int4('{"a": "text"}'::jsonb, 'a');
SELECT jsonb_object_field_int8('{"a": true}'::jsonb, 'a');
SELECT jsonb_object_field_float8('{"a": [1,2]}'::jsonb, 'a'); -- container to float8
-SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a'); -- string to int2
-SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a'); -- bool to float4
-SELECT jsonb_object_field_int2('{"a": [1,2]}'::jsonb, 'a'); -- container to int2
-SELECT jsonb_object_field_float4('{"a": {"x":1}}'::jsonb, 'a'); -- container to float4
-- direct calls: integer overflow
SELECT jsonb_object_field_int4('{"a": 9999999999}'::jsonb, 'a');
--- direct calls: int2 overflow
-SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
-- Optimized typed extraction: array-element family
-- The planner rewrites (j->idx)::type and (j[idx])::type into direct
@@ -287,8 +265,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int4 FROM test_jsonb_arr;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float8 FROM test_jsonb_arr;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
-- Section A1b: planner rewrite verification (array subscripting form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::numeric FROM test_jsonb_arr;
@@ -296,7 +272,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[1])::float8 FROM test_jsonb_arr;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[2])::bool FROM test_jsonb_arr;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int8 FROM test_jsonb_arr;
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr[0])::int2 FROM test_jsonb_arr;
-- Section A1c: planner rewrite verification (direct function call form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_array_element(test_arr, 0))::int4 FROM test_jsonb_arr;
@@ -307,8 +282,6 @@ SELECT (test_arr -> 0)::int8 FROM test_jsonb_arr;
SELECT (test_arr -> 0)::numeric FROM test_jsonb_arr;
SELECT (test_arr -> 1)::float8 FROM test_jsonb_arr;
SELECT (test_arr -> 2)::bool FROM test_jsonb_arr;
-SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
-SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
-- Section A2b: correct execution through subscripting
SELECT (test_arr[0])::int4 FROM test_jsonb_arr;
@@ -321,8 +294,6 @@ SELECT (test_arr -> 3)::numeric FROM test_jsonb_arr; -- JSON null element
SELECT (test_arr -> -1)::int4 FROM test_jsonb_arr; -- negative: last element is object, wrong type would error; use -4 for null
SELECT (test_arr -> -4)::numeric FROM test_jsonb_arr; -- negative index pointing to null element
SELECT ('{"k":1}'::jsonb -> 0)::int4; -- non-array input
-SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr; -- out of range, int2
-SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr; -- JSON null element, float4
-- Section A3b: NULL through subscripting
SELECT (test_arr[99])::float8 FROM test_jsonb_arr; -- out of range
@@ -334,8 +305,6 @@ SELECT (test_arr -> 4)::float8 FROM test_jsonb_arr; -- string to float8
SELECT (test_arr -> 5)::numeric FROM test_jsonb_arr; -- array container to numeric
SELECT (test_arr -> 6)::int8 FROM test_jsonb_arr; -- object container to int8
SELECT (test_arr -> 2)::int4 FROM test_jsonb_arr; -- bool to int4
-SELECT (test_arr -> 4)::int2 FROM test_jsonb_arr; -- string to int2
-SELECT (test_arr -> 4)::float4 FROM test_jsonb_arr; -- string to float4
-- Section A4b: error through subscripting
SELECT (test_arr[4])::int8 FROM test_jsonb_arr; -- string to int8
@@ -354,9 +323,6 @@ SELECT jsonb_array_element_float8('{"a":1}'::jsonb, 0); -- non-array
SELECT jsonb_array_element_int4('["text"]'::jsonb, 0);
SELECT jsonb_array_element_int8('[true]'::jsonb, 0);
SELECT jsonb_array_element_float8('[[1,2]]'::jsonb, 0); -- container to float8
-SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
-SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
-
-- Optimized typed extraction: extract-path family
-- The planner rewrites (j #> path)::type and (jsonb_extract_path(j, ...))::type
@@ -369,8 +335,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float8 FROM test_
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field6,f1}')::int8 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field7}')::bool FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
-- Section P1b: planner rewrite verification (direct function-call form)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_extract_path(test_json, 'field6', 'f1'))::int4 FROM test_jsonb WHERE json_type = 'object';
@@ -414,10 +378,6 @@ SELECT jsonb_extract_path_int4('{"a":1}'::jsonb, ARRAY['b']);
SELECT jsonb_extract_path_int4('{"a":"text"}'::jsonb, ARRAY['a']);
-- direct calls: array index in path
SELECT jsonb_extract_path_int4('{"a":[10,20]}'::jsonb, ARRAY['a','1']);
--- direct calls: int2 and float4
-SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
-SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
-
-- Optimized typed extraction: multi-subscript chains
-- The planner lowers j['a']['b'], j['a'][0], etc. to the extract-path typed
@@ -433,10 +393,6 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::numeric FROM te
-- Verify single subscript still uses the existing object-field family, not extract-path
EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field4'])::int4 FROM test_jsonb WHERE json_type = 'object';
-
--- Section M1b: multi-subscript int2/float4
-EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
-SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
-- Section M2: execution through multi-subscript rewrite
SELECT (('{"a":{"b":42}}'::jsonb)['a']['b'])::int4;
SELECT (('{"a":[10,20,30]}'::jsonb)['a'][1])::int4;
@@ -462,6 +418,173 @@ SELECT (('{"a":{"b":[1,2]}}'::jsonb)['a']['b'])::int4; -- container to int4
EXPLAIN (VERBOSE, COSTS OFF)
SELECT (('{"a":[[10,20],[30,40]]}'::jsonb)['a'][i][0])::int4 FROM generate_series(0,1) AS i;
+-- Optimized typed extraction: jsonb_path_query_first family
+-- The planner rewrites (jsonb_path_query_first(j, path))::type into a direct
+-- typed extractor call for the same target types as the other families.
+
+-- Section Q1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section Q2: execution through rewrite
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int4;
+SELECT (jsonb_path_query_first('{"a":3.14}'::jsonb, '$.a'))::float8;
+SELECT (jsonb_path_query_first('{"a":true}'::jsonb, '$.a'))::bool;
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::numeric;
+SELECT (jsonb_path_query_first('{"a":99}'::jsonb, '$.a'))::int8;
+-- nested object path
+SELECT (jsonb_path_query_first('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+-- array access in jsonpath
+SELECT (jsonb_path_query_first('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+
+-- Section Q3: NULL semantics
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+SELECT (jsonb_path_query_first('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+
+-- Section Q4: silent semantics
+-- silent=true suppresses jsonpath execution errors, NOT cast errors
+SELECT (jsonb_path_query_first('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+-- silent=false (default): jsonpath strict error
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict missing key error
+-- silent=true: jsonpath strict error suppressed
+SELECT (jsonb_path_query_first('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, returns NULL
+
+-- Section Q5: type-mismatch errors
+SELECT (jsonb_path_query_first('{"a":"hello"}'::jsonb, '$.a'))::int4; -- string to int4
+SELECT (jsonb_path_query_first('{"a":[1,2]}'::jsonb, '$.a'))::int4; -- container to int4
+
+-- Section Q6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+-- direct call: missing path
+SELECT jsonb_path_query_first_int4('{"a":1}'::jsonb, '$.b'::jsonpath, '{}'::jsonb, false);
+-- direct call: with vars
+SELECT jsonb_path_query_first_int4('{"a":42}'::jsonb, '$.a ? (@ > $x)'::jsonpath, '{"x":10}'::jsonb, false);
+
+-- Optimized typed extraction: jsonb_path_query_first_tz family
+-- Same pattern as the non-tz family, but with timezone-aware evaluation.
+
+-- Section T1: planner rewrite verification
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int4 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field7'))::bool FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::numeric FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int8 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::float8 FROM test_jsonb WHERE json_type = 'object';
+-- 3-arg form (vars explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}'))::int4 FROM test_jsonb WHERE json_type = 'object';
+-- 4-arg form (vars + silent explicit)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4', '{}', true))::float8 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section T2: execution through rewrite
+SELECT (jsonb_path_query_first_tz('{"a":42}'::jsonb, '$.a'))::int4;
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float8;
+SELECT (jsonb_path_query_first_tz('{"a":true}'::jsonb, '$.a'))::bool;
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::numeric;
+SELECT (jsonb_path_query_first_tz('{"a":99}'::jsonb, '$.a'))::int8;
+-- nested path
+SELECT (jsonb_path_query_first_tz('{"a":{"b":42}}'::jsonb, '$.a.b'))::int4;
+-- array access
+SELECT (jsonb_path_query_first_tz('{"a":[10,20]}'::jsonb, '$.a[1]'))::int4;
+
+-- Section T3: NULL semantics
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, '$.b'))::int4; -- missing path
+SELECT (jsonb_path_query_first_tz('{"a":null}'::jsonb, '$.a'))::int4; -- JSON null
+
+-- Section T4: silent semantics
+SELECT (jsonb_path_query_first_tz('{"a":"text"}'::jsonb, '$.a', '{}', true))::int4; -- cast error not suppressed
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', false))::int4; -- strict error
+SELECT (jsonb_path_query_first_tz('{"a":1}'::jsonb, 'strict $.b', '{}', true))::int4; -- suppressed, NULL
+
+-- Section T5: type-mismatch errors
+SELECT (jsonb_path_query_first_tz('{"a":"hello"}'::jsonb, '$.a'))::int4;
+
+-- Section T6: direct calls to typed extractor builtins
+SELECT jsonb_path_query_first_tz_int4('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_int8('{"a":99}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_float8('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_numeric('{"a":1.23}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_bool('{"a":true}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+
+-- Optimized typed extraction: int2 / float4 matrix completion
+-- Extends the existing typed extractor families to cover int2 (smallint)
+-- and float4 (real) target types.
+
+-- Section I1: direct calls to int2 / float4 typed extractor builtins (object-field)
+SELECT jsonb_object_field_int2('{"a": 42}'::jsonb, 'a');
+SELECT jsonb_object_field_int2('{"a": 3}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": 3.14}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": 1.5}'::jsonb, 'a');
+-- NULL semantics
+SELECT jsonb_object_field_int2('{"a": 1}'::jsonb, 'missing');
+SELECT jsonb_object_field_int2('{"a": null}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": 1.0}'::jsonb, 'missing');
+SELECT jsonb_object_field_float4('{"a": null}'::jsonb, 'a');
+-- type-mismatch errors
+SELECT jsonb_object_field_int2('{"a": "text"}'::jsonb, 'a');
+SELECT jsonb_object_field_float4('{"a": true}'::jsonb, 'a');
+SELECT jsonb_object_field_int2('{"a": [1,2]}'::jsonb, 'a'); -- container to int2
+SELECT jsonb_object_field_float4('{"a": {"x":1}}'::jsonb, 'a'); -- container to float4
+-- int2 overflow
+SELECT jsonb_object_field_int2('{"a": 99999}'::jsonb, 'a');
+
+-- Section I2: planner rewrite verification — one per family (using int2)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json #> '{field4}')::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jsonb_path_query_first_tz(test_json, '$.field4'))::int2 FROM test_jsonb WHERE json_type = 'object';
+-- float4 rewrite (one representative)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+
+-- Section I3: multi-subscript automatic coverage
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (test_json['field6']['f1'])::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (('{"a":{"b":7}}'::jsonb)['a']['b'])::int2;
+
+-- Section I4: representative execution through rewrite (distributed across families)
+-- object-field: int2
+SELECT (test_json -> 'field4')::int2 FROM test_jsonb WHERE json_type = 'object';
+-- object-field: float4
+SELECT (test_json -> 'field4')::float4 FROM test_jsonb WHERE json_type = 'object';
+-- array-element: int2
+SELECT (test_arr -> 0)::int2 FROM test_jsonb_arr;
+-- array-element: float4
+SELECT (test_arr -> 1)::float4 FROM test_jsonb_arr;
+-- extract-path: float4
+SELECT (test_json #> '{field4}')::float4 FROM test_jsonb WHERE json_type = 'object';
+-- jsonb_path_query_first: int2
+SELECT (jsonb_path_query_first('{"a":42}'::jsonb, '$.a'))::int2;
+-- jsonb_path_query_first_tz: float4
+SELECT (jsonb_path_query_first_tz('{"a":3.14}'::jsonb, '$.a'))::float4;
+
+-- Section I5: NULL through rewrite
+SELECT (test_json -> 'nonexistent')::int2 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_arr -> 3)::float4 FROM test_jsonb_arr; -- JSON null element
+SELECT (test_arr -> 99)::int2 FROM test_jsonb_arr; -- out of range
+
+-- Section I6: type-mismatch through rewrite
+SELECT (test_json -> 'field1')::int2 FROM test_jsonb WHERE json_type = 'object'; -- string to int2
+SELECT (test_json -> 'field1')::float4 FROM test_jsonb WHERE json_type = 'object'; -- string to float4
+
+-- Section I7: direct calls to other family builtins (representative)
+SELECT jsonb_array_element_int2('[10, 20]'::jsonb, 0);
+SELECT jsonb_array_element_float4('[3.14, 2.5]'::jsonb, 1);
+SELECT jsonb_extract_path_int2('{"a":{"b":7}}'::jsonb, ARRAY['a','b']);
+SELECT jsonb_extract_path_float4('{"a":3.14}'::jsonb, ARRAY['a']);
+SELECT jsonb_path_query_first_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_int2('{"a":42}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+SELECT jsonb_path_query_first_tz_float4('{"a":3.14}'::jsonb, '$.a'::jsonpath, '{}'::jsonb, false);
+
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object';
@@ -1919,4 +2042,4 @@ select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
select ('true'::jsonb)::bool;
select ('true'::jsonb).bool;
select ('{"text": "hello"}'::jsonb)::text;
-select ('{"text": "hello"}'::jsonb).text;
\ No newline at end of file
+select ('{"text": "hello"}'::jsonb).text;
--
2.52.0