v7-0004-Implement-read-only-dot-notation-for-jsonb-using-.patch

From 53bc1da78b0feb7769c571fbcafb70c2214a9c46 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Sat, 1 Apr 2023 23:17:53 +0300
Subject: [PATCH v7 4/5] Implement read-only dot notation for jsonb using
 jsonpath

---
 src/backend/utils/adt/jsonbsubs.c   | 438 +++++++++++++++++++++++-----
 src/include/nodes/primnodes.h       |   2 +
 src/test/regress/expected/jsonb.out | 265 ++++++++++++++++-
 src/test/regress/sql/jsonb.sql      |  56 ++++
 4 files changed, 670 insertions(+), 91 deletions(-)

diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index a0d38a0fd80..1ececb4efa2 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -15,12 +15,14 @@
 #include "postgres.h"
 
 #include "executor/execExpr.h"
+#include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "nodes/subscripting.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_expr.h"
 #include "utils/builtins.h"
 #include "utils/jsonb.h"
+#include "utils/jsonpath.h"
 
 
 /* SubscriptingRefState.workspace for jsonb subscripting execution */
@@ -30,8 +32,261 @@ typedef struct JsonbSubWorkspace
 	Oid		   *indexOid;		/* OID of coerced subscript expression, could
 								 * be only integer or text */
 	Datum	   *index;			/* Subscript values in Datum format */
+	JsonPath   *jsonpath;		/* jsonpath for dot notation execution */
+	List		vars;			/* jsonpath vars */
 } JsonbSubWorkspace;
 
+static bool
+jsonb_check_jsonpath_needed(List *indirection)
+{
+	ListCell   *lc;
+
+	foreach(lc, indirection)
+	{
+		Node	   *accessor = lfirst(lc);
+
+		if (IsA(accessor, String) ||
+			IsA(accessor, A_Star))
+			return true;
+		else if (IsA(accessor, A_Indices))
+		{
+			A_Indices  *ai = castNode(A_Indices, accessor);
+
+			if (!ai->uidx || ai->lidx)
+			{
+				Assert(ai->is_slice);
+				return true;
+			}
+		}
+		else
+			return true;
+	}
+
+	return false;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+	JsonPathParseItem *v = palloc(sizeof(*v));
+
+	v->type = type;
+	v->next = NULL;
+
+	return v;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item_int(int32 val, List **exprs)
+{
+	JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
+
+	jpi->value.numeric =
+		DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(val)));
+
+	*exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
+									   Int32GetDatum(val), false, true));
+
+	return jpi;
+}
+
+static Oid
+jsonb_subscript_type(Node *expr)
+{
+	if (expr && IsA(expr, String))
+		return TEXTOID;
+
+	return exprType(expr);
+}
+
+static Node *
+coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype)
+{
+	Oid			subExprType = jsonb_subscript_type(subExpr);
+	Oid			targetType = UNKNOWNOID;
+
+	if (subExprType != UNKNOWNOID)
+	{
+		Oid			targets[2] = {numtype, TEXTOID};
+
+		/*
+		 * Jsonb can handle multiple subscript types, but cases when a
+		 * subscript could be coerced to multiple target types must be
+		 * avoided, similar to overloaded functions. It could be
+		 * possibly extend with jsonpath in the future.
+		 */
+		for (int i = 0; i < 2; i++)
+		{
+			if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
+			{
+				/*
+				 * One type has already succeeded, it means there are
+				 * two coercion targets possible, failure.
+				 */
+				if (targetType != UNKNOWNOID)
+					ereport(ERROR,
+							(errcode(ERRCODE_DATATYPE_MISMATCH),
+							 errmsg("subscript type %s is not supported", format_type_be(subExprType)),
+							 errhint("jsonb subscript must be coercible to only one type, integer or text."),
+							 parser_errposition(pstate, exprLocation(subExpr))));
+
+				targetType = targets[i];
+			}
+		}
+
+		/*
+		 * No suitable types were found, failure.
+		 */
+		if (targetType == UNKNOWNOID)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("subscript type %s is not supported", format_type_be(subExprType)),
+					 errhint("jsonb subscript must be coercible to either integer or text."),
+					 parser_errposition(pstate, exprLocation(subExpr))));
+	}
+	else
+		targetType = TEXTOID;
+
+	/*
+	 * We known from can_coerce_type that coercion will succeed, so
+	 * coerce_type could be used. Note the implicit coercion context,
+	 * which is required to handle subscripts of different types,
+	 * similar to overloaded functions.
+	 */
+	subExpr = coerce_type(pstate,
+						  subExpr, subExprType,
+						  targetType, -1,
+						  COERCION_IMPLICIT,
+						  COERCE_IMPLICIT_CAST,
+						  -1);
+	if (subExpr == NULL)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("jsonb subscript must have text type"),
+				 parser_errposition(pstate, exprLocation(subExpr))));
+
+	return subExpr;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+{
+	JsonPathParseItem *jpi;
+
+	expr = transformExpr(pstate, expr, pstate->p_expr_kind);
+
+	if (IsA(expr, Const))
+	{
+		Const	   *cnst = (Const *) expr;
+
+		if (cnst->consttype == INT4OID && !cnst->constisnull)
+		{
+			int32		val = DatumGetInt32(cnst->constvalue);
+
+			return make_jsonpath_item_int(val, exprs);
+		}
+	}
+
+	*exprs = lappend(*exprs, coerce_jsonpath_subscript(pstate, expr, NUMERICOID));
+
+	jpi = make_jsonpath_item(jpiVariable);
+	jpi->value.string.val = psprintf("%d", list_length(*exprs));
+	jpi->value.string.len = strlen(jpi->value.string.val);
+
+	return jpi;
+}
+
+static Node *
+jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection,
+							  List **uexprs, List **lexprs)
+{
+	JsonPathParseResult jpres;
+	JsonPathParseItem *path = make_jsonpath_item(jpiRoot);
+	ListCell   *lc;
+	Datum		jsp;
+	int			pathlen = 0;
+
+	*uexprs = NIL;
+	*lexprs = NIL;
+
+	jpres.expr = path;
+	jpres.lax = true;
+
+	foreach(lc, *indirection)
+	{
+		Node	   *accessor = lfirst(lc);
+		JsonPathParseItem *jpi;
+
+		if (IsA(accessor, String))
+		{
+			char	   *field = strVal(accessor);
+
+			jpi = make_jsonpath_item(jpiKey);
+			jpi->value.string.val = field;
+			jpi->value.string.len = strlen(field);
+
+			*uexprs = lappend(*uexprs, accessor);
+		}
+		else if (IsA(accessor, A_Star))
+		{
+			jpi = make_jsonpath_item(jpiAnyKey);
+
+			*uexprs = lappend(*uexprs, NULL);
+		}
+		else if (IsA(accessor, A_Indices))
+		{
+			A_Indices  *ai = castNode(A_Indices, accessor);
+
+			jpi = make_jsonpath_item(jpiIndexArray);
+			jpi->value.array.nelems = 1;
+			jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+			if (ai->is_slice)
+			{
+				while (list_length(*lexprs) < list_length(*uexprs))
+					*lexprs = lappend(*lexprs, NULL);
+
+				if (ai->lidx)
+					jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->lidx, lexprs);
+				else
+					jpi->value.array.elems[0].from = make_jsonpath_item_int(0, lexprs);
+
+				if (ai->uidx)
+					jpi->value.array.elems[0].to = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+				else
+				{
+					jpi->value.array.elems[0].to = make_jsonpath_item(jpiLast);
+					*uexprs = lappend(*uexprs, NULL);
+				}
+			}
+			else
+			{
+				Assert(ai->uidx && !ai->lidx);
+				jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+				jpi->value.array.elems[0].to = NULL;
+			}
+		}
+		else
+			break;
+
+		/* append path item */
+		path->next = jpi;
+		path = jpi;
+		pathlen++;
+	}
+
+	if (*lexprs)
+	{
+		while (list_length(*lexprs) < list_length(*uexprs))
+			*lexprs = lappend(*lexprs, NULL);
+	}
+
+	*indirection = list_delete_first_n(*indirection, pathlen);
+
+	jsp = jsonPathFromParseResult(&jpres, 0, NULL);
+
+	return (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false);
+}
 
 /*
  * Finish parse analysis of a SubscriptingRef expression for a jsonb.
@@ -49,19 +304,35 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 	List	   *upperIndexpr = NIL;
 	ListCell   *idx;
 
+	/* Determine the result type of the subscripting operation; always jsonb */
+	sbsref->refrestype = JSONBOID;
+	sbsref->reftypmod = -1;
+
+	if (jsonb_check_jsonpath_needed(*indirection))
+	{
+		sbsref->refprivate =
+			jsonb_subscript_make_jsonpath(pstate, indirection,
+										  &sbsref->refupperindexpr,
+										  &sbsref->reflowerindexpr);
+		return;
+	}
+
 	/*
 	 * Transform and convert the subscript expressions. Jsonb subscripting
 	 * does not support slices, look only and the upper index.
 	 */
 	foreach(idx, *indirection)
 	{
+		Node	   *i = lfirst(idx);
 		A_Indices  *ai;
 		Node	   *subExpr;
 
-		if (!IsA(lfirst(idx), A_Indices))
+		Assert(IsA(i, A_Indices));
+
+		if (!IsA(i, A_Indices))
 			break;
 
-		ai = lfirst_node(A_Indices, idx);
+		ai = castNode(A_Indices, i);
 
 		if (isSlice)
 		{
@@ -75,71 +346,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 
 		if (ai->uidx)
 		{
-			Oid			subExprType = InvalidOid,
-						targetType = UNKNOWNOID;
-
 			subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
-			subExprType = exprType(subExpr);
-
-			if (subExprType != UNKNOWNOID)
-			{
-				Oid			targets[2] = {INT4OID, TEXTOID};
-
-				/*
-				 * Jsonb can handle multiple subscript types, but cases when a
-				 * subscript could be coerced to multiple target types must be
-				 * avoided, similar to overloaded functions. It could be
-				 * possibly extend with jsonpath in the future.
-				 */
-				for (int i = 0; i < 2; i++)
-				{
-					if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
-					{
-						/*
-						 * One type has already succeeded, it means there are
-						 * two coercion targets possible, failure.
-						 */
-						if (targetType != UNKNOWNOID)
-							ereport(ERROR,
-									(errcode(ERRCODE_DATATYPE_MISMATCH),
-									 errmsg("subscript type %s is not supported", format_type_be(subExprType)),
-									 errhint("jsonb subscript must be coercible to only one type, integer or text."),
-									 parser_errposition(pstate, exprLocation(subExpr))));
-
-						targetType = targets[i];
-					}
-				}
-
-				/*
-				 * No suitable types were found, failure.
-				 */
-				if (targetType == UNKNOWNOID)
-					ereport(ERROR,
-							(errcode(ERRCODE_DATATYPE_MISMATCH),
-							 errmsg("subscript type %s is not supported", format_type_be(subExprType)),
-							 errhint("jsonb subscript must be coercible to either integer or text."),
-							 parser_errposition(pstate, exprLocation(subExpr))));
-			}
-			else
-				targetType = TEXTOID;
-
-			/*
-			 * We known from can_coerce_type that coercion will succeed, so
-			 * coerce_type could be used. Note the implicit coercion context,
-			 * which is required to handle subscripts of different types,
-			 * similar to overloaded functions.
-			 */
-			subExpr = coerce_type(pstate,
-								  subExpr, subExprType,
-								  targetType, -1,
-								  COERCION_IMPLICIT,
-								  COERCE_IMPLICIT_CAST,
-								  -1);
-			if (subExpr == NULL)
-				ereport(ERROR,
-						(errcode(ERRCODE_DATATYPE_MISMATCH),
-						 errmsg("jsonb subscript must have text type"),
-						 parser_errposition(pstate, exprLocation(subExpr))));
+			subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID);
 		}
 		else
 		{
@@ -161,10 +369,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
 	sbsref->refupperindexpr = upperIndexpr;
 	sbsref->reflowerindexpr = NIL;
 
-	/* Determine the result type of the subscripting operation; always jsonb */
-	sbsref->refrestype = JSONBOID;
-	sbsref->reftypmod = -1;
-
 	/* Remove processed elements */
 	if (upperIndexpr)
 		*indirection = list_delete_first_n(*indirection, list_length(upperIndexpr));
@@ -219,7 +423,7 @@ jsonb_subscript_check_subscripts(ExprState *state,
 			 * For jsonb fetch and assign functions we need to provide path in
 			 * text format. Convert if it's not already text.
 			 */
-			if (workspace->indexOid[i] == INT4OID)
+			if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID)
 			{
 				Datum		datum = sbsrefstate->upperindex[i];
 				char	   *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
@@ -247,17 +451,44 @@ jsonb_subscript_fetch(ExprState *state,
 {
 	SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
 	JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
-	Jsonb	   *jsonbSource;
 
 	/* Should not get here if source jsonb (or any subscript) is null */
 	Assert(!(*op->resnull));
 
-	jsonbSource = DatumGetJsonbP(*op->resvalue);
-	*op->resvalue = jsonb_get_element(jsonbSource,
-									  workspace->index,
-									  sbsrefstate->numupper,
-									  op->resnull,
-									  false);
+	if (workspace->jsonpath)
+	{
+		bool		empty = false;
+		bool		error = false;
+		List	   *vars = &workspace->vars;
+		ListCell   *lc;
+
+		/* copy computed variable values */
+		foreach(lc, vars)
+		{
+			JsonPathVariable *var = lfirst(lc);
+			int			i = foreach_current_index(lc);
+
+			var->value = workspace->index[i];
+			var->isnull = false;
+		}
+
+		*op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath,
+									  JSW_CONDITIONAL,
+									  &empty, &error, vars,
+									  NULL);
+
+		*op->resnull = empty || error;
+	}
+	else
+	{
+		Jsonb	   *jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+		*op->resvalue = jsonb_get_element(jsonbSource,
+										  workspace->index,
+										  sbsrefstate->numupper,
+										  op->resnull,
+										  false);
+	}
 }
 
 /*
@@ -367,12 +598,57 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
 	ListCell   *lc;
 	int			nupper = sbsref->refupperindexpr->length;
 	char	   *ptr;
+	bool		useJsonpath = sbsref->refprivate != NULL;
+	JsonPathVariable *vars;
+	int			nvars = useJsonpath ? nupper : 0;
 
 	/* Allocate type-specific workspace with space for per-subscript data */
-	workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) +
+	workspace = palloc0(MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) + nvars * sizeof(ListCell)) +
+						MAXALIGN(nvars * sizeof(*vars) + nvars * 16) +
 						nupper * (sizeof(Datum) + sizeof(Oid)));
 	workspace->expectArray = false;
-	ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+	ptr = ((char *) workspace) +
+		MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) +
+				 nvars * sizeof(ListCell));
+
+	if (!useJsonpath)
+		workspace->jsonpath = NULL;
+	else
+	{
+		workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refprivate)->constvalue);
+
+		vars = (JsonPathVariable *) ptr;
+		ptr += MAXALIGN(nvars * sizeof(*vars));
+
+		workspace->vars.type = T_List;
+		workspace->vars.length = nvars;
+		workspace->vars.max_length = nvars;
+		workspace->vars.elements = &workspace->vars.initial_elements[0];
+
+		for (int i = 0; i < nvars; i++)
+		{
+			Node	   *expr = list_nth(sbsref->refupperindexpr, i);
+
+			workspace->vars.elements[i].ptr_value = &vars[i];
+
+			if (expr && IsA(expr, String))
+			{
+				vars[i].typid = TEXTOID;
+				vars[i].typmod = -1;
+			}
+			else
+			{
+				vars[i].typid = exprType(expr);
+				vars[i].typmod = exprTypmod(expr);
+			}
+
+			vars[i].name = ptr;
+			snprintf(ptr, 16, "%d", i + 1);
+			vars[i].namelen = strlen(ptr);
+
+			ptr += 16;
+		}
+	}
 
 	/*
 	 * This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
@@ -390,7 +666,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
 		Node	   *expr = lfirst(lc);
 		int			i = foreach_current_index(lc);
 
-		workspace->indexOid[i] = exprType(expr);
+		workspace->indexOid[i] = jsonb_subscript_type(expr);
 	}
 
 	/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 839e71d52f4..4b1e5de98e5 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -718,6 +718,8 @@ typedef struct SubscriptingRef
 	Expr	   *refexpr;
 	/* expression for the source value, or NULL if fetch */
 	Expr	   *refassgnexpr;
+	/* private expression */
+	Node	   *refprivate;
 } SubscriptingRef;
 
 /*
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf2..14123929475 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4939,6 +4939,12 @@ select ('123'::jsonb)['a'];
  
 (1 row)
 
+select ('123'::jsonb).a;
+ a 
+---
+ 
+(1 row)
+
 select ('123'::jsonb)[0];
  jsonb 
 -------
@@ -4957,6 +4963,12 @@ select ('{"a": 1}'::jsonb)['a'];
  1
 (1 row)
 
+select ('{"a": 1}'::jsonb).a;
+ a 
+---
+ 1
+(1 row)
+
 select ('{"a": 1}'::jsonb)[0];
  jsonb 
 -------
@@ -4969,6 +4981,12 @@ select ('{"a": 1}'::jsonb)['not_exist'];
  
 (1 row)
 
+select ('{"a": 1}'::jsonb)."not_exist";
+ not_exist 
+-----------
+ 
+(1 row)
+
 select ('{"a": 1}'::jsonb)[NULL];
  jsonb 
 -------
@@ -4981,6 +4999,12 @@ select ('[1, "2", null]'::jsonb)['a'];
  
 (1 row)
 
+select ('[1, "2", null]'::jsonb).a;
+ a 
+---
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[0];
  jsonb 
 -------
@@ -4993,6 +5017,12 @@ select ('[1, "2", null]'::jsonb)['1'];
  "2"
 (1 row)
 
+select ('[1, "2", null]'::jsonb)."1";
+ 1 
+---
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[1.0];
 ERROR:  subscript type numeric is not supported
 LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
@@ -5022,6 +5052,12 @@ select ('[1, "2", null]'::jsonb)[1]['a'];
  
 (1 row)
 
+select ('[1, "2", null]'::jsonb)[1].a;
+ a 
+---
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[1][0];
  jsonb 
 -------
@@ -5034,73 +5070,143 @@ select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
  "c"
 (1 row)
 
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
+  b  
+-----
+ "c"
+(1 row)
+
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
    jsonb   
 -----------
  [1, 2, 3]
 (1 row)
 
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d;
+     d     
+-----------
+ [1, 2, 3]
+(1 row)
+
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
  jsonb 
 -------
  2
 (1 row)
 
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1];
+ d 
+---
+ 2
+(1 row)
+
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
  jsonb 
 -------
  
 (1 row)
 
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+ d 
+---
+ 
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
+ a 
+---
+ 
+(1 row)
+
 select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
      jsonb     
 ---------------
  {"a2": "aaa"}
 (1 row)
 
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1;
+      a1       
+---------------
+ {"a2": "aaa"}
+(1 row)
+
 select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
  jsonb 
 -------
  "aaa"
 (1 row)
 
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2;
+  a2   
+-------
+ "aaa"
+(1 row)
+
 select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
  jsonb 
 -------
  
 (1 row)
 
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
+ a3 
+----
+ 
+(1 row)
+
 select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
          jsonb         
 -----------------------
  ["aaa", "bbb", "ccc"]
 (1 row)
 
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1;
+          b1           
+-----------------------
+ ["aaa", "bbb", "ccc"]
+(1 row)
+
 select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
  jsonb 
 -------
  "ccc"
 (1 row)
 
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
+  b1   
+-------
+ "ccc"
+(1 row)
+
 -- slices are not supported
 select ('{"a": 1}'::jsonb)['a':'b'];
-ERROR:  jsonb subscript does not support slices
-LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
-                                       ^
+ jsonb 
+-------
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[1:2];
-ERROR:  jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[1:2];
-                                           ^
+    jsonb    
+-------------
+ ["2", null]
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[:2];
 ERROR:  jsonb subscript does not support slices
 LINE 1: select ('[1, "2", null]'::jsonb)[:2];
                                           ^
 select ('[1, "2", null]'::jsonb)[1:];
-ERROR:  jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[1:];
-                                         ^
+    jsonb    
+-------------
+ ["2", null]
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[:];
-ERROR:  jsonb subscript does not support slices
+     jsonb      
+----------------
+ [1, "2", null]
+(1 row)
+
 create TEMP TABLE test_jsonb_subscript (
        id int,
        test_json jsonb
@@ -5781,3 +5887,142 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
  12345
 (1 row)
 
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+ERROR:  type jsonb is not composite
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ERROR:  type jsonb is not composite
+SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+ERROR:  missing FROM-clause entry for table "t"
+LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;
+                ^
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+ERROR:  type jsonb is not composite
+SELECT (jb).a FROM test_jsonb_dot_notation;
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).a FROM test_jsonb_dot_notation;
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).b FROM test_jsonb_dot_notation;
+                         b                         
+---------------------------------------------------
+ [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]
+(1 row)
+
+SELECT (jb).c FROM test_jsonb_dot_notation;
+ c 
+---
+ 
+(1 row)
+
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+     b      
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
+                        ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+                        ^
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ERROR:  row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+                 ^
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+ERROR:  row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+                 ^
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+ERROR:  row expansion via "*" is not supported here
+LINE 1: SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+                 ^
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x FROM test_jsonb_dot_notation;
+                        ^
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+                          ^
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+                            ^
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
+                 QUERY PLAN                 
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+   Output: jb.a
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+                 QUERY PLAN                 
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+   Output: jb.a[1]
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_...
+                                                          ^
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
+ERROR:  improper use of "*" at or near "FROM"
+LINE 1: ... (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_...
+                                                             ^
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2d..4b49d59222b 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1286,30 +1286,46 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
 
 -- jsonb subscript
 select ('123'::jsonb)['a'];
+select ('123'::jsonb).a;
 select ('123'::jsonb)[0];
 select ('123'::jsonb)[NULL];
 select ('{"a": 1}'::jsonb)['a'];
+select ('{"a": 1}'::jsonb).a;
 select ('{"a": 1}'::jsonb)[0];
 select ('{"a": 1}'::jsonb)['not_exist'];
+select ('{"a": 1}'::jsonb)."not_exist";
 select ('{"a": 1}'::jsonb)[NULL];
 select ('[1, "2", null]'::jsonb)['a'];
+select ('[1, "2", null]'::jsonb).a;
 select ('[1, "2", null]'::jsonb)[0];
 select ('[1, "2", null]'::jsonb)['1'];
+select ('[1, "2", null]'::jsonb)."1";
 select ('[1, "2", null]'::jsonb)[1.0];
 select ('[1, "2", null]'::jsonb)[2];
 select ('[1, "2", null]'::jsonb)[3];
 select ('[1, "2", null]'::jsonb)[-2];
 select ('[1, "2", null]'::jsonb)[1]['a'];
+select ('[1, "2", null]'::jsonb)[1].a;
 select ('[1, "2", null]'::jsonb)[1][0];
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d;
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1];
 select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
 select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1;
 select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2;
 select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
 select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1;
 select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
 
 -- slices are not supported
 select ('{"a": 1}'::jsonb)['a':'b'];
@@ -1572,3 +1588,43 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+
+SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (jb).* FROM test_jsonb_dot_notation t;
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+SELECT (jb).* FROM test_jsonb_dot_notation;
+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+SELECT (t.jb).* FROM test_jsonb_dot_notation t;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb).b FROM test_jsonb_dot_notation;
+SELECT (jb).c FROM test_jsonb_dot_notation;
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.b FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.x FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.y FROM test_jsonb_dot_notation;
+SELECT (jb).a.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+SELECT (jb).*.x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*).x FROM test_jsonb_dot_notation t;
+SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t;
+SELECT (jb).*.x FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.y FROM test_jsonb_dot_notation;
+SELECT (jb).*.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.y FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation;
-- 
2.39.5 (Apple Git-154)