public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alexandra Wang <[email protected]>
To: 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]>
Cc: jian he <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Mon, 3 Mar 2025 14:22:49 -0600
Message-ID: <CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com> (raw)
In-Reply-To: <CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
	<CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
	<CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
	<CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
	<CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
	<CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>

Hi Matheus,

On Mon, Mar 3, 2025 at 1:43 PM Matheus Alcantara <[email protected]>
wrote:

> On Mon, Mar 3, 2025 at 4:16 PM Matheus Alcantara
> <[email protected]> wrote:
> >
> > Hi Alex,
> >
> > The code comments and the commit messages help a lot when reviewing!
> Thanks for
> > the new version.
> >
> > The code LGTM and check-world is happy. I've also performed some tests
> and
> > everything looks good!

> Just some minor points about this new version:
> >
> > ## v9-0005
> >
> > Typo on commit message title

> ## v9-0006
> >
> > > + * The following functions create various types of JsonPathParseItem
> nodes,
> > > + * which are used to build JsonPath expressions for jsonb simplified
> accessor.
> > >
> > Just to avoid misinterpretation I think that we can replace "The
> following
> > functions" with "The make_jsonpath_item_* functions" since we can have
> more
> > functions in the future that are not fully related with these. Does that
> make
> > sense?
>

Thank you so much for reviewing! I've attached v10, which addresses your
feedback.

On Mon, Mar 3, 2025 at 1:43 PM Matheus Alcantara <[email protected]>
wrote:

> Sorry, I've forgotten to include a question. Do you have anything in mind
> about
> documentation changes for this patch?
>

For the documentation, I’m thinking of adding it under JSON Types [1].
I’d either add a new “Simple Dot-Notation” section after jsonb
subscripting [2] or replace it. Let me know what you think.

[1] https://www.postgresql.org/docs/current/datatype-json.html#DATATYPE-JSON
[2]
https://www.postgresql.org/docs/current/datatype-json.html#JSONB-SUBSCRIPTING

Best,
Alex


Attachments:

  [application/octet-stream] v10-0004-Extract-coerce_jsonpath_subscript.patch (5.5K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/3-v10-0004-Extract-coerce_jsonpath_subscript.patch)
  download | inline diff:
From f03fa1630122561006254b958ab9c14e4cb5bc06 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Wed, 26 Feb 2025 13:03:27 -0600
Subject: [PATCH v10 4/7] Extract coerce_jsonpath_subscript()

This is a preparation step for a future commit that will reuse the
aforementioned function.
---
 src/backend/utils/adt/jsonbsubs.c | 142 ++++++++++++++++--------------
 1 file changed, 78 insertions(+), 64 deletions(-)

diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index a0d38a0fd80..3ffe40cfa40 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -32,6 +32,83 @@ typedef struct JsonbSubWorkspace
 	Datum	   *index;			/* Subscript values in Datum format */
 } JsonbSubWorkspace;
 
