v11-0006-Implement-read-only-dot-notation-for-jsonb.patch

From b775394875ac683cb4cbce36863555a72a84db12 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 v11 6/8] 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             | 288 ++++++++++++++++-
 src/include/nodes/primnodes.h                 |   7 +
 .../ecpg/test/expected/sql-sqljson.c          | 102 +++++-
 .../ecpg/test/expected/sql-sqljson.stderr     |  86 +++++
 .../ecpg/test/expected/sql-sqljson.stdout     |   8 +
 src/interfaces/ecpg/test/sql/sqljson.pgc      |  30 ++
 src/test/regress/expected/jsonb.out           | 305 +++++++++++++++++-
 src/test/regress/sql/jsonb.sql                |  85 ++++-
 8 files changed, 877 insertions(+), 34 deletions(-)

diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 3ffe40cfa40..3588a1d062f 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,223 @@ 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
+		{
+			Assert(IsA(accessor, A_Indices));
+
+			if (castNode(A_Indices, accessor)->is_slice)
+				return true;
+		}
+	}
+
+	return false;
+}
+
+/*
+ * Helper functions for constructing JsonPath expressions.
+ *
+ * The make_jsonpath_item_* functions create various types of JsonPathParseItem
+ * nodes, which are used to build JsonPath expressions for jsonb simplified
+ * accessor.
+ */
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+	JsonPathParseItem *v = palloc(sizeof(*v));
+
+	v->type = type;
+	v->next = NULL;
+
+	return v;
+}
+
+static JsonPathParseItem *
+make_jsonpath_item_int(int32 val, List **exprs)
+{
+	JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
+
+	jpi->value.numeric =
+		DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(val)));
+
+	*exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
+									   Int32GetDatum(val), false, true));
+
+	return jpi;
+}
+
+/*
+ * Convert an expression into a JsonPathParseItem.
+ * If the expression is a constant integer, create a direct numeric item.
+ * Otherwise, create a variable reference and add it to the expression list.
+ */
+static JsonPathParseItem *
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+{
+	Const	   *cnst;
+
+	expr = transformExpr(pstate, expr, pstate->p_expr_kind);
+
+	if (!IsA(expr, Const))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("jsonb simplified accessor supports subscripting in const int4, got type: %s",
+						format_type_be(exprType(expr))),
+				 parser_errposition(pstate, exprLocation(expr))));
+
+	cnst = (Const *) expr;
+
+	if (cnst->consttype == INT4OID && !cnst->constisnull)
+	{
+		int32		val = DatumGetInt32(cnst->constvalue);
+
+		return make_jsonpath_item_int(val, exprs);
+	}
+
+	ereport(ERROR,
+			(errcode(ERRCODE_DATATYPE_MISMATCH),
+			 errmsg("jsonb simplified accessor supports subscripting in type: INT4, got type: %s",
+					format_type_be(cnst->consttype)),
+			 parser_errposition(pstate, exprLocation(expr))));
+}
+
+/*
+ * jsonb_subscript_make_jsonpath
+ *
+ * Constructs a JsonPath expression from a list of indirections.
+ * This function is used when jsonb subscripting involves dot notation,
+ * wildcards (*), or slice-based subscripting, requiring JsonPath-based
+ * evaluation.
+ *
+ * The function modifies the indirection list in place, removing processed
+ * elements as it converts them into JsonPath components, as follows:
+ * - String keys (dot notation) -> jpiKey items.
+ * - Wildcard (*) -> jpiAnyKey item.
+ * - Array indices and slices -> jpiIndexArray items.
+ *
+ * Parameters:
+ * - pstate: Parse state context.
+ * - indirection: List of subscripting expressions (modified in-place).
+ * - uexprs: Upper-bound expressions extracted from subscripts.
+ * - lexprs: Lower-bound expressions extracted from subscripts.
+ * Returns:
+ * - a Const node containing the transformed JsonPath expression.
+ */
+static Node *
+jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection,
+							  List **uexprs, List **lexprs)
+{
+	JsonPathParseResult jpres;
+	JsonPathParseItem *path = make_jsonpath_item(jpiRoot);
+	ListCell   *lc;
+	Datum		jsp;
+	int			pathlen = 0;
+
+	*uexprs = NIL;
+	*lexprs = NIL;
+
+	jpres.expr = path;
+	jpres.lax = true;
+
+	foreach(lc, *indirection)
+	{
+		Node	   *accessor = lfirst(lc);
+		JsonPathParseItem *jpi;
+
+		if (IsA(accessor, String))
+		{
+			char	   *field = strVal(accessor);
+
+			jpi = make_jsonpath_item(jpiKey);
+			jpi->value.string.val = field;
+			jpi->value.string.len = strlen(field);
+
+			*uexprs = lappend(*uexprs, accessor);
+		}
+		else if (IsA(accessor, A_Star))
+		{
+			jpi = make_jsonpath_item(jpiAnyKey);
+
+			*uexprs = lappend(*uexprs, NULL);
+		}
+		else if (IsA(accessor, A_Indices))
+		{
+			A_Indices  *ai = castNode(A_Indices, accessor);
+
+			jpi = make_jsonpath_item(jpiIndexArray);
+			jpi->value.array.nelems = 1;
+			jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+			if (ai->is_slice)
+			{
+				while (list_length(*lexprs) < list_length(*uexprs))
+					*lexprs = lappend(*lexprs, NULL);
+
+				if (ai->lidx)
+					jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->lidx, lexprs);
+				else
+					jpi->value.array.elems[0].from = make_jsonpath_item_int(0, lexprs);
+
+				if (ai->uidx)
+					jpi->value.array.elems[0].to = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+				else
+				{
+					jpi->value.array.elems[0].to = make_jsonpath_item(jpiLast);
+					*uexprs = lappend(*uexprs, NULL);
+				}
+			}
+			else
+			{
+				Assert(ai->uidx && !ai->lidx);
+				jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->uidx, uexprs);
+				jpi->value.array.elems[0].to = NULL;
+			}
+		}
+		else
+			break;
+
+		/* append path item */
+		path->next = jpi;
+		path = jpi;
+		pathlen++;
+	}
+
+	if (*lexprs)
+	{
+		while (list_length(*lexprs) < list_length(*uexprs))
+			*lexprs = lappend(*lexprs, NULL);
+	}
+
+	*indirection = list_delete_first_n(*indirection, pathlen);
+
+	jsp = jsonPathFromParseResult(&jpres, 0, NULL);
+
+	return (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false);
+}
+
 /*
  * Finish parse analysis of a SubscriptingRef expression for a jsonb.
  *
@@ -126,19 +352,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 +414,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 +468,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 +496,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 +631,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 +639,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 +658,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/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index 39221f9ea5d..e1e2b1e03f0 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -417,12 +417,112 @@ if (sqlca.sqlcode < 0) sqlprint();}
 	  for (int i = 0; i < sizeof(is_json); i++)
 		  printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
 
-  { ECPGdisconnect(__LINE__, "CURRENT");
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . \"a\" )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
 #line 118 "sqljson.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
 #line 118 "sqljson.pgc"
 
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb . \"a\" )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 121 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 121 "sqljson.pgc"
+
+	// error
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 124 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 124 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a . b )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 127 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 127 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( coalesce ( json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . c ) , 'null' ) )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 130 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 130 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 0 ] )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 133 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 133 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 136 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 136 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ : ] )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 139 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 139 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b . x )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 142 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 142 "sqljson.pgc"
+
+	printf("Found json=%s\n", json);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . * )", ECPGt_EOIT, 
+	ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 145 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 145 "sqljson.pgc"
+
+	// error
+
+  { ECPGdisconnect(__LINE__, "CURRENT");
+#line 148 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 148 "sqljson.pgc"
+
 
   return 0;
 }
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
index e55a95dd711..e532a8f44fa 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -268,5 +268,91 @@ SQL error: cannot use type jsonb in RETURNING clause of JSON_SERIALIZE() on line
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no
 [NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 118: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 118: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: query: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 121: bad response - ERROR:  schema "jsonb" does not exist
+LINE 1: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" )
+                                                   ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 3F000 (sqlcode -400): schema "jsonb" does not exist on line 121
+[NO_PID]: sqlca: code: -400, state: 3F000
+SQL error: schema "jsonb" does not exist on line 121
+[NO_PID]: ecpg_execute on line 124: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 124: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 124: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 124: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a . b ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 127: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 127: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: query: select json ( coalesce ( json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . c ) , 'null' ) ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 130: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 130: RESULT: null offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 133: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 133: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 136: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 136: RESULT: [12, {"y": 1}] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 139: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 139: RESULT: [{"x": 1}, {"x": [12, {"y": 1}]}] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b . x ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 142: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 142: RESULT: [1, [12, {"y": 1}]] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 145: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . * ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 145: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 145: bad response - ERROR:  row expansion via "*" is not supported here
+LINE 1: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x...
+                        ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 0A000 (sqlcode -400): row expansion via "*" is not supported here on line 145
+[NO_PID]: sqlca: code: -400, state: 0A000
+SQL error: row expansion via "*" is not supported here on line 145
 [NO_PID]: ecpg_finish: connection ecpg1_regression closed
 [NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
index 83f8df13e5a..bfa93e86d00 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -28,3 +28,11 @@ Found is_json[4]: false
 Found is_json[5]: false
 Found is_json[6]: true
 Found is_json[7]: false
+Found json={"b": 1, "c": 2}
+Found json={"b": 1, "c": 2}
+Found json=1
+Found json=null
+Found json={"x": 1}
+Found json=[12, {"y": 1}]
+Found json=[{"x": 1}, {"x": [12, {"y": 1}]}]
+Found json=[1, [12, {"y": 1}]]
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
index ddcbcc3b3cb..96be3919928 100644
--- a/src/interfaces/ecpg/test/sql/sqljson.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -115,6 +115,36 @@ EXEC SQL END DECLARE SECTION;
 	  for (int i = 0; i < sizeof(is_json); i++)
 		  printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
 
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb)."a") INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON('{"a": {"b": 1, "c": 2}}'::jsonb."a") INTO :json;
+	// error
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a.b) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(COALESCE(JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).c), 'null')) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[0]) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[1].x[0:]) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[:]) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b.x) INTO :json;
+	printf("Found json=%s\n", json);
+
+	EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*) INTO :json;
+	// error
+
   EXEC SQL DISCONNECT;
 
   return 0;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 5a1eb18aba2..91a7b825764 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4989,6 +4989,12 @@ select ('123'::jsonb)['a'];
  
 (1 row)
 
+select ('123'::jsonb).a;
+ a 
+---
+ 
+(1 row)
+
 select ('123'::jsonb)[0];
  jsonb 
 -------
@@ -5001,12 +5007,24 @@ select ('123'::jsonb)[NULL];
  
 (1 row)
 
+select ('123'::jsonb).NULL;
+ null 
+------
+ 
+(1 row)
+
 select ('{"a": 1}'::jsonb)['a'];
  jsonb 
 -------
  1
 (1 row)
 
+select ('{"a": 1}'::jsonb).a;
+ a 
+---
+ 1
+(1 row)
+
 select ('{"a": 1}'::jsonb)[0];
  jsonb 
 -------
@@ -5019,6 +5037,12 @@ select ('{"a": 1}'::jsonb)['not_exist'];
  
 (1 row)
 
+select ('{"a": 1}'::jsonb)."not_exist";
+ not_exist 
+-----------
+ 
+(1 row)
+
 select ('{"a": 1}'::jsonb)[NULL];
  jsonb 
 -------
@@ -5031,6 +5055,12 @@ select ('[1, "2", null]'::jsonb)['a'];
  
 (1 row)
 
+select ('[1, "2", null]'::jsonb).a;
+ a 
+---
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[0];
  jsonb 
 -------
@@ -5043,6 +5073,12 @@ select ('[1, "2", null]'::jsonb)['1'];
  "2"
 (1 row)
 
+select ('[1, "2", null]'::jsonb)."1";
+ 1 
+---
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[1.0];
 ERROR:  subscript type numeric is not supported
 LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
@@ -5072,6 +5108,12 @@ select ('[1, "2", null]'::jsonb)[1]['a'];
  
 (1 row)
 
+select ('[1, "2", null]'::jsonb)[1].a;
+ a 
+---
+ 
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[1][0];
  jsonb 
 -------
@@ -5084,73 +5126,140 @@ 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)
 
--- slices are not supported
-select ('{"a": 1}'::jsonb)['a':'b'];
-ERROR:  jsonb subscript does not support slices
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
+  b1   
+-------
+ "ccc"
+(1 row)
+
+select ('{"a": 1}'::jsonb)['a':'b']; -- fails
+ERROR:  jsonb 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];
-                                          ^
+     jsonb      
+----------------
+ [1, "2", null]
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[1:];
-ERROR:  jsonb subscript does not support slices
-LINE 1: select ('[1, "2", null]'::jsonb)[1:];
-                                         ^
+    jsonb    
+-------------
+ ["2", null]
+(1 row)
+
 select ('[1, "2", null]'::jsonb)[:];
-ERROR:  jsonb subscript does not support slices
+     jsonb      
+----------------
+ [1, "2", null]
+(1 row)
+
 create TEMP TABLE test_jsonb_subscript (
        id int,
        test_json jsonb
@@ -5831,3 +5940,171 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
  12345
 (1 row)
 
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+ERROR:  syntax error at or near "'a'"
+LINE 1: SELECT (jb).'a' FROM test_jsonb_dot_notation;
+                    ^
+SELECT (jb).b FROM test_jsonb_dot_notation;
+                         b                         
+---------------------------------------------------
+ [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]
+(1 row)
+
+SELECT (jb).c FROM test_jsonb_dot_notation;
+ c 
+---
+ 
+(1 row)
+
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+     b      
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+  b  
+-----
+ "c"
+(1 row)
+
+SELECT (jb).a[2:3].b FROM test_jsonb_dot_notation;
+     b      
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[2:].b FROM test_jsonb_dot_notation;
+     b      
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[:2].b FROM test_jsonb_dot_notation;
+  b  
+-----
+ "c"
+(1 row)
+
+SELECT (jb).a[:].b FROM test_jsonb_dot_notation;
+     b      
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
+   y   
+-------
+ "yyy"
+(1 row)
+
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+            x             
+--------------------------
+ {"y": "YYY", "z": "ZZZ"}
+(1 row)
+
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+   z   
+-------
+ "ZZZ"
+(1 row)
+
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+ c 
+---
+ 
+(1 row)
+
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+ERROR:  type jsonb is not composite
+-- explains should work
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+                  QUERY PLAN                  
+----------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation t
+   Output: jb.a
+(2 rows)
+
+SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+                                    a                                    
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+                 QUERY PLAN                 
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+   Output: jb.a[1]
+(2 rows)
+
+SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+ a 
+---
+ 2
+(1 row)
+
+-- jsonb array access in plpgsql
+DO $$
+DECLARE
+  a jsonb := '[1,2,3,4,5,6,7]'::jsonb;
+BEGIN
+  WHILE a IS NOT NULL
+  LOOP
+    RAISE NOTICE '%', a;
+    a := a[2:];
+  END LOOP;
+END
+$$ LANGUAGE plpgsql;
+NOTICE:  [1, 2, 3, 4, 5, 6, 7]
+NOTICE:  [3, 4, 5, 6, 7]
+NOTICE:  [5, 6, 7]
+NOTICE:  7
+-- jsonb dot access in plpgsql
+DO $$
+DECLARE
+  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+BEGIN
+  WHILE a IS NOT NULL
+  LOOP
+    RAISE NOTICE '%', a;
+    a := COALESCE(a."NU", a[2]); -- fails
+  END LOOP;
+END
+$$ LANGUAGE plpgsql;
+NOTICE:  {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}
+ERROR:  missing FROM-clause entry for table "a"
+LINE 1: a := COALESCE(a."NU", a[2])
+                      ^
+QUERY:  a := COALESCE(a."NU", a[2])
+CONTEXT:  PL/pgSQL function inline_code_block line 8 at assignment
+DO $$
+    DECLARE
+        a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+    BEGIN
+        WHILE a IS NOT NULL
+            LOOP
+                RAISE NOTICE '%', a;
+                a := COALESCE((a)."NU", a[2]); -- succeeds
+            END LOOP;
+    END
+$$ LANGUAGE plpgsql;
+NOTICE:  {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}
+NOTICE:  [{"": [[3]]}, [6], [2], "bCi"]
+NOTICE:  [2]
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 57c11acddfe..4bd3990fb55 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1304,33 +1304,49 @@ 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'];
+select ('{"a": 1}'::jsonb)['a':'b']; -- fails
 select ('[1, "2", null]'::jsonb)[1:2];
 select ('[1, "2", null]'::jsonb)[:2];
 select ('[1, "2", null]'::jsonb)[1:];
@@ -1590,3 +1606,68 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
 select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+SELECT (jb).b FROM test_jsonb_dot_notation;
+SELECT (jb).c FROM test_jsonb_dot_notation;
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2:3].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2:].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[:2].b FROM test_jsonb_dot_notation;
+SELECT (jb).a[:].b FROM test_jsonb_dot_notation;
+SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t;
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+SELECT (jb).a.* FROM test_jsonb_dot_notation;
+
+-- explains should work
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+
+-- jsonb array access in plpgsql
+DO $$
+DECLARE
+  a jsonb := '[1,2,3,4,5,6,7]'::jsonb;
+BEGIN
+  WHILE a IS NOT NULL
+  LOOP
+    RAISE NOTICE '%', a;
+    a := a[2:];
+  END LOOP;
+END
+$$ LANGUAGE plpgsql;
+
+-- jsonb dot access in plpgsql
+DO $$
+DECLARE
+  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+BEGIN
+  WHILE a IS NOT NULL
+  LOOP
+    RAISE NOTICE '%', a;
+    a := COALESCE(a."NU", a[2]); -- fails
+  END LOOP;
+END
+$$ LANGUAGE plpgsql;
+
+DO $$
+    DECLARE
+        a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb;
+    BEGIN
+        WHILE a IS NOT NULL
+            LOOP
+                RAISE NOTICE '%', a;
+                a := COALESCE((a)."NU", a[2]); -- succeeds
+            END LOOP;
+    END
+$$ LANGUAGE plpgsql;
-- 
2.39.5 (Apple Git-154)