public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandra Wang <[email protected]>
To: 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]>
Cc: jian he <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Wed, 5 Feb 2025 07:39:15 -0600
Message-ID: <CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
Hi,
On Wed, Feb 5, 2025 at 1:20 AM Alexandra Wang <[email protected]>
wrote:
> I attached a minimized version of Nikita’s patch (v7):
>
> - The first three patches are refactoring steps that could be squashed
> if preferred.
> - The last two patches implement dot notation and wildcard access,
> respectively.
>
> Changes in this new version:
> - Removed code handling hstore, as Andrew pointed out it isn’t
> directly relevant to JSON access and should be handled separately.
> - Split tests for dot notation and wildcard access.
> - Dropped the two patches in v6 that enabled non-parenthesized column
> references (per Nikita’s suggestion, this will need its own separate
> discussion).
>
It appears that the Commitfest app selected the wrong patch. Sorry
about the confusion! I'm reposting the patches to correct this.
Attachments:
[application/octet-stream] v7-0002-Pass-field-accessors-to-generic-subscripting.patch (15.7K, ../CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com/3-v7-0002-Pass-field-accessors-to-generic-subscripting.patch)
download | inline diff:
From 29845033cce1de0e80b3c19d4e56b514d094e956 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 16:21:20 +0300
Subject: [PATCH v7 2/5] Pass field accessors to generic subscripting
---
src/backend/executor/execExpr.c | 24 +++++++---
src/backend/nodes/nodeFuncs.c | 73 ++++++++++++++++++++++++++----
src/backend/parser/parse_collate.c | 22 +++++++--
src/backend/parser/parse_expr.c | 58 ++++++++++++++++--------
src/backend/parser/parse_node.c | 39 ++++++++++++++--
src/backend/parser/parse_target.c | 3 +-
src/backend/utils/adt/arraysubs.c | 13 ++++--
src/backend/utils/adt/jsonbsubs.c | 11 ++++-
src/backend/utils/adt/ruleutils.c | 29 ++++++++----
src/include/parser/parse_node.h | 3 +-
10 files changed, 218 insertions(+), 57 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 03566c4d181..be4213455e5 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -3328,9 +3328,15 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
{
sbsrefstate->upperprovided[i] = true;
/* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->upperindex[i],
- &sbsrefstate->upperindexnull[i]);
+ if (IsA(e, String))
+ {
+ sbsrefstate->upperindex[i] = CStringGetTextDatum(strVal(e));
+ sbsrefstate->upperindexnull[i] = false;
+ }
+ else
+ ExecInitExprRec(e, state,
+ &sbsrefstate->upperindex[i],
+ &sbsrefstate->upperindexnull[i]);
}
i++;
}
@@ -3351,9 +3357,15 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
{
sbsrefstate->lowerprovided[i] = true;
/* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->lowerindex[i],
- &sbsrefstate->lowerindexnull[i]);
+ if (IsA(e, String))
+ {
+ sbsrefstate->lowerindex[i] = CStringGetTextDatum(strVal(e));
+ sbsrefstate->lowerindexnull[i] = false;
+ }
+ else
+ ExecInitExprRec(e, state,
+ &sbsrefstate->lowerindex[i],
+ &sbsrefstate->lowerindexnull[i]);
}
i++;
}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..a9c29ab8f29 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2182,12 +2182,28 @@ expression_tree_walker_impl(Node *node,
case T_SubscriptingRef:
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
+ ListCell *lc;
+
+ /*
+ * Recurse directly for upper/lower container index lists,
+ * skipping String subscripts used for dot notation.
+ */
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String) && WALK(expr))
+ return true;
+ }
+
+ foreach(lc, sbsref->reflowerindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String) && WALK(expr))
+ return true;
+ }
- /* recurse directly for upper/lower container index lists */
- if (LIST_WALK(sbsref->refupperindexpr))
- return true;
- if (LIST_WALK(sbsref->reflowerindexpr))
- return true;
/* walker must see the refexpr and refassgnexpr, however */
if (WALK(sbsref->refexpr))
return true;
@@ -3082,12 +3098,51 @@ expression_tree_mutator_impl(Node *node,
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
SubscriptingRef *newnode;
+ ListCell *lc;
+ List *exprs = NIL;
FLATCOPY(newnode, sbsref, SubscriptingRef);
- MUTATE(newnode->refupperindexpr, sbsref->refupperindexpr,
- List *);
- MUTATE(newnode->reflowerindexpr, sbsref->reflowerindexpr,
- List *);
+
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && IsA(expr, String))
+ {
+ String *str;
+
+ FLATCOPY(str, expr, String);
+ expr = (Node *) str;
+ }
+ else
+ expr = mutator(expr, context);
+
+ exprs = lappend(exprs, expr);
+ }
+
+ newnode->refupperindexpr = exprs;
+
+ exprs = NIL;
+
+ foreach(lc, sbsref->reflowerindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && IsA(expr, String))
+ {
+ String *str;
+
+ FLATCOPY(str, expr, String);
+ expr = (Node *) str;
+ }
+ else
+ expr = mutator(expr, context);
+
+ exprs = lappend(exprs, expr);
+ }
+
+ newnode->reflowerindexpr = exprs;
+
MUTATE(newnode->refexpr, sbsref->refexpr,
Expr *);
MUTATE(newnode->refassgnexpr, sbsref->refassgnexpr,
diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c
index d2e218353f3..be6dea6ffd2 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -680,11 +680,25 @@ assign_collations_walker(Node *node, assign_collations_context *context)
* contribute anything.)
*/
SubscriptingRef *sbsref = (SubscriptingRef *) node;
+ ListCell *lc;
+
+ /* skip String subscripts used for dot notation */
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String))
+ assign_expr_collations(context->pstate, expr);
+ }
+
+ foreach(lc, sbsref->reflowerindexpr)
+ {
+ Node *expr = lfirst(lc);
+
+ if (expr && !IsA(expr, String))
+ assign_expr_collations(context->pstate, expr);
+ }
- assign_expr_collations(context->pstate,
- (Node *) sbsref->refupperindexpr);
- assign_expr_collations(context->pstate,
- (Node *) sbsref->reflowerindexpr);
(void) assign_collations_walker((Node *) sbsref->refexpr,
&loccontext);
(void) assign_collations_walker((Node *) sbsref->refassgnexpr,
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 2c0f4a50b21..afe953fdbea 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -461,19 +461,40 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
}
else
{
- Node *newresult;
-
Assert(IsA(n, String));
+ subscripts = lappend(subscripts, n);
+ }
+ }
+
+ /* process trailing subscripts, if any */
+ while (subscripts)
+ {
+ Node *newresult = (Node *)
+ transformContainerSubscripts(pstate,
+ result,
+ exprType(result),
+ exprTypmod(result),
+ &subscripts,
+ false,
+ true);
+
+ if (!newresult)
+ {
+ /* generic subscripting failed */
+ Node *n;
+
+ Assert(subscripts);
+
+ 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),
@@ -481,19 +502,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
NULL,
false,
location);
- if (newresult == NULL)
+
+ if (!newresult)
unknown_attribute(pstate, result, strVal(n), location);
- result = newresult;
+
+ /* consume field select */
+ subscripts = list_delete_first(subscripts);
}
+
+ result = newresult;
}
- /* process trailing subscripts, if any */
- while (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- &subscripts,
- false);
return result;
}
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index 19a6b678e67..c1f8055564f 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -245,13 +245,15 @@ transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List **indirection,
- bool isAssignment)
+ bool isAssignment,
+ bool noError)
{
SubscriptingRef *sbsref;
const SubscriptRoutines *sbsroutines;
Oid elementType;
bool isSlice = false;
ListCell *idx;
+ int indirection_length = list_length(*indirection);
/*
* Determine the actual container type, smashing any domain. In the
@@ -267,11 +269,16 @@ transformContainerSubscripts(ParseState *pstate,
*/
sbsroutines = getSubscriptingRoutines(containerType, &elementType);
if (!sbsroutines)
+ {
+ if (noError)
+ return NULL;
+
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot subscript type %s because it does not support subscripting",
format_type_be(containerType)),
parser_errposition(pstate, exprLocation(containerBase))));
+ }
/*
* Detect whether any of the indirection items are slice specifiers.
@@ -282,9 +289,9 @@ transformContainerSubscripts(ParseState *pstate,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ Node *ai = lfirst(idx);
- if (ai->is_slice)
+ if (IsA(ai, A_Indices) && castNode(A_Indices, ai)->is_slice)
{
isSlice = true;
break;
@@ -312,6 +319,32 @@ transformContainerSubscripts(ParseState *pstate,
sbsroutines->transform(sbsref, indirection, pstate,
isSlice, isAssignment);
+ /*
+ * Error out, if datatyoe falied 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 column notation",
+ format_type_be(containerType)),
+ parser_errposition(pstate, exprLocation(containerBase))));
+ else if (IsA(ind, A_Indices))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("type %s does not support array subscripting",
+ format_type_be(containerType)),
+ parser_errposition(pstate, exprLocation(containerBase))));
+ else
+ elog(ERROR, "invalid indirection operation: %d", nodeTag(ind));
+ }
+
/*
* Verify we got a valid type (this defends, for example, against someone
* using array_subscript_handler as typsubscript without setting typelem).
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 39fd82f8371..5e126145ea5 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -937,7 +937,8 @@ transformAssignmentSubscripts(ParseState *pstate,
containerType,
containerTypMod,
&subscripts,
- true);
+ true,
+ false);
if (subscripts)
elog(ERROR, "subscripting assignment is not supported");
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index edd85f7ba67..fe18df86e45 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -61,6 +61,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
List *lowerIndexpr = NIL;
ListCell *idx;
+ int ndim;
/*
* Transform the subscript expressions, and separate upper and lower
@@ -72,9 +73,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)
@@ -144,14 +150,15 @@ array_subscript_transform(SubscriptingRef *sbsref,
sbsref->reflowerindexpr = lowerIndexpr;
/* Verify subscript list lengths are within implementation limit */
- if (list_length(upperIndexpr) > MAXDIM)
+ ndim = list_length(upperIndexpr);
+ if (ndim > MAXDIM)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
list_length(upperIndexpr), MAXDIM)));
/* We need not check lowerIndexpr separately */
- *indirection = NIL;
+ *indirection = list_delete_first_n(*indirection, ndim);
/*
* Determine the result type of the subscripting operation. It's the same
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 8ad6aa1ad4f..a0d38a0fd80 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -55,9 +55,14 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, *indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subExpr;
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
if (isSlice)
{
Node *expr = ai->uidx ? ai->uidx : ai->lidx;
@@ -160,7 +165,9 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
- *indirection = NIL;
+ /* Remove processed elements */
+ if (upperIndexpr)
+ *indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 54dad975553..af5417d0859 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -47,6 +47,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/pathnodes.h"
+#include "nodes/subscripting.h"
#include "optimizer/optimizer.h"
#include "parser/parse_agg.h"
#include "parser/parse_func.h"
@@ -12916,17 +12917,29 @@ 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 *up = (Node *) lfirst(uplist_item);
+
+ if (IsA(up, String))
+ {
+ appendStringInfoChar(buf, '.');
+ appendStringInfoString(buf, quote_identifier(strVal(up)));
+ }
+ else
{
+ 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, ':');
+ }
/* 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((Node *) lfirst(uplist_item), context, false);
+ appendStringInfoChar(buf, ']');
}
- /* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(uplist_item), context, false);
- appendStringInfoChar(buf, ']');
+
+ if (lowlist_item)
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
}
}
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 5ae11ccec33..71b04bd503c 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -378,7 +378,8 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List **indirection,
- bool isAssignment);
+ bool isAssignment,
+ bool noError);
extern Const *make_const(ParseState *pstate, A_Const *aconst);
#endif /* PARSE_NODE_H */
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0005-Allow-processing-of-.-by-generic-subscripting.patch (18.0K, ../CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com/4-v7-0005-Allow-processing-of-.-by-generic-subscripting.patch)
download | inline diff:
From b376b023aed5b4769cb29daa7f55e6eb22b90afd Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:26 +0300
Subject: [PATCH v7 5/5] Allow processing of .* by generic subscripting
---
src/backend/parser/gram.y | 2 +
src/backend/parser/parse_expr.c | 34 +++--
src/backend/parser/parse_target.c | 60 ++++++---
src/backend/utils/adt/ruleutils.c | 6 +-
src/include/parser/parse_expr.h | 3 +
src/test/regress/expected/jsonb.out | 195 +++++++++++++++++++---------
6 files changed, 206 insertions(+), 94 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00c409..5f69ac661bd 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -18959,6 +18959,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)
@@ -18969,6 +18970,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 afe953fdbea..b0c38529872 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -74,7 +74,6 @@ static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref);
static Node *transformWholeRowRef(ParseState *pstate,
ParseNamespaceItem *nsitem,
int sublevels_up, int location);
-static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
static Node *transformJsonObjectConstructor(ParseState *pstate,
@@ -158,7 +157,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
break;
case T_A_Indirection:
- result = transformIndirection(pstate, (A_Indirection *) expr);
+ result = transformIndirection(pstate, (A_Indirection *) expr, NULL);
break;
case T_A_ArrayExpr:
@@ -432,8 +431,9 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname,
}
}
-static Node *
-transformIndirection(ParseState *pstate, A_Indirection *ind)
+Node *
+transformIndirection(ParseState *pstate, A_Indirection *ind,
+ bool *trailing_star_expansion)
{
Node *last_srf = pstate->p_last_srf;
Node *result = transformExprRecurse(pstate, ind->arg);
@@ -453,12 +453,7 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
if (IsA(n, A_Indices))
subscripts = lappend(subscripts, n);
else if (IsA(n, A_Star))
- {
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row expansion via \"*\" is not supported here"),
- parser_errposition(pstate, location)));
- }
+ subscripts = lappend(subscripts, n);
else
{
Assert(IsA(n, String));
@@ -487,7 +482,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",
@@ -513,6 +522,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 5e126145ea5..965e2f06e7b 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -48,7 +48,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate,
static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
bool make_target_entry);
static List *ExpandAllTables(ParseState *pstate, int location);
-static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
+static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
bool make_target_entry, ParseExprKind exprKind);
static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem,
int sublevels_up, int location,
@@ -134,6 +134,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
foreach(o_target, targetlist)
{
ResTarget *res = (ResTarget *) lfirst(o_target);
+ Node *transformed = NULL;
/*
* Check for "something.*". Depending on the complexity of the
@@ -162,13 +163,19 @@ transformTargetList(ParseState *pstate, List *targetlist,
if (IsA(llast(ind->indirection), A_Star))
{
- /* It is something.*, expand into multiple items */
- p_target = list_concat(p_target,
- ExpandIndirectionStar(pstate,
- ind,
- true,
- exprKind));
- continue;
+ Node *columns = ExpandIndirectionStar(pstate,
+ ind,
+ true,
+ exprKind);
+
+ if (IsA(columns, List))
+ {
+ /* It is something.*, expand into multiple items */
+ p_target = list_concat(p_target, (List *) columns);
+ continue;
+ }
+
+ transformed = (Node *) columns;
}
}
}
@@ -180,7 +187,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
p_target = lappend(p_target,
transformTargetEntry(pstate,
res->val,
- NULL,
+ transformed,
exprKind,
res->name,
false));
@@ -251,10 +258,15 @@ transformExpressionList(ParseState *pstate, List *exprlist,
if (IsA(llast(ind->indirection), A_Star))
{
- /* It is something.*, expand into multiple items */
- result = list_concat(result,
- ExpandIndirectionStar(pstate, ind,
- false, exprKind));
+ Node *cols = ExpandIndirectionStar(pstate, ind,
+ false, exprKind);
+
+ if (!cols || IsA(cols, List))
+ /* It is something.*, expand into multiple items */
+ result = list_concat(result, (List *) cols);
+ else
+ result = lappend(result, cols);
+
continue;
}
}
@@ -1348,22 +1360,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);
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index af5417d0859..10006b749dc 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12919,7 +12919,11 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
{
Node *up = (Node *) lfirst(uplist_item);
- if (IsA(up, String))
+ if (!up)
+ {
+ appendStringInfoString(buf, ".*");
+ }
+ else if (IsA(up, String))
{
appendStringInfoChar(buf, '.');
appendStringInfoString(buf, quote_identifier(strVal(up)));
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/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 14123929475..9057186bdf3 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5891,19 +5891,39 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
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).* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[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;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[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;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[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;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[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;
-ERROR: type jsonb is not composite
+ jb
+------------------------------------------------------------------------------------------------------------------------------
+ [[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;
a
-------------------------------------------------------------------------
@@ -5935,75 +5955,120 @@ SELECT (jb).a.b FROM test_jsonb_dot_notation;
(1 row)
SELECT (jb).a.* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ a
+-------------------------------------------
+ ["c", "d", "f", {"y": "yyy", "z": "zzz"}]
+(1 row)
+
SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
- ^
+ b
+---
+
+(1 row)
+
SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
- ^
+ x
+---
+
+(1 row)
+
SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
- ^
+ y
+-------
+ "yyy"
+(1 row)
+
SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
- ^
+ a
+----------------
+ ["yyy", "zzz"]
+(1 row)
+
SELECT (jb).*.x FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: 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;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: 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;
-ERROR: row expansion via "*" is not supported here
-LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
- ^
+ x
+---
+
+(1 row)
+
SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
-ERROR: row expansion via "*" is not supported here
-LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
- ^
+ x
+---
+
+(1 row)
+
SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
-ERROR: row expansion via "*" is not supported here
-LINE 1: 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;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: 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;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
- ^
+ x
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
- ^
+ y
+----------------
+ ["yyy", "YYY"]
+(1 row)
+
SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
- ^
+ z
+----------------
+ ["zzz", "ZZZ"]
+(1 row)
+
SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
- ^
+ y
+----------------
+ ["yyy", "YYY"]
+(1 row)
+
SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
- ^
+ jb
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
- ^
+ jb
+----
+
+(1 row)
+
SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ c
+---
+
+(1 row)
+
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
-ERROR: type jsonb is not composite
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.*
+(2 rows)
+
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
QUERY PLAN
--------------------------------------------
@@ -6019,10 +6084,16 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
(2 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_...
- ^
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.a.*['b'::text]
+(2 rows)
+
EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
-ERROR: improper use of "*" at or near "FROM"
-LINE 1: ... (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_...
- ^
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: jb.a.*[1:2][:'b'::text].b
+(2 rows)
+
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch (6.3K, ../CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com/5-v7-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch)
download | inline diff:
From 5da3d365c8eb72bf467fe33c7173fedd61c9f96f Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Fri, 14 Oct 2022 15:35:22 +0300
Subject: [PATCH v7 1/5] Allow transformation only of a sublist of subscripts
---
src/backend/parser/parse_expr.c | 9 ++++-----
src/backend/parser/parse_node.c | 4 ++--
src/backend/parser/parse_target.c | 5 ++++-
src/backend/utils/adt/arraysubs.c | 6 ++++--
src/backend/utils/adt/jsonbsubs.c | 6 ++++--
src/include/nodes/subscripting.h | 2 +-
src/include/parser/parse_node.h | 2 +-
7 files changed, 20 insertions(+), 14 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index bad1df732ea..2c0f4a50b21 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -466,14 +466,13 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
Assert(IsA(n, String));
/* process subscripts before this field selection */
- if (subscripts)
+ while (subscripts)
result = (Node *) transformContainerSubscripts(pstate,
result,
exprType(result),
exprTypmod(result),
- subscripts,
+ &subscripts,
false);
- subscripts = NIL;
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
@@ -488,12 +487,12 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
}
}
/* process trailing subscripts, if any */
- if (subscripts)
+ while (subscripts)
result = (Node *) transformContainerSubscripts(pstate,
result,
exprType(result),
exprTypmod(result),
- subscripts,
+ &subscripts,
false);
return result;
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index d6feb16aef3..19a6b678e67 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -244,7 +244,7 @@ transformContainerSubscripts(ParseState *pstate,
Node *containerBase,
Oid containerType,
int32 containerTypMod,
- List *indirection,
+ List **indirection,
bool isAssignment)
{
SubscriptingRef *sbsref;
@@ -280,7 +280,7 @@ transformContainerSubscripts(ParseState *pstate,
* element. If any of the items are slice specifiers (lower:upper), then
* the subscript expression means a container slice operation.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4aba0d9d4d5..39fd82f8371 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -936,9 +936,12 @@ transformAssignmentSubscripts(ParseState *pstate,
basenode,
containerType,
containerTypMod,
- subscripts,
+ &subscripts,
true);
+ if (subscripts)
+ elog(ERROR, "subscripting assignment is not supported");
+
typeNeeded = sbsref->refrestype;
typmodNeeded = sbsref->reftypmod;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 562179b3799..edd85f7ba67 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -53,7 +53,7 @@ typedef struct ArraySubWorkspace
*/
static void
array_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -70,7 +70,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;
@@ -151,6 +151,8 @@ array_subscript_transform(SubscriptingRef *sbsref,
list_length(upperIndexpr), MAXDIM)));
/* We need not check lowerIndexpr separately */
+ *indirection = NIL;
+
/*
* Determine the result type of the subscripting operation. It's the same
* as the array type if we're slicing, else it's the element type. In
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index de64d498512..8ad6aa1ad4f 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -41,7 +41,7 @@ typedef struct JsonbSubWorkspace
*/
static void
jsonb_subscript_transform(SubscriptingRef *sbsref,
- List *indirection,
+ List **indirection,
ParseState *pstate,
bool isSlice,
bool isAssignment)
@@ -53,7 +53,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
* Transform and convert the subscript expressions. Jsonb subscripting
* does not support slices, look only and the upper index.
*/
- foreach(idx, indirection)
+ foreach(idx, *indirection)
{
A_Indices *ai = lfirst_node(A_Indices, idx);
Node *subExpr;
@@ -159,6 +159,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
/* Determine the result type of the subscripting operation; always jsonb */
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
+
+ *indirection = NIL;
}
/*
diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h
index 234e8ad8012..ed2f1cefb49 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -93,7 +93,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 994284019fb..5ae11ccec33 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -377,7 +377,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] v7-0003-Export-jsonPathFromParseResult.patch (2.4K, ../CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com/6-v7-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From 015f01b44fa99fe1242b21cb83736e4d31fa5671 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:55 +0300
Subject: [PATCH v7 3/5] Export jsonPathFromParseResult()
---
src/backend/utils/adt/jsonpath.c | 24 ++++++++++++++++++------
src/include/utils/jsonpath.h | 4 ++++
2 files changed, 22 insertions(+), 6 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 762f7e8a09d..a3270bdaba3 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -165,16 +165,12 @@ 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
- * representation of jsonpath.
+ * Uses jsonpath parser to turn string into an AST.
*/
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 +181,24 @@ 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 AST to a jsonpath value.
+ *
+ * flattenJsonPathParseItem() does second pass turning AST into binary
+ * representation of jsonpath.
+ */
+Datum
+jsonPathFromParseResult(JsonPathParseResult *jsonpath, int estimated_len,
+ struct Node *escontext)
+{
+ JsonPath *res;
+ StringInfoData buf;
+
initStringInfo(&buf);
- enlargeStringInfo(&buf, 4 * len /* estimation */ );
+ enlargeStringInfo(&buf, estimated_len);
appendStringInfoSpaces(&buf, JSONPATH_HDRSZ);
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 23a76d233e9..e05941623e7 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,6 +281,10 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
extern bool jspConvertRegexFlags(uint32 xflags, int *result,
struct Node *escontext);
+extern Datum jsonPathFromParseResult(JsonPathParseResult *jsonpath,
+ int estimated_len,
+ struct Node *escontext);
+
/*
* Struct for details about external variables passed into jsonpath executor
*/
--
2.39.5 (Apple Git-154)
[application/octet-stream] v7-0004-Implement-read-only-dot-notation-for-jsonb-using-.patch (31.0K, ../CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com/7-v7-0004-Implement-read-only-dot-notation-for-jsonb-using-.patch)
download | inline diff:
From 53bc1da78b0feb7769c571fbcafb70c2214a9c46 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:17:53 +0300
Subject: [PATCH v7 4/5] Implement read-only dot notation for jsonb using
jsonpath
---
src/backend/utils/adt/jsonbsubs.c | 438 +++++++++++++++++++++++-----
src/include/nodes/primnodes.h | 2 +
src/test/regress/expected/jsonb.out | 265 ++++++++++++++++-
src/test/regress/sql/jsonb.sql | 56 ++++
4 files changed, 670 insertions(+), 91 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index a0d38a0fd80..1ececb4efa2 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -15,12 +15,14 @@
#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 */
@@ -30,8 +32,261 @@ typedef struct JsonbSubWorkspace
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 */
+ List vars; /* jsonpath vars */
} JsonbSubWorkspace;
+static bool
+jsonb_check_jsonpath_needed(List *indirection)
+{
+ ListCell *lc;
+
+ foreach(lc, indirection)
+ {
+ Node *accessor = lfirst(lc);
+
+ if (IsA(accessor, String) ||
+ IsA(accessor, A_Star))
+ return true;
+ else if (IsA(accessor, A_Indices))
+ {
+ A_Indices *ai = castNode(A_Indices, accessor);
+
+ if (!ai->uidx || ai->lidx)
+ {
+ Assert(ai->is_slice);
+ return true;
+ }
+ }
+ else
+ return true;
+ }
+
+ return false;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+ JsonPathParseItem *v = palloc(sizeof(*v));
+
+ v->type = type;
+ v->next = NULL;
+
+ return v;
+}
+
+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;
+}
+
+static Oid
+jsonb_subscript_type(Node *expr)
+{
+ if (expr && IsA(expr, String))
+ return TEXTOID;
+
+ return exprType(expr);
+}
+
+static Node *
+coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype)
+{
+ Oid subExprType = jsonb_subscript_type(subExpr);
+ Oid targetType = UNKNOWNOID;
+
+ if (subExprType != UNKNOWNOID)
+ {
+ Oid targets[2] = {numtype, TEXTOID};
+
+ /*
+ * Jsonb can handle multiple subscript types, but cases when a
+ * subscript could be coerced to multiple target types must be
+ * avoided, similar to overloaded functions. It could be
+ * possibly extend with jsonpath in the future.
+ */
+ for (int i = 0; i < 2; i++)
+ {
+ if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
+ {
+ /*
+ * One type has already succeeded, it means there are
+ * two coercion targets possible, failure.
+ */
+ if (targetType != UNKNOWNOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("subscript type %s is not supported", format_type_be(subExprType)),
+ errhint("jsonb subscript must be coercible to only one type, integer or text."),
+ parser_errposition(pstate, exprLocation(subExpr))));
+
+ targetType = targets[i];
+ }
+ }
+
+ /*
+ * No suitable types were found, failure.
+ */
+ if (targetType == UNKNOWNOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("subscript type %s is not supported", format_type_be(subExprType)),
+ errhint("jsonb subscript must be coercible to either integer or text."),
+ parser_errposition(pstate, exprLocation(subExpr))));
+ }
+ else
+ targetType = TEXTOID;
+
+ /*
+ * We known from can_coerce_type that coercion will succeed, so
+ * coerce_type could be used. Note the implicit coercion context,
+ * which is required to handle subscripts of different types,
+ * similar to overloaded functions.
+ */
+ subExpr = coerce_type(pstate,
+ subExpr, subExprType,
+ targetType, -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+ if (subExpr == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("jsonb subscript must have text type"),
+ parser_errposition(pstate, exprLocation(subExpr))));
+
+ return subExpr;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+{
+ JsonPathParseItem *jpi;
+
+ expr = transformExpr(pstate, expr, pstate->p_expr_kind);
+
+ if (IsA(expr, Const))
+ {
+ Const *cnst = (Const *) expr;
+
+ if (cnst->consttype == INT4OID && !cnst->constisnull)
+ {
+ int32 val = DatumGetInt32(cnst->constvalue);
+
+ return make_jsonpath_item_int(val, exprs);
+ }
+ }
+
+ *exprs = lappend(*exprs, coerce_jsonpath_subscript(pstate, expr, NUMERICOID));
+
+ jpi = make_jsonpath_item(jpiVariable);
+ jpi->value.string.val = psprintf("%d", list_length(*exprs));
+ jpi->value.string.len = strlen(jpi->value.string.val);
+
+ return jpi;
+}
+
+static Node *
+jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection,
+ List **uexprs, List **lexprs)
+{
+ JsonPathParseResult jpres;
+ JsonPathParseItem *path = make_jsonpath_item(jpiRoot);
+ ListCell *lc;
+ Datum jsp;
+ int pathlen = 0;
+
+ *uexprs = NIL;
+ *lexprs = NIL;
+
+ jpres.expr = path;
+ jpres.lax = true;
+
+ foreach(lc, *indirection)
+ {
+ Node *accessor = lfirst(lc);
+ JsonPathParseItem *jpi;
+
+ if (IsA(accessor, String))
+ {
+ char *field = strVal(accessor);
+
+ jpi = make_jsonpath_item(jpiKey);
+ jpi->value.string.val = field;
+ jpi->value.string.len = strlen(field);
+
+ *uexprs = lappend(*uexprs, accessor);
+ }
+ else if (IsA(accessor, A_Star))
+ {
+ jpi = make_jsonpath_item(jpiAnyKey);
+
+ *uexprs = lappend(*uexprs, NULL);
+ }
+ else if (IsA(accessor, A_Indices))
+ {
+ A_Indices *ai = castNode(A_Indices, accessor);
+
+ jpi = make_jsonpath_item(jpiIndexArray);
+ jpi->value.array.nelems = 1;
+ jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+ if (ai->is_slice)
+ {
+ while (list_length(*lexprs) < list_length(*uexprs))
+ *lexprs = lappend(*lexprs, NULL);
+
+ if (ai->lidx)
+ jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->lidx, lexprs);
+ else
+ jpi->value.array.elems[0].from = make_jsonpath_item_int(0, lexprs);
+
+ if (ai->uidx)
+ jpi->value.array.elems[0].to = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+ else
+ {
+ jpi->value.array.elems[0].to = make_jsonpath_item(jpiLast);
+ *uexprs = lappend(*uexprs, NULL);
+ }
+ }
+ else
+ {
+ Assert(ai->uidx && !ai->lidx);
+ jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+ jpi->value.array.elems[0].to = NULL;
+ }
+ }
+ else
+ break;
+
+ /* append path item */
+ path->next = jpi;
+ path = jpi;
+ pathlen++;
+ }
+
+ if (*lexprs)
+ {
+ while (list_length(*lexprs) < list_length(*uexprs))
+ *lexprs = lappend(*lexprs, NULL);
+ }
+
+ *indirection = list_delete_first_n(*indirection, pathlen);
+
+ jsp = jsonPathFromParseResult(&jpres, 0, NULL);
+
+ return (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false);
+}
/*
* Finish parse analysis of a SubscriptingRef expression for a jsonb.
@@ -49,19 +304,35 @@ 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))
+ {
+ sbsref->refprivate =
+ jsonb_subscript_make_jsonpath(pstate, indirection,
+ &sbsref->refupperindexpr,
+ &sbsref->reflowerindexpr);
+ return;
+ }
+
/*
* Transform and convert the subscript expressions. Jsonb subscripting
* does not support slices, look only and the upper index.
*/
foreach(idx, *indirection)
{
+ Node *i = lfirst(idx);
A_Indices *ai;
Node *subExpr;
- if (!IsA(lfirst(idx), A_Indices))
+ Assert(IsA(i, A_Indices));
+
+ if (!IsA(i, A_Indices))
break;
- ai = lfirst_node(A_Indices, idx);
+ ai = castNode(A_Indices, i);
if (isSlice)
{
@@ -75,71 +346,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
if (ai->uidx)
{
- Oid subExprType = InvalidOid,
- targetType = UNKNOWNOID;
-
subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
- subExprType = exprType(subExpr);
-
- if (subExprType != UNKNOWNOID)
- {
- Oid targets[2] = {INT4OID, TEXTOID};
-
- /*
- * Jsonb can handle multiple subscript types, but cases when a
- * subscript could be coerced to multiple target types must be
- * avoided, similar to overloaded functions. It could be
- * possibly extend with jsonpath in the future.
- */
- for (int i = 0; i < 2; i++)
- {
- if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
- {
- /*
- * One type has already succeeded, it means there are
- * two coercion targets possible, failure.
- */
- if (targetType != UNKNOWNOID)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("subscript type %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to only one type, integer or text."),
- parser_errposition(pstate, exprLocation(subExpr))));
-
- targetType = targets[i];
- }
- }
-
- /*
- * No suitable types were found, failure.
- */
- if (targetType == UNKNOWNOID)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("subscript type %s is not supported", format_type_be(subExprType)),
- errhint("jsonb subscript must be coercible to either integer or text."),
- parser_errposition(pstate, exprLocation(subExpr))));
- }
- else
- targetType = TEXTOID;
-
- /*
- * We known from can_coerce_type that coercion will succeed, so
- * coerce_type could be used. Note the implicit coercion context,
- * which is required to handle subscripts of different types,
- * similar to overloaded functions.
- */
- subExpr = coerce_type(pstate,
- subExpr, subExprType,
- targetType, -1,
- COERCION_IMPLICIT,
- COERCE_IMPLICIT_CAST,
- -1);
- if (subExpr == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript must have text type"),
- parser_errposition(pstate, exprLocation(subExpr))));
+ subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
}
else
{
@@ -161,10 +369,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refupperindexpr = upperIndexpr;
sbsref->reflowerindexpr = NIL;
- /* Determine the result type of the subscripting operation; always jsonb */
- sbsref->refrestype = JSONBOID;
- sbsref->reftypmod = -1;
-
/* Remove processed elements */
if (upperIndexpr)
*indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
@@ -219,7 +423,7 @@ jsonb_subscript_check_subscripts(ExprState *state,
* For jsonb fetch and assign functions we need to provide path in
* text format. Convert if it's not already text.
*/
- if (workspace->indexOid[i] == INT4OID)
+ if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID)
{
Datum datum = sbsrefstate->upperindex[i];
char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
@@ -247,17 +451,44 @@ 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;
+ List *vars = &workspace->vars;
+ ListCell *lc;
+
+ /* copy computed variable values */
+ foreach(lc, vars)
+ {
+ JsonPathVariable *var = lfirst(lc);
+ int i = foreach_current_index(lc);
+
+ var->value = workspace->index[i];
+ var->isnull = false;
+ }
+
+ *op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath,
+ JSW_CONDITIONAL,
+ &empty, &error, vars,
+ NULL);
+
+ *op->resnull = empty || error;
+ }
+ else
+ {
+ Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+ *op->resvalue = jsonb_get_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ op->resnull,
+ false);
+ }
}
/*
@@ -367,12 +598,57 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
ListCell *lc;
int nupper = sbsref->refupperindexpr->length;
char *ptr;
+ bool useJsonpath = sbsref->refprivate != NULL;
+ JsonPathVariable *vars;
+ int nvars = useJsonpath ? nupper : 0;
/* Allocate type-specific workspace with space for per-subscript data */
- workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) +
+ workspace = palloc0(MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) + nvars * sizeof(ListCell)) +
+ MAXALIGN(nvars * sizeof(*vars) + nvars * 16) +
nupper * (sizeof(Datum) + sizeof(Oid)));
workspace->expectArray = false;
- ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+ ptr = ((char *) workspace) +
+ MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) +
+ nvars * sizeof(ListCell));
+
+ if (!useJsonpath)
+ workspace->jsonpath = NULL;
+ else
+ {
+ workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refprivate)->constvalue);
+
+ vars = (JsonPathVariable *) ptr;
+ ptr += MAXALIGN(nvars * sizeof(*vars));
+
+ workspace->vars.type = T_List;
+ workspace->vars.length = nvars;
+ workspace->vars.max_length = nvars;
+ workspace->vars.elements = &workspace->vars.initial_elements[0];
+
+ for (int i = 0; i < nvars; i++)
+ {
+ Node *expr = list_nth(sbsref->refupperindexpr, i);
+
+ workspace->vars.elements[i].ptr_value = &vars[i];
+
+ if (expr && IsA(expr, String))
+ {
+ vars[i].typid = TEXTOID;
+ vars[i].typmod = -1;
+ }
+ else
+ {
+ vars[i].typid = exprType(expr);
+ vars[i].typmod = exprTypmod(expr);
+ }
+
+ vars[i].name = ptr;
+ snprintf(ptr, 16, "%d", i + 1);
+ vars[i].namelen = strlen(ptr);
+
+ ptr += 16;
+ }
+ }
/*
* This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
@@ -390,7 +666,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
Node *expr = lfirst(lc);
int i = foreach_current_index(lc);
- workspace->indexOid[i] = exprType(expr);
+ workspace->indexOid[i] = jsonb_subscript_type(expr);
}
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 839e71d52f4..4b1e5de98e5 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -718,6 +718,8 @@ typedef struct SubscriptingRef
Expr *refexpr;
/* expression for the source value, or NULL if fetch */
Expr *refassgnexpr;
+ /* private expression */
+ Node *refprivate;
} SubscriptingRef;
/*
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf2..14123929475 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4939,6 +4939,12 @@ select ('123'::jsonb)['a'];
(1 row)
+select ('123'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('123'::jsonb)[0];
jsonb
-------
@@ -4957,6 +4963,12 @@ select ('{"a": 1}'::jsonb)['a'];
1
(1 row)
+select ('{"a": 1}'::jsonb).a;
+ a
+---
+ 1
+(1 row)
+
select ('{"a": 1}'::jsonb)[0];
jsonb
-------
@@ -4969,6 +4981,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
-------
@@ -4981,6 +4999,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
-------
@@ -4993,6 +5017,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];
@@ -5022,6 +5052,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
-------
@@ -5034,73 +5070,143 @@ 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": {"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)
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
+ b1
+-------
+ "ccc"
+(1 row)
+
-- slices are not supported
select ('{"a": 1}'::jsonb)['a':'b'];
-ERROR: jsonb subscript does not support slices
-LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
- ^
+ jsonb
+-------
+
+(1 row)
+
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];
^
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
@@ -5781,3 +5887,142 @@ 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).* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+ERROR: type jsonb is not composite
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ERROR: type jsonb is not composite
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+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;
+ERROR: type jsonb is not composite
+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;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(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.* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+ ^
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ERROR: row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ ^
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ERROR: row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ ^
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+ERROR: row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+ ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ ^
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR: type jsonb is not composite
+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)
+
+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)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_...
+ ^
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
+ERROR: improper use of "*" at or near "FROM"
+LINE 1: ... (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_...
+ ^
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2d..4b49d59222b 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1286,30 +1286,46 @@ 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 ('{"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": {"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'];
@@ -1572,3 +1588,43 @@ 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).* 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 FROM test_jsonb_dot_notation;
+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.* 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;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
--
2.39.5 (Apple Git-154)
view thread (67+ 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]
Subject: Re: SQL:2023 JSON simplified accessor support
In-Reply-To: <CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@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