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: Mon, 25 Aug 2025 20:52:28 -0700
Message-ID: <CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxG34m9BGnfD9RD5OEohkV3Oh-+7Xf=3epXyHfQj4DPiOw@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>
<CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com>
<CACJufxGW0Uq2Xx2NLexYR410pvLx2+QKBvBjgM+5qHsqvT5BFQ@mail.gmail.com>
<CAK98qZ3CvSfKS5yV3FAtOpWhbWHkB5aFWrngV_wYnwUbmHF4SQ@mail.gmail.com>
<CACJufxG34m9BGnfD9RD5OEohkV3Oh-+7Xf=3epXyHfQj4DPiOw@mail.gmail.com>
Hi Jian,
I’ve attached v14, which includes only indentation and comment changes
from v13.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> On Sat, Aug 23, 2025 at 3:34 AM Alexandra Wang
> <[email protected]> wrote:
> >
> > I don’t understand the question. In the case of an unsupported Node
> > type (not an Indices in patch 0001 or 0002), we break out of the loop
> > to stop transforming the remaining subscripts. So there won’t be any
> > ‘not contiguous’ indirection elements.
> >
>
> Sorry, my last message is wrong.
> this time, I applied v13-0001 to v13-0005.
> and I found some minor issues.....
>
No problem. Thanks for reviewing.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> v13-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch
> + /*
> + * 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));
> + }
>
> these error message still not reached after I apply
> v13-0005-Implement-read-only-dot-notation-for-jsonb.patch
> maybe we can simply do
>
> + if (noError)
> + return NULL;
> +
> + ereport(ERROR,
> + errcode(ERRCODE_DATATYPE_MISMATCH),
> + errmsg("type %s does not support subscripting",
> + format_type_be(containerType)),
> + parser_errposition(pstate, exprLocation(containerBase)));
> ?
>
The regression tests do not currently trigger these ERROR cases
because Assignment for dot-notation has not yet been implemented. At
present, the noError argument of transformContainerSubscripts() is set
to true for SELECT and false for UPDATE. As a result, the
ereport(ERROR) calls are never reached in SELECT queries. This patch
series also does not aim to implement dot-notation for UPDATE
statements that assign to a jsonb field. We have therefore made only
minimal adjustments in transformAssignmentIndirection(). Since the
assignment code path is separate, it likewise does not reach the
ereport(ERROR) cases with in-core data types. Once dot-notation
support for Assignment is added, those ERROR messages will become
relevant.
That said, the ERROR messages are not "dead code." They can still be
triggered when working with custom data types. For example, you can
define a custom array-like type by copying the in-core array type and
slightly modifying its *_subscript_transform() function. For now, you
can simply update array_subscript_transform() in-place with the
following diff:
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -82,6 +82,8 @@ array_subscript_transform(SubscriptingRef *sbsref,
ai = lfirst_node(A_Indices, idx);
+ if (isSlice)
+ break;
if (isSlice)
{
if (ai->lidx)
This way, we have a *_subscript_transform function that you can
register for a custom array-like data type that does not handle
slicing. Then you can hit the error message like this:
test=# update t set arr[1:] = 1;
ERROR: 42804: type integer[] does not support array subscripting
LINE 1: update t set arr[1:] = 1;
^
LOCATION: transformContainerSubscripts, parse_node.c:345
test=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
arr | integer[] | | |
I know this error message is not very precise for the demo case in
particular (since one might expect something about slicing), but it
still demonstrates how this code path is relevant. In the meantime, I
don’t think it’s worth adding a new data type to exercise this error
message, given that we are likely to make changes to the assignment
code path for in-core types.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> for V13-0004.
> in master we have
> if (subExpr == NULL)
> ereport(ERROR,
> (errcode(ERRCODE_DATATYPE_MISMATCH),
> errmsg("jsonb subscript must have text type"),
> parser_errposition(pstate,
> exprLocation(subExpr))));
> but this part is removed from v13-0004-Extract-coerce_jsonpath_subscript
> ?
I removed this one because you asked me to (see our past conversation
for v11).
On Wed, Jul 9, 2025 at 1:01 AM Alexandra Wang <[email protected]>
wrote:
> On Tue, Jun 24, 2025 at 10:57 PM jian he <[email protected]>
> wrote:
>
>> v11-0004-Extract-coerce_jsonpath_subscript.patch
>> + /*
>> + * We known from can_coerce_type that coercion will succeed, so
>> + * coerce_type could be used. Note the implicit coercion context, which
>> is
>> + * required to handle subscripts of different types, similar to
>> overloaded
>> + * functions.
>> + */
>> + subExpr = coerce_type(pstate,
>> + subExpr, subExprType,
>> + targetType, -1,
>> + COERCION_IMPLICIT,
>> + COERCE_IMPLICIT_CAST,
>> + -1);
>> + if (subExpr == NULL)
>> + ereport(ERROR,
>> + (errcode(ERRCODE_DATATYPE_MISMATCH),
>> + errmsg("jsonb subscript must have text type"),
>>
>> the targetType can be "integer", then the error message
>> errmsg("jsonb subscript must have text type") would be wrong?
>> Also this error handling is not necessary.
>> since we can_coerce_type already tell us that coercion will succeed.
>> Also, do we really put v11-0004 as a separate patch?
>>
>
> Thanks for pointing this out! I’ve removed the unnecessary error
> handling as you suggested. I kept v12-0004 as a separate patch to make
> the review easier, but I’ll leave it up to the committer to decide
> whether to squash it with the other commits.
>
I think either way works, but I’d appreciate it if we could settle on
one option so we don’t keep going back and forth.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> coerce_jsonpath_subscript_to_int4_or_text
> maybe we can just rename to
> coerce_jsonpath_subscript,
> then add some comments explaining why currently only support result node
> coerce
> to text or int4 data type.
>
What is the reason for renaming this function to a less explicit name?
It was previously called coerce_jsonpath_subscript() because we passed
INT4OID as an argument to specify the target type. I followed your
earlier review comment (for v12) to remove that argument and hard-code
INT4OID within the function. Given that change, I think it’s clearer
to keep a more explicit name that describes what the function
achieves.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> for v13-0005-Implement-read-only-dot-notation-for-jsonb.patch
>
> + 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++;
> }
> curly braces should be put into the next new line.
>
Fixed. Thank you!
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> there are two
> + if (!sbsref->refjsonbpath)
> +{
> +}
> maybe we can put these two together.
>
The code blocks are different enough, and merging them would only
reduce readability in my opinion.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> +SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation; -- returns NULL
> with warnings
> + z
> +---
> +
> +(1 row)
> there is no warning, "returns NULL with warnings" should be removed.
>
Fixed. Thank you!
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> +-- clean up
> +DROP TABLE test_jsonb_dot_notation;
> \ No newline at end of file
>
> to remove "\ No newline at end of file"
> you need to add a newline to jsonb.sql, you may see other regress sql
> test files.
>
Fixed. Thank you!
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]> wrote:
> + foreach(lc, *indirection)
> + if()
> + {
> + }
> + 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 the above else branch is reached, it will crash due to `Assert(false);`,
> which contradicts the preceding comments.
> to make the comments accurate, we need remove
> " Assert(false); /* not reachable */"
> ?
>
The Assert is intentional. We want to fail fast for the “not
reachable” case in the development build, while breaking out of the
loop in the production build.
On Mon, Aug 25, 2025 at 1:10 AM jian he <[email protected]>
wrote:
> /*
> - * 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.
> */
> the above comments, "non-integer" part is wrong?
> For example, the below two SELECTs both use "traditional" jsonb
> subscripting logic.
> CREATE TABLE ts1 AS SELECT '[1, [2]]' ::jsonb jb;
> SELECT (jb)[1] FROM ts1;
> SELECT (jb)['a'] FROM ts1;
>
I updated the comment. In the updated comment, I also chose to use
“pre-standard” json subscripting instead of “traditional” json
subscripting. Hope that helps!
Best,
Alex
Attachments:
[application/octet-stream] v14-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch (10.5K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/3-v14-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch)
download | inline diff:
From 823d053382966d1bf2335c4d950cf8d58694e4bc Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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)
[application/octet-stream] v14-0003-Export-jsonPathFromParseResult.patch (2.7K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/4-v14-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From 8555a4774e6bcfd0dc2cb3af6898519c02931f61 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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] v14-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch (9.0K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/5-v14-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch)
download | inline diff:
From 74824f1cc949041c0b5e0a35a668ab73ce08a23d Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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] v14-0005-Implement-read-only-dot-notation-for-jsonb.patch (62.2K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/6-v14-0005-Implement-read-only-dot-notation-for-jsonb.patch)
download | inline diff:
From d94b63a492134c8bc53938eaa2225eade6f5155a Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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 | 354 ++++++++++++++--
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, 1209 insertions(+), 99 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..eac31b097e4 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -3320,50 +3320,54 @@ 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);
+ 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]);
+ /* 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..ddf45dd73b2 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,45 @@ 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 reach here only in two cases: (a) the JSON simplified accessor is
+ * not needed at all (for example, a plain array subscript like [1]), or
+ * (b) jsonb_subscript_make_jsonpath() examined the first indirection
+ * element but could not turn it into a JsonPath component (for example,
+ * ['a']).
+ *
+ * In both cases we fall back to pre-standard jsonb subscripting, coercing
+ * each subscript to array index or object key as needed.
*/
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 +417,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 +471,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 +499,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 +632,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 +641,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..b054b8b5dd0 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
+ 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
+ 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
+ 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
+ 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..4db16c47563 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
+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
+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
+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
+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;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a13e8162890..cc64642f399 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] v14-0007-Implement-jsonb-wildcard-member-accessor.patch (31.4K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/7-v14-0007-Implement-jsonb-wildcard-member-accessor.patch)
download | inline diff:
From 1f6f6a435c9b3dbd45a628bcc4791b7bd83ac848 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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 87e3029513a..cb72d12ca3f 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 9176927afc1..1ffa984feb4 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 bf9faf52cab..346576a085b 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;
--
2.39.5 (Apple Git-154)
[application/octet-stream] v14-0006-Implement-Jsonb-subscripting-with-slicing.patch (17.4K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/8-v14-0006-Implement-Jsonb-subscripting-with-slicing.patch)
download | inline diff:
From 77a7f2437faca688a140a2f4662dff16ccfe9e44 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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 ddf45dd73b2..87e3029513a 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 b054b8b5dd0..9176927afc1 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 4db16c47563..bf9faf52cab 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;
--
2.39.5 (Apple Git-154)
[application/octet-stream] v14-0004-Extract-coerce_jsonpath_subscript.patch (5.8K, ../CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com/9-v14-0004-Extract-coerce_jsonpath_subscript.patch)
download | inline diff:
From 9cc44094d896768c6fce0a250efca674f2324291 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v14 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)
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: <CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@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