+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;
+}
 
 /*
  * Finish parse analysis of a SubscriptingRef expression for a jsonb.
@@ -75,71 +152,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 
 		if (ai->uidx)
 		{
-			Oid			subExprType = InvalidOid,
-						targetType = UNKNOWNOID;
-
 			subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
-			subExprType = exprType(subExpr);
-
-			if (subExprType != UNKNOWNOID)
-			{
-				Oid			targets[2] = {INT4OID, TEXTOID};
-
-				/*
-				 * Jsonb can handle multiple subscript types, but cases when a
-				 * subscript could be coerced to multiple target types must be
-				 * avoided, similar to overloaded functions. It could be
-				 * possibly extend with jsonpath in the future.
-				 */
-				for (int i = 0; i < 2; i++)
-				{
-					if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
-					{
-						/*
-						 * One type has already succeeded, it means there are
-						 * two coercion targets possible, failure.
-						 */
-						if (targetType != UNKNOWNOID)
-							ereport(ERROR,
-									(errcode(ERRCODE_DATATYPE_MISMATCH),
-									 errmsg("subscript type %s is not supported", format_type_be(subExprType)),
-									 errhint("jsonb subscript must be coercible to only one type, integer or text."),
-									 parser_errposition(pstate, exprLocation(subExpr))));
-
-						targetType = targets[i];
-					}
-				}
-
-				/*
-				 * No suitable types were found, failure.
-				 */
-				if (targetType == UNKNOWNOID)
-					ereport(ERROR,
-							(errcode(ERRCODE_DATATYPE_MISMATCH),
-							 errmsg("subscript type %s is not supported", format_type_be(subExprType)),
-							 errhint("jsonb subscript must be coercible to either integer or text."),
-							 parser_errposition(pstate, exprLocation(subExpr))));
-			}
-			else
-				targetType = TEXTOID;
-
-			/*
-			 * We known from can_coerce_type that coercion will succeed, so
-			 * coerce_type could be used. Note the implicit coercion context,
-			 * which is required to handle subscripts of different types,
-			 * similar to overloaded functions.
-			 */
-			subExpr = coerce_type(pstate,
-								  subExpr, subExprType,
-								  targetType, -1,
-								  COERCION_IMPLICIT,
-								  COERCE_IMPLICIT_CAST,
-								  -1);
-			if (subExpr == NULL)
-				ereport(ERROR,
-						(errcode(ERRCODE_DATATYPE_MISMATCH),
-						 errmsg("jsonb subscript must have text type"),
-						 parser_errposition(pstate, exprLocation(subExpr))));
+			subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
 		}
 		else
 		{
-- 
2.39.5 (Apple Git-154)



  [application/octet-stream] v10-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch (10.3K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/4-v10-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch)
  download | inline diff:
From 050703ccb4e1786710722b451eabcf1a908cd763 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 16:21:20 +0300
Subject: [PATCH v10 2/7] Allow Generic Type Subscripting to Accept Dot
 Notation (.) as Input

This change extends generic type subscripting to recognize dot
notation (.) in addition to bracket notation ([]). While this does not
yet provide full support for dot notation, it enables subscripting
containers to process it in the future.

For now, container-specific transform functions only handle
subscripting indices and stop processing when encountering dot
notation. It is up to individual containers to decide how to transform
dot notation in subsequent updates.
---
 src/backend/parser/parse_expr.c   | 66 ++++++++++++++++++++-----------
 src/backend/parser/parse_node.c   | 41 +++++++++++++++++--
 src/backend/parser/parse_target.c |  3 +-
 src/backend/utils/adt/arraysubs.c | 13 ++++--
 src/backend/utils/adt/jsonbsubs.c | 11 +++++-
 src/include/parser/parse_node.h   |  3 +-
 6 files changed, 105 insertions(+), 32 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 2c0f4a50b21..8ea51176196 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -442,8 +442,9 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
 	ListCell   *i;
 
 	/*
-	 * We have to split any field-selection operations apart from
-	 * subscripting.  Adjacent A_Indices nodes have to be treated as a single
+	 * Combine field names and subscripts into a single indirection list, as
+	 * some subscripting containers, such as jsonb, support field access using
+	 * dot notation. Adjacent A_Indices nodes have to be treated as a single
 	 * multidimensional subscript operation.
 	 */
 	foreach(i, ind->indirection)
@@ -461,19 +462,43 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
 		}
 		else
 		{
-			Node	   *newresult;
-
 			Assert(IsA(n, String));
+			subscripts = lappend(subscripts, n);
+		}
+	}
+
+	while (subscripts)
+	{
+		/* try processing container subscripts first */
+		Node	   *newresult = (Node *)
+			transformContainerSubscripts(pstate,
+										 result,
+										 exprType(result),
+										 exprTypmod(result),
+										 &subscripts,
+										 false,
+										 true);
+
+		if (!newresult)
+		{
+			/*
+			 * generic subscripting failed; falling back to function call or
+			 * field selection for a composite type.
+			 */
+			Node	   *n;
+
+			Assert(subscripts);
 
-			/* process subscripts before this field selection */
-			while (subscripts)
-				result = (Node *) transformContainerSubscripts(pstate,
-															   result,
-															   exprType(result),
-															   exprTypmod(result),
-															   &subscripts,
-															   false);
+			n = linitial(subscripts);
+
+			if (!IsA(n, String))
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot subscript type %s because it does not support subscripting",
+								format_type_be(exprType(result))),
+						 parser_errposition(pstate, exprLocation(result))));
 
+			/* try to find function for field selection */
 			newresult = ParseFuncOrColumn(pstate,
 										  list_make1(n),
 										  list_make1(result),
@@ -481,19 +506,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
 										  NULL,
 										  false,
 										  location);
-			if (newresult == NULL)
+
+			if (!newresult)
 				unknown_attribute(pstate, result, strVal(n), location);
-			result = newresult;
+
+			/* consume field select */
+			subscripts = list_delete_first(subscripts);
 		}
+
+		result = newresult;
 	}
-	/* process trailing subscripts, if any */
-	while (subscripts)
-		result = (Node *) transformContainerSubscripts(pstate,
-													   result,
-													   exprType(result),
-													   exprTypmod(result),
-													   &subscripts,
-													   false);
 
 	return result;
 }
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index 19a6b678e67..b3e476eb181 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -238,6 +238,8 @@ transformContainerType(Oid *containerType, int32 *containerTypmod)
  * containerTypMod	typmod for the container
  * indirection		Untransformed list of subscripts (must not be NIL)
  * isAssignment		True if this will become a container assignment.
+ * noError			True for return NULL with no error, if the container type
+ * 					is not subscriptable.
  */
 SubscriptingRef *
 transformContainerSubscripts(ParseState *pstate,
@@ -245,13 +247,15 @@ transformContainerSubscripts(ParseState *pstate,
 							 Oid containerType,
 							 int32 containerTypMod,
 							 List **indirection,
-							 bool isAssignment)
+							 bool isAssignment,
+							 bool noError)
 {
 	SubscriptingRef *sbsref;
 	const SubscriptRoutines *sbsroutines;
 	Oid			elementType;
 	bool		isSlice = false;
 	ListCell   *idx;
+	int			indirection_length = list_length(*indirection);
 
 	/*
 	 * Determine the actual container type, smashing any domain.  In the
@@ -267,11 +271,16 @@ transformContainerSubscripts(ParseState *pstate,
 	 */
 	sbsroutines = getSubscriptingRoutines(containerType, &elementType);
 	if (!sbsroutines)
+	{
+		if (noError)
+			return NULL;
+
 		ereport(ERROR,
 				(errcode(ERRCODE_DATATYPE_MISMATCH),
 				 errmsg("cannot subscript type %s because it does not support subscripting",
 						format_type_be(containerType)),
 				 parser_errposition(pstate, exprLocation(containerBase))));
+	}
 
 	/*
 	 * Detect whether any of the indirection items are slice specifiers.
@@ -282,9 +291,9 @@ transformContainerSubscripts(ParseState *pstate,
 	 */
 	foreach(idx, *indirection)
 	{
-		A_Indices  *ai = lfirst_node(A_Indices, idx);
+		Node	   *ai = lfirst(idx);
 
-		if (ai->is_slice)
+		if (IsA(ai, A_Indices) && castNode(A_Indices, ai)->is_slice)
 		{
 			isSlice = true;
 			break;
@@ -312,6 +321,32 @@ transformContainerSubscripts(ParseState *pstate,
 	sbsroutines->transform(sbsref, indirection, pstate,
 						   isSlice, isAssignment);
 
+	/*
+	 * Error out, if datatype failed to consume any indirection elements.
+	 */
+	if (list_length(*indirection) == indirection_length)
+	{
+		Node	   *ind = linitial(*indirection);
+
+		if (noError)
+			return NULL;
+
+		if (IsA(ind, String))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("type %s does not support dot notation",
+							format_type_be(containerType)),
+					 parser_errposition(pstate, exprLocation(containerBase))));
+		else if (IsA(ind, A_Indices))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("type %s does not support array subscripting",
+							format_type_be(containerType)),
+					 parser_errposition(pstate, exprLocation(containerBase))));
+		else
+			elog(ERROR, "invalid indirection operation: %d", nodeTag(ind));
+	}
+
 	/*
 	 * Verify we got a valid type (this defends, for example, against someone
 	 * using array_subscript_handler as typsubscript without setting typelem).
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4675a523045..3ef5897f2eb 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -937,7 +937,8 @@ transformAssignmentSubscripts(ParseState *pstate,
 										  containerType,
 										  containerTypMod,
 										  &subscripts,
-										  true);
+										  true,
+										  false);
 
 	typeNeeded = sbsref->refrestype;
 	typmodNeeded = sbsref->reftypmod;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 234c2c278c1..d03d3519dfd 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -62,6 +62,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
 	List	   *upperIndexpr = NIL;
 	List	   *lowerIndexpr = NIL;
 	ListCell   *idx;
+	int			ndim;
 
 	/*
 	 * Transform the subscript expressions, and separate upper and lower
@@ -73,9 +74,14 @@ array_subscript_transform(SubscriptingRef *sbsref,
 	 */
 	foreach(idx, *indirection)
 	{
-		A_Indices  *ai = lfirst_node(A_Indices, idx);
+		A_Indices  *ai;
 		Node	   *subexpr;
 
+		if (!IsA(lfirst(idx), A_Indices))
+			break;
+
+		ai = lfirst_node(A_Indices, idx);
+
 		if (isSlice)
 		{
 			if (ai->lidx)
@@ -145,14 +151,15 @@ array_subscript_transform(SubscriptingRef *sbsref,
 	sbsref->reflowerindexpr = lowerIndexpr;
 
 	/* Verify subscript list lengths are within implementation limit */
-	if (list_length(upperIndexpr) > MAXDIM)
+	ndim = list_length(upperIndexpr);
+	if (ndim > MAXDIM)
 		ereport(ERROR,
 				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 				 errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)",
 						list_length(upperIndexpr), MAXDIM)));
 	/* We need not check lowerIndexpr separately */
 
-	*indirection = NIL;
+	*indirection = list_delete_first_n(*indirection, ndim);
 
 	/*
 	 * Determine the result type of the subscripting operation.  It's the same
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 8ad6aa1ad4f..a0d38a0fd80 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -55,9 +55,14 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 	 */
 	foreach(idx, *indirection)
 	{
-		A_Indices  *ai = lfirst_node(A_Indices, idx);
+		A_Indices  *ai;
 		Node	   *subExpr;
 
+		if (!IsA(lfirst(idx), A_Indices))
+			break;
+
+		ai = lfirst_node(A_Indices, idx);
+
 		if (isSlice)
 		{
 			Node	   *expr = ai->uidx ? ai->uidx : ai->lidx;
@@ -160,7 +165,9 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 	sbsref->refrestype = JSONBOID;
 	sbsref->reftypmod = -1;
 
-	*indirection = NIL;
+	/* Remove processed elements */
+	if (upperIndexpr)
+		*indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
 }
 
 /*
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 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] v10-0003-Export-jsonPathFromParseResult.patch (2.5K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/5-v10-0003-Export-jsonPathFromParseResult.patch)
  download | inline diff:
From 6029a27fcad003f1c6643ddba011fc3660db21e3 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:55 +0300
Subject: [PATCH v10 3/7] Export jsonPathFromParseResult()

This is a preparation step for a future commit that will reuse the
aforementioned function.
---
 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..1536797cf23 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 AST into jsonpath value in binary.
+ */
+Datum
+jsonPathFromParseResult(JsonPathParseResult *jsonpath, int estimated_len,
+						struct Node *escontext)
+{
+	JsonPath   *res;
+	StringInfoData buf;
+
 	initStringInfo(&buf);
-	enlargeStringInfo(&buf, 4 * len /* estimation */ );
+	enlargeStringInfo(&buf, estimated_len);
 
 	appendStringInfoSpaces(&buf, JSONPATH_HDRSZ);
 
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 23a76d233e9..e05941623e7 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,6 +281,10 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
 extern bool jspConvertRegexFlags(uint32 xflags, int *result,
 								 struct Node *escontext);
 
+extern Datum jsonPathFromParseResult(JsonPathParseResult *jsonpath,
+									 int estimated_len,
+									 struct Node *escontext);
+
 /*
  * Struct for details about external variables passed into jsonpath executor
  */
-- 
2.39.5 (Apple Git-154)



  [application/octet-stream] v10-0005-Enable-String-node-as-field-accessors-in-generic.patch (7.7K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/6-v10-0005-Enable-String-node-as-field-accessors-in-generic.patch)
  download | inline diff:
From 6a9eec5d7a0df7deaf2b94c52af0377b6577cca2 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 16:21:20 +0300
Subject: [PATCH v10 5/7] Enable String node as field accessors in generic
 subscripting

Now that we are allowing container generic subscripting to take dot
notation in the list of indirections, and it is transformed as a
String node.

For jsonb, we want to represent field accessors as String nodes in
refupperexprs for distinguishing from ordinary text subscripts which
can be needed for correct EXPLAIN.

Strings node is no longer a valid expression nodes, so added special
handling for them in walkers in nodeFuncs etc.
---
 src/backend/executor/execExpr.c    | 24 +++++++---
 src/backend/nodes/nodeFuncs.c      | 73 ++++++++++++++++++++++++++----
 src/backend/parser/parse_collate.c | 22 +++++++--
 src/backend/utils/adt/ruleutils.c  | 29 ++++++++----
 4 files changed, 121 insertions(+), 27 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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d11a8a20eea..cfae8159a76 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"
@@ -12923,17 +12924,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);
 	}
 }
 
-- 
2.39.5 (Apple Git-154)



  [application/octet-stream] v10-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch (8.8K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/7-v10-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch)
  download | inline diff:
From bd946db88d0f149f073f76eaa2501e4845b9b2b5 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Fri, 14 Oct 2022 15:35:22 +0300
Subject: [PATCH v10 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.
---
 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 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..4675a523045 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -936,7 +936,7 @@ transformAssignmentSubscripts(ParseState *pstate,
 										  basenode,
 										  containerType,
 										  containerTypMod,
-										  subscripts,
+										  &subscripts,
 										  true);
 
 	typeNeeded = sbsref->refrestype;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 2940fb8e8d7..234c2c278c1 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -54,7 +54,7 @@ typedef struct ArraySubWorkspace
  */
 static void
 array_subscript_transform(SubscriptingRef *sbsref,
-						  List *indirection,
+						  List **indirection,
 						  ParseState *pstate,
 						  bool isSlice,
 						  bool isAssignment)
@@ -71,7 +71,7 @@ array_subscript_transform(SubscriptingRef *sbsref,
 	 * indirection items to slices by treating the single subscript as the
 	 * upper bound and supplying an assumed lower bound of 1.
 	 */
-	foreach(idx, indirection)
+	foreach(idx, *indirection)
 	{
 		A_Indices  *ai = lfirst_node(A_Indices, idx);
 		Node	   *subexpr;
@@ -152,6 +152,8 @@ array_subscript_transform(SubscriptingRef *sbsref,
 						list_length(upperIndexpr), MAXDIM)));
 	/* We need not check lowerIndexpr separately */
 
+	*indirection = NIL;
+
 	/*
 	 * Determine the result type of the subscripting operation.  It's the same
 	 * as the array type if we're slicing, else it's the element type.  In
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index de64d498512..8ad6aa1ad4f 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -41,7 +41,7 @@ typedef struct JsonbSubWorkspace
  */
 static void
 jsonb_subscript_transform(SubscriptingRef *sbsref,
-						  List *indirection,
+						  List **indirection,
 						  ParseState *pstate,
 						  bool isSlice,
 						  bool isAssignment)
@@ -53,7 +53,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 	 * Transform and convert the subscript expressions. Jsonb subscripting
 	 * does not support slices, look only and the upper index.
 	 */
-	foreach(idx, indirection)
+	foreach(idx, *indirection)
 	{
 		A_Indices  *ai = lfirst_node(A_Indices, idx);
 		Node	   *subExpr;
@@ -159,6 +159,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 	/* Determine the result type of the subscripting operation; always jsonb */
 	sbsref->refrestype = JSONBOID;
 	sbsref->reftypmod = -1;
+
+	*indirection = NIL;
 }
 
 /*
diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h
index 234e8ad8012..5d576af346f 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -71,6 +71,11 @@ struct SubscriptExecSteps;
  * does not care to support slicing, it can just throw an error if isSlice.)
  * See array_subscript_transform() for sample code.
  *
+ * The transform method receives a pointer to a list of raw indirections.
+ * This allows the method to parse a sublist of the indirections (typically
+ * the prefix) and modify the original list in place, enabling the caller to
+ * either process the remaining indirections differently or raise an error.
+ *
  * The transform method is also responsible for identifying the result type
  * of the subscripting operation.  At call, refcontainertype and reftypmod
  * describe the container type (this will be a base type not a domain), and
@@ -93,7 +98,7 @@ struct SubscriptExecSteps;
  * assignment must return.
  */
 typedef void (*SubscriptTransform) (SubscriptingRef *sbsref,
-									List *indirection,
+									List **indirection,
 									struct ParseState *pstate,
 									bool isSlice,
 									bool isAssignment);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 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] v10-0006-Implement-read-only-dot-notation-for-jsonb.patch (26.8K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/8-v10-0006-Implement-read-only-dot-notation-for-jsonb.patch)
  download | inline diff:
From 809e88954658cca633a56566521071188070db53 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:17:53 +0300
Subject: [PATCH v10 6/7] Implement read-only dot notation for jsonb

This patch introduces JSONB member access using dot notation, wildcard
access, and array subscripting with slicing, aligning with the JSON
simplified accessor specified in SQL:2023. Specifically, the following
syntax enhancements are added:

1. Simple dot-notation access to JSONB object fields
2. Wildcard dot-notation access to JSONB object fields
2. Subscripting for index range access to JSONB array elements

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 predates the SQL standard:
select (t.y)->'b' from t;
select (t.y)->'b'->'c' from t;
select (t.y)->'d'->0 from t;

-- JSON simplified accessor specified by the SQL standard:
select (t.y).b from t;
select (t.y).b.c from t;
select (t.y).d[0] from t;

The SQL standard states that simplified access is equivalent to:
JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)

where:
  VEP = <value expression primary>
  JC = <JSON simplified accessor op chain>

For example, the JSON_QUERY equivalents of the above queries are:

select json_query(y, 'lax $.b' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
select json_query(y, 'lax $.b.c' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
select json_query(y, 'lax $.d[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;

Implementation details:

Extends the existing container subscripting interface to support
container-specific information, specifically a JSONPath expression for
jsonb.

During query transformation, detects dot-notation, wildcard access,
and sliced subscripting. If any of these accessors are present,
constructs a JSONPath expression representing the access chain.

During execution, if a JSONPath expression is present in
JsonbSubWorkspace, executes it via JsonPathQuery().

Does not transform accessors directly into JSON_QUERY during
transformation to preserve the original query structure for EXPLAIN
and CREATE VIEW.
---
 src/backend/utils/adt/jsonbsubs.c   | 294 ++++++++++++++++++++++++++--
 src/include/nodes/primnodes.h       |   7 +
 src/test/regress/expected/jsonb.out | 249 ++++++++++++++++++++++-
 src/test/regress/sql/jsonb.sql      |  45 +++++
 4 files changed, 569 insertions(+), 26 deletions(-)

diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 3ffe40cfa40..edada16025b 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 Oid
@@ -110,6 +119,229 @@ coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype)
 	return subExpr;
 }
 
+/*
+ * During transformation, determine whether to build a JsonPath
+ * for JsonPathQuery() execution.
+ *
+ * JsonPath is needed if the indirection list includes:
+ * - String-based access (dot notation)
+ * - Wildcard (`*`)
+ * - Slice-based subscripting
+ *
+ * Otherwise, simple jsonb subscripting is sufficient.
+ */
+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
+		{
+			A_Indices  *ai;
+
+			Assert(IsA(accessor, A_Indices));
+			ai = castNode(A_Indices, accessor);
+
+			if (!ai->uidx || ai->lidx)
+			{
+				Assert(ai->is_slice);
+				return true;
+			}
+		}
+	}
+
+	return false;
+}
+
+/*
+ * Helper functions for constructing JsonPath expressions.
+ *
+ * The make_jsonpath_item_* functions create various types of JsonPathParseItem
+ * nodes, which are used to build JsonPath expressions for jsonb simplified
+ * accessor.
+ */
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+	JsonPathParseItem *v = palloc(sizeof(*v));
+
+	v->type = type;
+	v->next = NULL;
+
+	return v;
+}
+
+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 an expression into a JsonPathParseItem.
+ * If the expression is a constant integer, create a direct numeric item.
+ * Otherwise, create a variable reference and add it to the expression list.
+ */
+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))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("jsonb simplified accessor supports subscripting in const int4, got type: %s",
+						format_type_be(exprType(expr))),
+				 parser_errposition(pstate, exprLocation(expr))));
+
+	cnst = (Const *) expr;
+
+	if (cnst->consttype == INT4OID && !cnst->constisnull)
+	{
+		int32		val = DatumGetInt32(cnst->constvalue);
+
+		return make_jsonpath_item_int(val, exprs);
+	}
+
+	ereport(ERROR,
+			(errcode(ERRCODE_DATATYPE_MISMATCH),
+			 errmsg("jsonb simplified accessor supports subscripting in type: INT4, got type: %s",
+					format_type_be(cnst->consttype)),
+			 parser_errposition(pstate, exprLocation(expr))));
+}
+
+/*
+ * jsonb_subscript_make_jsonpath
+ *
+ * Constructs a JsonPath expression from a list of indirections.
+ * This function is used when jsonb subscripting involves dot notation,
+ * wildcards (*), or slice-based subscripting, 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.
+ * - Wildcard (*) -> jpiAnyKey item.
+ * - Array indices and slices -> jpiIndexArray items.
+ *
+ * Parameters:
+ * - pstate: Parse state context.
+ * - indirection: List of subscripting expressions (modified in-place).
+ * - uexprs: Upper-bound expressions extracted from subscripts.
+ * - lexprs: Lower-bound expressions extracted from subscripts.
+ * Returns:
+ * - a Const node containing the transformed JsonPath expression.
+ */
+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.
  *
@@ -126,19 +358,32 @@ 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->refjsonbpath =
+			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))
-			break;
+		Assert(IsA(i, A_Indices));
 
-		ai = lfirst_node(A_Indices, idx);
+		ai = castNode(A_Indices, i);
 
 		if (isSlice)
 		{
@@ -175,10 +420,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));
@@ -233,7 +474,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));
@@ -261,17 +502,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);
+	}
 }
 
 /*
@@ -381,6 +637,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
 	ListCell   *lc;
 	int			nupper = sbsref->refupperindexpr->length;
 	char	   *ptr;
+	bool		useJsonpath = sbsref->refjsonbpath != NULL;
 
 	/* Allocate type-specific workspace with space for per-subscript data */
 	workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) +
@@ -388,6 +645,9 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
 	workspace->expectArray = false;
 	ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
 
