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, 20 Aug 2025 21:53:53 -0700
Message-ID: <CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com> (raw)
In-Reply-To: <CACJufxHqiKbh1RN4-rquYdnS8qK9kEQq=bpt6ED_yo1+OkU8jg@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>
<CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com>
<CACJufxHqiKbh1RN4-rquYdnS8qK9kEQq=bpt6ED_yo1+OkU8jg@mail.gmail.com>
Hi Jian,
Thanks for reviewing! I’ve attached v13, which addresses your
feedback.
See my individual replies below, and let me know if you have any
questions!
On Thu, Jul 10, 2025 at 1:54 AM jian he <[email protected]> wrote:
> in v12-0001 and v12-0002.
> in transformIndirection
> if (!newresult)
> {
> /*
> * generic subscripting failed; falling back to function call
> or
> * field selection for a composite type.
> */
> Node *n;
> /* try to find function for field selection */
> newresult = ParseFuncOrColumn(pstate,
> list_make1(n),
> list_make1(result),
> last_srf,
> NULL,
> false,
> location);
> }
> the above comments mentioning "function call" is wrong?
> you passed NULL for (FuncCall *fn) in ParseFuncOrColumn.
> and ParseFuncOrColumn comments says
> ```If fn is null, we're dealing with column syntax not function syntax.``
>
You are right. Fixed.
On Thu, Jul 10, 2025 at 1:54 AM jian he <[email protected]> wrote:
> I think coerce_jsonpath_subscript can be further simplified.
> we already have message like:
> errhint("jsonb subscript must be coercible to either integer or text."),
> no need to pass the third argument a constant (INT4OID).
> also
> ``Oid targetType = UNKNOWNOID;``
> set it as InvalidOid would be better.
> attached is a minor refactoring of coerce_jsonpath_subscript
> based on (v12-0001 to v12-0004).
>
Thanks for the patch! I squashed it with v13-0004 and renamed the
function to coerce_jsonpath_subscript_to_int4_or_text() for clarity.
On Thu, Jul 10, 2025 at 1:54 AM jian he <[email protected]> wrote:
> after applied v12-0001 to v12-0006
> + /* 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));
>
> src7=# select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['1'::int8];
> WARNING: mixed usage of jsonb simplified accessor syntax and jsonb
> subscripting.
> LINE 1: ...t ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['1'::int8]...
> ^
> HINT: use dot-notation for member access, or use non-null integer
> constants subscripting for array access.
> ERROR: subscript type bigint is not supported
> LINE 1: ...t ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['1'::int8]...
> ^
> HINT: jsonb subscript must be coercible to either integer or text.
>
> The above example looks very bad. location printed twice, hint message
> is different.
> two messages level (ERROR, WARNING).
>
> also "or use non-null integer constants subscripting for array
> access." seems wrong?
> as you can see the below hint message saying it could be text or integer.
>
> select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['1'::int8];
> ERROR: subscript type bigint is not supported
> LINE 1: ...ect ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['1'::int8]...
> ^
> HINT: jsonb subscript must be coercible to either integer or text.
>
> also select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)[NULL::int4];
> return NULL, so "use non-null integer constants" is wrong.
>
I see your confusion about the WARNING/ERROR messages. My intent was
to encourage users to use consistent syntax flavors rather than mixing
the SQL standard JSON simplified accessor with the pre-standard
PostgreSQL jsonb subscripting. In the meantime, I want to support
mixed syntax flavors as much as possible. However, your feedback makes
it clear that users don’t need that level of details. So, in v13 I’ve
removed the WARNING message and instead added a comment explaining how
we support mixed syntaxes.
Here's the relevant diff between v12 and v13, hope it helps:
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -247,7 +247,6 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List
**indirection, Subscripti
ListCell *lc;
Datum jsp;
int pathlen = 0;
- int warning_location = -1;
sbsref->refupperindexpr = NIL;
sbsref->reflowerindexpr = NIL;
@@ -311,10 +310,27 @@ jsonb_subscript_make_jsonpath(ParseState *pstate,
List **indirection, Subscripti
if (jpi_from == NULL)
{
/*
- * postpone emitting the warning
until the end of this
- * function
+ * Break out of the loop if the
subscript is not a
+ * non-null integer constant, so
that we can fall back to
+ * jsonb subscripting logic.
+ *
+ * This is needed to handle cases
with mixed usage of SQL
+ * standard json simplified
accessor syntax and PostgreSQL
+ * jsonb subscripting syntax, e.g:
+ *
+ * select (jb).a['b'].c from
jsonb_table;
+ *
+ * where dot-notation (.a and .c)
is the SQL standard json
+ * simplified accessor syntax, and
the ['b'] subscript is
+ * the PostgreSQL jsonb
subscripting syntax, because 'b'
+ * is not a non-null constant
integer and cannot be used
+ * for json array access.
+ *
+ * In this case, we cannot create a
JsonPath item, so we
+ * break out of the loop and let
+ * jsonb_subscript_transform()
handle this indirection as
+ * a PostgreSQL jsonb subscript.
*/
- warning_location =
exprLocation(ai->uidx);
pfree(jpi->value.array.elems);
pfree(jpi);
break;
@@ -360,14 +376,6 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List
**indirection, Subscripti
*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);
On Thu, Jul 10, 2025 at 6:34 AM jian he <[email protected]> wrote:
> 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;
>
> first field as NodeTag should be just fine?
> I am not sure the field "location" is needed now, if it is needed, it
> should be
> type as ParseLoc.
> we should add it to src/tools/pgindent/typedefs.list
Good catch! I changed the first field from Expr to NodeTag, removed
the location field, and added the Node to typedefs.list.
On Thu, Jul 10, 2025 at 10:54 PM jian he <[email protected]>
wrote:
> On Thu, Jul 10, 2025 at 9:34 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_Indices))
> > 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;
> > }
> > the above ELSE branch comments look suspicious to me.
>
I kept the "else" branch, but added an Assert with updated comments.
Here's the relevant diff between v12 and v13:
In jsonb_subscript_make_jsonpath():
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.
+ * Unexpected node type in indirection list. This should not
+ * happen with current grammar, but we handle it defensively by
+ * breaking out of the loop rather than crashing. In case of
+ * future grammar changes that might introduce new node types,
+ * this allows us to create a jsonpath from as many indirection
+ * elements as we can and let transformIndirection() fallback to
+ * alternative logic to handle the remaining indirection elements.
+ Assert(false); /* not reachable */
break;
}
Hope the updated comment clarifies why I kept it.
On Thu, Jul 10, 2025 at 10:54 PM jian he <[email protected]>
wrote:
> >
> transformIndirection->transformContainerSubscripts->jsonb_subscript_transform->jsonb_subscript_make_jsonpath
> > As you can see, transformIndirection have a long distance from
> > jsonb_subscript_make_jsonpath,
> > let transformIndirection handle remaining indirection elements seems not
> good.
>
In 0001's commit message, we are given the following promise:
Author: Nikita Glukhov <[email protected]>
Date: Tue Jul 8 22:18:07 2025 -0700
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.
With this contract provided by the subscripting interface to the
container's transform function, I don't feel terribly inappropriate to
let jsonb's transform function: jsonb_subscript_transform(), to
transform indirections as much as it can, and leave the rest to
transformIndirection().
0002 is a good example that shows why the else branch is useful: it
adds support for String as a subscript node of jsonb, while String is
still unsupported in other in-core container subscripting.
For example, array_subscript_transform() has:
if (!IsA(lfirst(idx), A_Indices))
break;
The else branch in jsonb_subscript_make_jsonpath() just follows that
same convention.
> context v12-0001 to v12-0006.
> this ELSE branch comments is wrong, because
> + if (jsonb_check_jsonpath_needed(*indirection))
> + {
> + jsonb_subscript_make_jsonpath(pstate, indirection, sbsref);
> + if (sbsref->refjsonbpath)
> + return;
> + }
> in jsonb_check_jsonpath_needed we already use Assert to confirm that
> list "indirection"
> is either String or A_Indices Node.
>
The Assert I added in v13, as well as the one you mentioned in
jsonb_check_jsonpath_needed(), are both only applicable in development
build and won’t help in production. So I’d prefer to break here and
let the callee handle any unexpected Node with an ERROR.
Let me know if this explanation makes sense. If not, and if you think
the current logic is overly complicated, we can discuss further.
On Thu, Jul 10, 2025 at 10:54 PM jian he <[email protected]>
wrote:
> in transformContainerSubscripts we have
> 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));
> }
> sbsroutines->transform currently will call
> array_subscript_transform, hstore_subscript_transform,
> jsonb_subscript_transform
>
> in jsonb_subscript_transform callee we unconditionally do:
> *indirection = list_delete_first_n(*indirection, pathlen);
> *indirection = list_delete_first_n(*indirection,
> list_length(upperIndexpr));
> in array_subscript_transform, we do
> *indirection = list_delete_first_n(*indirection, ndim);
>
> That means, if sbsroutines->transform not error out and indirection is
> not NIL (which is unlikely)
> then sbsroutines->transform will consume some induction elements.
> instead of the above verbose ereport(ERROR, error handling, we can use
> Assert
> Assert(indirection_length > list_length(*indirection));
>
> for the above comments, i did a refactoring based on v12 (0001 to 0006).
Thanks for the patch! I didn’t apply it for the same reason I
mentioned in the previous quote reply. In the case of an unexpected
Node type, I prefer raising an explicit ERROR rather than using an
Assert() that crashes in a dev build but silently continues in
production. One possible way to hit these ERRORs is by creating a
custom data type that supports subscripting, but only for a subset of
Node types that transformIndirection() allows.
Best,
Alex
Attachments:
[application/octet-stream] v13-0004-Extract-coerce_jsonpath_subscript.patch (5.8K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/3-v13-0004-Extract-coerce_jsonpath_subscript.patch)
download | inline diff:
From 3486f5619488ad1ced9acf93e73371bc722d4579 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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..f944d1544ca 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_to_int4_or_text(ParseState *pstate, Node *subExpr)
+{
+ Oid subExprType = exprType(subExpr);
+ Oid targetType = InvalidOid;
+
+ 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 (OidIsValid(targetType))
+ 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 (!OidIsValid(targetType))
+ 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_to_int4_or_text(pstate, subExpr);
}
else
{
--
2.39.5 (Apple Git-154)
[application/octet-stream] v13-0007-Implement-jsonb-wildcard-member-accessor.patch (31.4K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/4-v13-0007-Implement-jsonb-wildcard-member-accessor.patch)
download | inline diff:
From 9594fc742a468b9d6d3f88491f55f82b3c8307d5 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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 db43034b9db..703c7416b0d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -18979,6 +18979,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)
@@ -18989,6 +18990,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 dc1c7e9b75c..7d5c56caa70 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 458ca69d645..0b00530d868 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
{
@@ -340,6 +340,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 7e89621bd65..7e418830f22 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2419,4 +2419,16 @@ typedef struct FieldAccessorExpr
Oid faecollid pg_node_attr(query_jumble_ignore);
} 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 f4a20694102..b99e9cf519f 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -6052,8 +6052,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
@@ -6081,6 +6248,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
@@ -6109,6 +6315,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;
@@ -6344,4 +6561,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] v13-0006-Implement-Jsonb-subscripting-with-slicing.patch (17.4K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/5-v13-0006-Implement-Jsonb-subscripting-with-slicing.patch)
download | inline diff:
From 75471b513e83e62c2804881a5d820fcb82f5eaf9 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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 5808383db26..458ca69d645 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)));
}
/*
@@ -277,19 +285,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 d781f1518eb..f4a20694102 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5247,23 +5247,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
@@ -6005,8 +6016,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
@@ -6042,6 +6087,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;
@@ -6226,5 +6293,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] v13-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch (9.0K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/6-v13-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch)
download | inline diff:
From 140fd8bb3d38e685e52a6c2fa697b3a9103061ff Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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 203b7a32178..f05baa50a15 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] v13-0005-Implement-read-only-dot-notation-for-jsonb.patch (62.3K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/7-v13-0005-Implement-read-only-dot-notation-for-jsonb.patch)
download | inline diff:
From 7e9278c86d62e747bca862b6bc8a11e31fcd36ab Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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 | 352 +++++++++++++--
src/backend/utils/adt/ruleutils.c | 43 +-
src/include/nodes/primnodes.h | 54 ++-
.../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 | 401 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 111 ++++-
src/tools/pgindent/typedefs.list | 1 +
13 files changed, 1206 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 f944d1544ca..5808383db26 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,255 @@ coerce_jsonpath_subscript_to_int4_or_text(ParseState *pstate, Node *subExpr)
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;
+
+ 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->type = T_FieldAccessorExpr;
+ accessor_expr->fieldname = field;
+
+ 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)
+ {
+ /*
+ * Break out of the loop if the subscript is not a
+ * non-null integer constant, so that we can fall back to
+ * jsonb subscripting logic.
+ *
+ * This is needed to handle cases with mixed usage of SQL
+ * standard json simplified accessor syntax and PostgreSQL
+ * jsonb subscripting syntax, e.g:
+ *
+ * select (jb).a['b'].c from jsonb_table;
+ *
+ * where dot-notation (.a and .c) is the SQL standard json
+ * simplified accessor syntax, and the ['b'] subscript is
+ * the PostgreSQL jsonb subscripting syntax, because 'b'
+ * is not a non-null constant integer and cannot be used
+ * for json array access.
+ *
+ * In this case, we cannot create a JsonPath item, so we
+ * break out of the loop and let
+ * jsonb_subscript_transform() handle this indirection as
+ * a PostgreSQL jsonb subscript.
+ */
+ pfree(jpi->value.array.elems);
+ pfree(jpi);
+ break;
+ }
+
+ jpi->value.array.elems[0].from = jpi_from;
+ jpi->value.array.elems[0].to = NULL;
+ }
+ }
+ else
+ {
+ /*
+ * Unexpected node type in indirection list. This should not
+ * happen with current grammar, but we handle it defensively by
+ * breaking out of the loop rather than crashing. In case of
+ * future grammar changes that might introduce new node types,
+ * this allows us to create a jsonpath from as many indirection
+ * elements as we can and let transformIndirection() fallback to
+ * alternative logic to handle the remaining indirection elements.
+ */
+ Assert(false); /* not reachable */
+ break;
+ }
+
+ /* append path item */
+ path->next = jpi;
+ path = jpi;
+ pathlen++;
+ }
+
+ if (pathlen == 0)
+ return;
+
+ *indirection = list_delete_first_n(*indirection, pathlen);
+
+ 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 +370,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 (isSlice || 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_to_int4_or_text(pstate, subExpr);
- }
- 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_to_int4_or_text(pstate, subExpr);
upperIndexpr = lappend(upperIndexpr, subExpr);
}
@@ -161,10 +415,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 +469,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 +497,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 +630,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 +639,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..7e89621bd65 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,40 @@ 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
+{
+ NodeTag type;
+ char *fieldname; /* name of the JSONB object field accessed via
+ * dot notation */
+ Oid faecollid pg_node_attr(query_jumble_ignore);
+} 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..d781f1518eb 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,127 @@ 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'];
+ 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];
+ 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 +5944,287 @@ 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;
+ 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
+ a
+---
+
+(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)['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;
+ 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
+ x
+-------
+ "ZZZ"
+(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.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;
+ 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
+ x
+-------
+ "ZZZ"
+(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)['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;
+ 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
+ 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)['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;
+ 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
+ b
+---
+
+(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)['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
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e4a9ec65ab4..6fca8eb7b23 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -805,6 +805,7 @@ FdwRoutine
FetchDirection
FetchDirectionKeywords
FetchStmt
+FieldAccessorExpr
FieldSelect
FieldStore
File
--
2.39.5 (Apple Git-154)
[application/octet-stream] v13-0003-Export-jsonPathFromParseResult.patch (2.7K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/8-v13-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From 8766d64a43f0d84c0395781edcc210753a908556 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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] v13-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch (10.5K, ../CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com/9-v13-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch)
download | inline diff:
From 711a28579fe187177cbf47a1f70edfc06484cd45 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v13 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..dc1c7e9b75c 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 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 f05baa50a15..c44a5a0effd 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: <CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@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