public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandra Wang <[email protected]>
To: Chao Li <[email protected]>
Cc: jian he <[email protected]>
Cc: Nikita Malakhov <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Mark Dilger <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David E. Wheeler <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Wed, 10 Sep 2025 09:40:33 -0700
Message-ID: <CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAK98qZ1XFee_x1uQFcwJkZCSVcPBy-A1ib3N5Ls2uDdZviKVOw@mail.gmail.com>
<[email protected]>
<CAK98qZ1xhjKfo_C-s_bnpkPJ4xkdqEwdRYq4pbFKM8fKqcfKZw@mail.gmail.com>
<[email protected]>
Hi Chao,
On Tue, Sep 9, 2025 at 8:52 PM Chao Li <[email protected]> wrote:
>
> <v16-0007-Implement-jsonb-wildcard-member-accessor.patch>
> <v16-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch>
> <v16-0004-Extract-coerce_jsonpath_subscript.patch>
> <v16-0006-Implement-Jsonb-subscripting-with-slicing.patch>
> <v16-0005-Implement-read-only-dot-notation-for-jsonb.patch>
> <v16-0003-Export-jsonPathFromParseResult.patch>
> <v16-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch>
>
>
> A few more comment for v16.
>
> 1 - 0002
> ```
> --- a/contrib/hstore/hstore_subs.c
> +++ b/contrib/hstore/hstore_subs.c
>
> - if (isSlice || list_length(*indirection) != 1)
> + if (ai->is_slice || list_length(*indirection) != 1)
> ```
>
> We should put list_length() check before ai->is_slace. Because when
> indirection length is greater than 1, it take a single check; other it may
> need to check the both conditions.
>
> 2 - 0002 - also in hstore_subs.c
> ```
> *indirection = NIL;
> ```
>
> We should do “list_delete_first_n(indirection, 1)”.
>
> 3 - 0003
> ```
> +Datum
> +jsonPathFromParseResult(JsonPathParseResult *jsonpath, int estimated_len,
> + struct Node *escontext)
> +{
> ```
>
> `jsonpath` is not changed in this function, so it should be defined as a
> const: “const JsonPathParseResult *jsonpath”.
>
> 4 - 0004
> ```
> + 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++)
> ```
>
> This is a nit optimization. We can do:
>
> Oid targets[] = {INT4OID, TEXTOID};
> For (int I = 0; I < sizeof(targets) / sizeof(targets[0]); I ++)
>
> This way lets the compiler to decide length of “targets”. So that avoids
> hard code “2” in “for”. If we need to add more elements to “targets”, we
> can just add it without updating “for”.
>
Thanks for the feedback! Here’s v17. I’ve addressed 1 - 3 exactly as
you suggested. For 4, I made the targets array const and used the
lengthof macro to determine the array length.
Best,
Alex
Attachments:
[application/octet-stream] v17-0007-Implement-jsonb-wildcard-member-accessor.patch (31.5K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/3-v17-0007-Implement-jsonb-wildcard-member-accessor.patch)
download | inline diff:
From ef0292c845114bf30139f8c138f3f83ebe2d765b Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v17 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 | 39 +--
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, 406 insertions(+), 55 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 9fd48acb1f8..c86ab6fd512 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -18980,6 +18980,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)
@@ -18990,6 +18991,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 f8a0617f823..38ac6503a22 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -73,7 +73,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,
@@ -157,7 +156,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:
@@ -431,8 +430,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);
@@ -450,16 +450,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
{
Node *n = lfirst(i);
- if (IsA(n, A_Indices) || IsA(n, String))
- subscripts = lappend(subscripts, n);
- else
- {
- Assert(IsA(n, A_Star));
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row expansion via \"*\" is not supported here"),
- parser_errposition(pstate, location)));
- }
+ Assert (IsA(n, A_Indices) || IsA(n, String) || IsA(n, A_Star));
+ subscripts = lappend(subscripts, n);
}
while (subscripts)
@@ -486,7 +478,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",
@@ -512,6 +518,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 b89736ff1ea..85c05c7434c 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -47,7 +47,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,
@@ -133,6 +133,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
@@ -161,13 +162,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;
}
}
}
@@ -179,7 +186,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
p_target = lappend(p_target,
transformTargetEntry(pstate,
res->val,
- NULL,
+ transformed,
exprKind,
res->name,
false));
@@ -250,10 +257,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;
}
}
@@ -1344,22 +1356,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);
}
/*
@@ -1784,13 +1804,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 4762f6bd11c..1dcde658739 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
{
@@ -339,6 +339,16 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, Subscripti
}
}
}
+ 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 2702edc9705..86b7f7677fc 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -6064,8 +6064,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
@@ -6093,6 +6260,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
@@ -6121,6 +6327,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;
@@ -6356,4 +6573,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 de2c9cda094..07facc280d7 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1631,13 +1631,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;
@@ -1648,6 +1684,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;
@@ -1749,4 +1788,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] v17-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch (9.0K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/4-v17-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch)
download | inline diff:
From 15f69ce64cc58b1b96836aaa5e37515e470bf9da Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v17 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]>
Reviewed-by: Chao Li <[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 e1979a80c19..6e8fd42c612 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -465,14 +465,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),
@@ -487,12 +486,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 905c975d83b..a097736229a 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -935,7 +935,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 e8626d3b4fc..5ead693a3b2 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 at 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..df988a85fad 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.
+ * It may parse a sublist (typically the prefix) of these indirections and
+ * modify the original list in place, allowing the caller to handle any
+ * remaining indirections differently or to raise an error as needed.
+ *
* 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] v17-0006-Implement-Jsonb-subscripting-with-slicing.patch (21.5K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/5-v17-0006-Implement-Jsonb-subscripting-with-slicing.patch)
download | inline diff:
From 2d32ec307203bdc07013c57d9f0cd89adcbd4b35 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v17 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]>
Reviewed-by: Chao Li <[email protected]>
Tested-by: Mark Dilger <[email protected]>
Tested-by: Jian He <[email protected]>
---
src/backend/utils/adt/jsonbsubs.c | 119 ++++++++------
.../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, 286 insertions(+), 83 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index ccc5f3c6e94..4762f6bd11c 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -111,7 +111,7 @@ coerce_jsonpath_subscript_to_int4_or_text(ParseState *pstate, Node *subExpr)
*
* JsonPath is needed if the indirection list includes:
* - String-based access (dot notation)
- * - Slice-based subscripting (when isSlice is true)
+ * - Slice-based subscripting
*
* Otherwise, simple jsonb subscripting is enough.
*/
@@ -127,7 +127,11 @@ jsonb_check_jsonpath_needed(List *indirection)
if (IsA(accessor, String))
return true;
else
+ {
Assert(IsA(accessor, A_Indices));
+ if (castNode(A_Indices, accessor)->is_slice)
+ return true;
+ }
}
return false;
@@ -152,20 +156,45 @@ make_jsonpath_item(JsonPathItemType type)
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.
- * 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.
+ * 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)
+ * - 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;
@@ -175,20 +204,17 @@ make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
{
cnst = (Const *) expr;
if (cnst->consttype == INT4OID && !(cnst->constisnull))
- {
- JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
-
- jpi->value.numeric =
- DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(cnst->constvalue)));
-
- *exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
- Int32GetDatum(cnst->constvalue), false, true));
-
- return jpi;
- }
+ 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)));
}
/*
@@ -251,13 +277,12 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, Subscripti
else if (IsA(accessor, A_Indices))
{
A_Indices *ai = castNode(A_Indices, accessor);
+ JsonPathParseItem *jpi_from = NULL;
if (!ai->is_slice)
{
- 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)
{
/*
@@ -284,22 +309,34 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, Subscripti
*/
break;
}
+ }
- jpi = make_jsonpath_item(jpiIndexArray);
- jpi->value.array.nelems = 1;
- jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+ 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)
+ {
jpi->value.array.elems[0].from = jpi_from;
jpi->value.array.elems[0].to = NULL;
}
else
{
- 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
@@ -381,32 +418,12 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
ai = lfirst_node(A_Indices, idx);
if (ai->is_slice)
- {
- Node *expr = ai->uidx ? ai->uidx : ai->lidx;
+ 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(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);
}
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 37d71b23d5d..2702edc9705 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
@@ -6017,8 +6028,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
@@ -6054,6 +6099,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;
@@ -6238,5 +6305,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 5fc53e1c9aa..de2c9cda094 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1625,7 +1625,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
@@ -1637,6 +1642,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;
@@ -1697,5 +1708,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] v17-0004-Extract-coerce_jsonpath_subscript.patch (5.5K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/6-v17-0004-Extract-coerce_jsonpath_subscript.patch)
download | inline diff:
From 16585b8f90f657aecc7be0d5c8856d45c9318c7a Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v17 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 | 128 +++++++++++++++---------------
1 file changed, 64 insertions(+), 64 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 2aa410f605b..8852c27a198 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)
+ {
+ const Oid targets[] = {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 < lengthof(targets); 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.
@@ -74,71 +137,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
if (ai->uidx)
{
- Oid subExprType = InvalidOid,
- targetType = UNKNOWNOID;
-
subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
- subExprType = exprType(subExpr);
-
- if (subExprType != UNKNOWNOID)
- {
- Oid targets[2] = {INT4OID, TEXTOID};
-
- /*
- * Jsonb can handle multiple subscript types, but cases when a
- * subscript could be coerced to multiple target types must be
- * avoided, similar to overloaded functions. It could be
- * possibly extend with jsonpath in the future.
- */
- for (int i = 0; i < 2; i++)
- {
- if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
- {
- /*
- * One type has already succeeded, it means there are
- * two coercion targets possible, failure.
- */
- if (targetType != UNKNOWNOID)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("subscript type %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to only one type, integer or text."),
- parser_errposition(pstate, exprLocation(subExpr))));
-
- targetType = targets[i];
- }
- }
-
- /*
- * No suitable types were found, failure.
- */
- if (targetType == UNKNOWNOID)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("subscript type %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to either integer or text."),
- parser_errposition(pstate, exprLocation(subExpr))));
- }
- else
- targetType = TEXTOID;
-
- /*
- * We known from can_coerce_type that coercion will succeed, so
- * coerce_type could be used. Note the implicit coercion context,
- * which is required to handle subscripts of different types,
- * similar to overloaded functions.
- */
- subExpr = coerce_type(pstate,
- subExpr, subExprType,
- targetType, -1,
- COERCION_IMPLICIT,
- COERCE_IMPLICIT_CAST,
- -1);
- if (subExpr == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript must have text type"),
- parser_errposition(pstate, exprLocation(subExpr))));
+ subExpr = coerce_jsonpath_subscript_to_int4_or_text(pstate, subExpr);
}
else
{
--
2.39.5 (Apple Git-154)
[application/octet-stream] v17-0005-Implement-read-only-dot-notation-for-jsonb.patch (61.2K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/7-v17-0005-Implement-read-only-dot-notation-for-jsonb.patch)
download | inline diff:
From c25a81c6e8a45d7e4c6f803561e66ba9af7e964b Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v17 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:
This patch extends the existing container subscripting interface to
support container-specific information, namely a JSONPath expression
for jsonb.
During query transformation, if dot-notation is present, a JSONPath
expression is constructed to represent the access chain.
Then during execution, if a JSONPath expression is present in
JsonbSubWorkspace, executes it via JsonPathQuery().
Note that we cannot simply rewrite the accessors into JSON_QUERY()
during transformation, because the original query structure must be
preserved 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]>
Reviewed-by: Chao Li <[email protected]>
Tested-by: Jelte Fennema-Nio <[email protected]>
---
src/backend/catalog/sql_features.txt | 4 +-
src/backend/executor/execExpr.c | 81 ++--
src/backend/nodes/nodeFuncs.c | 12 +
src/backend/utils/adt/jsonbsubs.c | 301 ++++++++++++-
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 | 413 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 113 ++++-
src/tools/pgindent/typedefs.list | 1 +
13 files changed, 1201 insertions(+), 73 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..385c8d0cefe 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -3320,50 +3320,59 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
state->steps_len - 1);
}
- /* Evaluate upper subscripts */
- i = 0;
- foreach(lc, sbsref->refupperindexpr)
+ /* Evaluate upper subscripts, unless refjsonbpath is used for execution */
+ if (!sbsref->refjsonbpath)
{
- Expr *e = (Expr *) lfirst(lc);
-
- /* When slicing, individual subscript bounds can be omitted */
- if (!e)
- {
- sbsrefstate->upperprovided[i] = false;
- sbsrefstate->upperindexnull[i] = true;
- }
- else
+ i = 0;
+ foreach(lc, sbsref->refupperindexpr)
{
- sbsrefstate->upperprovided[i] = true;
- /* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->upperindex[i],
- &sbsrefstate->upperindexnull[i]);
+ Expr *e = (Expr *) lfirst(lc);
+
+ /* When slicing, individual subscript bounds can be omitted */
+ if (!e)
+ {
+ sbsrefstate->upperprovided[i] = false;
+ sbsrefstate->upperindexnull[i] = true;
+ }
+ else
+ {
+ sbsrefstate->upperprovided[i] = true;
+ /* Each subscript is evaluated into appropriate array entry */
+ ExecInitExprRec(e, state,
+ &sbsrefstate->upperindex[i],
+ &sbsrefstate->upperindexnull[i]);
+ }
+ i++;
}
- i++;
}
- /* Evaluate lower subscripts similarly */
- i = 0;
- foreach(lc, sbsref->reflowerindexpr)
+ /*
+ * Evaluate lower subscripts similarly, unless refjsonbpath is used for
+ * execution
+ */
+ if (!sbsref->refjsonbpath)
{
- Expr *e = (Expr *) lfirst(lc);
-
- /* When slicing, individual subscript bounds can be omitted */
- if (!e)
- {
- sbsrefstate->lowerprovided[i] = false;
- sbsrefstate->lowerindexnull[i] = true;
- }
- else
+ i = 0;
+ foreach(lc, sbsref->reflowerindexpr)
{
- 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 8852c27a198..ccc5f3c6e94 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,234 @@ 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 (when isSlice is true)
+ *
+ * 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));
+ }
+
+ 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;
+}
+
+/*
+ * Convert a constant integer expression into a JsonPathParseItem.
+ *
+ * The input expression must be a non-null constant of type INT4. Returns NULL otherwise.
+ * 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:
+ * - 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))
+ {
+ JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
+
+ jpi->value.numeric =
+ DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(cnst->constvalue)));
+
+ *exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
+ Int32GetDatum(cnst->constvalue), false, true));
+
+ return jpi;
+ }
+ }
+
+ 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 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);
+
+ if (!ai->is_slice)
+ {
+ 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.
+ */
+ break;
+ }
+
+ jpi = make_jsonpath_item(jpiIndexArray);
+ jpi->value.array.nelems = 1;
+ jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+ jpi->value.array.elems[0].from = jpi_from;
+ jpi->value.array.elems[0].to = NULL;
+ }
+ else
+ {
+ 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
+ {
+ /*
+ * 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.
*
@@ -111,9 +348,27 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
ListCell *idx;
+ /* Determine the result type of the subscripting operation; always jsonb */
+ sbsref->refrestype = JSONBOID;
+ sbsref->reftypmod = -1;
+
+ if (jsonb_check_jsonpath_needed(*indirection))
+ {
+ jsonb_subscript_make_jsonpath(pstate, indirection, sbsref);
+ if (sbsref->refjsonbpath)
+ return;
+ }
+
/*
- * Transform and convert the subscript expressions. Jsonb subscripting
- * does not support slices, look only at the upper index.
+ * We 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
+ * object key access like ['a']), or (b) jsonb_subscript_make_jsonpath()
+ * was called but could not complete the JsonPath construction (for
+ * example, when mixing dot notation with non-integer subscripts like
+ * (jb)['a'].b where 'a' is not a constant integer).
+ *
+ * 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)
{
@@ -160,10 +415,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refupperindexpr = upperIndexpr;
sbsref->reflowerindexpr = NIL;
- /* Determine the result type of the subscripting operation; always jsonb */
- sbsref->refrestype = JSONBOID;
- sbsref->reftypmod = -1;
-
/* Remove processed elements */
if (upperIndexpr)
*indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
@@ -218,7 +469,7 @@ jsonb_subscript_check_subscripts(ExprState *state,
* For jsonb fetch and assign functions we need to provide path in
* text format. Convert if it's not already text.
*/
- if (workspace->indexOid[i] == INT4OID)
+ if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID)
{
Datum datum = sbsrefstate->upperindex[i];
char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
@@ -246,17 +497,32 @@ jsonb_subscript_fetch(ExprState *state,
{
SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
- Jsonb *jsonbSource;
/* Should not get here if source jsonb (or any subscript) is null */
Assert(!(*op->resnull));
- jsonbSource = DatumGetJsonbP(*op->resvalue);
- *op->resvalue = jsonb_get_element(jsonbSource,
- workspace->index,
- sbsrefstate->numupper,
- op->resnull,
- false);
+ if (workspace->jsonpath)
+ {
+ bool empty = false;
+ bool error = false;
+
+ *op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath,
+ JSW_CONDITIONAL,
+ &empty, &error, NULL,
+ NULL);
+
+ *op->resnull = empty || error;
+ }
+ else
+ {
+ Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+ *op->resvalue = jsonb_get_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ op->resnull,
+ false);
+ }
}
/*
@@ -364,7 +630,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
{
JsonbSubWorkspace *workspace;
ListCell *lc;
- int nupper = sbsref->refupperindexpr->length;
+ int nupper = list_length(sbsref->refupperindexpr);
char *ptr;
/* Allocate type-specific workspace with space for per-subscript data */
@@ -373,6 +639,9 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
workspace->expectArray = false;
ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+ if (sbsref->refjsonbpath)
+ workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refjsonbpath)->constvalue);
+
/*
* This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
* misalign the indexOid pointer
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3d6e6bdbfd2..baa3ae97d57 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9329,10 +9329,13 @@ get_rule_expr(Node *node, deparse_context *context,
* Parenthesize the argument unless it's a simple Var or a
* FieldSelect. (In particular, if it's another
* SubscriptingRef, we *must* parenthesize to avoid
- * confusion.)
+ * confusion.) Always add parenthesis if JSON simplified
+ * accessor is used, for now.
*/
- need_parens = !IsA(sbsref->refexpr, Var) &&
- !IsA(sbsref->refexpr, FieldSelect);
+ need_parens = (!IsA(sbsref->refexpr, Var) &&
+ !IsA(sbsref->refexpr, FieldSelect)) ||
+ sbsref->refjsonbpath;
+
if (need_parens)
appendStringInfoChar(buf, '(');
get_rule_expr((Node *) sbsref->refexpr, context, showimplicit);
@@ -13005,17 +13008,35 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
lowlist_item = list_head(sbsref->reflowerindexpr); /* could be NULL */
foreach(uplist_item, sbsref->refupperindexpr)
{
- appendStringInfoChar(buf, '[');
- if (lowlist_item)
+ Node *upper = (Node *) lfirst(uplist_item);
+
+ if (upper && IsA(upper, FieldAccessorExpr))
{
+ FieldAccessorExpr *fae = (FieldAccessorExpr *) upper;
+
+ /* Use dot-notation for field access */
+ appendStringInfoChar(buf, '.');
+ appendStringInfoString(buf, quote_identifier(fae->fieldname));
+
+ /* Skip matching low index — field access doesn't use slices */
+ if (lowlist_item)
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ }
+ else
+ {
+ /* Use JSONB array subscripting */
+ appendStringInfoChar(buf, '[');
+ if (lowlist_item)
+ {
+ /* If subexpression is NULL, get_rule_expr prints nothing */
+ get_rule_expr((Node *) lfirst(lowlist_item), context, false);
+ appendStringInfoChar(buf, ':');
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ }
/* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(lowlist_item), context, false);
- appendStringInfoChar(buf, ':');
- lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ get_rule_expr(upper, context, false);
+ appendStringInfoChar(buf, ']');
}
- /* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(uplist_item), context, false);
- appendStringInfoChar(buf, ']');
}
}
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6dfca3cb35b..7e89621bd65 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -708,18 +708,30 @@ typedef struct SubscriptingRef
int32 reftypmod pg_node_attr(query_jumble_ignore);
/* collation of result, or InvalidOid if none */
Oid refcollid pg_node_attr(query_jumble_ignore);
- /* expressions that evaluate to upper container indexes */
+
+ /*
+ * expressions that evaluate to upper container indexes or expressions
+ * that are collected but not evaluated when refjsonbpath is set.
+ */
List *refupperindexpr;
/*
- * expressions that evaluate to lower container indexes, or NIL for single
- * container element.
+ * expressions that evaluate to lower container indexes, or NIL for a
+ * single container element, or expressions that are collected but not
+ * evaluated when refjsonbpath is set.
*/
List *reflowerindexpr;
/* the expression that evaluates to a container value */
Expr *refexpr;
/* expression for the source value, or NULL if fetch */
Expr *refassgnexpr;
+
+ /*
+ * container-specific extra information, currently used only by jsonb.
+ * stores a JsonPath expression when jsonb dot notation is used. NULL for
+ * simple subscripting.
+ */
+ Node *refjsonbpath;
} SubscriptingRef;
/*
@@ -2371,4 +2383,40 @@ typedef struct OnConflictExpr
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*
+ * FieldAccessorExpr - represents a single object member access using dot-notation
+ * in JSON simplified accessor syntax (e.g., jsonb_col.a).
+ *
+ * These nodes appear as list elements in SubscriptingRef.refupperindexpr to
+ * indicate JSON object key access. They are not evaluable expressions by
+ * themselves but serve as placeholders to preserve source-level syntax for
+ * rule rewriting and deparsing (e.g., in EXPLAIN and view definitions).
+ * Execution is handled by the enclosing SubscriptingRef.
+ *
+ * If dot-notation is used in a SubscriptingRef, the JSON path is represented
+ * as a flat list of FieldAccessorExpr nodes (for object field access), Const
+ * nodes (for array indexes), and NULLs (for omitted slice bounds), rather than
+ * through nested expression trees.
+ *
+ * Note: The flat representation avoids nested FieldAccessorExpr chains,
+ * simplifying evaluation and enabling standard-compliant behavior such as
+ * conditional array wrapping. This avoids the need for position-aware
+ * wrapping/unwrapping logic during execution.
+ *
+ * For example, in the expression:
+ * ('{"a": [{"b": 1}]}'::jsonb).a[0].b
+ * the SubscriptingRef will contain:
+ * - refexpr: the base expression (the jsonb value)
+ * - refupperindexpr: [FieldAccessorExpr("a"), Const(0),
+ * FieldAccessorExpr("b")]
+ * - reflowerindexpr: [NULL, NULL, NULL] (slice lower bounds not used here)
+ */
+typedef struct FieldAccessorExpr
+{
+ NodeTag type;
+ char *fieldname; /* name of the JSONB object field accessed via
+ * dot notation */
+ Oid faecollid pg_node_attr(query_jumble_ignore);
+} FieldAccessorExpr;
+
#endif /* PRIMNODES_H */
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index 39221f9ea5d..e6a7ece6dab 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -417,12 +417,122 @@ if (sqlca.sqlcode < 0) sqlprint();}
for (int i = 0; i < sizeof(is_json); i++)
printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
- { ECPGdisconnect(__LINE__, "CURRENT");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . \"a\" )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 118 "sqljson.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 118 "sqljson.pgc"
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb . \"a\" )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 121 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 121 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 124 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 124 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a . b )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 127 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 127 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( coalesce ( json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . c ) , 'null' ) )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 130 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 130 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 0 ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 133 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 133 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b . x )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 136 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 136 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 139 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 139 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 142 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 142 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . * )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 145 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 145 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 148 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 148 "sqljson.pgc"
+
+ // error
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 151 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 151 "sqljson.pgc"
+
return 0;
}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
index e55a95dd711..19f8c58af06 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -268,5 +268,105 @@ SQL error: cannot use type jsonb in RETURNING clause of JSON_SERIALIZE() on line
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 118: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 118: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: query: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 121: bad response - ERROR: schema "jsonb" does not exist
+LINE 1: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 3F000 (sqlcode -400): schema "jsonb" does not exist on line 121
+[NO_PID]: sqlca: code: -400, state: 3F000
+SQL error: schema "jsonb" does not exist on line 121
+[NO_PID]: ecpg_execute on line 124: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 124: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 124: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 124: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a . b ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 127: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 127: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: query: select json ( coalesce ( json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . c ) , 'null' ) ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 130: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 130: RESULT: null offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 133: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 133: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b . x ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 136: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 136: RESULT: [1, [12, {"y": 1}]] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 139: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 139: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 142: bad response - ERROR: jsonb subscript does not support slices
+LINE 1: ..., {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 142
+[NO_PID]: sqlca: code: -400, state: 42804
+SQL error: jsonb subscript does not support slices on line 142
+[NO_PID]: ecpg_execute on line 145: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . * ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 145: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 145: bad response - ERROR: row expansion via "*" is not supported here
+LINE 1: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x...
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 0A000 (sqlcode -400): row expansion via "*" is not supported here on line 145
+[NO_PID]: sqlca: code: -400, state: 0A000
+SQL error: row expansion via "*" is not supported here on line 145
+[NO_PID]: ecpg_execute on line 148: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 148: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 148: bad response - ERROR: jsonb subscript does not support slices
+LINE 1: ...x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 148
+[NO_PID]: sqlca: code: -400, state: 42804
+SQL error: jsonb subscript does not support slices on line 148
[NO_PID]: ecpg_finish: connection ecpg1_regression closed
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
index 83f8df13e5a..442d36931f1 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -28,3 +28,10 @@ Found is_json[4]: false
Found is_json[5]: false
Found is_json[6]: true
Found is_json[7]: false
+Found json={"b": 1, "c": 2}
+Found json={"b": 1, "c": 2}
+Found json=1
+Found json=null
+Found json={"x": 1}
+Found json=[1, [12, {"y": 1}]]
+Found json={"x": 1}
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
index ddcbcc3b3cb..57a9bff424d 100644
--- a/src/interfaces/ecpg/test/sql/sqljson.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -115,6 +115,39 @@ EXEC SQL END DECLARE SECTION;
for (int i = 0; i < sizeof(is_json); i++)
printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb)."a") INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON('{"a": {"b": 1, "c": 2}}'::jsonb."a") INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a.b) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(COALESCE(JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).c), 'null')) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[0]) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b.x) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0]) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[1].x[0:]) INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*) INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0:]) INTO :json;
+ // error
+
EXEC SQL DISCONNECT;
return 0;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 5a1eb18aba2..37d71b23d5d 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,299 @@ 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)[0].a from test_jsonb_dot_notation; -- returns same result as (jb).a
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL
+ a
+---
+
+(1 row)
+
+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 because ['b'] looks for strict match in an object
+ 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..5fc53e1c9aa 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,94 @@ 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)[0].a from test_jsonb_dot_notation; -- returns same result as (jb).a
+select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL
+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 because ['b'] looks for strict match in an object
+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] v17-0003-Export-jsonPathFromParseResult.patch (2.7K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/8-v17-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From 8692fccb6967f79736d457142f889a73dc2d05ba Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v17 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..c83774b2a16 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(const 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..0958bc22bb6 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(const 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] v17-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch (18.2K, ../CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@mail.gmail.com/9-v17-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch)
download | inline diff:
From 31386c23ea14e2b86d1c22829f3650a85ec42d33 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Fri, 5 Sep 2025 14:17:37 -0700
Subject: [PATCH v17 2/7] Allow Generic Type Subscripting to Accept Dot
Notation (.) as Input
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
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.
This change also updates the SubscriptTransform() API to remove the
"bool isSlice" argument. Each container’s transform function now
determines slice-ness itself, since it may only process a prefix of
the indirection list. For example, array_subscript_transform() stops
at dot notation, so "isSlice" should not depend on slice specifiers
that appear afterward.
Authored-by: Nikita Glukhov <[email protected]>
Authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Chao Li <[email protected]>
---
contrib/hstore/hstore_subs.c | 11 +++--
src/backend/parser/parse_expr.c | 68 ++++++++++++++++++----------
src/backend/parser/parse_node.c | 57 ++++++++++++++---------
src/backend/parser/parse_target.c | 3 +-
src/backend/utils/adt/arraysubs.c | 40 ++++++++++++++--
src/backend/utils/adt/jsonbsubs.c | 16 +++++--
src/include/nodes/subscripting.h | 3 +-
src/include/parser/parse_node.h | 3 +-
src/test/regress/expected/arrays.out | 26 +++++++++--
src/test/regress/sql/arrays.sql | 7 ++-
10 files changed, 163 insertions(+), 71 deletions(-)
diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c
index 1b29543ab67..f008e5faf03 100644
--- a/contrib/hstore/hstore_subs.c
+++ b/contrib/hstore/hstore_subs.c
@@ -42,14 +42,16 @@ static void
hstore_subscript_transform(SubscriptingRef *sbsref,
List **indirection,
ParseState *pstate,
- bool isSlice,
bool isAssignment)
{
A_Indices *ai;
Node *subexpr;
+ Assert(*indirection != NIL);
+ ai = linitial_node(A_Indices, *indirection);
+
/* We support only single-subscript, non-slice cases */
- if (isSlice || list_length(*indirection) != 1)
+ if (list_length(*indirection) != 1 || ai->is_slice)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("hstore allows only one subscript"),
@@ -57,8 +59,7 @@ hstore_subscript_transform(SubscriptingRef *sbsref,
exprLocation((Node *) *indirection))));
/* Transform the subscript expression to type text */
- ai = linitial_node(A_Indices, *indirection);
- Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice);
+ Assert(ai->uidx != NULL && ai->lidx == NULL);
subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
/* If it's not text already, try to coerce */
@@ -82,7 +83,7 @@ hstore_subscript_transform(SubscriptingRef *sbsref,
sbsref->refrestype = TEXTOID;
sbsref->reftypmod = -1;
- *indirection = NIL;
+ *indirection = list_delete_first_n(*indirection, 1);
}
/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 6e8fd42c612..f8a0617f823 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -441,38 +441,59 @@ 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)
{
Node *n = lfirst(i);
- if (IsA(n, A_Indices))
+ if (IsA(n, A_Indices) || IsA(n, String))
subscripts = lappend(subscripts, n);
- else if (IsA(n, A_Star))
+ else
{
+ Assert(IsA(n, A_Star));
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("row expansion via \"*\" is not supported here"),
parser_errposition(pstate, location)));
}
- else
+ }
+
+ while (subscripts)
+ {
+ /* try processing container subscripts first */
+ Node *newresult = (Node *)
+ transformContainerSubscripts(pstate,
+ result,
+ exprType(result),
+ exprTypmod(result),
+ &subscripts,
+ false,
+ true);
+
+ if (!newresult)
{
- Node *newresult;
+ /*
+ * generic subscripting failed; falling back to field selection
+ * for a composite type.
+ */
+ Node *n;
+
+ Assert(subscripts);
- Assert(IsA(n, String));
+ n = linitial(subscripts);
- /* process subscripts before this field selection */
- while (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- &subscripts,
- false);
+ 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),
@@ -480,19 +501,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..d7b23688b9b 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,13 @@ 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,28 +269,15 @@ 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.
- *
- * A list containing only simple subscripts refers to a single container
- * element. If any of the items are slice specifiers (lower:upper), then
- * the subscript expression means a container slice operation.
- */
- foreach(idx, *indirection)
- {
- A_Indices *ai = lfirst_node(A_Indices, idx);
-
- if (ai->is_slice)
- {
- isSlice = true;
- break;
- }
}
/*
@@ -310,7 +299,33 @@ transformContainerSubscripts(ParseState *pstate,
* determine the subscripting result type.
*/
sbsroutines->transform(sbsref, indirection, pstate,
- isSlice, isAssignment);
+ 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
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index a097736229a..b89736ff1ea 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -936,7 +936,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..378b6bf16cb 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -56,12 +56,38 @@ static void
array_subscript_transform(SubscriptingRef *sbsref,
List **indirection,
ParseState *pstate,
- bool isSlice,
bool isAssignment)
{
List *upperIndexpr = NIL;
List *lowerIndexpr = NIL;
ListCell *idx;
+ int ndim;
+ bool isSlice = false;
+
+ /*
+ * Detect whether any of the indirection items are slice specifiers.
+ *
+ * A list containing only simple subscripts refers to a single container
+ * element. If any of the items are slice specifiers (lower:upper), then
+ * the subscript expression means a container slice operation.
+ */
+ foreach(idx, *indirection)
+ {
+ Node *ai = lfirst(idx);
+
+ /*
+ * We should not inspect slice specifiers beyond an indirection node
+ * type that we don't support.
+ */
+ if (!IsA(ai, A_Indices))
+ break;
+
+ if (castNode(A_Indices, ai)->is_slice)
+ {
+ isSlice = true;
+ break;
+ }
+ }
/*
* Transform the subscript expressions, and separate upper and lower
@@ -73,9 +99,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 +176,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 5ead693a3b2..2aa410f605b 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -43,7 +43,6 @@ static void
jsonb_subscript_transform(SubscriptingRef *sbsref,
List **indirection,
ParseState *pstate,
- bool isSlice,
bool isAssignment)
{
List *upperIndexpr = NIL;
@@ -55,10 +54,15 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subExpr;
- if (isSlice)
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
+ if (ai->is_slice)
{
Node *expr = ai->uidx ? ai->uidx : ai->lidx;
@@ -142,7 +146,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
* 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);
+ Assert(ai->is_slice);
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("jsonb subscript does not support slices"),
@@ -160,7 +164,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/nodes/subscripting.h b/src/include/nodes/subscripting.h
index df988a85fad..14ef414a180 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -68,7 +68,7 @@ struct SubscriptExecSteps;
* same length as refupperindexpr for a slice operation. Insert NULLs
* (that is, an empty parse tree, not a null Const node) for any omitted
* subscripts in a slice operation. (Of course, if the transform method
- * does not care to support slicing, it can just throw an error if isSlice.)
+ * does not care to support slicing, it can just throw an error.)
* See array_subscript_transform() for sample code.
*
* The transform method receives a pointer to a list of raw indirections.
@@ -100,7 +100,6 @@ struct SubscriptExecSteps;
typedef void (*SubscriptTransform) (SubscriptingRef *sbsref,
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 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 */
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index b815473f414..78dd9e71bf3 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1782,17 +1782,17 @@ SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
(1 row)
-- A few simple tests for arrays of composite types
-create type comptype as (f1 int, f2 text);
+create type comptype as (f1 int, f2 text, f3 int[]);
create table comptable (c1 comptype, c2 comptype[]);
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
- values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
+ values (row(1,'foo',array[10,20]), array[row(2,'bar',array[30,40])::comptype, row(3,'baz',array[50,60])::comptype]);
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
- c1 | c2
----------+-----------------------
- (1,foo) | {"(2,bar)","(3,baz)"}
+ c1 | c2
+-------------------+-----------------------------------------------
+ (1,foo,"{10,20}") | {"(2,bar,\"{30,40}\")","(3,baz,\"{50,60}\")"}
(1 row)
select c2[2].f2 from comptable;
@@ -1801,6 +1801,22 @@ select c2[2].f2 from comptable;
baz
(1 row)
+select c2[2].f3 from comptable;
+ f3
+---------
+ {50,60}
+(1 row)
+
+select c2[2].f3[1:2] from comptable;
+ f3
+---------
+ {50,60}
+(1 row)
+
+select c2[1:2].f3[1:2] from comptable;
+ERROR: column notation .f3 applied to type comptype[], which is not a composite type
+LINE 1: select c2[1:2].f3[1:2] from comptable;
+ ^
drop type _comptype;
drop table comptable;
drop type comptype;
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47d62c1d38d..450389831a0 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -555,19 +555,22 @@ SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
-- A few simple tests for arrays of composite types
-create type comptype as (f1 int, f2 text);
+create type comptype as (f1 int, f2 text, f3 int[]);
create table comptable (c1 comptype, c2 comptype[]);
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
- values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
+ values (row(1,'foo',array[10,20]), array[row(2,'bar',array[30,40])::comptype, row(3,'baz',array[50,60])::comptype]);
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
select c2[2].f2 from comptable;
+select c2[2].f3 from comptable;
+select c2[2].f3[1:2] from comptable;
+select c2[1:2].f3[1:2] from comptable;
drop type _comptype;
drop table comptable;
--
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], [email protected]
Subject: Re: SQL:2023 JSON simplified accessor support
In-Reply-To: <CAK98qZ1FK1ZhH_uyqg02_n7Q5gnPAooC8zUdFAx3_XQiBdXo6Q@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