public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandra Wang <[email protected]>
To: jian he <[email protected]>
Cc: Nikita Malakhov <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Mark Dilger <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David E. Wheeler <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Wed, 9 Jul 2025 01:01:45 -0700
Message-ID: <CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEt5bL-xYojenA7x1Fq=-DfhW3KGH=3Zz9TjJ1k95=uuQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
<CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
<CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
<CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
<CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
<CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
<CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>
<CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com>
<CAK98qZ1nz6ZZhQqTOCNwRguZE5GsBLW5BQT_k=s7AA6gc2CN_g@mail.gmail.com>
<[email protected]>
<CAN-LCVM6A9z6AzxsEX-vx9=3XgEUU6+zjUqejQw8LfjaRY5P1A@mail.gmail.com>
<CAK98qZ2Pmf6ZSChLq+CEPKJ_8jSa0gFTNJWJTcWbCziDpqa=CA@mail.gmail.com>
<CACJufxEt5bL-xYojenA7x1Fq=-DfhW3KGH=3Zz9TjJ1k95=uuQ@mail.gmail.com>
Hi Jian,
On Tue, Jun 24, 2025 at 3:30 PM jian he <[email protected]> wrote:
> hi.
>
> I have applied for 0001 to 0006.
>
Thank you so much for the very detailed and thorough review, and for
the patches!
I've attached v12 that addresses your feedback:
v12-0001 to v12-0004 are refactoring patches that prepare for the
implementation.
v12-0005 - implements jsonb dot-notation (e.g., (jb).a)
Changes from v11-0006 include:
a. subscripting with slicing and wildcard-related code has been
removed and will be added in the following commit
b. introduced a dedicated FieldAccessorExpr node to transform Strings
in dot-notation.
c. mixed syntax (such as (jb).a['b'].c is now allowed, with warnings.
d. miscellaneous bug fixes and code cleanup based on Jian’s feedback.
v12-0006 - implements jsonb subscripting with slicing.
This was split out from v11-0006 into a separate commit.
v12-0007 - implements wildcard member accessor.
I added the Star node that Jelte suggested for transforming the
wildcard member accessor.
Please find more detailed replies below:
On Tue, Jun 24, 2025 at 3:30 PM jian he <[email protected]> wrote:
> static void
> jsonb_subscript_transform(SubscriptingRef *sbsref,
> List **indirection,
> ParseState *pstate,
> bool isSlice,
> bool isAssignment)
> {
> List *upperIndexpr = NIL;
> ListCell *idx;
> sbsref->refrestype = JSONBOID;
> sbsref->reftypmod = -1;
> if (jsonb_check_jsonpath_needed(*indirection))
> {
> sbsref->refjsonbpath =
> jsonb_subscript_make_jsonpath(pstate, indirection,
> &sbsref->refupperindexpr,
> &sbsref->reflowerindexpr);
> return;
> }
> foreach(idx, *indirection)
> {
> Node *i = lfirst(idx);
> A_Indices *ai;
> Node *subExpr;
> Assert(IsA(i, A_Indices));
> ai = castNode(A_Indices, i);
> if (isSlice)
> {
> Node *expr = ai->uidx ? ai->uidx : ai->lidx;
> ereport(ERROR,
> (errcode(ERRCODE_DATATYPE_MISMATCH),
> errmsg("jsonb subscript does not support slices"),
> parser_errposition(pstate, exprLocation(expr))));
> }
>
> I am confused by the above error handling:
> errmsg("jsonb subscript does not support slices").
> we can do
> select (jsonb '[1,2,3]')[0:1];
> or
> SELECT (jb).a[2:3].b FROM test_jsonb_dot_notation;
>
> this is by definition, "slices"?
> Anyway, I doubt this error handling will ever be reachable.
> jsonb_check_jsonpath_needed checks whether the indirection contains
> is_slice,
> but jsonb_subscript_transform already takes isSlice as an argument.
> Maybe we can refactor it somehow.
>
Good catch! I believe this error handling was originally copied
because the existing JSONB subscripting in Postgres doesn’t support
slices.
In v12, I’ve separated the slicing implementation into its own commit,
distinct from the one that implements dot-notation. Hope that helps!
On Tue, Jun 24, 2025 at 3:30 PM jian he <[email protected]> wrote:
> T_String is a primitive node type with no subnodes.
> typedef struct String
> {
> pg_node_attr(special_read_write)
> NodeTag type;
> char *sval;
> } String;
> then in src/backend/nodes/nodeFuncs.c:
> if (expr && !IsA(expr, String) && WALK(expr))
> return true;
> we can change it to
> if (WALK(expr))
> return true;
> but in function expression_tree_walker_impl
> we have to change it as
>
> case T_MergeSupportFunc:
> case T_String:
> /* primitive node types with no expression subnodes */
> break;
>
You’re right, the changes to the walker-related code weren’t very
clean. In v12, I introduced a new node, FieldAccessorExpr, to serve as
the expression node for dot-notation. This helped minimize changes to
the walker code and, in my opinion, results in a cleaner design than
using a more general Const(text) node.
On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
wrote:
> in src/backend/catalog/sql_features.txt
> should we mark any T860, T861, T862, T863, T864
> items as YES?
>
I’ve updated T860 and T861. I’m not entirely sure about the rest so
left them unchanged.
On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
wrote:
> typedef struct SubscriptingRef
> {
> /* expressions that evaluate to upper container indexes */
> List *refupperindexpr;
> }
> SubscriptingRef.refupperindexpr meaning changed,
> So the above comments also need to be changed?
>
Thanks. Done.
On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
wrote:
> v11-0004-Extract-coerce_jsonpath_subscript.patch
> + /*
> + * We known from can_coerce_type that coercion will succeed, so
> + * coerce_type could be used. Note the implicit coercion context, which is
> + * required to handle subscripts of different types, similar to overloaded
> + * functions.
> + */
> + subExpr = coerce_type(pstate,
> + subExpr, subExprType,
> + targetType, -1,
> + COERCION_IMPLICIT,
> + COERCE_IMPLICIT_CAST,
> + -1);
> + if (subExpr == NULL)
> + ereport(ERROR,
> + (errcode(ERRCODE_DATATYPE_MISMATCH),
> + errmsg("jsonb subscript must have text type"),
>
> the targetType can be "integer", then the error message
> errmsg("jsonb subscript must have text type") would be wrong?
> Also this error handling is not necessary.
> since we can_coerce_type already tell us that coercion will succeed.
> Also, do we really put v11-0004 as a separate patch?
>
Thanks for pointing this out! I’ve removed the unnecessary error
handling as you suggested. I kept v12-0004 as a separate patch to make
the review easier, but I’ll leave it up to the committer to decide
whether to squash it with the other commits.
On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
wrote:
> in gram.y we have:
> if (!IsA($5, A_Const) ||
> castNode(A_Const, $5)->val.node.type != T_String)
> ereport(ERROR,
> errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("only string constants are
> supported in JSON_TABLE path specification"),
> so simply, in make_jsonpath_item_expr we can
>
> expr = transformExpr(pstate, expr, pstate->p_expr_kind);
> if (!IsA(expr, Const) || ((Const *) expr)->consttype != INT4OID)
> ereport(ERROR,
> errcode(ERRCODE_DATATYPE_MISMATCH),
> errmsg("only integer constants are supported in jsonb
> simplified accessor subscripting"),
> parser_errposition(pstate, exprLocation(expr)));
>
> because I think the current error message "got type: unknown" is not good.
> select ('123'::jsonb).a['1'];
> ERROR: jsonb simplified accessor supports subscripting in type: INT4,
> got type: unknown
> then we don't need two "ereport(ERROR" within make_jsonpath_item_expr
> we can also Assert (cnst->constisnull) is false.
> see gram.y:16976
>
Thanks for pointing this out! In v12, I added support for mixed
subscripting syntax, and updated the ERROR (or WARNING) messages as
follows:
postgres=# SELECT (jb)['a'].b FROM test_jsonb_dot_notation; -- returns an
array due to lax mode
b
------------
["c", "d"]
(1 row)
postgres=# SELECT (jb).a['b'] FROM test_jsonb_dot_notation; -- returns NULL
due to strict mode with warnings
WARNING: 01000: mixed usage of jsonb simplified accessor syntax and jsonb
subscripting.
LINE 1: SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
^
HINT: use dot-notation for member access, or use non-null integer
constants subscripting for array access.
LOCATION: jsonb_subscript_make_jsonpath, jsonbsubs.c:366
a
---
(1 row)
postgres=# select ('{"a": 1}'::jsonb)['a':'b']; -- fails
ERROR: 42804: only non-null integer constants are supported for jsonb
simplified accessor subscripting
LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
^
HINT: use int data type for subscripting with slicing.
LOCATION: make_jsonpath_item_expr, jsonbsubs.c:218
So some of the ERRORs now disappear or downgrade to WARNINGs.
On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
wrote:
> I saw you introduce the word "AST", for example
> "Converts jsonpath AST into jsonpath value in binary."
> I am not sure that is fine.
>
Fixed.
On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
wrote:
> in jsonb_subscript_make_jsonpath we have:
> + foreach(lc, *indirection)
> + {
> +
> + if (IsA(accessor, String))
> + ....
> + else if (IsA(accessor, A_Star))
> + ....
> + else if (IsA(accessor, A_Indices))
> + ....
> + else
> + break;
>
> Is the last else branch unreliable? since indirection only support for
> String, A_Star, A_Indices, we already have Assert in
> jsonb_check_jsonpath_needed
> to ensure that.
> + *indirection = list_delete_first_n(*indirection, pathlen);
> also this is not necessary,
> because pathlen will be the same length as list *indirection in current
> design.
>
I kept this logic in v12, because in order to support the mixed usage
of dot-notation and existing jsonb subscripting (e.g., (jb).a['b'].c),
we need to switch between making jsonpath and transforming the upper
indexes for evaluation. So now, *indirection =
list_delete_first_n(*indirection, pathlen); is necessary, and pathlen
can differ.
The else break; can be removed, but I choose to keep it for now and
added comments to clarify the behavior.
On Wed, Jun 25, 2025 at 12:41 AM jian he <[email protected]>
wrote:
> On Wed, Jun 25, 2025 at 1:56 PM jian he <[email protected]>
> wrote:
> >
> > hi.
>
> CREATE TABLE test_jsonb_dot_notation AS
> SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y":
> "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z":
> "ZZZ"}}]}'::jsonb jb;
> CREATE VIEW v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
> CREATE VIEW v2 AS SELECT (jb).a[3:].x.y[:-1] FROM test_jsonb_dot_notation;
> CREATE VIEW v3 AS SELECT (jb).a[:3].x.y[-1:] FROM test_jsonb_dot_notation;
>
> \sv v2
> \sv v3
> will trigger segment fault.
>
Great catch, thanks! Fixed and added your tests.
On Wed, Jun 25, 2025 at 12:41 AM jian he <[email protected]>
wrote:
> also do we need ban subscript slicing, when upper bound is less than
> lower bound,
> for example:
> SELECT (jb).a[3:].x.y[0:'-1'::integer] AS y FROM test_jsonb_dot_notation;
>
I don't think we need to ban this case, since the SQL standard doesn't
ban it, and the (empty) result seems reasonable.
On Thu, Jun 26, 2025 at 7:10 PM jian he <[email protected]> wrote:
> in gram.y we have:
> indirection_el:
> '.' attr_name
> {
> $$ = (Node *) makeString($2);
> }
>
> we can be sure that dot notation, following dot is a plain string.
> then in jsonb_subscript_transform, we can transform the String Node to
> a TEXTOID Const.
> with that, most of the
> v11-0005-Enable-String-node-as-field-accessors-in-generic.patch
> would be unnecessary.
> Also in v11-0006-Implement-read-only-dot-notation-for-jsonb.patch
> all these with pattern
> ``if (IsA(expr, String)``
> can be removed.
>
Thanks for giving so much thought into this, I really appreciate it!
As I mentioned earlier, instead of using the Const(text) node, I added
a dedicated FieldAccessorExpr node for dot-notation. I think this
addresses the same concern.
On Thu, Jun 26, 2025 at 7:10 PM jian he <[email protected]> wrote:
> in transformContainerSubscripts we have:
> sbsref = makeNode(SubscriptingRef);
> sbsref->refcontainertype = containerType;
> sbsref->refelemtype = elementType;
> sbsref->reftypmod = containerTypMod;
> sbsref->refexpr = (Expr *) containerBase;
> sbsref->refassgnexpr = NULL; /* caller will fill if it's an
> assignment */
> sbsroutines->transform(sbsref, indirection, pstate,
> isSlice, isAssignment);
>
> then jsonb_subscript_transform we have
> sbsref->refjsonbpath =
> jsonb_subscript_make_jsonpath(pstate, indirection,
> &sbsref->refupperindexpr,
> &sbsref->reflowerindexpr);
> of course sbsref->refupperindexpr, sbsref->reflowerindexpr is NIL
> since we first called jsonb_subscript_make_jsonpath.
>
> so we can simplify the function signature as
> static void jsonb_subscript_make_jsonpath(pstate, indirection, sbsref)
>
> Within jsonb_subscript_make_jsonpath we are going to populate
> refupperindexpr, reflowerindexpr, refjsonbpath.
>
You are right. I applied your suggestion.
On Thu, Jun 26, 2025 at 7:10 PM jian he <[email protected]> wrote:
> The attached patch addresses both of these issues, along with additional
> related
> refactoring. It consolidates all the changes I think are appropriate,
> based on
> patches v1-0001 to v1-0006. This will include patches previously I sent in
> the earlier thread.
>
Thanks again for the patch! It was really helpful! I didn't directly
apply it as I made a few different choices, but I think I have
addressed all the points you covered in it.
Let me know your thoughts!
Best,
Alex
Attachments:
[application/octet-stream] v12-0006-Implement-Jsonb-subscripting-with-slicing.patch (17.4K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/3-v12-0006-Implement-Jsonb-subscripting-with-slicing.patch)
download | inline diff:
From e84373f02b26351d1df8b66d272e50d1d8d6aefa Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 6/7] Implement Jsonb subscripting with slicing
Previously, slicing was not supported for jsonb subscripting. This commit
implements subscripting with slicing as part of the JSON simplified accessor
syntax specified in SQL:2023.
Co-authored-by: Nikita Glukhov <[email protected]>
Co-authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Mark Dilger <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Vik Fearing <[email protected]>
Reviewed-by: Nikita Malakhov <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Tested-by: Mark Dilger <[email protected]>
Tested-by: Jian He <[email protected]>
---
src/backend/utils/adt/jsonbsubs.c | 33 +++-
.../ecpg/test/expected/sql-sqljson.c | 16 +-
.../ecpg/test/expected/sql-sqljson.stderr | 26 ++--
.../ecpg/test/expected/sql-sqljson.stdout | 3 +
src/interfaces/ecpg/test/sql/sqljson.pgc | 7 +-
src/test/regress/expected/jsonb.out | 145 ++++++++++++++++--
src/test/regress/sql/jsonb.sql | 53 ++++++-
7 files changed, 243 insertions(+), 40 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 82cf60df822..c35d20933ea 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -192,9 +192,10 @@ make_jsonpath_item_int(int32 val, List **exprs)
* - pstate: parse state context
* - expr: input expression node
* - exprs: list of expression nodes (updated in place)
+ * - no_error: returns NULL when the data type doesn't match. Otherwise, emits an ERROR.
*/
static JsonPathParseItem *
-make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs, bool no_error)
{
Const *cnst;
@@ -207,7 +208,14 @@ make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
return make_jsonpath_item_int(DatumGetInt32(cnst->constvalue), exprs);
}
- return NULL;
+ if (no_error)
+ return NULL;
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("only non-null integer constants are supported for jsonb simplified accessor subscripting"),
+ errhint("use int data type for subscripting with slicing."),
+ parser_errposition(pstate, exprLocation(expr)));
}
/*
@@ -279,19 +287,28 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, Subscripti
if (ai->is_slice)
{
- Node *expr = ai->uidx ? ai->uidx : ai->lidx;
+ while (list_length(sbsref->reflowerindexpr) < list_length(sbsref->refupperindexpr))
+ sbsref->reflowerindexpr = lappend(sbsref->reflowerindexpr, NULL);
+
+ if (ai->lidx)
+ jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->lidx, &sbsref->reflowerindexpr, false);
+ else
+ jpi->value.array.elems[0].from = make_jsonpath_item_int(0, &sbsref->reflowerindexpr);
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript does not support slices"),
- parser_errposition(pstate, exprLocation(expr))));
+ if (ai->uidx)
+ jpi->value.array.elems[0].to = make_jsonpath_item_expr(pstate, ai->uidx, &sbsref->refupperindexpr, false);
+ else
+ {
+ jpi->value.array.elems[0].to = make_jsonpath_item(jpiLast);
+ sbsref->refupperindexpr = lappend(sbsref->refupperindexpr, NULL);
+ }
}
else
{
JsonPathParseItem *jpi_from = NULL;
Assert(ai->uidx && !ai->lidx);
- jpi_from = make_jsonpath_item_expr(pstate, ai->uidx, &sbsref->refupperindexpr);
+ jpi_from = make_jsonpath_item_expr(pstate, ai->uidx, &sbsref->refupperindexpr, true);
if (jpi_from == NULL)
{
/*
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index e6a7ece6dab..935b47a3b9a 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -505,7 +505,7 @@ if (sqlca.sqlcode < 0) sqlprint();}
if (sqlca.sqlcode < 0) sqlprint();}
#line 142 "sqljson.pgc"
- // error
+ printf("Found json=%s\n", json);
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . * )", ECPGt_EOIT,
ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
@@ -525,14 +525,24 @@ if (sqlca.sqlcode < 0) sqlprint();}
if (sqlca.sqlcode < 0) sqlprint();}
#line 148 "sqljson.pgc"
- // error
+ printf("Found json=%s\n", json);
- { ECPGdisconnect(__LINE__, "CURRENT");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 151 "sqljson.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 151 "sqljson.pgc"
+ printf("Found json=%s\n", json);
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 154 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 154 "sqljson.pgc"
+
return 0;
}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
index 19f8c58af06..f3f899c6d87 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -339,13 +339,10 @@ SQL error: schema "jsonb" does not exist on line 121
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 142: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_check_PQresult on line 142: bad response - ERROR: jsonb subscript does not support slices
-LINE 1: ..., {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )
- ^
+[NO_PID]: ecpg_process_output on line 142: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 142: RESULT: [12, {"y": 1}] offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 142
-[NO_PID]: sqlca: code: -400, state: 42804
-SQL error: jsonb subscript does not support slices on line 142
[NO_PID]: ecpg_execute on line 145: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . * ); with 0 parameter(s) on connection ecpg1_regression
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 145: using PQexec
@@ -361,12 +358,17 @@ SQL error: row expansion via "*" is not supported here on line 145
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 148: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_check_PQresult on line 148: bad response - ERROR: jsonb subscript does not support slices
-LINE 1: ...x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )
- ^
+[NO_PID]: ecpg_process_output on line 148: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 148: RESULT: [{"x": 1}, {"x": [12, {"y": 1}]}] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 151: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 151: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 151: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 151: RESULT: [{"x": 1}, {"x": [12, {"y": 1}]}] offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 148
-[NO_PID]: sqlca: code: -400, state: 42804
-SQL error: jsonb subscript does not support slices on line 148
[NO_PID]: ecpg_finish: connection ecpg1_regression closed
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
index 442d36931f1..d01a8457f01 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -35,3 +35,6 @@ Found json=null
Found json={"x": 1}
Found json=[1, [12, {"y": 1}]]
Found json={"x": 1}
+Found json=[12, {"y": 1}]
+Found json=[{"x": 1}, {"x": [12, {"y": 1}]}]
+Found json=[{"x": 1}, {"x": [12, {"y": 1}]}]
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
index 57a9bff424d..9423d25fd0b 100644
--- a/src/interfaces/ecpg/test/sql/sqljson.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -140,13 +140,16 @@ EXEC SQL END DECLARE SECTION;
printf("Found json=%s\n", json);
EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[1].x[0:]) INTO :json;
- // error
+ printf("Found json=%s\n", json);
EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*) INTO :json;
// error
EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0:]) INTO :json;
- // error
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[:]) INTO :json;
+ printf("Found json=%s\n", json);
EXEC SQL DISCONNECT;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 30a7023513d..4b3a596e697 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5255,23 +5255,34 @@ select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b
(1 row)
select ('{"a": 1}'::jsonb)['a':'b']; -- fails
-ERROR: jsonb subscript does not support slices
+ERROR: only non-null integer constants are supported for jsonb simplified accessor subscripting
LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
- ^
+ ^
+HINT: use int data type for subscripting with slicing.
select ('[1, "2", null]'::jsonb)[1:2];
-ERROR: jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[1:2];
- ^
+ jsonb
+-------------
+ ["2", null]
+(1 row)
+
select ('[1, "2", null]'::jsonb)[:2];
-ERROR: jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[:2];
- ^
+ jsonb
+----------------
+ [1, "2", null]
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1:];
-ERROR: jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[1:];
- ^
+ jsonb
+-------------
+ ["2", null]
+(1 row)
+
select ('[1, "2", null]'::jsonb)[:];
-ERROR: jsonb subscript does not support slices
+ jsonb
+----------------
+ [1, "2", null]
+(1 row)
+
create TEMP TABLE test_jsonb_subscript (
id int,
test_json jsonb
@@ -6013,8 +6024,42 @@ SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
(1 row)
-SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t; -- fails
-ERROR: jsonb subscript does not support slices
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+ x
+--------------------------
+ {"y": "YYY", "z": "ZZZ"}
+(1 row)
+
+SELECT (jb).a[2:3].b FROM test_jsonb_dot_notation;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[2:].b FROM test_jsonb_dot_notation;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[:2].b FROM test_jsonb_dot_notation;
+ b
+-----
+ "c"
+(1 row)
+
+SELECT (jb).a[:].b FROM test_jsonb_dot_notation;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+ x
+--------------------------
+ {"y": "YYY", "z": "ZZZ"}
+(1 row)
+
SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
ERROR: type jsonb is not composite
-- explains should work
@@ -6050,6 +6095,28 @@ CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_d
CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
SELECT (jb).a[3].x.y AS y
FROM test_jsonb_dot_notation
+CREATE VIEW v2 AS SELECT (jb).a[3:].x.y[:-1] FROM test_jsonb_dot_notation;
+\sv v2
+CREATE OR REPLACE VIEW public.v2 AS
+ SELECT (jb).a[3:].x.y[0:'-1'::integer] AS y
+ FROM test_jsonb_dot_notation
+SELECT * from v2;
+ y
+---
+
+(1 row)
+
+CREATE VIEW v3 AS SELECT (jb).a[:3].x.y[-1:] FROM test_jsonb_dot_notation;
+\sv v3
+CREATE OR REPLACE VIEW public.v3 AS
+ SELECT (jb).a[0:3].x.y['-1'::integer:] AS y
+ FROM test_jsonb_dot_notation
+SELECT * from v3;
+ y
+-------
+ "yyy"
+(1 row)
+
-- mixed syntax
DROP VIEW test_jsonb_dot_notation_v1;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
@@ -6294,5 +6361,55 @@ SELECT * from test_jsonb_dot_notation_v1;
(1 row)
DROP VIEW public.test_jsonb_dot_notation_v1;
+-- jsonb array access in plpgsql
+DO $$
+ DECLARE
+ a jsonb := '[1,2,3,4,5,6,7]'::jsonb;
+ BEGIN
+ WHILE a IS NOT NULL
+ LOOP
+ RAISE NOTICE '%', a;
+ a := a[2:];
+ END LOOP;
+ END
+$$ LANGUAGE plpgsql;
+NOTICE: [1, 2, 3, 4, 5, 6, 7]
+NOTICE: [3, 4, 5, 6, 7]
+NOTICE: [5, 6, 7]
+NOTICE: 7
+-- jsonb dot access in plpgsql
+DO $$
+ DECLARE
+ a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+ BEGIN
+ WHILE a IS NOT NULL
+ LOOP
+ RAISE NOTICE '%', a;
+ a := COALESCE(a."NU", a[2]); -- fails
+ END LOOP;
+ END
+$$ LANGUAGE plpgsql;
+NOTICE: {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}
+ERROR: missing FROM-clause entry for table "a"
+LINE 1: a := COALESCE(a."NU", a[2])
+ ^
+QUERY: a := COALESCE(a."NU", a[2])
+CONTEXT: PL/pgSQL function inline_code_block line 8 at assignment
+DO $$
+ DECLARE
+ a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+ BEGIN
+ WHILE a IS NOT NULL
+ LOOP
+ RAISE NOTICE '%', a;
+ a := COALESCE((a)."NU", a[2]); -- succeeds
+ END LOOP;
+ END
+$$ LANGUAGE plpgsql;
+NOTICE: {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}
+NOTICE: [{"": [[3]]}, [6], [2], "bCi"]
+NOTICE: [2]
-- clean up
+DROP VIEW v2;
+DROP VIEW v3;
DROP TABLE test_jsonb_dot_notation;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index a0fad90a9d5..b8ecefda04d 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1623,7 +1623,12 @@ SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
-SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t; -- fails
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+SELECT (jb).a[2:3].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2:].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[:2].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[:].b FROM test_jsonb_dot_notation;
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
-- explains should work
@@ -1635,6 +1640,12 @@ SELECT (jb).a[1] FROM test_jsonb_dot_notation;
-- views should work
CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
\sv test_jsonb_dot_notation_v1
+CREATE VIEW v2 AS SELECT (jb).a[3:].x.y[:-1] FROM test_jsonb_dot_notation;
+\sv v2
+SELECT * from v2;
+CREATE VIEW v3 AS SELECT (jb).a[:3].x.y[-1:] FROM test_jsonb_dot_notation;
+\sv v3
+SELECT * from v3;
-- mixed syntax
DROP VIEW test_jsonb_dot_notation_v1;
@@ -1695,5 +1706,45 @@ SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
SELECT * from test_jsonb_dot_notation_v1;
DROP VIEW public.test_jsonb_dot_notation_v1;
+-- jsonb array access in plpgsql
+DO $$
+ DECLARE
+ a jsonb := '[1,2,3,4,5,6,7]'::jsonb;
+ BEGIN
+ WHILE a IS NOT NULL
+ LOOP
+ RAISE NOTICE '%', a;
+ a := a[2:];
+ END LOOP;
+ END
+$$ LANGUAGE plpgsql;
+
+-- jsonb dot access in plpgsql
+DO $$
+ DECLARE
+ a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+ BEGIN
+ WHILE a IS NOT NULL
+ LOOP
+ RAISE NOTICE '%', a;
+ a := COALESCE(a."NU", a[2]); -- fails
+ END LOOP;
+ END
+$$ LANGUAGE plpgsql;
+
+DO $$
+ DECLARE
+ a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+ BEGIN
+ WHILE a IS NOT NULL
+ LOOP
+ RAISE NOTICE '%', a;
+ a := COALESCE((a)."NU", a[2]); -- succeeds
+ END LOOP;
+ END
+$$ LANGUAGE plpgsql;
+
-- clean up
+DROP VIEW v2;
+DROP VIEW v3;
DROP TABLE test_jsonb_dot_notation;
\ No newline at end of file
--
2.39.5 (Apple Git-154)
[application/octet-stream] v12-0004-Extract-coerce_jsonpath_subscript.patch (5.7K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/4-v12-0004-Extract-coerce_jsonpath_subscript.patch)
download | inline diff:
From dfaa519306c528cc1ce0ef67da1cc8f1b1e0593e Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 4/7] Extract coerce_jsonpath_subscript()
This is a preparation step for a future commit that will reuse the
aforementioned function.
Co-authored-by: Nikita Glukhov <[email protected]>
Co-authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
---
src/backend/utils/adt/jsonbsubs.c | 130 +++++++++++++++---------------
1 file changed, 65 insertions(+), 65 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index a0d38a0fd80..5d0ec6bf2fa 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -32,6 +32,69 @@ typedef struct JsonbSubWorkspace
Datum *index; /* Subscript values in Datum format */
} JsonbSubWorkspace;
+static Node *
+coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype)
+{
+ Oid subExprType = exprType(subExpr);
+ Oid targetType = UNKNOWNOID;
+
+ if (subExprType != UNKNOWNOID)
+ {
+ Oid targets[2] = {numtype, TEXTOID};
+
+ /*
+ * Jsonb can handle multiple subscript types, but cases when a
+ * subscript could be coerced to multiple target types must be
+ * avoided, similar to overloaded functions. It could be possibly
+ * extend with jsonpath in the future.
+ */
+ for (int i = 0; i < 2; i++)
+ {
+ if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
+ {
+ /*
+ * One type has already succeeded, it means there are two
+ * coercion targets possible, failure.
+ */
+ if (targetType != UNKNOWNOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("subscript type %s is not supported", format_type_be(subExprType)),
+ errhint("jsonb subscript must be coercible to only one type, integer or text."),
+ parser_errposition(pstate, exprLocation(subExpr))));
+
+ targetType = targets[i];
+ }
+ }
+
+ /*
+ * No suitable types were found, failure.
+ */
+ if (targetType == UNKNOWNOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("subscript type %s is not supported", format_type_be(subExprType)),
+ errhint("jsonb subscript must be coercible to either integer or text."),
+ parser_errposition(pstate, exprLocation(subExpr))));
+ }
+ else
+ targetType = TEXTOID;
+
+ /*
+ * We known from can_coerce_type that coercion will succeed, so
+ * coerce_type could be used. Note the implicit coercion context, which is
+ * required to handle subscripts of different types, similar to overloaded
+ * functions.
+ */
+ subExpr = coerce_type(pstate,
+ subExpr, subExprType,
+ targetType, -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+
+ return subExpr;
+}
/*
* Finish parse analysis of a SubscriptingRef expression for a jsonb.
@@ -51,7 +114,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
/*
* Transform and convert the subscript expressions. Jsonb subscripting
- * does not support slices, look only and the upper index.
+ * does not support slices, look only at the upper index.
*/
foreach(idx, *indirection)
{
@@ -75,71 +138,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
if (ai->uidx)
{
- Oid subExprType = InvalidOid,
- targetType = UNKNOWNOID;
-
subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
- subExprType = exprType(subExpr);
-
- if (subExprType != UNKNOWNOID)
- {
- Oid targets[2] = {INT4OID, TEXTOID};
-
- /*
- * Jsonb can handle multiple subscript types, but cases when a
- * subscript could be coerced to multiple target types must be
- * avoided, similar to overloaded functions. It could be
- * possibly extend with jsonpath in the future.
- */
- for (int i = 0; i < 2; i++)
- {
- if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
- {
- /*
- * One type has already succeeded, it means there are
- * two coercion targets possible, failure.
- */
- if (targetType != UNKNOWNOID)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("subscript type %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to only one type, integer or text."),
- parser_errposition(pstate, exprLocation(subExpr))));
-
- targetType = targets[i];
- }
- }
-
- /*
- * No suitable types were found, failure.
- */
- if (targetType == UNKNOWNOID)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("subscript type %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to either integer or text."),
- parser_errposition(pstate, exprLocation(subExpr))));
- }
- else
- targetType = TEXTOID;
-
- /*
- * We known from can_coerce_type that coercion will succeed, so
- * coerce_type could be used. Note the implicit coercion context,
- * which is required to handle subscripts of different types,
- * similar to overloaded functions.
- */
- subExpr = coerce_type(pstate,
- subExpr, subExprType,
- targetType, -1,
- COERCION_IMPLICIT,
- COERCE_IMPLICIT_CAST,
- -1);
- if (subExpr == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript must have text type"),
- parser_errposition(pstate, exprLocation(subExpr))));
+ subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
}
else
{
--
2.39.5 (Apple Git-154)
[application/octet-stream] v12-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch (9.0K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/5-v12-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch)
download | inline diff:
From 18f0f086d55eb44ad6d2853bc3998a43ddc73abb Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 1/7] Allow transformation of only a sublist of subscripts
This is a preparation step for allowing subscripting containers to
transform only a prefix of an indirection list and modify the list
in-place by removing the processed elements. Currently, all elements
are consumed, and the list is set to NIL after transformation.
In the following commit, subscripting containers will gain the
flexibility to stop transformation when encountering an unsupported
indirection and return the remaining indirections to the caller.
Reviewed-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
---
contrib/hstore/hstore_subs.c | 10 ++++++----
src/backend/parser/parse_expr.c | 9 ++++-----
src/backend/parser/parse_node.c | 4 ++--
src/backend/parser/parse_target.c | 2 +-
src/backend/utils/adt/arraysubs.c | 6 ++++--
src/backend/utils/adt/jsonbsubs.c | 6 ++++--
src/include/nodes/subscripting.h | 7 ++++++-
src/include/parser/parse_node.h | 2 +-
8 files changed, 28 insertions(+), 18 deletions(-)
diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c
index 3d03f66fa0d..1b29543ab67 100644
--- a/contrib/hstore/hstore_subs.c
+++ b/contrib/hstore/hstore_subs.c
@@ -40,7 +40,7 @@
*/
static void
hstore_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -49,15 +49,15 @@ hstore_subscript_transform(SubscriptingRef *sbsref,
Node *subexpr;
/* We support only single-subscript, non-slice cases */
- if (isSlice || list_length(indirection) != 1)
+ if (isSlice || list_length(*indirection) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("hstore allows only one subscript"),
parser_errposition(pstate,
- exprLocation((Node *) indirection))));
+ exprLocation((Node *) *indirection))));
/* Transform the subscript expression to type text */
- ai = linitial_node(A_Indices, indirection);
+ ai = linitial_node(A_Indices, *indirection);
Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice);
subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
@@ -81,6 +81,8 @@ hstore_subscript_transform(SubscriptingRef *sbsref,
/* Determine the result type of the subscripting operation; always text */
sbsref->refrestype = TEXTOID;
sbsref->reftypmod = -1;
+
+ *indirection = NIL;
}
/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..e1565e11d09 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -466,14 +466,13 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
Assert(IsA(n, String));
/* process subscripts before this field selection */
- if (subscripts)
+ while (subscripts)
result = (Node *) transformContainerSubscripts(pstate,
result,
exprType(result),
exprTypmod(result),
- subscripts,
+ &subscripts,
false);
- subscripts = NIL;
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
@@ -488,12 +487,12 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
}
}
/* process trailing subscripts, if any */
- if (subscripts)
+ while (subscripts)
result = (Node *) transformContainerSubscripts(pstate,
result,
exprType(result),
exprTypmod(result),
- subscripts,
+ &subscripts,
false);
return result;
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index d6feb16aef3..19a6b678e67 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -244,7 +244,7 @@ transformContainerSubscripts(ParseState *pstate,
Node *containerBase,
Oid containerType,
int32 containerTypMod,
- List *indirection,
+ List **indirection,
bool isAssignment)
{
SubscriptingRef *sbsref;
@@ -280,7 +280,7 @@ transformContainerSubscripts(ParseState *pstate,
* element. If any of the items are slice specifiers (lower:upper), then
* the subscript expression means a container slice operation.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4aba0d9d4d5..4675a523045 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -936,7 +936,7 @@ transformAssignmentSubscripts(ParseState *pstate,
basenode,
containerType,
containerTypMod,
- subscripts,
+ &subscripts,
true);
typeNeeded = sbsref->refrestype;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 2940fb8e8d7..234c2c278c1 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -54,7 +54,7 @@ typedef struct ArraySubWorkspace
*/
static void
array_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -71,7 +71,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
* indirection items to slices by treating the single subscript as the
* upper bound and supplying an assumed lower bound of 1.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
Node *subexpr;
@@ -152,6 +152,8 @@ array_subscript_transform(SubscriptingRef *sbsref,
list_length(upperIndexpr), MAXDIM)));
/* We need not check lowerIndexpr separately */
+ *indirection = NIL;
+
/*
* Determine the result type of the subscripting operation. It's the same
* as the array type if we're slicing, else it's the element type. In
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index de64d498512..8ad6aa1ad4f 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -41,7 +41,7 @@ typedef struct JsonbSubWorkspace
*/
static void
jsonb_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -53,7 +53,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
* Transform and convert the subscript expressions. Jsonb subscripting
* does not support slices, look only and the upper index.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
Node *subExpr;
@@ -159,6 +159,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
/* Determine the result type of the subscripting operation; always jsonb */
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
+
+ *indirection = NIL;
}
/*
diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h
index 234e8ad8012..5d576af346f 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -71,6 +71,11 @@ struct SubscriptExecSteps;
* does not care to support slicing, it can just throw an error if isSlice.)
* See array_subscript_transform() for sample code.
*
+ * The transform method receives a pointer to a list of raw indirections.
+ * This allows the method to parse a sublist of the indirections (typically
+ * the prefix) and modify the original list in place, enabling the caller to
+ * either process the remaining indirections differently or raise an error.
+ *
* The transform method is also responsible for identifying the result type
* of the subscripting operation. At call, refcontainertype and reftypmod
* describe the container type (this will be a base type not a domain), and
@@ -93,7 +98,7 @@ struct SubscriptExecSteps;
* assignment must return.
*/
typedef void (*SubscriptTransform) (SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
struct ParseState *pstate,
bool isSlice,
bool isAssignment);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..58a4b9df157 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -361,7 +361,7 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate,
Node *containerBase,
Oid containerType,
int32 containerTypMod,
- List *indirection,
+ List **indirection,
bool isAssignment);
extern Const *make_const(ParseState *pstate, A_Const *aconst);
--
2.39.5 (Apple Git-154)
[application/octet-stream] v12-0007-Implement-jsonb-wildcard-member-accessor.patch (31.4K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/6-v12-0007-Implement-jsonb-wildcard-member-accessor.patch)
download | inline diff:
From 40199a1ca6ce4fb975190df99b746205b1ebb5b8 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 7/7] Implement jsonb wildcard member accessor
This commit adds support for wildcard member access in jsonb, as
specified by the JSON simplified accessor syntax in SQL:2023.
Co-authored-by: Nikita Glukhov <[email protected]>
Co-authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Mark Dilger <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Vik Fearing <[email protected]>
Reviewed-by: Nikita Malakhov <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Reviewed-by: Jelte Fennema-Nio <[email protected]>
Tested-by: Jelte Fennema-Nio <[email protected]>
---
src/backend/nodes/nodeFuncs.c | 8 +
src/backend/parser/gram.y | 2 +
src/backend/parser/parse_expr.c | 34 ++-
src/backend/parser/parse_target.c | 67 ++++--
src/backend/utils/adt/jsonbsubs.c | 12 +-
src/backend/utils/adt/ruleutils.c | 6 +-
src/include/nodes/primnodes.h | 12 +
src/include/parser/parse_expr.h | 3 +
.../ecpg/test/expected/sql-sqljson.c | 18 +-
.../ecpg/test/expected/sql-sqljson.stderr | 23 +-
.../ecpg/test/expected/sql-sqljson.stdout | 2 +
src/interfaces/ecpg/test/sql/sqljson.pgc | 5 +-
src/test/regress/expected/jsonb.out | 222 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 42 +++-
14 files changed, 405 insertions(+), 51 deletions(-)
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index d1bd575d9bd..5f3038a1c26 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -291,6 +291,9 @@ exprType(const Node *expr)
*/
type = TEXTOID;
break;
+ case T_Star:
+ type = UNKNOWNOID;
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
type = InvalidOid; /* keep compiler quiet */
@@ -1156,6 +1159,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_FieldAccessorExpr:
((FieldAccessorExpr *) expr)->faecollid = collation;
break;
+ case T_Star:
+ Assert(!OidIsValid(collation));
+ break;
case T_SubscriptingRef:
((SubscriptingRef *) expr)->refcollid = collation;
break;
@@ -2140,6 +2146,7 @@ expression_tree_walker_impl(Node *node,
case T_CTESearchClause:
case T_MergeSupportFunc:
case T_FieldAccessorExpr:
+ case T_Star:
/* primitive node types with no expression subnodes */
break;
case T_WithCheckOption:
@@ -3020,6 +3027,7 @@ expression_tree_mutator_impl(Node *node,
case T_CTESearchClause:
case T_MergeSupportFunc:
case T_FieldAccessorExpr:
+ case T_Star:
return copyObject(node);
case T_WithCheckOption:
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 70a0d832a11..532217425b6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -18984,6 +18984,7 @@ check_func_name(List *names, core_yyscan_t yyscanner)
static List *
check_indirection(List *indirection, core_yyscan_t yyscanner)
{
+#if 0
ListCell *l;
foreach(l, indirection)
@@ -18994,6 +18995,7 @@ check_indirection(List *indirection, core_yyscan_t yyscanner)
parser_yyerror("improper use of \"*\"");
}
}
+#endif
return indirection;
}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 07d46747811..d91700ebf39 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -74,7 +74,6 @@ static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref);
static Node *transformWholeRowRef(ParseState *pstate,
ParseNamespaceItem *nsitem,
int sublevels_up, int location);
-static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
static Node *transformJsonObjectConstructor(ParseState *pstate,
@@ -158,7 +157,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
break;
case T_A_Indirection:
- result = transformIndirection(pstate, (A_Indirection *) expr);
+ result = transformIndirection(pstate, (A_Indirection *) expr, NULL);
break;
case T_A_ArrayExpr:
@@ -432,8 +431,9 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname,
}
}
-static Node *
-transformIndirection(ParseState *pstate, A_Indirection *ind)
+Node *
+transformIndirection(ParseState *pstate, A_Indirection *ind,
+ bool *trailing_star_expansion)
{
Node *last_srf = pstate->p_last_srf;
Node *result = transformExprRecurse(pstate, ind->arg);
@@ -454,12 +454,7 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
if (IsA(n, A_Indices))
subscripts = lappend(subscripts, n);
else if (IsA(n, A_Star))
- {
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row expansion via \"*\" is not supported here"),
- parser_errposition(pstate, location)));
- }
+ subscripts = lappend(subscripts, n);
else
{
Assert(IsA(n, String));
@@ -491,7 +486,21 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
n = linitial(subscripts);
- if (!IsA(n, String))
+ if (IsA(n, A_Star))
+ {
+ /* Success, if trailing star expansion is allowed */
+ if (trailing_star_expansion && list_length(subscripts) == 1)
+ {
+ *trailing_star_expansion = true;
+ return result;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row expansion via \"*\" is not supported here"),
+ parser_errposition(pstate, location)));
+ }
+ else if (!IsA(n, String))
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot subscript type %s because it does not support subscripting",
@@ -517,6 +526,9 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
result = newresult;
}
+ if (trailing_star_expansion)
+ *trailing_star_expansion = false;
+
return result;
}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 3ef5897f2eb..141fc1dfeb1 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -48,7 +48,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate,
static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
bool make_target_entry);
static List *ExpandAllTables(ParseState *pstate, int location);
-static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
+static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
bool make_target_entry, ParseExprKind exprKind);
static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem,
int sublevels_up, int location,
@@ -134,6 +134,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
foreach(o_target, targetlist)
{
ResTarget *res = (ResTarget *) lfirst(o_target);
+ Node *transformed = NULL;
/*
* Check for "something.*". Depending on the complexity of the
@@ -162,13 +163,19 @@ transformTargetList(ParseState *pstate, List *targetlist,
if (IsA(llast(ind->indirection), A_Star))
{
- /* It is something.*, expand into multiple items */
- p_target = list_concat(p_target,
- ExpandIndirectionStar(pstate,
- ind,
- true,
- exprKind));
- continue;
+ Node *columns = ExpandIndirectionStar(pstate,
+ ind,
+ true,
+ exprKind);
+
+ if (IsA(columns, List))
+ {
+ /* It is something.*, expand into multiple items */
+ p_target = list_concat(p_target, (List *) columns);
+ continue;
+ }
+
+ transformed = (Node *) columns;
}
}
}
@@ -180,7 +187,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
p_target = lappend(p_target,
transformTargetEntry(pstate,
res->val,
- NULL,
+ transformed,
exprKind,
res->name,
false));
@@ -251,10 +258,15 @@ transformExpressionList(ParseState *pstate, List *exprlist,
if (IsA(llast(ind->indirection), A_Star))
{
- /* It is something.*, expand into multiple items */
- result = list_concat(result,
- ExpandIndirectionStar(pstate, ind,
- false, exprKind));
+ Node *cols = ExpandIndirectionStar(pstate, ind,
+ false, exprKind);
+
+ if (!cols || IsA(cols, List))
+ /* It is something.*, expand into multiple items */
+ result = list_concat(result, (List *) cols);
+ else
+ result = lappend(result, cols);
+
continue;
}
}
@@ -1345,22 +1357,30 @@ ExpandAllTables(ParseState *pstate, int location)
* For robustness, we use a separate "make_target_entry" flag to control
* this rather than relying on exprKind.
*/
-static List *
+static Node *
ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
bool make_target_entry, ParseExprKind exprKind)
{
Node *expr;
+ ParseExprKind sv_expr_kind;
+ bool trailing_star_expansion = false;
+
+ /* Save and restore identity of expression type we're parsing */
+ Assert(exprKind != EXPR_KIND_NONE);
+ sv_expr_kind = pstate->p_expr_kind;
+ pstate->p_expr_kind = exprKind;
/* Strip off the '*' to create a reference to the rowtype object */
- ind = copyObject(ind);
- ind->indirection = list_truncate(ind->indirection,
- list_length(ind->indirection) - 1);
+ expr = transformIndirection(pstate, ind, &trailing_star_expansion);
+
+ pstate->p_expr_kind = sv_expr_kind;
- /* And transform that */
- expr = transformExpr(pstate, (Node *) ind, exprKind);
+ /* '*' was consumed by generic type subscripting */
+ if (!trailing_star_expansion)
+ return expr;
/* Expand the rowtype expression into individual fields */
- return ExpandRowReference(pstate, expr, make_target_entry);
+ return (Node *) ExpandRowReference(pstate, expr, make_target_entry);
}
/*
@@ -1785,13 +1805,18 @@ FigureColnameInternal(Node *node, char **name)
char *fname = NULL;
ListCell *l;
- /* find last field name, if any, ignoring "*" and subscripts */
+ /*
+ * find last field name, if any, ignoring subscripts, and use
+ * '?column?' when there's a trailing '*'.
+ */
foreach(l, ind->indirection)
{
Node *i = lfirst(l);
if (IsA(i, String))
fname = strVal(i);
+ else if (IsA(i, A_Star))
+ fname = "?column?";
}
if (fname)
{
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index c35d20933ea..a29808b6e76 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -124,7 +124,7 @@ jsonb_check_jsonpath_needed(List *indirection)
{
Node *accessor = lfirst(lc);
- if (IsA(accessor, String))
+ if (IsA(accessor, String) || IsA(accessor, A_Star))
return true;
else
{
@@ -325,6 +325,16 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, Subscripti
jpi->value.array.elems[0].to = NULL;
}
}
+ else if (IsA(accessor, A_Star))
+ {
+ Star *star_node;
+ jpi = make_jsonpath_item(jpiAnyKey);
+
+ star_node = makeNode(Star);
+ star_node->type = T_Star;
+
+ sbsref->refupperindexpr = lappend(sbsref->refupperindexpr, star_node);
+ }
else
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index baa3ae97d57..ace0eff52e2 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -13010,7 +13010,11 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
{
Node *upper = (Node *) lfirst(uplist_item);
- if (upper && IsA(upper, FieldAccessorExpr))
+ if (upper && IsA(upper, Star))
+ {
+ appendStringInfoString(buf, ".*");
+ }
+ else if (upper && IsA(upper, FieldAccessorExpr))
{
FieldAccessorExpr *fae = (FieldAccessorExpr *) upper;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index b208d7949b1..63446edaad7 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2420,4 +2420,16 @@ typedef struct FieldAccessorExpr
int location;
} FieldAccessorExpr;
+/*
+ * Star - represents a wildcard member accessor (e.g., ".*") used in JSONB simplified accessor.
+ *
+ * This node serves as a syntactic placeholder in the expression tree and does not get evaluated, hence it
+ * has no associated type or collation.
+ */
+typedef struct Star
+{
+ NodeTag type;
+} Star;
+
+
#endif /* PRIMNODES_H */
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index efbaff8e710..c9f6a7724c0 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -22,4 +22,7 @@ extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKin
extern const char *ParseExprKindName(ParseExprKind exprKind);
+extern Node *transformIndirection(ParseState *pstate, A_Indirection *ind,
+ bool *trailing_star_expansion);
+
#endif /* PARSE_EXPR_H */
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index 935b47a3b9a..585d9b14445 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -515,9 +515,9 @@ if (sqlca.sqlcode < 0) sqlprint();}
if (sqlca.sqlcode < 0) sqlprint();}
#line 145 "sqljson.pgc"
- // error
+ printf("Found json=%s\n", json);
- { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )", ECPGt_EOIT,
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . * . x )", ECPGt_EOIT,
ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 148 "sqljson.pgc"
@@ -527,7 +527,7 @@ if (sqlca.sqlcode < 0) sqlprint();}
printf("Found json=%s\n", json);
- { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ : ] )", ECPGt_EOIT,
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )", ECPGt_EOIT,
ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 151 "sqljson.pgc"
@@ -537,12 +537,22 @@ if (sqlca.sqlcode < 0) sqlprint();}
printf("Found json=%s\n", json);
- { ECPGdisconnect(__LINE__, "CURRENT");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 154 "sqljson.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 154 "sqljson.pgc"
+ printf("Found json=%s\n", json);
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 157 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 157 "sqljson.pgc"
+
return 0;
}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
index f3f899c6d87..4b9088545d6 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -347,22 +347,19 @@ SQL error: schema "jsonb" does not exist on line 121
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 145: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_check_PQresult on line 145: bad response - ERROR: row expansion via "*" is not supported here
-LINE 1: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x...
- ^
+[NO_PID]: ecpg_process_output on line 145: correctly got 1 tuples with 1 fields
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: raising sqlstate 0A000 (sqlcode -400): row expansion via "*" is not supported here on line 145
-[NO_PID]: sqlca: code: -400, state: 0A000
-SQL error: row expansion via "*" is not supported here on line 145
-[NO_PID]: ecpg_execute on line 148: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: ecpg_get_data on line 145: RESULT: [{"b": 1, "c": 2}, [{"x": 1}, {"x": [12, {"y": 1}]}]] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 148: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . * . x ); with 0 parameter(s) on connection ecpg1_regression
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 148: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_process_output on line 148: correctly got 1 tuples with 1 fields
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_get_data on line 148: RESULT: [{"x": 1}, {"x": [12, {"y": 1}]}] offset: -1; array: no
+[NO_PID]: ecpg_get_data on line 148: RESULT: [1, [12, {"y": 1}]] offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_execute on line 151: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: ecpg_execute on line 151: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 151: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
@@ -370,5 +367,13 @@ SQL error: row expansion via "*" is not supported here on line 145
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_get_data on line 151: RESULT: [{"x": 1}, {"x": [12, {"y": 1}]}] offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 154: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 154: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 154: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 154: RESULT: [{"x": 1}, {"x": [12, {"y": 1}]}] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_finish: connection ecpg1_regression closed
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
index d01a8457f01..145dc95d430 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -36,5 +36,7 @@ Found json={"x": 1}
Found json=[1, [12, {"y": 1}]]
Found json={"x": 1}
Found json=[12, {"y": 1}]
+Found json=[{"b": 1, "c": 2}, [{"x": 1}, {"x": [12, {"y": 1}]}]]
+Found json=[1, [12, {"y": 1}]]
Found json=[{"x": 1}, {"x": [12, {"y": 1}]}]
Found json=[{"x": 1}, {"x": [12, {"y": 1}]}]
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
index 9423d25fd0b..2af50b5da4b 100644
--- a/src/interfaces/ecpg/test/sql/sqljson.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -143,7 +143,10 @@ EXEC SQL END DECLARE SECTION;
printf("Found json=%s\n", json);
EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*) INTO :json;
- // error
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*.x) INTO :json;
+ printf("Found json=%s\n", json);
EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0:]) INTO :json;
printf("Found json=%s\n", json);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4b3a596e697..741505d3b6d 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -6060,8 +6060,175 @@ SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
{"y": "YYY", "z": "ZZZ"}
(1 row)
-SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
-ERROR: type jsonb is not composite
+/* wild card member access */
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ ?column?
+-------------------------------------------
+ ["c", "d", "f", {"y": "yyy", "z": "zzz"}]
+(1 row)
+
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ ?column?
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+ ?column?
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ ?column?
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ ?column?
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+ERROR: missing FROM-clause entry for table "t"
+LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;
+ ^
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ ?column?
+------------------------------------------------------------------------------------------------------------------------------
+ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]]
+(1 row)
+
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ ?column?
+-------------------------------------------
+ ["c", "d", "f", {"y": "yyy", "z": "zzz"}]
+(1 row)
+
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+ b
+---
+
+(1 row)
+
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+ x
+---
+
+(1 row)
+
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+ y
+-------
+ "yyy"
+(1 row)
+
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ ?column?
+----------------
+ ["yyy", "zzz"]
+(1 row)
+
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ x
+---
+
+(1 row)
+
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ x
+---
+
+(1 row)
+
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ x
+------------------------------------------------------
+ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}]
+(1 row)
+
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+ ?column?
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+ y
+----------------
+ ["yyy", "YYY"]
+(1 row)
+
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+ z
+----------------
+ ["zzz", "ZZZ"]
+(1 row)
+
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+ y
+----------------
+ ["yyy", "YYY"]
+(1 row)
+
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+ ?column?
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ ?column?
+----------
+
+(1 row)
+
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+ ?column?
+----------
+
+(1 row)
+
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ ?column?
+----------------
+ ["yyy", "zzz"]
+(1 row)
+
+SELECT (jb).a.*[:].* FROM test_jsonb_dot_notation;
+ ?column?
+----------------
+ ["yyy", "zzz"]
+(1 row)
+
+SELECT (jb).a.*[*].* FROM test_jsonb_dot_notation; -- not supported
+ERROR: syntax error at or near "*"
+LINE 1: SELECT (jb).a.*[*].* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.**.x FROM test_jsonb_dot_notation; -- not supported
+ERROR: syntax error at or near "**"
+LINE 1: SELECT (jb).a.**.x FROM test_jsonb_dot_notation;
+ ^
-- explains should work
EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
QUERY PLAN
@@ -6089,6 +6256,45 @@ SELECT (jb).a[1] FROM test_jsonb_dot_notation;
2
(1 row)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb).a.*
+(2 rows)
+
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ ?column?
+-------------------------------------------
+ ["c", "d", "f", {"y": "yyy", "z": "zzz"}]
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:].* FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb).a.*[:].*
+(2 rows)
+
+SELECT (jb).a.*[1:].* FROM test_jsonb_dot_notation;
+ ?column?
+----------
+
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2].*.b FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb).a.*[:2].*.b
+(2 rows)
+
+SELECT (jb).a.*[1:2].*.b FROM test_jsonb_dot_notation;
+ b
+---
+
+(1 row)
+
-- views should work
CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
\sv test_jsonb_dot_notation_v1
@@ -6117,6 +6323,17 @@ SELECT * from v3;
"yyy"
(1 row)
+CREATE VIEW v4 AS SELECT (jb).a.*[:].* FROM test_jsonb_dot_notation;
+\sv v4
+CREATE OR REPLACE VIEW public.v4 AS
+ SELECT (jb).a.*[:].* AS "?column?"
+ FROM test_jsonb_dot_notation
+SELECT * from v4;
+ ?column?
+----------------
+ ["yyy", "zzz"]
+(1 row)
+
-- mixed syntax
DROP VIEW test_jsonb_dot_notation_v1;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
@@ -6412,4 +6629,5 @@ NOTICE: [2]
-- clean up
DROP VIEW v2;
DROP VIEW v3;
+DROP VIEW v4;
DROP TABLE test_jsonb_dot_notation;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index b8ecefda04d..440ea1c6549 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1629,13 +1629,49 @@ SELECT (jb).a[2:].b FROM test_jsonb_dot_notation;
SELECT (jb).a[:2].b FROM test_jsonb_dot_notation;
SELECT (jb).a[:].b FROM test_jsonb_dot_notation;
SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
-SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
+
+/* wild card member access */
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*[:].* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*[*].* FROM test_jsonb_dot_notation; -- not supported
+SELECT (jb).a.**.x FROM test_jsonb_dot_notation; -- not supported
-- explains should work
EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
SELECT (t.jb).a FROM test_jsonb_dot_notation t;
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:].* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*[1:].* FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2].*.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.*[1:2].*.b FROM test_jsonb_dot_notation;
-- views should work
CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
@@ -1646,6 +1682,9 @@ SELECT * from v2;
CREATE VIEW v3 AS SELECT (jb).a[:3].x.y[-1:] FROM test_jsonb_dot_notation;
\sv v3
SELECT * from v3;
+CREATE VIEW v4 AS SELECT (jb).a.*[:].* FROM test_jsonb_dot_notation;
+\sv v4
+SELECT * from v4;
-- mixed syntax
DROP VIEW test_jsonb_dot_notation_v1;
@@ -1747,4 +1786,5 @@ $$ LANGUAGE plpgsql;
-- clean up
DROP VIEW v2;
DROP VIEW v3;
+DROP VIEW v4;
DROP TABLE test_jsonb_dot_notation;
\ No newline at end of file
--
2.39.5 (Apple Git-154)
[application/octet-stream] v12-0005-Implement-read-only-dot-notation-for-jsonb.patch (66.3K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/7-v12-0005-Implement-read-only-dot-notation-for-jsonb.patch)
download | inline diff:
From 12b7b98a3dc5a784a7178846b54915ea1be6e4e5 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 5/7] Implement read-only dot notation for jsonb
This patch introduces JSONB member access using dot notation that
aligns with the JSON simplified accessor specified in SQL:2023.
Examples:
-- Setup
create table t(x int, y jsonb);
insert into t select 1, '{"a": 1, "b": 42}'::jsonb;
insert into t select 1, '{"a": 2, "b": {"c": 42}}'::jsonb;
insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::jsonb;
-- Existing syntax in PostgreSQL that predates the SQL standard:
select (t.y)->'b' from t;
select (t.y)->'b'->'c' from t;
select (t.y)->'d'->0 from t;
-- JSON simplified accessor specified by the SQL standard:
select (t.y).b from t;
select (t.y).b.c from t;
select (t.y).d[0] from t;
The SQL standard states that simplified access is equivalent to:
JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)
where:
VEP = <value expression primary>
JC = <JSON simplified accessor op chain>
For example, the JSON_QUERY equivalents of the above queries are:
select json_query(y, 'lax $.b' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
select json_query(y, 'lax $.b.c' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
select json_query(y, 'lax $.d[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
Implementation details:
Extends the existing container subscripting interface to support
container-specific information, specifically a JSONPath expression for
jsonb.
During query transformation, if dot-notation is present, constructs a
JSONPath expression representing the access chain.
During execution, if a JSONPath expression is present in
JsonbSubWorkspace, executes it via JsonPathQuery().
Does not transform accessors directly into JSON_QUERY during
transformation to preserve the original query structure for EXPLAIN
and CREATE VIEW.
Co-authored-by: Nikita Glukhov <[email protected]>
Co-authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Mark Dilger <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Vik Fearing <[email protected]>
Reviewed-by: Nikita Malakhov <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Tested-by: Jelte Fennema-Nio <[email protected]>
---
src/backend/catalog/sql_features.txt | 4 +-
src/backend/executor/execExpr.c | 76 +--
src/backend/nodes/nodeFuncs.c | 12 +
src/backend/utils/adt/jsonbsubs.c | 341 +++++++++++--
src/backend/utils/adt/ruleutils.c | 43 +-
src/include/nodes/primnodes.h | 55 +-
.../ecpg/test/expected/sql-sqljson.c | 112 ++++-
.../ecpg/test/expected/sql-sqljson.stderr | 100 ++++
.../ecpg/test/expected/sql-sqljson.stdout | 7 +
src/interfaces/ecpg/test/sql/sqljson.pgc | 33 ++
src/test/regress/expected/jsonb.out | 469 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 111 ++++-
12 files changed, 1263 insertions(+), 100 deletions(-)
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ebe85337c28..457e993305e 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -568,8 +568,8 @@ T838 JSON_TABLE: PLAN DEFAULT clause NO
T839 Formatted cast of datetimes to/from character strings NO
T840 Hex integer literals in SQL/JSON path language YES
T851 SQL/JSON: optional keywords for default syntax YES
-T860 SQL/JSON simplified accessor: column reference only NO
-T861 SQL/JSON simplified accessor: case-sensitive JSON member accessor NO
+T860 SQL/JSON simplified accessor: column reference only YES
+T861 SQL/JSON simplified accessor: case-sensitive JSON member accessor YES
T862 SQL/JSON simplified accessor: wildcard member accessor NO
T863 SQL/JSON simplified accessor: single-quoted string literal as member accessor NO
T864 SQL/JSON simplified accessor NO
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..75e57ca0c79 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -3320,50 +3320,52 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
state->steps_len - 1);
}
- /* Evaluate upper subscripts */
- i = 0;
- foreach(lc, sbsref->refupperindexpr)
+ /* Evaluate upper subscripts, unless refjsonbpath is used for execution */
+ if (!sbsref->refjsonbpath)
{
- Expr *e = (Expr *) lfirst(lc);
-
- /* When slicing, individual subscript bounds can be omitted */
- if (!e)
- {
- sbsrefstate->upperprovided[i] = false;
- sbsrefstate->upperindexnull[i] = true;
- }
- else
- {
- sbsrefstate->upperprovided[i] = true;
- /* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->upperindex[i],
- &sbsrefstate->upperindexnull[i]);
+ i = 0;
+ foreach(lc, sbsref->refupperindexpr) {
+ Expr *e = (Expr *) lfirst(lc);
+
+ /* When slicing, individual subscript bounds can be omitted */
+ if (!e) {
+ sbsrefstate->upperprovided[i] = false;
+ sbsrefstate->upperindexnull[i] = true;
+ } else {
+ sbsrefstate->upperprovided[i] = true;
+ /* Each subscript is evaluated into appropriate array entry */
+ ExecInitExprRec(e, state,
+ &sbsrefstate->upperindex[i],
+ &sbsrefstate->upperindexnull[i]);
+ }
+ i++;
}
- i++;
}
- /* Evaluate lower subscripts similarly */
- i = 0;
- foreach(lc, sbsref->reflowerindexpr)
+ /* Evaluate lower subscripts similarly, unless refjsonbpath is used for execution */
+ if (!sbsref->refjsonbpath)
{
- Expr *e = (Expr *) lfirst(lc);
-
- /* When slicing, individual subscript bounds can be omitted */
- if (!e)
+ i = 0;
+ foreach(lc, sbsref->reflowerindexpr)
{
- sbsrefstate->lowerprovided[i] = false;
- sbsrefstate->lowerindexnull[i] = true;
- }
- else
- {
- sbsrefstate->lowerprovided[i] = true;
- /* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->lowerindex[i],
- &sbsrefstate->lowerindexnull[i]);
+ Expr *e = (Expr *) lfirst(lc);
+
+ /* When slicing, individual subscript bounds can be omitted */
+ if (!e)
+ {
+ sbsrefstate->lowerprovided[i] = false;
+ sbsrefstate->lowerindexnull[i] = true;
+ }
+ else
+ {
+ sbsrefstate->lowerprovided[i] = true;
+ /* Each subscript is evaluated into appropriate array entry */
+ ExecInitExprRec(e, state,
+ &sbsrefstate->lowerindex[i],
+ &sbsrefstate->lowerindexnull[i]);
+ }
+ i++;
}
- i++;
}
/* SBSREF_SUBSCRIPTS checks and converts all the subscripts at once */
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..d1bd575d9bd 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -284,6 +284,13 @@ exprType(const Node *expr)
case T_PlaceHolderVar:
type = exprType((Node *) ((const PlaceHolderVar *) expr)->phexpr);
break;
+ case T_FieldAccessorExpr:
+ /*
+ * FieldAccessorExpr is not evaluable. Treat it as TEXT for collation,
+ * deparsing, and similar purposes, since it represents a JSON field name.
+ */
+ type = TEXTOID;
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
type = InvalidOid; /* keep compiler quiet */
@@ -1146,6 +1153,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_MergeSupportFunc:
((MergeSupportFunc *) expr)->msfcollid = collation;
break;
+ case T_FieldAccessorExpr:
+ ((FieldAccessorExpr *) expr)->faecollid = collation;
+ break;
case T_SubscriptingRef:
((SubscriptingRef *) expr)->refcollid = collation;
break;
@@ -2129,6 +2139,7 @@ expression_tree_walker_impl(Node *node,
case T_SortGroupClause:
case T_CTESearchClause:
case T_MergeSupportFunc:
+ case T_FieldAccessorExpr:
/* primitive node types with no expression subnodes */
break;
case T_WithCheckOption:
@@ -3008,6 +3019,7 @@ expression_tree_mutator_impl(Node *node,
case T_SortGroupClause:
case T_CTESearchClause:
case T_MergeSupportFunc:
+ case T_FieldAccessorExpr:
return copyObject(node);
case T_WithCheckOption:
{
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 5d0ec6bf2fa..82cf60df822 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -15,21 +15,30 @@
#include "postgres.h"
#include "executor/execExpr.h"
+#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/subscripting.h"
#include "parser/parse_coerce.h"
#include "parser/parse_expr.h"
#include "utils/builtins.h"
#include "utils/jsonb.h"
+#include "utils/jsonpath.h"
-/* SubscriptingRefState.workspace for jsonb subscripting execution */
+/*
+ * SubscriptingRefState.workspace for generic jsonb subscripting execution.
+ *
+ * Stores state for both jsonb simple subscripting and dot notation access.
+ * Dot notation additionally uses `jsonpath` for JsonPath evaluation.
+ */
typedef struct JsonbSubWorkspace
{
bool expectArray; /* jsonb root is expected to be an array */
Oid *indexOid; /* OID of coerced subscript expression, could
* be only integer or text */
Datum *index; /* Subscript values in Datum format */
+ JsonPath *jsonpath; /* JsonPath for dot notation execution via
+ * JsonPathQuery() */
} JsonbSubWorkspace;
static Node *
@@ -96,6 +105,244 @@ coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype)
return subExpr;
}
+/*
+ * During transformation, determine whether to build a JsonPath
+ * for JsonPathQuery() execution.
+ *
+ * JsonPath is needed if the indirection list includes:
+ * - String-based access (dot notation)
+ * - Slice-based subscripting
+ *
+ * Otherwise, simple jsonb subscripting is enough.
+ */
+static bool
+jsonb_check_jsonpath_needed(List *indirection)
+{
+ ListCell *lc;
+
+ foreach(lc, indirection)
+ {
+ Node *accessor = lfirst(lc);
+
+ if (IsA(accessor, String))
+ return true;
+ else
+ {
+ Assert(IsA(accessor, A_Indices));
+
+ if (castNode(A_Indices, accessor)->is_slice)
+ return true;
+ }
+ }
+
+ return false;
+}
+
+/*
+ * Helper functions for constructing JsonPath expressions.
+ *
+ * The make_jsonpath_item_* functions create various types of JsonPathParseItem
+ * nodes, which are used to build JsonPath expressions for jsonb simplified
+ * accessor.
+ */
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+ JsonPathParseItem *v = palloc(sizeof(*v));
+
+ v->type = type;
+ v->next = NULL;
+
+ return v;
+}
+
+/*
+ * Build a JsonPathParseItem for a constant integer value.
+ *
+ * This function constructs a jpiNumeric item for use in JsonPath,
+ * and appends a matching Const(INT4) node to the given expression list
+ * for use in EXPLAIN, views, etc.
+ *
+ * Parameters:
+ * - val: integer constant value
+ * - exprs: list of expression nodes (updated in place)
+ */
+static JsonPathParseItem *
+make_jsonpath_item_int(int32 val, List **exprs)
+{
+ JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
+
+ jpi->value.numeric =
+ DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(val)));
+
+ *exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
+ Int32GetDatum(val), false, true));
+
+ return jpi;
+}
+
+/*
+ * Convert a constant integer expression into a JsonPathParseItem.
+ *
+ * The input expression must be a non-null constant of type INT4. Returns NULL otherwise.
+ * The function extracts the value and delegates it to make_jsonpath_item_int().
+ *
+ * Parameters:
+ * - pstate: parse state context
+ * - expr: input expression node
+ * - exprs: list of expression nodes (updated in place)
+ */
+static JsonPathParseItem *
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+{
+ Const *cnst;
+
+ expr = transformExpr(pstate, expr, pstate->p_expr_kind);
+
+ if (IsA(expr, Const))
+ {
+ cnst = (Const *) expr;
+ if (cnst->consttype == INT4OID && !(cnst->constisnull))
+ return make_jsonpath_item_int(DatumGetInt32(cnst->constvalue), exprs);
+ }
+
+ return NULL;
+}
+
+/*
+ * Constructs a JsonPath expression from a list of indirections.
+ * This function is used when jsonb subscripting involves dot notation,
+ * requiring JsonPath-based evaluation.
+ *
+ * The function modifies the indirection list in place, removing processed
+ * elements as it converts them into JsonPath components, as follows:
+ * - String keys (dot notation) -> jpiKey items.
+ * - Array indices -> jpiIndexArray items.
+ *
+ * In addition to building the JsonPath expression, this function populates
+ * the following fields of the given SubscriptingRef:
+ * - refjsonbpath: the generated JsonPath
+ * - refupperindexpr: upper index expressions (object keys or array indexes)
+ * - reflowerindexpr: lower index expressions, remains NIL as slices are not yet supported.
+ *
+ * Parameters:
+ * - pstate: Parse state context.
+ * - indirection: List of subscripting expressions (modified in-place).
+ * - sbsref: SubscriptingRef node to update
+ */
+static void
+jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, SubscriptingRef *sbsref)
+{
+ JsonPathParseResult jpres;
+ JsonPathParseItem *path = make_jsonpath_item(jpiRoot);
+ ListCell *lc;
+ Datum jsp;
+ int pathlen = 0;
+ int warning_location = -1;
+
+ sbsref->refupperindexpr = NIL;
+ sbsref->reflowerindexpr = NIL;
+ sbsref->refjsonbpath = NULL;
+
+ jpres.expr = path;
+ jpres.lax = true;
+
+ foreach(lc, *indirection)
+ {
+ Node *accessor = lfirst(lc);
+ JsonPathParseItem *jpi;
+
+ if (IsA(accessor, String))
+ {
+ char *field = strVal(accessor);
+ FieldAccessorExpr *accessor_expr;
+
+ jpi = make_jsonpath_item(jpiKey);
+ jpi->value.string.val = field;
+ jpi->value.string.len = strlen(field);
+
+ accessor_expr = makeNode(FieldAccessorExpr);
+ accessor_expr->xpr.type = T_FieldAccessorExpr;
+ accessor_expr->fieldname = field;
+ accessor_expr->location = -1;
+
+ sbsref->refupperindexpr = lappend(sbsref->refupperindexpr, accessor_expr);
+ }
+ else if (IsA(accessor, A_Indices))
+ {
+ A_Indices *ai = castNode(A_Indices, accessor);
+
+ jpi = make_jsonpath_item(jpiIndexArray);
+ jpi->value.array.nelems = 1;
+ jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+ if (ai->is_slice)
+ {
+ Node *expr = ai->uidx ? ai->uidx : ai->lidx;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("jsonb subscript does not support slices"),
+ parser_errposition(pstate, exprLocation(expr))));
+ }
+ else
+ {
+ JsonPathParseItem *jpi_from = NULL;
+
+ Assert(ai->uidx && !ai->lidx);
+ jpi_from = make_jsonpath_item_expr(pstate, ai->uidx, &sbsref->refupperindexpr);
+ if (jpi_from == NULL)
+ {
+ /*
+ * postpone emitting the warning until the end of this
+ * function
+ */
+ warning_location = exprLocation(ai->uidx);
+ pfree(jpi->value.array.elems);
+ pfree(jpi);
+ break;
+ }
+
+ jpi->value.array.elems[0].from = jpi_from;
+ jpi->value.array.elems[0].to = NULL;
+ }
+ }
+ else
+
+ /*
+ * Unsupported node type for creating jsonpath. Instead of
+ * throwing an ERROR, break here so that we create a jsonpath from
+ * as many indirection elements as we can and let
+ * transformIndirection() fallback to alternative logic to handle
+ * the remaining indirection elements.
+ */
+ break;
+
+ /* append path item */
+ path->next = jpi;
+ path = jpi;
+ pathlen++;
+ }
+
+ if (pathlen == 0)
+ return;
+
+ *indirection = list_delete_first_n(*indirection, pathlen);
+
+ /* emit warning conditionally to minimize duplicate warnings */
+ if (list_length(*indirection) > 0)
+ ereport(WARNING,
+ errcode(ERRCODE_WARNING),
+ errmsg("mixed usage of jsonb simplified accessor syntax and jsonb subscripting."),
+ errhint("use dot-notation for member access, or use non-null integer constants subscripting for array access."),
+ parser_errposition(pstate, warning_location));
+
+ jsp = jsonPathFromParseResult(&jpres, 0, NULL);
+
+ sbsref->refjsonbpath = (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false);
+}
+
/*
* Finish parse analysis of a SubscriptingRef expression for a jsonb.
*
@@ -112,47 +359,43 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
ListCell *idx;
+ /* Determine the result type of the subscripting operation; always jsonb */
+ sbsref->refrestype = JSONBOID;
+ sbsref->reftypmod = -1;
+
+ if (jsonb_check_jsonpath_needed(*indirection))
+ {
+ jsonb_subscript_make_jsonpath(pstate, indirection, sbsref);
+ if (sbsref->refjsonbpath)
+ return;
+ }
+
/*
- * Transform and convert the subscript expressions. Jsonb subscripting
- * does not support slices, look only at the upper index.
+ * We would only reach here if json simplified accessor is not needed, or
+ * if jsonb_subscript_make_jsonpath() didn't consume any indirection
+ * element — either way, the first indirection element could not be
+ * converted into a JsonPath component. This happens when it's a non-slice
+ * A_Indices with a non-integer upper index. The code below falls back to
+ * traditional jsonb subscripting for such cases.
*/
foreach(idx, *indirection)
{
+ Node *i = lfirst(idx);
A_Indices *ai;
Node *subExpr;
if (!IsA(lfirst(idx), A_Indices))
break;
- ai = lfirst_node(A_Indices, idx);
+ ai = castNode(A_Indices, i);
- if (isSlice)
- {
- Node *expr = ai->uidx ? ai->uidx : ai->lidx;
+ if (ai->is_slice)
+ break;
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript does not support slices"),
- parser_errposition(pstate, exprLocation(expr))));
- }
+ Assert(!ai->lidx && ai->uidx);
- if (ai->uidx)
- {
- subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
- subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
- }
- else
- {
- /*
- * Slice with omitted upper bound. Should not happen as we already
- * errored out on slice earlier, but handle this just in case.
- */
- Assert(isSlice && ai->is_slice);
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript does not support slices"),
- parser_errposition(pstate, exprLocation(ai->uidx))));
- }
+ subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
+ subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
upperIndexpr = lappend(upperIndexpr, subExpr);
}
@@ -161,10 +404,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refupperindexpr = upperIndexpr;
sbsref->reflowerindexpr = NIL;
- /* Determine the result type of the subscripting operation; always jsonb */
- sbsref->refrestype = JSONBOID;
- sbsref->reftypmod = -1;
-
/* Remove processed elements */
if (upperIndexpr)
*indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
@@ -219,7 +458,7 @@ jsonb_subscript_check_subscripts(ExprState *state,
* For jsonb fetch and assign functions we need to provide path in
* text format. Convert if it's not already text.
*/
- if (workspace->indexOid[i] == INT4OID)
+ if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID)
{
Datum datum = sbsrefstate->upperindex[i];
char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
@@ -247,17 +486,32 @@ jsonb_subscript_fetch(ExprState *state,
{
SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
- Jsonb *jsonbSource;
/* Should not get here if source jsonb (or any subscript) is null */
Assert(!(*op->resnull));
- jsonbSource = DatumGetJsonbP(*op->resvalue);
- *op->resvalue = jsonb_get_element(jsonbSource,
- workspace->index,
- sbsrefstate->numupper,
- op->resnull,
- false);
+ if (workspace->jsonpath)
+ {
+ bool empty = false;
+ bool error = false;
+
+ *op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath,
+ JSW_CONDITIONAL,
+ &empty, &error, NULL,
+ NULL);
+
+ *op->resnull = empty || error;
+ }
+ else
+ {
+ Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+ *op->resvalue = jsonb_get_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ op->resnull,
+ false);
+ }
}
/*
@@ -365,7 +619,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
{
JsonbSubWorkspace *workspace;
ListCell *lc;
- int nupper = sbsref->refupperindexpr->length;
+ int nupper = list_length(sbsref->refupperindexpr);
char *ptr;
/* Allocate type-specific workspace with space for per-subscript data */
@@ -374,6 +628,9 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
workspace->expectArray = false;
ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+ if (sbsref->refjsonbpath)
+ workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refjsonbpath)->constvalue);
+
/*
* This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
* misalign the indexOid pointer
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3d6e6bdbfd2..baa3ae97d57 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9329,10 +9329,13 @@ get_rule_expr(Node *node, deparse_context *context,
* Parenthesize the argument unless it's a simple Var or a
* FieldSelect. (In particular, if it's another
* SubscriptingRef, we *must* parenthesize to avoid
- * confusion.)
+ * confusion.) Always add parenthesis if JSON simplified
+ * accessor is used, for now.
*/
- need_parens = !IsA(sbsref->refexpr, Var) &&
- !IsA(sbsref->refexpr, FieldSelect);
+ need_parens = (!IsA(sbsref->refexpr, Var) &&
+ !IsA(sbsref->refexpr, FieldSelect)) ||
+ sbsref->refjsonbpath;
+
if (need_parens)
appendStringInfoChar(buf, '(');
get_rule_expr((Node *) sbsref->refexpr, context, showimplicit);
@@ -13005,17 +13008,35 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
lowlist_item = list_head(sbsref->reflowerindexpr); /* could be NULL */
foreach(uplist_item, sbsref->refupperindexpr)
{
- appendStringInfoChar(buf, '[');
- if (lowlist_item)
+ Node *upper = (Node *) lfirst(uplist_item);
+
+ if (upper && IsA(upper, FieldAccessorExpr))
{
+ FieldAccessorExpr *fae = (FieldAccessorExpr *) upper;
+
+ /* Use dot-notation for field access */
+ appendStringInfoChar(buf, '.');
+ appendStringInfoString(buf, quote_identifier(fae->fieldname));
+
+ /* Skip matching low index — field access doesn't use slices */
+ if (lowlist_item)
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ }
+ else
+ {
+ /* Use JSONB array subscripting */
+ appendStringInfoChar(buf, '[');
+ if (lowlist_item)
+ {
+ /* If subexpression is NULL, get_rule_expr prints nothing */
+ get_rule_expr((Node *) lfirst(lowlist_item), context, false);
+ appendStringInfoChar(buf, ':');
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ }
/* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(lowlist_item), context, false);
- appendStringInfoChar(buf, ':');
- lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ get_rule_expr(upper, context, false);
+ appendStringInfoChar(buf, ']');
}
- /* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(uplist_item), context, false);
- appendStringInfoChar(buf, ']');
}
}
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6dfca3cb35b..b208d7949b1 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -708,18 +708,30 @@ typedef struct SubscriptingRef
int32 reftypmod pg_node_attr(query_jumble_ignore);
/* collation of result, or InvalidOid if none */
Oid refcollid pg_node_attr(query_jumble_ignore);
- /* expressions that evaluate to upper container indexes */
+
+ /*
+ * expressions that evaluate to upper container indexes or expressions
+ * that are collected but not evaluated when refjsonbpath is set.
+ */
List *refupperindexpr;
/*
- * expressions that evaluate to lower container indexes, or NIL for single
- * container element.
+ * expressions that evaluate to lower container indexes, or NIL for a
+ * single container element, or expressions that are collected but not
+ * evaluated when refjsonbpath is set.
*/
List *reflowerindexpr;
/* the expression that evaluates to a container value */
Expr *refexpr;
/* expression for the source value, or NULL if fetch */
Expr *refassgnexpr;
+
+ /*
+ * container-specific extra information, currently used only by jsonb.
+ * stores a JsonPath expression when jsonb dot notation is used. NULL for
+ * simple subscripting.
+ */
+ Node *refjsonbpath;
} SubscriptingRef;
/*
@@ -2371,4 +2383,41 @@ typedef struct OnConflictExpr
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*
+ * FieldAccessorExpr - represents a single object member access using dot-notation
+ * in JSON simplified accessor syntax (e.g., jsonb_col.a).
+ *
+ * These nodes appear as list elements in SubscriptingRef.refupperindexpr to
+ * indicate JSON object key access. They are not evaluable expressions by
+ * themselves but serve as placeholders to preserve source-level syntax for
+ * rule rewriting and deparsing (e.g., in EXPLAIN and view definitions).
+ * Execution is handled by the enclosing SubscriptingRef.
+ *
+ * If dot-notation is used in a SubscriptingRef, the JSON path is represented
+ * as a flat list of FieldAccessorExpr nodes (for object field access), Const
+ * nodes (for array indexes), and NULLs (for omitted slice bounds), rather than
+ * through nested expression trees.
+ *
+ * Note: The flat representation avoids nested FieldAccessorExpr chains,
+ * simplifying evaluation and enabling standard-compliant behavior such as
+ * conditional array wrapping. This avoids the need for position-aware
+ * wrapping/unwrapping logic during execution.
+ *
+ * For example, in the expression:
+ * ('{"a": [{"b": 1}]}'::jsonb).a[0].b
+ * the SubscriptingRef will contain:
+ * - refexpr: the base expression (the jsonb value)
+ * - refupperindexpr: [FieldAccessorExpr("a"), Const(0),
+ * FieldAccessorExpr("b")]
+ * - reflowerindexpr: [NULL, NULL, NULL] (slice lower bounds not used here)
+ */
+typedef struct FieldAccessorExpr
+{
+ Expr xpr;
+ char *fieldname; /* name of the JSONB object field accessed via
+ * dot notation */
+ Oid faecollid pg_node_attr(query_jumble_ignore);
+ int location;
+} FieldAccessorExpr;
+
#endif /* PRIMNODES_H */
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index 39221f9ea5d..e6a7ece6dab 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -417,12 +417,122 @@ if (sqlca.sqlcode < 0) sqlprint();}
for (int i = 0; i < sizeof(is_json); i++)
printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
- { ECPGdisconnect(__LINE__, "CURRENT");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . \"a\" )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 118 "sqljson.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 118 "sqljson.pgc"
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb . \"a\" )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 121 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 121 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 124 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 124 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a . b )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 127 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 127 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( coalesce ( json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . c ) , 'null' ) )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 130 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 130 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 0 ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 133 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 133 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b . x )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 136 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 136 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 139 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 139 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 142 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 142 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . * )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 145 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 145 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 148 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 148 "sqljson.pgc"
+
+ // error
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 151 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 151 "sqljson.pgc"
+
return 0;
}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
index e55a95dd711..19f8c58af06 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -268,5 +268,105 @@ SQL error: cannot use type jsonb in RETURNING clause of JSON_SERIALIZE() on line
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 118: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 118: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: query: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 121: bad response - ERROR: schema "jsonb" does not exist
+LINE 1: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 3F000 (sqlcode -400): schema "jsonb" does not exist on line 121
+[NO_PID]: sqlca: code: -400, state: 3F000
+SQL error: schema "jsonb" does not exist on line 121
+[NO_PID]: ecpg_execute on line 124: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 124: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 124: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 124: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a . b ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 127: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 127: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: query: select json ( coalesce ( json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . c ) , 'null' ) ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 130: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 130: RESULT: null offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 133: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 133: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b . x ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 136: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 136: RESULT: [1, [12, {"y": 1}]] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 139: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 139: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 142: bad response - ERROR: jsonb subscript does not support slices
+LINE 1: ..., {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 142
+[NO_PID]: sqlca: code: -400, state: 42804
+SQL error: jsonb subscript does not support slices on line 142
+[NO_PID]: ecpg_execute on line 145: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . * ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 145: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 145: bad response - ERROR: row expansion via "*" is not supported here
+LINE 1: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x...
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 0A000 (sqlcode -400): row expansion via "*" is not supported here on line 145
+[NO_PID]: sqlca: code: -400, state: 0A000
+SQL error: row expansion via "*" is not supported here on line 145
+[NO_PID]: ecpg_execute on line 148: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 148: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 148: bad response - ERROR: jsonb subscript does not support slices
+LINE 1: ...x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 148
+[NO_PID]: sqlca: code: -400, state: 42804
+SQL error: jsonb subscript does not support slices on line 148
[NO_PID]: ecpg_finish: connection ecpg1_regression closed
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
index 83f8df13e5a..442d36931f1 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -28,3 +28,10 @@ Found is_json[4]: false
Found is_json[5]: false
Found is_json[6]: true
Found is_json[7]: false
+Found json={"b": 1, "c": 2}
+Found json={"b": 1, "c": 2}
+Found json=1
+Found json=null
+Found json={"x": 1}
+Found json=[1, [12, {"y": 1}]]
+Found json={"x": 1}
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
index ddcbcc3b3cb..57a9bff424d 100644
--- a/src/interfaces/ecpg/test/sql/sqljson.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -115,6 +115,39 @@ EXEC SQL END DECLARE SECTION;
for (int i = 0; i < sizeof(is_json); i++)
printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb)."a") INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON('{"a": {"b": 1, "c": 2}}'::jsonb."a") INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a.b) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(COALESCE(JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).c), 'null')) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[0]) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b.x) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0]) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[1].x[0:]) INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*) INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0:]) INTO :json;
+ // error
+
EXEC SQL DISCONNECT;
return 0;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 5a1eb18aba2..30a7023513d 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4989,6 +4989,12 @@ select ('123'::jsonb)['a'];
(1 row)
+select ('123'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('123'::jsonb)[0];
jsonb
-------
@@ -5001,12 +5007,24 @@ select ('123'::jsonb)[NULL];
(1 row)
+select ('123'::jsonb).NULL;
+ null
+------
+
+(1 row)
+
select ('{"a": 1}'::jsonb)['a'];
jsonb
-------
1
(1 row)
+select ('{"a": 1}'::jsonb).a;
+ a
+---
+ 1
+(1 row)
+
select ('{"a": 1}'::jsonb)[0];
jsonb
-------
@@ -5019,6 +5037,12 @@ select ('{"a": 1}'::jsonb)['not_exist'];
(1 row)
+select ('{"a": 1}'::jsonb)."not_exist";
+ not_exist
+-----------
+
+(1 row)
+
select ('{"a": 1}'::jsonb)[NULL];
jsonb
-------
@@ -5031,6 +5055,12 @@ select ('[1, "2", null]'::jsonb)['a'];
(1 row)
+select ('[1, "2", null]'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[0];
jsonb
-------
@@ -5043,6 +5073,12 @@ select ('[1, "2", null]'::jsonb)['1'];
"2"
(1 row)
+select ('[1, "2", null]'::jsonb)."1";
+ 1
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1.0];
ERROR: subscript type numeric is not supported
LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
@@ -5072,6 +5108,12 @@ select ('[1, "2", null]'::jsonb)[1]['a'];
(1 row)
+select ('[1, "2", null]'::jsonb)[1].a;
+ a
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1][0];
jsonb
-------
@@ -5084,56 +5126,135 @@ select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
"c"
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
+ b
+-----
+ "c"
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
jsonb
-----------
[1, 2, 3]
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d;
+ d
+-----------
+ [1, 2, 3]
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
jsonb
-------
2
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1];
+ d
+---
+ 2
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
jsonb
-------
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ d
+---
+
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
+ a
+---
+
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d['a'][0];
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: ..."a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d['a'][0];
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ d
+---
+ 1
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d.a[0];
+ a
+---
+ 1
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
jsonb
---------------
{"a2": "aaa"}
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1;
+ a1
+---------------
+ {"a2": "aaa"}
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
jsonb
-------
"aaa"
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2;
+ a2
+-------
+ "aaa"
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
jsonb
-------
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
+ a3
+----
+
+(1 row)
+
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
jsonb
-----------------------
["aaa", "bbb", "ccc"]
(1 row)
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1;
+ b1
+-----------------------
+ ["aaa", "bbb", "ccc"]
+(1 row)
+
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
jsonb
-------
"ccc"
(1 row)
--- slices are not supported
-select ('{"a": 1}'::jsonb)['a':'b'];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
+ b1
+-------
+ "ccc"
+(1 row)
+
+select ('{"a": 1}'::jsonb)['a':'b']; -- fails
ERROR: jsonb subscript does not support slices
LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
^
@@ -5831,3 +5952,347 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+ERROR: syntax error at or near "'a'"
+LINE 1: SELECT (jb).'a' FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).b FROM test_jsonb_dot_notation;
+ b
+---------------------------------------------------
+ [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]
+(1 row)
+
+SELECT (jb).c FROM test_jsonb_dot_notation;
+ c
+---
+
+(1 row)
+
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+ b
+-----
+ "c"
+(1 row)
+
+SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
+ y
+-------
+ "yyy"
+(1 row)
+
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+ z
+-------
+ "ZZZ"
+(1 row)
+
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+ c
+---
+
+(1 row)
+
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t; -- fails
+ERROR: jsonb subscript does not support slices
+SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
+ERROR: type jsonb is not composite
+-- explains should work
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation t
+ Output: (jb).a
+(2 rows)
+
+SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb).a[1]
+(2 rows)
+
+SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+ a
+---
+ 2
+(1 row)
+
+-- views should work
+CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (jb).a[3].x.y AS y
+ FROM test_jsonb_dot_notation
+-- mixed syntax
+DROP VIEW test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb['a'::text]).b
+(2 rows)
+
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation; -- returns an array due to lax mode
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (jb['a'::text]).b AS b
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a['b'] FROM test_js...
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb).a)['b'::text]
+(2 rows)
+
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation; -- returns NULL due to strict mode with warnings
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ a
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 2: SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb).a)['b'::text] AS a
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ a
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b.x['z'] FROM test_...
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb).b.x)['z'::text]
+(2 rows)
+
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation; -- warnings
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ x
+-------
+ "ZZZ"
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 2: SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb).b.x)['z'::text] AS x
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ x
+-------
+ "ZZZ"
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b'].x['z'] FROM te...
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb['b'::text]).x)['z'::text]
+(2 rows)
+
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation; -- warnings
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ x
+-------
+ "ZZZ"
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 2: SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb['b'::text]).x)['z'::text] AS x
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ x
+-------
+ "ZZZ"
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x'].z FROM test_...
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (((jb).b)['x'::text]).z
+(2 rows)
+
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation; -- returns NULL with warnings
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ z
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 2: SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (((jb).b)['x'::text]).z AS z
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ z
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x']['z'] FROM te...
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb).b)['x'::text]['z'::text]
+(2 rows)
+
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation; -- returns NULL with warnings
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 1: SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+ b
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+WARNING: mixed usage of jsonb simplified accessor syntax and jsonb subscripting.
+LINE 2: SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+ ^
+HINT: use dot-notation for member access, or use non-null integer constants subscripting for array access.
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb).b)['x'::text]['z'::text] AS b
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ b
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb['b'::text]['x'::text]).z
+(2 rows)
+
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation; -- returns NULL with warnings
+ z
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (jb['b'::text]['x'::text]).z AS z
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ z
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+-- clean up
+DROP TABLE test_jsonb_dot_notation;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 57c11acddfe..a0fad90a9d5 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1304,33 +1304,51 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
-- jsonb subscript
select ('123'::jsonb)['a'];
+select ('123'::jsonb).a;
select ('123'::jsonb)[0];
select ('123'::jsonb)[NULL];
+select ('123'::jsonb).NULL;
select ('{"a": 1}'::jsonb)['a'];
+select ('{"a": 1}'::jsonb).a;
select ('{"a": 1}'::jsonb)[0];
select ('{"a": 1}'::jsonb)['not_exist'];
+select ('{"a": 1}'::jsonb)."not_exist";
select ('{"a": 1}'::jsonb)[NULL];
select ('[1, "2", null]'::jsonb)['a'];
+select ('[1, "2", null]'::jsonb).a;
select ('[1, "2", null]'::jsonb)[0];
select ('[1, "2", null]'::jsonb)['1'];
+select ('[1, "2", null]'::jsonb)."1";
select ('[1, "2", null]'::jsonb)[1.0];
select ('[1, "2", null]'::jsonb)[2];
select ('[1, "2", null]'::jsonb)[3];
select ('[1, "2", null]'::jsonb)[-2];
select ('[1, "2", null]'::jsonb)[1]['a'];
+select ('[1, "2", null]'::jsonb)[1].a;
select ('[1, "2", null]'::jsonb)[1][0];
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d;
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1];
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d['a'][0];
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d.a[0];
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1;
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2;
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1;
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
--- slices are not supported
-select ('{"a": 1}'::jsonb)['a':'b'];
+select ('{"a": 1}'::jsonb)['a':'b']; -- fails
select ('[1, "2", null]'::jsonb)[1:2];
select ('[1, "2", null]'::jsonb)[:2];
select ('[1, "2", null]'::jsonb)[1:];
@@ -1590,3 +1608,92 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+SELECT (jb).b FROM test_jsonb_dot_notation;
+SELECT (jb).c FROM test_jsonb_dot_notation;
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t; -- fails
+SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
+
+-- explains should work
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+
+-- views should work
+CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+
+-- mixed syntax
+DROP VIEW test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation; -- returns an array due to lax mode
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation; -- returns NULL due to strict mode with warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation; -- warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation; -- warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation; -- returns NULL with warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation; -- returns NULL with warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation; -- returns NULL with warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+-- clean up
+DROP TABLE test_jsonb_dot_notation;
\ No newline at end of file
--
2.39.5 (Apple Git-154)
[application/octet-stream] v12-0003-Export-jsonPathFromParseResult.patch (2.7K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/8-v12-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From ba112213dfab562ea790c825c857fe0df3241927 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 3/7] Export jsonPathFromParseResult()
This is a preparation step for a future commit that will reuse the
aforementioned function.
Authored-by: Nikita Glukhov <[email protected]>
Reviewed-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
---
src/backend/utils/adt/jsonpath.c | 19 +++++++++++++++----
src/include/utils/jsonpath.h | 4 ++++
2 files changed, 19 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 762f7e8a09d..976aee84cf2 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -166,15 +166,13 @@ jsonpath_send(PG_FUNCTION_ARGS)
* Converts C-string to a jsonpath value.
*
* Uses jsonpath parser to turn string into an AST, then
- * flattenJsonPathParseItem() does second pass turning AST into binary
+ * jsonPathFromParseResult() does second pass turning AST into binary
* representation of jsonpath.
*/
static Datum
jsonPathFromCstring(char *in, int len, struct Node *escontext)
{
JsonPathParseResult *jsonpath = parsejsonpath(in, len, escontext);
- JsonPath *res;
- StringInfoData buf;
if (SOFT_ERROR_OCCURRED(escontext))
return (Datum) 0;
@@ -185,8 +183,21 @@ jsonPathFromCstring(char *in, int len, struct Node *escontext)
errmsg("invalid input syntax for type %s: \"%s\"", "jsonpath",
in)));
+ return jsonPathFromParseResult(jsonpath, 4 * len, escontext);
+}
+
+/*
+ * Converts jsonpath Abstract Syntax Tree (AST) into jsonpath value in binary.
+ */
+Datum
+jsonPathFromParseResult(JsonPathParseResult *jsonpath, int estimated_len,
+ struct Node *escontext)
+{
+ JsonPath *res;
+ StringInfoData buf;
+
initStringInfo(&buf);
- enlargeStringInfo(&buf, 4 * len /* estimation */ );
+ enlargeStringInfo(&buf, estimated_len);
appendStringInfoSpaces(&buf, JSONPATH_HDRSZ);
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 23a76d233e9..e05941623e7 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,6 +281,10 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
extern bool jspConvertRegexFlags(uint32 xflags, int *result,
struct Node *escontext);
+extern Datum jsonPathFromParseResult(JsonPathParseResult *jsonpath,
+ int estimated_len,
+ struct Node *escontext);
+
/*
* Struct for details about external variables passed into jsonpath executor
*/
--
2.39.5 (Apple Git-154)
[application/octet-stream] v12-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch (10.6K, ../CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com/9-v12-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch)
download | inline diff:
From 21464ae4f20536366ffc06edadae5e76407a4f11 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v12 2/7] Allow Generic Type Subscripting to Accept Dot
Notation (.) as Input
This change extends generic type subscripting to recognize dot
notation (.) in addition to bracket notation ([]). While this does not
yet provide full support for dot notation, it enables subscripting
containers to process it in the future.
For now, container-specific transform functions only handle
subscripting indices and stop processing when encountering dot
notation. It is up to individual containers to decide how to transform
dot notation in subsequent updates.
Authored-by: Nikita Glukhov <[email protected]>
Reviewed-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
---
src/backend/parser/parse_expr.c | 66 ++++++++++++++++++++-----------
src/backend/parser/parse_node.c | 41 +++++++++++++++++--
src/backend/parser/parse_target.c | 3 +-
src/backend/utils/adt/arraysubs.c | 13 ++++--
src/backend/utils/adt/jsonbsubs.c | 11 +++++-
src/include/parser/parse_node.h | 3 +-
6 files changed, 105 insertions(+), 32 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index e1565e11d09..07d46747811 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -442,8 +442,9 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
ListCell *i;
/*
- * We have to split any field-selection operations apart from
- * subscripting. Adjacent A_Indices nodes have to be treated as a single
+ * Combine field names and subscripts into a single indirection list, as
+ * some subscripting containers, such as jsonb, support field access using
+ * dot notation. Adjacent A_Indices nodes have to be treated as a single
* multidimensional subscript operation.
*/
foreach(i, ind->indirection)
@@ -461,19 +462,43 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
}
else
{
- Node *newresult;
-
Assert(IsA(n, String));
+ subscripts = lappend(subscripts, n);
+ }
+ }
+
+ while (subscripts)
+ {
+ /* try processing container subscripts first */
+ Node *newresult = (Node *)
+ transformContainerSubscripts(pstate,
+ result,
+ exprType(result),
+ exprTypmod(result),
+ &subscripts,
+ false,
+ true);
+
+ if (!newresult)
+ {
+ /*
+ * generic subscripting failed; falling back to function call or
+ * field selection for a composite type.
+ */
+ Node *n;
+
+ Assert(subscripts);
- /* process subscripts before this field selection */
- while (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- &subscripts,
- false);
+ n = linitial(subscripts);
+
+ if (!IsA(n, String))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot subscript type %s because it does not support subscripting",
+ format_type_be(exprType(result))),
+ parser_errposition(pstate, exprLocation(result))));
+ /* try to find function for field selection */
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
list_make1(result),
@@ -481,19 +506,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
NULL,
false,
location);
- if (newresult == NULL)
+
+ if (!newresult)
unknown_attribute(pstate, result, strVal(n), location);
- result = newresult;
+
+ /* consume field select */
+ subscripts = list_delete_first(subscripts);
}
+
+ result = newresult;
}
- /* process trailing subscripts, if any */
- while (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- &subscripts,
- false);
return result;
}
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index 19a6b678e67..b3e476eb181 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -238,6 +238,8 @@ transformContainerType(Oid *containerType, int32 *containerTypmod)
* containerTypMod typmod for the container
* indirection Untransformed list of subscripts (must not be NIL)
* isAssignment True if this will become a container assignment.
+ * noError True for return NULL with no error, if the container type
+ * is not subscriptable.
*/
SubscriptingRef *
transformContainerSubscripts(ParseState *pstate,
@@ -245,13 +247,15 @@ transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List **indirection,
- bool isAssignment)
+ bool isAssignment,
+ bool noError)
{
SubscriptingRef *sbsref;
const SubscriptRoutines *sbsroutines;
Oid elementType;
bool isSlice = false;
ListCell *idx;
+ int indirection_length = list_length(*indirection);
/*
* Determine the actual container type, smashing any domain. In the
@@ -267,11 +271,16 @@ transformContainerSubscripts(ParseState *pstate,
*/
sbsroutines = getSubscriptingRoutines(containerType, &elementType);
if (!sbsroutines)
+ {
+ if (noError)
+ return NULL;
+
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot subscript type %s because it does not support subscripting",
format_type_be(containerType)),
parser_errposition(pstate, exprLocation(containerBase))));
+ }
/*
* Detect whether any of the indirection items are slice specifiers.
@@ -282,9 +291,9 @@ transformContainerSubscripts(ParseState *pstate,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ Node *ai = lfirst(idx);
- if (ai->is_slice)
+ if (IsA(ai, A_Indices) && castNode(A_Indices, ai)->is_slice)
{
isSlice = true;
break;
@@ -312,6 +321,32 @@ transformContainerSubscripts(ParseState *pstate,
sbsroutines->transform(sbsref, indirection, pstate,
isSlice, isAssignment);
+ /*
+ * Error out, if datatype failed to consume any indirection elements.
+ */
+ if (list_length(*indirection) == indirection_length)
+ {
+ Node *ind = linitial(*indirection);
+
+ if (noError)
+ return NULL;
+
+ if (IsA(ind, String))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("type %s does not support dot notation",
+ format_type_be(containerType)),
+ parser_errposition(pstate, exprLocation(containerBase))));
+ else if (IsA(ind, A_Indices))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("type %s does not support array subscripting",
+ format_type_be(containerType)),
+ parser_errposition(pstate, exprLocation(containerBase))));
+ else
+ elog(ERROR, "invalid indirection operation: %d", nodeTag(ind));
+ }
+
/*
* Verify we got a valid type (this defends, for example, against someone
* using array_subscript_handler as typsubscript without setting typelem).
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4675a523045..3ef5897f2eb 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -937,7 +937,8 @@ transformAssignmentSubscripts(ParseState *pstate,
containerType,
containerTypMod,
&subscripts,
- true);
+ true,
+ false);
typeNeeded = sbsref->refrestype;
typmodNeeded = sbsref->reftypmod;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 234c2c278c1..d03d3519dfd 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -62,6 +62,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
List *lowerIndexpr = NIL;
ListCell *idx;
+ int ndim;
/*
* Transform the subscript expressions, and separate upper and lower
@@ -73,9 +74,14 @@ array_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subexpr;
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
if (isSlice)
{
if (ai->lidx)
@@ -145,14 +151,15 @@ array_subscript_transform(SubscriptingRef *sbsref,
sbsref->reflowerindexpr = lowerIndexpr;
/* Verify subscript list lengths are within implementation limit */
- if (list_length(upperIndexpr) > MAXDIM)
+ ndim = list_length(upperIndexpr);
+ if (ndim > MAXDIM)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
list_length(upperIndexpr), MAXDIM)));
/* We need not check lowerIndexpr separately */
- *indirection = NIL;
+ *indirection = list_delete_first_n(*indirection, ndim);
/*
* Determine the result type of the subscripting operation. It's the same
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 8ad6aa1ad4f..a0d38a0fd80 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -55,9 +55,14 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subExpr;
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
if (isSlice)
{
Node *expr = ai->uidx ? ai->uidx : ai->lidx;
@@ -160,7 +165,9 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
- *indirection = NIL;
+ /* Remove processed elements */
+ if (upperIndexpr)
+ *indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
}
/*
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 58a4b9df157..5cc3ce58c30 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -362,7 +362,8 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List **indirection,
- bool isAssignment);
+ bool isAssignment,
+ bool noError);
extern Const *make_const(ParseState *pstate, A_Const *aconst);
#endif /* PARSE_NODE_H */
--
2.39.5 (Apple Git-154)
view thread (68+ 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: SQL:2023 JSON simplified accessor support
In-Reply-To: <CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox