public inbox for [email protected]  

help / color / mirror / Atom feed

From: 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]>
Cc: [email protected]
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Thu, 27 Feb 2025 09:46:48 -0600
Message-ID: <CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@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>

Hello hackers,

I’ve fixed the compilation failure for hstore and updated the patches.
In this version, I’ve further cleaned up the code and added more
comments. I hope this helps!

Summary of changes:

v8-0001 through v8-0005:
Refactoring and preparatory steps for the actual implementation.

v8-0006 (Implement read-only dot notation for JSONB):
I removed the vars field (introduced in v7) from JsonbSubWorkspace
after realizing that JsonPathVariable is not actually needed for
dot-notation.

v8-0007 (Allow wildcard member access for JSONB):
I'm aware that the #if 0 in check_indirection() is not ideal. I
haven’t removed it yet because I’m still reviewing other cases—beyond
our JSONB simplified accessor use case—where this check should remain
strict. I’ll post an additional patch to address this.

Looking forward to comments and feedback!

Thanks,
Alex


Attachments:

  [application/octet-stream] v8-0002-Pass-field-accessors-to-generic-subscripting.patch (9.8K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/3-v8-0002-Pass-field-accessors-to-generic-subscripting.patch)
  download | inline diff:
From 282521568617a704df3e09279f568f31b1871411 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 16:21:20 +0300
Subject: [PATCH v8 2/7] Pass field accessors to generic subscripting

---
 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] v8-0004-Extract-coerce_jsonpath_subscript.patch (5.4K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/4-v8-0004-Extract-coerce_jsonpath_subscript.patch)
  download | inline diff:
From d0db105c2abc7059f5765eb490b6102151162691 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Wed, 26 Feb 2025 13:03:27 -0600
Subject: [PATCH v8 4/7] Extract coerce_jsonpath_subscript()

---
 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] v8-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch (8.3K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/5-v8-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch)
  download | inline diff:
From 0dc5c192db78e352201f5a778eb0f545835fdb56 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Fri, 14 Oct 2022 15:35:22 +0300
Subject: [PATCH v8 1/7] Allow transformation only of a sublist of subscripts

---
 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] v8-0005-Eanble-String-node-as-field-accessors-in-generic-.patch (7.5K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/6-v8-0005-Eanble-String-node-as-field-accessors-in-generic-.patch)
  download | inline diff:
From 7a1ee3d55222625976854c53d985a823cd018407 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 16:21:20 +0300
Subject: [PATCH v8 5/7] Eanble String node as field accessors in generic
 subscripting

Field accessors are represented 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] v8-0006-Implement-read-only-dot-notation-for-jsonb.patch (26.1K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/7-v8-0006-Implement-read-only-dot-notation-for-jsonb.patch)
  download | inline diff:
From 82a22db3f286764934ff5fa6b611a6b7af2c0fc8 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:17:53 +0300
Subject: [PATCH v8 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   | 293 ++++++++++++++++++++++++++--
 src/include/nodes/primnodes.h       |   7 +
 src/test/regress/expected/jsonb.out | 239 ++++++++++++++++++++++-
 src/test/regress/sql/jsonb.sql      |  43 ++++
 4 files changed, 556 insertions(+), 26 deletions(-)

diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 3ffe40cfa40..409cb20539c 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,228 @@ 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 following 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 +357,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 +419,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 +473,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 +501,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 +636,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 +644,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 +663,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..0edd19bed9c 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,116 @@ 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).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..37d40e76610 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,29 @@ 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).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] v8-0003-Export-jsonPathFromParseResult.patch (2.4K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/8-v8-0003-Export-jsonPathFromParseResult.patch)
  download | inline diff:
From 2d1b29146ee6b4caa21dfef1475bf133b6322105 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:55 +0300
Subject: [PATCH v8 3/7] Export jsonPathFromParseResult()

---
 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] v8-0007-Allow-wild-card-member-access-for-jsonb.patch (18.1K, ../CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com/9-v8-0007-Allow-wild-card-member-access-for-jsonb.patch)
  download | inline diff:
From 47c48c12204e3ea4dc172004d54a94e5c773b9df Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Sat, 1 Apr 2023 23:15:26 +0300
Subject: [PATCH v8 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   |  60 ++++++---
 src/backend/utils/adt/ruleutils.c   |   6 +-
 src/include/parser/parse_expr.h     |   3 +
 src/test/regress/expected/jsonb.out | 188 +++++++++++++++++++++++++++-
 src/test/regress/sql/jsonb.sql      |  30 +++++
 7 files changed, 288 insertions(+), 35 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..64e0f01c0f0 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);
 }
 
 /*
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 0edd19bed9c..91aa0511a18 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5970,12 +5970,168 @@ 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
+                     a                     
+-------------------------------------------
+ ["c", "d", "f", {"y": "yyy", "z": "zzz"}]
+(1 row)
+
+SELECT (jb).* FROM test_jsonb_dot_notation;
+                                                              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;
+                                                              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;
+                                                              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;
+                                                              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;
+                                                              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                     
+-------------------------------------------
+ ["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;
+       a        
+----------------
+ ["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;
+              x               
+------------------------------
+ ["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;
+              jb              
+------------------------------
+ ["yyy", "zzz", "YYY", "ZZZ"]
+(1 row)
+
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ jb 
+----
+ 
+(1 row)
+
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+ 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)
+
 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)
+
 EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
                   QUERY PLAN                  
 ----------------------------------------------
@@ -6002,3 +6158,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;
+                     a                     
+-------------------------------------------
+ ["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 37d40e76610..5b44e791849 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1607,7 +1607,33 @@ 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;
 
 EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
 SELECT (jb).* FROM test_jsonb_dot_notation;
@@ -1615,3 +1641,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: <CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@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