+	if (useJsonpath)
+		workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refjsonbpath)->constvalue);
+
 	/*
 	 * This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
 	 * misalign the indexOid pointer
@@ -404,7 +664,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 d0576da3e25..9d380ed60d6 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -718,6 +718,13 @@ typedef struct SubscriptingRef
 	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;
 
 /*
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf2..480ecb44a94 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 
 -------
@@ -4951,12 +4957,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 
 -------
@@ -4969,6 +4987,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 +5005,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 +5023,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 +5058,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 +5076,139 @@ 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'];
+ERROR:  jsonb simplified accessor supports subscripting in type: INT4, got type: unknown
+LINE 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;
+ 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
+ERROR:  jsonb simplified accessor supports subscripting in type: INT4, got type: unknown
 LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
-                                       ^
+                                   ^
 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 +5889,126 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
  12345
 (1 row)
 
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+ERROR:  syntax error at or near "'a'"
+LINE 1: SELECT (jb).'a' FROM test_jsonb_dot_notation;
+                    ^
+SELECT (jb).b FROM test_jsonb_dot_notation;
+                         b                         
+---------------------------------------------------
+ [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]
+(1 row)
+
+SELECT (jb).c FROM test_jsonb_dot_notation;
+ c 
+---
+ 
+(1 row)
+
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+     b      
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+  b  
+-----
+ "c"
+(1 row)
+
+SELECT (jb).a[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).a.x.y FROM test_jsonb_dot_notation;
+   y   
+-------
+ "yyy"
+(1 row)
+
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+            x             
+--------------------------
+ {"y": "YYY", "z": "ZZZ"}
+(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).a.* 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
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+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)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2d..55b6c07f462 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1286,30 +1286,47 @@ 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": {"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 +1589,31 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+SELECT (jb).b FROM test_jsonb_dot_notation;
+SELECT (jb).c FROM test_jsonb_dot_notation;
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[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).a.x.y FROM test_jsonb_dot_notation;
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+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;
-- 
2.39.5 (Apple Git-154)



  [application/octet-stream] v10-0007-Allow-wild-card-member-access-for-jsonb.patch (18.9K, ../CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com/9-v10-0007-Allow-wild-card-member-access-for-jsonb.patch)
  download | inline diff:
From a0e2790c6d724aa27d78f8d3b7abea189d3440cf Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:26 +0300
Subject: [PATCH v10 7/7] Allow wild card member access for jsonb

---
 src/backend/parser/gram.y           |   2 +
 src/backend/parser/parse_expr.c     |  34 +++--
 src/backend/parser/parse_target.c   |  67 +++++++---
 src/backend/utils/adt/ruleutils.c   |   6 +-
 src/include/parser/parse_expr.h     |   3 +
 src/test/regress/expected/jsonb.out | 192 +++++++++++++++++++++++++++-
 src/test/regress/sql/jsonb.sql      |  31 +++++
 7 files changed, 299 insertions(+), 36 deletions(-)

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d99c9355c6..041968c35db 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -18968,6 +18968,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)
@@ -18978,6 +18979,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 8ea51176196..512ac5b4970 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -74,7 +74,6 @@ static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref);
 static Node *transformWholeRowRef(ParseState *pstate,
 								  ParseNamespaceItem *nsitem,
 								  int sublevels_up, int location);
-static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
 static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
 static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
 static Node *transformJsonObjectConstructor(ParseState *pstate,
@@ -158,7 +157,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			break;
 
 		case T_A_Indirection:
-			result = transformIndirection(pstate, (A_Indirection *) expr);
+			result = transformIndirection(pstate, (A_Indirection *) expr, NULL);
 			break;
 
 		case T_A_ArrayExpr:
@@ -432,8 +431,9 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname,
 	}
 }
 
-static Node *
-transformIndirection(ParseState *pstate, A_Indirection *ind)
+Node *
+transformIndirection(ParseState *pstate, A_Indirection *ind,
+					 bool *trailing_star_expansion)
 {
 	Node	   *last_srf = pstate->p_last_srf;
 	Node	   *result = transformExprRecurse(pstate, ind->arg);
@@ -454,12 +454,7 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
 		if (IsA(n, A_Indices))
 			subscripts = lappend(subscripts, n);
 		else if (IsA(n, A_Star))
-		{
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("row expansion via \"*\" is not supported here"),
-					 parser_errposition(pstate, location)));
-		}
+			subscripts = lappend(subscripts, n);
 		else
 		{
 			Assert(IsA(n, String));
@@ -491,7 +486,21 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
 
 			n = linitial(subscripts);
 
-			if (!IsA(n, String))
+			if (IsA(n, A_Star))
+			{
+				/* Success, if trailing star expansion is allowed */
+				if (trailing_star_expansion && list_length(subscripts) == 1)
+				{
+					*trailing_star_expansion = true;
+					return result;
+				}
+
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("row expansion via \"*\" is not supported here"),
+						 parser_errposition(pstate, location)));
+			}
+			else if (!IsA(n, String))
 				ereport(ERROR,
 						(errcode(ERRCODE_DATATYPE_MISMATCH),
 						 errmsg("cannot subscript type %s because it does not support subscripting",
@@ -517,6 +526,9 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
 		result = newresult;
 	}
 
+	if (trailing_star_expansion)
+		*trailing_star_expansion = false;
+
 	return result;
 }
 
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 3ef5897f2eb..141fc1dfeb1 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -48,7 +48,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate,
 static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
 								 bool make_target_entry);
 static List *ExpandAllTables(ParseState *pstate, int location);
-static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
+static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
 								   bool make_target_entry, ParseExprKind exprKind);
 static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem,
 							   int sublevels_up, int location,
@@ -134,6 +134,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
 	foreach(o_target, targetlist)
 	{
 		ResTarget  *res = (ResTarget *) lfirst(o_target);
+		Node	   *transformed = NULL;
 
 		/*
 		 * Check for "something.*".  Depending on the complexity of the
@@ -162,13 +163,19 @@ transformTargetList(ParseState *pstate, List *targetlist,
 
 				if (IsA(llast(ind->indirection), A_Star))
 				{
-					/* It is something.*, expand into multiple items */
-					p_target = list_concat(p_target,
-										   ExpandIndirectionStar(pstate,
-																 ind,
-																 true,
-																 exprKind));
-					continue;
+					Node	   *columns = ExpandIndirectionStar(pstate,
+																ind,
+																true,
+																exprKind);
+
+					if (IsA(columns, List))
+					{
+						/* It is something.*, expand into multiple items */
+						p_target = list_concat(p_target, (List *) columns);
+						continue;
+					}
+
+					transformed = (Node *) columns;
 				}
 			}
 		}
@@ -180,7 +187,7 @@ transformTargetList(ParseState *pstate, List *targetlist,
 		p_target = lappend(p_target,
 						   transformTargetEntry(pstate,
 												res->val,
-												NULL,
+												transformed,
 												exprKind,
 												res->name,
 												false));
@@ -251,10 +258,15 @@ transformExpressionList(ParseState *pstate, List *exprlist,
 
 			if (IsA(llast(ind->indirection), A_Star))
 			{
-				/* It is something.*, expand into multiple items */
-				result = list_concat(result,
-									 ExpandIndirectionStar(pstate, ind,
-														   false, exprKind));
+				Node	   *cols = ExpandIndirectionStar(pstate, ind,
+														 false, exprKind);
+
+				if (!cols || IsA(cols, List))
+					/* It is something.*, expand into multiple items */
+					result = list_concat(result, (List *) cols);
+				else
+					result = lappend(result, cols);
+
 				continue;
 			}
 		}
@@ -1345,22 +1357,30 @@ ExpandAllTables(ParseState *pstate, int location)
  * For robustness, we use a separate "make_target_entry" flag to control
  * this rather than relying on exprKind.
  */
-static List *
+static Node *
 ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind,
 					  bool make_target_entry, ParseExprKind exprKind)
 {
 	Node	   *expr;
+	ParseExprKind sv_expr_kind;
+	bool		trailing_star_expansion = false;
+
+	/* Save and restore identity of expression type we're parsing */
+	Assert(exprKind != EXPR_KIND_NONE);
+	sv_expr_kind = pstate->p_expr_kind;
+	pstate->p_expr_kind = exprKind;
 
 	/* Strip off the '*' to create a reference to the rowtype object */
-	ind = copyObject(ind);
-	ind->indirection = list_truncate(ind->indirection,
-									 list_length(ind->indirection) - 1);
+	expr = transformIndirection(pstate, ind, &trailing_star_expansion);
+
+	pstate->p_expr_kind = sv_expr_kind;
 
-	/* And transform that */
-	expr = transformExpr(pstate, (Node *) ind, exprKind);
+	/* '*' was consumed by generic type subscripting */
+	if (!trailing_star_expansion)
+		return expr;
 
 	/* Expand the rowtype expression into individual fields */
-	return ExpandRowReference(pstate, expr, make_target_entry);
+	return (Node *) ExpandRowReference(pstate, expr, make_target_entry);
 }
 
 /*
@@ -1785,13 +1805,18 @@ FigureColnameInternal(Node *node, char **name)
 				char	   *fname = NULL;
 				ListCell   *l;
 
-				/* find last field name, if any, ignoring "*" and subscripts */
+				/*
+				 * find last field name, if any, ignoring subscripts, and use
+				 * '?column?' when there's a trailing '*'.
+				 */
 				foreach(l, ind->indirection)
 				{
 					Node	   *i = lfirst(l);
 
 					if (IsA(i, String))
 						fname = strVal(i);
+					else if (IsA(i, A_Star))
+						fname = "?column?";
 				}
 				if (fname)
 				{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cfae8159a76..1bc292c5c45 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12926,7 +12926,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 480ecb44a94..aab69b4722c 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5980,12 +5980,172 @@ SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
  
 (1 row)
 
+/* wild card member access */
 SELECT (jb).a.* FROM test_jsonb_dot_notation;
-ERROR:  type jsonb is not composite
+                 ?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.**.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;
+                      ^
 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)
+
 SELECT (jb).* FROM test_jsonb_dot_notation;
-ERROR:  type jsonb is not composite
+                                                           ?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)
+
 EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
                   QUERY PLAN                  
 ----------------------------------------------
@@ -6012,3 +6172,29 @@ 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:2].*.b FROM test_jsonb_dot_notation;
+                 QUERY PLAN                 
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+   Output: jb.a.*[1:2].*.b
+(2 rows)
+
+SELECT (jb).a.*[1:2].*.b FROM test_jsonb_dot_notation;
+ b 
+---
+ 
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 55b6c07f462..7d602bd8dac 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1609,7 +1609,34 @@ SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
 SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
 SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
 SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+
+/* 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.**.x FROM test_jsonb_dot_notation; -- not supported
 
 EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
 SELECT (jb).* FROM test_jsonb_dot_notation;
@@ -1617,3 +1644,7 @@ 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:2].*.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.*[1:2].*.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], [email protected]
  Subject: Re: SQL:2023 JSON simplified accessor support
  In-Reply-To: <CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@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