v47-0002-JSON_TABLE-Add-support-for-NESTED-columns.patch

From 7189446ed35811814e8d4b8b4a2f238e4234b585 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Thu, 18 Jan 2024 18:00:06 +0900
Subject: [PATCH v47 2/2] JSON_TABLE: Add support for NESTED columns
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Jian He <jian.universality@gmail.com>

Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby, Álvaro Herrera,
Jian He

Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
---
 doc/src/sgml/func.sgml                        |  85 +++++-
 src/backend/catalog/sql_features.txt          |   2 +-
 src/backend/nodes/nodeFuncs.c                 |   2 +
 src/backend/parser/gram.y                     |  38 ++-
 src/backend/parser/parse_jsontable.c          | 125 +++++++-
 src/backend/utils/adt/jsonpath_exec.c         | 202 ++++++++++++-
 src/backend/utils/adt/ruleutils.c             |  35 +++
 src/include/nodes/parsenodes.h                |   2 +
 src/include/nodes/primnodes.h                 |  22 ++
 src/include/parser/kwlist.h                   |   1 +
 .../test/expected/sql-sqljson_jsontable.c     |  15 +
 .../expected/sql-sqljson_jsontable.stderr     |  15 +
 .../ecpg/test/sql/sqljson_jsontable.pgc       |  12 +-
 .../regress/expected/sqljson_jsontable.out    | 274 ++++++++++++++++++
 src/test/regress/sql/sqljson_jsontable.sql    | 129 +++++++++
 src/tools/pgindent/typedefs.list              |   2 +
 16 files changed, 929 insertions(+), 32 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 46f7a87d0a..3d690b7d3a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18888,14 +18888,29 @@ DETAIL:  Missing "]" after array dimensions.
    schema of the created view. For each column to be constructed,
    this clause provides a separate path expression that evaluates
    the row pattern, extracts a JSON item, and returns it as a
-   separate SQL value for the specified column.
+   separate SQL value for the specified column. If the required value
+   is stored in a nested level of the row pattern, it can be extracted
+   using the <literal>NESTED PATH</literal> subclause. Joining the
+   columns returned by <literal>NESTED PATH</literal> can add multiple
+   new rows to the constructed view. Such rows are called
+   <firstterm>child rows</firstterm>, as opposed to the
+   <firstterm>parent row</firstterm> that generates them.
   </para>
 
   <para>
    The rows produced by <function>JSON_TABLE</function> are laterally
    joined to the row that generated them, so you do not have to explicitly join
    the constructed view with the original table holding <acronym>JSON</acronym>
-   data.
+
+  <para>
+   Each <literal>NESTED PATH</literal> clause can generate one or more
+   columns. Columns produced by <literal>NESTED PATH</literal>s at the
+   same level are considered to be <firstterm>siblings</firstterm>,
+   while a column produced by a <literal>NESTED PATH</literal> is
+   considered to be a child of the column produced by a
+   <literal>NESTED PATH</literal> or row expression at a higher level.
+   Sibling columns are always joined first. Once they are processed,
+   the resulting rows are joined to the parent row.
   </para>
 
   <para>
@@ -18926,6 +18941,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
         <optional> { ERROR | NULL | EMPTY { ARRAY | OBJECT } | DEFAULT <replaceable>expression</replaceable> } ON ERROR </optional>
   | <replaceable>name</replaceable> <replaceable>type</replaceable> EXISTS <optional> PATH <replaceable>json_path_specification</replaceable> </optional>
         <optional> { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR </optional>
+  | NESTED PATH <replaceable>json_path_specification</replaceable> <optional> AS <replaceable>path_name</replaceable> </optional>
+        COLUMNS ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
 </synopsis>
 
   <para>
@@ -18972,7 +18989,9 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
     <para>
      Adds an ordinality column that provides sequential row numbering.
      You can have only one ordinality column per table. Row numbering
-     is 1-based.
+     is 1-based.  For child rows that result from the
+     <literal>NESTED PATH</literal> clauses (see below), the parent row
+     number is repeated.
     </para>
     </listitem>
    </varlistentry>
@@ -19079,6 +19098,33 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
     </note>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term>
+      <literal>NESTED PATH</literal> <replaceable>json_path_specification</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
+          <literal>COLUMNS</literal> ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+    </term>
+    <listitem>
+
+    <para>
+     Extracts SQL/JSON items from nested levels of the row pattern,
+     generates one or more columns as defined by the <literal>COLUMNS</literal>
+     subclause, and inserts the extracted SQL/JSON items into each row of these
+     columns.  The <replaceable>json_table_column</replaceable> expression in
+     the <literal>COLUMNS</literal> subclause uses the same syntax as in the
+     parent <literal>COLUMNS</literal> clause.
+    </para>
+
+    <para>
+     The <literal>NESTED PATH</literal> syntax is recursive,
+     so you can go down multiple nested levels by specifying several
+     <literal>NESTED PATH</literal> subclauses within each other.
+     It allows to unnest the hierarchy of JSON objects and arrays
+     in a single function invocation rather than chaining several
+     <function>JSON_TABLE</function> expressions in an SQL statement.
+    </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
     </listitem>
@@ -19142,6 +19188,39 @@ SELECT jt.* FROM
   3 | thriller | Vertigo | Alfred Hitchcock
   4 | drama    | Yojimbo | Akira Kurosawa
 (4 rows)
+</screen>
+     </para>
+
+     <para>
+      Find a director that has done films in two different genres:
+<screen>
+SELECT
+  director1 AS director, title1, kind1, title2, kind2
+FROM
+  my_films,
+  JSON_TABLE ( js, '$.favorites' AS favs COLUMNS (
+    NESTED PATH '$[*]' AS films1 COLUMNS (
+      kind1 text PATH '$.kind',
+      NESTED PATH '$.films[*]' AS film1 COLUMNS (
+        title1 text PATH '$.title',
+        director1 text PATH '$.director')
+    ),
+    NESTED PATH '$[*]' AS films2 COLUMNS (
+      kind2 text PATH '$.kind',
+      NESTED PATH '$.films[*]' AS film2 COLUMNS (
+        title2 text PATH '$.title',
+        director2 text PATH '$.director'
+      )
+    )
+   )
+   PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2)))
+  ) AS jt
+ WHERE kind1 > kind2 AND director1 = director2;
+
+     director     | title1  |  kind1   | title2 | kind2
+------------------+---------+----------+--------+--------
+ Alfred Hitchcock | Vertigo | thriller | Psycho | horror
+(1 row)
 </screen>
      </para>
   </sect2>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 80ac59fba4..c002f37202 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -553,7 +553,7 @@ T823	SQL/JSON: PASSING clause			YES
 T824	JSON_TABLE: specific PLAN clause			NO	
 T825	SQL/JSON: ON EMPTY and ON ERROR clauses			YES	
 T826	General value expression in ON ERROR or ON EMPTY clauses			YES	
-T827	JSON_TABLE: sibling NESTED COLUMNS clauses			NO	
+T827	JSON_TABLE: sibling NESTED COLUMNS clauses			YES	
 T828	JSON_QUERY			YES	
 T829	JSON_QUERY: array wrapper options			YES	
 T830	Enforcing unique keys in SQL/JSON constructor functions			YES	
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 333fee7d19..1fd70b7e0c 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4156,6 +4156,8 @@ raw_expression_tree_walker_impl(Node *node,
 					return true;
 				if (WALK(jtc->on_error))
 					return true;
+				if (WALK(jtc->columns))
+					return true;
 			}
 			break;
 		case T_JsonTablePathSpec:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index cf0c56bd19..77eceef770 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -750,7 +750,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD
 	MINUTE_P MINVALUE MODE MONTH_P MOVE
 
-	NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO
+	NAME_P NAMES NATIONAL NATURAL NCHAR NESTED NEW NEXT NFC NFD NFKC NFKD NO
 	NONE NORMALIZE NORMALIZED
 	NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
 	NULLS_P NUMERIC
@@ -879,8 +879,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
  * the same precedence as IDENT.  This allows resolving conflicts in the
  * json_predicate_type_constraint and json_key_uniqueness_constraint_opt
  * productions (see comments there).
+ *
+ * Like the UNBOUNDED PRECEDING/FOLLOWING case, NESTED is assigned a lower
+ * precedence than PATH to fix ambiguity in the json_table production.
  */
-%nonassoc	UNBOUNDED /* ideally would have same precedence as IDENT */
+%nonassoc	UNBOUNDED NESTED /* ideally would have same precedence as IDENT */
 %nonassoc	IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
 			SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH
 %left		Op OPERATOR		/* multi-character ops and user-defined operators */
@@ -14199,6 +14202,35 @@ json_table_column_definition:
 					n->location = @1;
 					$$ = (Node *) n;
 				}
+			| NESTED path_opt Sconst
+				COLUMNS '(' json_table_column_definition_list ')'
+				{
+					JsonTableColumn *n = makeNode(JsonTableColumn);
+
+					n->coltype = JTC_NESTED;
+					n->pathspec = (JsonTablePathSpec *)
+						makeJsonTablePathSpec($3, NULL, @3, -1);
+					n->columns = $6;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| NESTED path_opt Sconst AS name
+				COLUMNS '(' json_table_column_definition_list ')'
+				{
+					JsonTableColumn *n = makeNode(JsonTableColumn);
+
+					n->coltype = JTC_NESTED;
+					n->pathspec = (JsonTablePathSpec *)
+						makeJsonTablePathSpec($3, $5, @3, @5);
+					n->columns = $8;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
+
+path_opt:
+			PATH
+			| /* EMPTY */
 		;
 
 json_table_column_path_clause_opt:
@@ -17617,6 +17649,7 @@ unreserved_keyword:
 			| MOVE
 			| NAME_P
 			| NAMES
+			| NESTED
 			| NEW
 			| NEXT
 			| NFC
@@ -18229,6 +18262,7 @@ bare_label_keyword:
 			| NATIONAL
 			| NATURAL
 			| NCHAR
+			| NESTED
 			| NEW
 			| NEXT
 			| NFC
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 3c752f9d9e..ab1ffc1ac5 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -49,11 +49,18 @@ static Node *transformJsonTableColumn(JsonTableColumn *jtc,
 									  bool errorOnError);
 static bool typeIsComposite(Oid typid);
 static JsonTablePlan *makeJsonTablePlan(JsonTablePathSpec *pathspec,
-										bool errorOnError);
+										bool errorOnError,
+										int colMin, int colMax,
+										Node *childplan);
 static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
 											List *columns);
 static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name);
 static char *generateJsonTablePathName(JsonTableParseContext *cxt);
+static Node *transformJsonTableChildPlan(JsonTableParseContext *cxt,
+										 List *columns);
+static Node *transformNestedJsonTableColumn(JsonTableParseContext *cxt,
+											JsonTableColumn *jtc);
+static Node *makeJsonTableSiblingJoin(Node *lnode, Node *rnode);
 
 /*
  * transformJsonTable -
@@ -167,13 +174,32 @@ CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
 	{
 		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
 
-		if (LookupPathOrColumnName(cxt, jtc->name))
-			ereport(ERROR,
-					errcode(ERRCODE_DUPLICATE_ALIAS),
-					errmsg("duplicate JSON_TABLE column or path name: %s",
-						   jtc->name),
-					parser_errposition(cxt->pstate, jtc->location));
-		cxt->pathNames = lappend(cxt->pathNames, jtc->name);
+		if (jtc->coltype == JTC_NESTED)
+		{
+			if (jtc->pathspec->name)
+			{
+				if (LookupPathOrColumnName(cxt, jtc->pathspec->name))
+					ereport(ERROR,
+							errcode(ERRCODE_DUPLICATE_ALIAS),
+							errmsg("duplicate JSON_TABLE column or path name: %s",
+								   jtc->pathspec->name),
+							parser_errposition(cxt->pstate,
+											   jtc->pathspec->name_location));
+				cxt->pathNames = lappend(cxt->pathNames, jtc->pathspec->name);
+			}
+
+			CheckDuplicateColumnOrPathNames(cxt, jtc->columns);
+		}
+		else
+		{
+			if (LookupPathOrColumnName(cxt, jtc->name))
+				ereport(ERROR,
+						errcode(ERRCODE_DUPLICATE_ALIAS),
+						errmsg("duplicate JSON_TABLE column or path name: %s",
+							   jtc->name),
+						parser_errposition(cxt->pstate, jtc->location));
+			cxt->pathNames = lappend(cxt->pathNames, jtc->name);
+		}
 	}
 }
 
@@ -228,6 +254,12 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 	bool		errorOnError = jt->on_error &&
 		jt->on_error->btype == JSON_BEHAVIOR_ERROR;
 	Oid			contextItemTypid = exprType(tf->docexpr);
+	int			colMin,
+				colMax;
+	Node	   *childplan;
+
+	/* Start of column range */
+	colMin = list_length(tf->colvalexprs);
 
 	foreach(col, columns)
 	{
@@ -293,6 +325,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 					break;
 				}
 
+			case JTC_NESTED:
+				continue;
+
 			default:
 				elog(ERROR, "unknown JSON_TABLE column type: %d", rawc->coltype);
 				break;
@@ -304,7 +339,13 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 		tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
 	}
 
-	return makeJsonTablePlan(pathspec, errorOnError);
+	/* End of column range */
+	colMax = list_length(tf->colvalexprs) - 1;
+
+	/* Transform recursively nested columns */
+	childplan = transformJsonTableChildPlan(cxt, columns);
+
+	return makeJsonTablePlan(pathspec, errorOnError, colMin, colMax, childplan);
 }
 
 /*
@@ -369,11 +410,70 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	return (Node *) jfexpr;
 }
 
+/*
+ * Recursively transform child JSON_TABLE plan.
+ *
+ * Default plan is transformed into a cross/union join of its nested columns.
+ * Simple and outer/inner plans are transformed into a JsonTablePlan by
+ * finding and transforming corresponding nested column.
+ * Sibling plans are recursively transformed into a JsonTableSibling.
+ */
+static Node *
+transformJsonTableChildPlan(JsonTableParseContext *cxt,
+							List *columns)
+{
+	Node	   *plan = NULL;
+	ListCell   *lc;
+
+	/* transform all nested columns into UNION join */
+	foreach(lc, columns)
+	{
+		JsonTableColumn *col = castNode(JsonTableColumn, lfirst(lc));
+		Node	   *nested;
+
+		if (col->coltype != JTC_NESTED)
+			continue;
+
+		nested = transformNestedJsonTableColumn(cxt, col);
+
+		/* join transformed node with previous sibling nodes */
+		if (plan)
+			plan = makeJsonTableSiblingJoin(plan, nested);
+		else
+			plan = nested;
+	}
+
+	return plan;
+}
+
+static Node *
+transformNestedJsonTableColumn(JsonTableParseContext *cxt,
+							   JsonTableColumn *jtc)
+{
+	if (jtc->pathspec->name == NULL)
+		jtc->pathspec->name = generateJsonTablePathName(cxt);
+
+	return (Node *) transformJsonTableColumns(cxt, jtc->columns,
+											  jtc->pathspec);
+}
+
+static Node *
+makeJsonTableSiblingJoin(Node *lnode, Node *rnode)
+{
+	JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin);
+
+	join->larg = lnode;
+	join->rarg = rnode;
+
+	return (Node *) join;
+}
+
 /*
  * Create a JsonTablePlan for given path and ON ERROR behavior.
  */
 static JsonTablePlan *
-makeJsonTablePlan(JsonTablePathSpec *pathspec, bool errorOnError)
+makeJsonTablePlan(JsonTablePathSpec *pathspec, bool errorOnError,
+				  int colMin, int colMax, Node *childplan)
 {
 	JsonTablePlan *plan = makeNode(JsonTablePlan);
 	char	   *pathstring;
@@ -388,6 +488,11 @@ makeJsonTablePlan(JsonTablePathSpec *pathspec, bool errorOnError)
 	plan->path = makeJsonTablePath(value, pathspec->name);
 	plan->errorOnError = errorOnError;
 
+	plan->colMin = colMin;
+	plan->colMax = colMax;
+
+	plan->child = childplan;
+
 	return plan;
 }
 
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 58498a8df6..0ee70294d4 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -167,7 +167,7 @@ typedef struct JsonValueListIterator
 typedef struct JsonTablePlanState
 {
 	/* Original plan */
-	JsonTablePlan *plan;
+	Node	   *plan;
 
 	/* jsonpath to evaluate against the input doc to get the row pattern. */
 	JsonPath   *path;
@@ -188,6 +188,23 @@ typedef struct JsonTablePlanState
 
 	/* Counter for ORDINAL columns for JsonTableGetValue() to use */
 	int			ordinal;
+
+	/* Nested plan, if any */
+	struct JsonTablePlanState *nested;
+
+	/* Left sibling, if any */
+	struct JsonTablePlanState *left;
+
+	/* Right sibling, if any */
+	struct JsonTablePlanState *right;
+
+	/* Parent plan, if this is a nested plan */
+	struct JsonTablePlanState *parent;
+
+	/**/
+	bool		advanceNested;
+	bool		advanceRight;
+	bool		reset;
 } JsonTablePlanState;
 
 /* Random number to identify JsonTableExecContext for sanity checking */
@@ -197,6 +214,7 @@ typedef struct JsonTableExecContext
 {
 	int			magic;
 	JsonTablePlanState *rootplanstate;
+	JsonTablePlanState **colexprplans;
 } JsonTableExecContext;
 
 /* strict/lax flags is decomposed into four [un]wrap/error flags */
@@ -320,9 +338,14 @@ static void checkTimezoneIsUsedForCast(bool useTz, const char *type1,
 
 static void JsonTableInitOpaque(TableFuncScanState *state, int natts);
 static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt,
-											 JsonTablePlan *plan,
+											 Node *plan,
+											 JsonTablePlanState *parentstate,
 											 List *args,
 											 MemoryContext mcxt);
+static void JsonTableInitPathScan(JsonTableExecContext *cxt,
+								  JsonTablePlanState *planstate,
+								  List *args,
+								  MemoryContext mcxt);
 static void JsonTableSetDocument(TableFuncScanState *state, Datum value);
 static void JsonTableResetContextItem(JsonTablePlanState *plan, Datum item);
 static void JsonTableRescan(JsonTablePlanState *planstate);
@@ -331,6 +354,9 @@ static Datum JsonTableGetValue(TableFuncScanState *state, int colnum,
 							   Oid typid, int32 typmod, bool *isnull);
 static void JsonTableDestroyOpaque(TableFuncScanState *state);
 static bool JsonTablePlanNextRow(JsonTablePlanState *planstate);
+static bool JsonTablePlanPathNextRow(JsonTablePlanState *planstate);
+static void JsonTableRescan(JsonTablePlanState *planstate);
+static void JsonTablePlanReset(JsonTablePlanState *planstate);
 
 const TableFuncRoutine JsonbTableRoutine =
 {
@@ -4072,8 +4098,11 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts)
 		}
 	}
 
+	cxt->colexprplans = palloc(sizeof(JsonTablePlanState *) *
+							   list_length(tf->colvalexprs));
+
 	/* Initialize plan */
-	cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, args,
+	cxt->rootplanstate = JsonTableInitPlan(cxt, (Node *) rootplan, NULL, args,
 										   CurrentMemoryContext);
 
 	state->opaque = cxt;
@@ -4100,13 +4129,14 @@ JsonTableDestroyOpaque(TableFuncScanState *state)
  *		Initialize information for evaluating a jsonpath given in
  *		JsonTablePlan
  */
-static JsonTablePlanState *
-JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
-				  List *args, MemoryContext mcxt)
+static void
+JsonTableInitPathScan(JsonTableExecContext *cxt,
+					  JsonTablePlanState *planstate,
+					  List *args, MemoryContext mcxt)
 {
-	JsonTablePlanState *planstate = palloc0(sizeof(*planstate));
+	JsonTablePlan *plan = (JsonTablePlan *) planstate->plan;
+	int			i;
 
-	planstate->plan = plan;
 	planstate->path = DatumGetJsonPathP(plan->path->value->constvalue);
 	planstate->args = args;
 	planstate->mcxt = AllocSetContextCreate(mcxt, "JsonTableExecContext",
@@ -4116,6 +4146,43 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
 	planstate->currentRow = PointerGetDatum(NULL);
 	planstate->currentRowIsNull = true;
 
+	for (i = plan->colMin; i <= plan->colMax; i++)
+		cxt->colexprplans[i] = planstate;
+}
+
+/*
+ * JsonTableInitPlan
+ * 		Recursively initializes a JsonTablePlan and any child plans
+ */
+static JsonTablePlanState *
+JsonTableInitPlan(JsonTableExecContext *cxt, Node *plan,
+				  JsonTablePlanState *parentstate,
+				  List *args, MemoryContext mcxt)
+{
+	JsonTablePlanState *planstate = palloc0(sizeof(*planstate));
+
+	planstate->plan = plan;
+	planstate->parent = parentstate;
+
+	if (IsA(plan, JsonTableSiblingJoin))
+	{
+		JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan;
+
+		planstate->left = JsonTableInitPlan(cxt, (Node *) join->larg,
+											parentstate, args, mcxt);
+		planstate->right = JsonTableInitPlan(cxt, (Node *) join->rarg,
+											 parentstate, args, mcxt);
+	}
+	else
+	{
+		JsonTablePlan *scan = (JsonTablePlan *) plan;
+
+		JsonTableInitPathScan(cxt, planstate, args, mcxt);
+
+		planstate->nested = scan->child ?
+			JsonTableInitPlan(cxt, scan->child, planstate, args, mcxt) : NULL;
+	}
+
 	return planstate;
 }
 
@@ -4171,11 +4238,43 @@ JsonTableResetContextItem(JsonTablePlanState *planstate, Datum item)
 static void
 JsonTableRescan(JsonTablePlanState *planstate)
 {
-	/* Reset plan iterator to the beginning of the item list */
-	JsonValueListInitIterator(&planstate->found, &planstate->iter);
-	planstate->currentRow = PointerGetDatum(NULL);
-	planstate->currentRowIsNull = true;
-	planstate->ordinal = 0;
+	if (IsA(planstate, JsonTableSiblingJoin))
+	{
+		JsonTableRescan(planstate->left);
+		JsonTableRescan(planstate->right);
+		planstate->advanceRight = false;
+	}
+	else
+	{
+		/* Reset plan iterator to the beginning of the item list */
+		JsonValueListInitIterator(&planstate->found, &planstate->iter);
+		planstate->currentRow = PointerGetDatum(NULL);
+		planstate->currentRowIsNull = true;
+		planstate->ordinal = 0;
+
+		if (planstate->nested)
+			JsonTableRescan(planstate->nested);
+	}
+}
+
+/* Recursively set 'reset' flag of planstate and its child nodes */
+static void
+JsonTablePlanReset(JsonTablePlanState *planstate)
+{
+	if (IsA(planstate->plan, JsonTableSiblingJoin))
+	{
+		JsonTablePlanReset(planstate->left);
+		JsonTablePlanReset(planstate->right);
+		planstate->advanceRight = false;
+	}
+	else
+	{
+		planstate->reset = true;
+		planstate->advanceNested = false;
+
+		if (planstate->nested)
+			JsonTablePlanReset(planstate->nested);
+	}
 }
 
 /*
@@ -4184,7 +4283,7 @@ JsonTableRescan(JsonTablePlanState *planstate)
  * Returns false if the plan has run out of rows, true otherwise.
  */
 static bool
-JsonTablePlanNextRow(JsonTablePlanState *planstate)
+JsonTablePlanPathNextRow(JsonTablePlanState *planstate)
 {
 	JsonbValue *jbv = JsonValueListNext(&planstate->found, &planstate->iter);
 	MemoryContext oldcxt;
@@ -4212,6 +4311,79 @@ JsonTablePlanNextRow(JsonTablePlanState *planstate)
 	return true;
 }
 
+/*
+ * Fetch next row from a JsonTablePlan.
+ *
+ * Returns false if the plan has run out of rows, true otherwise.
+ */
+static bool
+JsonTablePlanNextRow(JsonTablePlanState *planstate)
+{
+	if (IsA(planstate->plan, JsonTableSiblingJoin))
+	{
+		if (planstate->advanceRight)
+		{
+			/* fetch next inner row */
+			if (JsonTablePlanNextRow(planstate->right))
+				return true;
+
+			/* inner rows are exhausted */
+			/* next outer row */
+			planstate->advanceRight = false;
+		}
+
+		while (!planstate->advanceRight)
+		{
+			/* fetch next outer row */
+			if (!JsonTablePlanNextRow(planstate->left))
+			{
+				if (!JsonTablePlanNextRow(planstate->right))
+					return false;	/* end of scan */
+
+				planstate->advanceRight = true; /* next inner row */
+			}
+
+			break;
+		}
+	}
+	else
+	{
+		/* reset context item if requested */
+		if (planstate->reset)
+		{
+			JsonTablePlanState *parent = planstate->parent;
+
+			Assert(parent != NULL && !parent->currentRowIsNull);
+			JsonTableResetContextItem(planstate, parent->currentRow);
+			planstate->reset = false;
+		}
+
+		if (planstate->advanceNested)
+		{
+			/* fetch next nested row */
+			planstate->advanceNested = JsonTablePlanNextRow(planstate->nested);
+			if (planstate->advanceNested)
+				return true;
+		}
+
+		for (;;)
+		{
+			if (!JsonTablePlanPathNextRow(planstate))
+				return false;
+
+			if (planstate->nested == NULL)
+				break;
+
+			JsonTablePlanReset(planstate->nested);
+			planstate->advanceNested = JsonTablePlanNextRow(planstate->nested);
+			if (planstate->advanceNested || planstate->nested)
+				break;
+		}
+	}
+
+	return true;
+}
+
 /*
  * JsonTableFetchRow
  *		Prepare the next "current" row for upcoming GetValue calls.
@@ -4242,7 +4414,7 @@ JsonTableGetValue(TableFuncScanState *state, int colnum,
 		GetJsonTableExecContext(state, "JsonTableGetValue");
 	ExprContext *econtext = state->ss.ps.ps_ExprContext;
 	ExprState  *estate = list_nth(state->colvalexprs, colnum);
-	JsonTablePlanState *planstate = cxt->rootplanstate;
+	JsonTablePlanState *planstate = cxt->colexprplans[colnum];
 	Datum		result;
 
 	/* Row pattern value is NULL */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0187074d84..f80e7dd194 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11583,6 +11583,37 @@ get_xmltable(TableFunc *tf, deparse_context *context, bool showimplicit)
 	appendStringInfoChar(buf, ')');
 }
 
+/*
+ * get_json_nested_columns - Parse back nested JSON_TABLE columns
+ */
+static void
+get_json_table_nested_columns(TableFunc *tf, Node *node,
+							  deparse_context *context, bool showimplicit,
+							  bool needcomma)
+{
+	if (IsA(node, JsonTableSiblingJoin))
+	{
+		JsonTableSiblingJoin *n = (JsonTableSiblingJoin *) node;
+
+		get_json_table_nested_columns(tf, n->larg, context, showimplicit,
+									  needcomma);
+		get_json_table_nested_columns(tf, n->rarg, context, showimplicit, true);
+	}
+	else
+	{
+		JsonTablePlan *n = castNode(JsonTablePlan, node);
+
+		if (needcomma)
+			appendStringInfoChar(context->buf, ',');
+
+		appendStringInfoChar(context->buf, ' ');
+		appendContextKeyword(context, "NESTED PATH ", 0, 0, 0);
+		get_const_expr(n->path->value, context, -1);
+		appendStringInfo(context->buf, " AS %s", quote_identifier(n->path->name));
+		get_json_table_columns(tf, n, context, showimplicit);
+	}
+}
+
 /*
  * get_json_table_columns - Parse back JSON_TABLE columns
  */
@@ -11668,6 +11699,10 @@ get_json_table_columns(TableFunc *tf, JsonTablePlan *plan,
 		get_json_expr_options(colexpr, context, default_behavior);
 	}
 
+	if (plan->child)
+		get_json_table_nested_columns(tf, plan->child, context, showimplicit,
+									  plan->colMax >= plan->colMin);
+
 	if (PRETTY_INDENT(context))
 		context->indentLevel -= PRETTYINDENT_VAR;
 
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7f5b742b3c..9cc7eda6cb 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1811,6 +1811,7 @@ typedef enum JsonTableColumnType
 	JTC_REGULAR,
 	JTC_EXISTS,
 	JTC_FORMATTED,
+	JTC_NESTED,
 } JsonTableColumnType;
 
 /*
@@ -1827,6 +1828,7 @@ typedef struct JsonTableColumn
 	JsonFormat *format;			/* JSON format clause, if specified */
 	JsonWrapper wrapper;		/* WRAPPER behavior for formatted columns */
 	JsonQuotes	quotes;			/* omit or keep quotes on scalar strings? */
+	List	   *columns;		/* nested columns */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
 	int			location;		/* token location, or -1 if unknown */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index c5b8966d33..5bc8ab4666 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1855,8 +1855,30 @@ typedef struct JsonTablePlan
 
 	/* ERROR/EMPTY ON ERROR behavior */
 	bool		errorOnError;
+
+	/*
+	 * 0-based index in TableFunc.colvalexprs of the 1st and the last column
+	 * covered by this plan.
+	 */
+	int			colMin;
+	int			colMax;
+
+	/* Plan for nested columns, if any. */
+	Node	   *child;
 } JsonTablePlan;
 
+/*
+ * JsonTableSiblingJoin -
+ *		Plan to union-join rows of nested paths of the same level
+ */
+typedef struct JsonTableSiblingJoin
+{
+	NodeTag		type;
+
+	Node	   *larg;
+	Node	   *rarg;
+} JsonTableSiblingJoin;
+
 /* ----------------
  * NullTest
  *
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index d57c0f2c42..cfc76ba181 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -286,6 +286,7 @@ PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("natural", NATURAL, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("nchar", NCHAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("nested", NESTED, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("new", NEW, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("next", NEXT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("nfc", NFC, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
index eb690b9451..9854e574b8 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
@@ -113,19 +113,34 @@ if (sqlca.sqlcode < 0) sqlprint();}
 #line 14 "sqljson_jsontable.pgc"
 
 
+<<<<<<< HEAD
   { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( foo int , bar int , baz int ) ) jt", ECPGt_EOIT, ECPGt_EORT);
 #line 21 "sqljson_jsontable.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
 #line 21 "sqljson_jsontable.pgc"
+=======
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) , nested path '$' as p12 columns ( bar int ) ) , nested path '$' as p2 columns ( nested path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt", ECPGt_EOIT, ECPGt_EORT);
+#line 26 "sqljson_jsontable.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 26 "sqljson_jsontable.pgc"
+>>>>>>> be9e710d74 (JSON_TABLE)
 
   // error
 
   { ECPGdisconnect(__LINE__, "CURRENT");
+<<<<<<< HEAD
 #line 24 "sqljson_jsontable.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
 #line 24 "sqljson_jsontable.pgc"
+=======
+#line 29 "sqljson_jsontable.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 29 "sqljson_jsontable.pgc"
+>>>>>>> be9e710d74 (JSON_TABLE)
 
 
   return 0;
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
index 42536c0006..86acf28771 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
@@ -4,6 +4,7 @@
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ECPGsetcommit on line 14: action "on"; connection "ecpg1_regression"
 [NO_PID]: sqlca: code: 0, state: 00000
+<<<<<<< HEAD
 [NO_PID]: ecpg_execute on line 16: query: select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( foo int , bar int , baz int ) ) jt; with 0 parameter(s) on connection ecpg1_regression
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ecpg_execute on line 16: using PQexec
@@ -13,5 +14,19 @@
 [NO_PID]: raising sqlcode -202 on line 16: too few arguments on line 16
 [NO_PID]: sqlca: code: -202, state: 07002
 SQL error: too few arguments on line 16
+=======
+[NO_PID]: ecpg_execute on line 16: query: select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) , nested path '$' as p12 columns ( bar int ) ) , nested path '$' as p2 columns ( nested path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 16: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 16: bad response - ERROR:  invalid JSON_TABLE plan
+LINE 1: ...ted path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt
+                                                              ^
+DETAIL:  PATH name mismatch: expected p0 but p1 is given.
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42601 (sqlcode -400): invalid JSON_TABLE plan on line 16
+[NO_PID]: sqlca: code: -400, state: 42601
+SQL error: invalid JSON_TABLE plan on line 16
+>>>>>>> be9e710d74 (JSON_TABLE)
 [NO_PID]: ecpg_finish: connection ecpg1_regression closed
 [NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
index a161479f72..9cd1498bf4 100644
--- a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
@@ -19,7 +19,17 @@ main ()
 			bar int,
 			baz int
 	)) jt;
-  // error
+
+  EXEC SQL SELECT * FROM JSON_TABLE(jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED '$' AS p1 COLUMNS (
+			NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+			NESTED PATH '$' AS p12 COLUMNS ( bar int )
+		),
+		NESTED PATH '$' AS p2 COLUMNS (
+			NESTED PATH '$' AS p21 COLUMNS ( baz int )
+		)
+	);
 
   EXEC SQL DISCONNECT;
 
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 53495935ba..fb4e1da99b 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -218,6 +218,29 @@ FROM json_table_test vals
 (14 rows)
 
 -- JSON_TABLE: Test backward parsing
+CREATE VIEW jsonb_table_view1 AS
+SELECT * FROM
+	JSON_TABLE(
+		jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+		COLUMNS (
+			id FOR ORDINALITY,
+			NESTED PATH '$[1]' AS p1 COLUMNS (
+				a1 int,
+				NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+					a11 text
+				),
+				b1 text
+			),
+			NESTED PATH '$[2]' AS p2 COLUMNS (
+				NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+					a21 text
+				),
+				NESTED PATH '$[*]' AS p22 COLUMNS (
+					a22 text
+				)
+			)
+		)
+	);
 CREATE VIEW jsonb_table_view2 AS
 SELECT * FROM
 	JSON_TABLE(
@@ -267,6 +290,73 @@ SELECT * FROM
 			ia int[] PATH '$',
 			ta text[] PATH '$',
 			jba jsonb[] PATH '$'));
+\sv jsonb_table_view1
+CREATE OR REPLACE VIEW public.jsonb_table_view1 AS
+ SELECT id,
+    a1,
+    b1,
+    a11,
+    a21,
+    a22
+   FROM JSON_TABLE(
+            'null'::jsonb, '$[*]' AS json_table_path_0
+            PASSING
+                1 + 2 AS a,
+                '"foo"'::json AS "b c"
+            COLUMNS (
+                id FOR ORDINALITY,
+                a1 integer PATH '$."a1"',
+                b1 text PATH '$."b1"',
+                a11 text PATH '$."a11"',
+                a21 text PATH '$."a21"',
+                a22 text PATH '$."a22"',
+                NESTED PATH '$[1]' AS p1
+                COLUMNS (
+                    id FOR ORDINALITY,
+                    a1 integer PATH '$."a1"',
+                    b1 text PATH '$."b1"',
+                    a11 text PATH '$."a11"',
+                    a21 text PATH '$."a21"',
+                    a22 text PATH '$."a22"',
+                    NESTED PATH '$[*]' AS "p1 1"
+                    COLUMNS (
+                        id FOR ORDINALITY,
+                        a1 integer PATH '$."a1"',
+                        b1 text PATH '$."b1"',
+                        a11 text PATH '$."a11"',
+                        a21 text PATH '$."a21"',
+                        a22 text PATH '$."a22"'
+                    )
+                ),
+                NESTED PATH '$[2]' AS p2
+                COLUMNS (
+                    id FOR ORDINALITY,
+                    a1 integer PATH '$."a1"',
+                    b1 text PATH '$."b1"',
+                    a11 text PATH '$."a11"',
+                    a21 text PATH '$."a21"',
+                    a22 text PATH '$."a22"'
+                    NESTED PATH '$[*]' AS "p2:1"
+                    COLUMNS (
+                        id FOR ORDINALITY,
+                        a1 integer PATH '$."a1"',
+                        b1 text PATH '$."b1"',
+                        a11 text PATH '$."a11"',
+                        a21 text PATH '$."a21"',
+                        a22 text PATH '$."a22"'
+                    ),
+                    NESTED PATH '$[*]' AS p22
+                    COLUMNS (
+                        id FOR ORDINALITY,
+                        a1 integer PATH '$."a1"',
+                        b1 text PATH '$."b1"',
+                        a11 text PATH '$."a11"',
+                        a21 text PATH '$."a21"',
+                        a22 text PATH '$."a22"'
+                    )
+                )
+            )
+        )
 \sv jsonb_table_view2
 CREATE OR REPLACE VIEW public.jsonb_table_view2 AS
  SELECT "int",
@@ -365,6 +455,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
                 jba jsonb[] PATH '$'
             )
         )
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view1;
+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "json_table"
+   Output: "json_table".id, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"', NESTED PATH '$[1]' AS p1 COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"')), NESTED PATH '$[2]' AS p2 COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"' NESTED PATH '$[*]' AS "p2:1" COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"'), NESTED PATH '$[*]' AS p22 COLUMNS (id FOR ORDINALITY, a1 integer PATH '$."a1"', b1 text PATH '$."b1"', a11 text PATH '$."a11"', a21 text PATH '$."a21"', a22 text PATH '$."a22"'))))
+(3 rows)
+
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
                                                                                                                                             QUERY PLAN                                                                                                                                             
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@@ -448,6 +546,7 @@ SELECT * FROM
  ]
 (1 row)
 
+DROP VIEW jsonb_table_view1;
 DROP VIEW jsonb_table_view2;
 DROP VIEW jsonb_table_view3;
 DROP VIEW jsonb_table_view4;
@@ -599,6 +698,181 @@ SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH W
 ERROR:  SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
 LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
                                                              ^
+-- JSON_TABLE: nested paths
+-- Duplicate path names
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS a
+	COLUMNS (
+		b int,
+		NESTED PATH '$' AS a
+		COLUMNS (
+			c int
+		)
+	)
+) jt;
+ERROR:  duplicate JSON_TABLE column or path name: a
+LINE 5:   NESTED PATH '$' AS a
+                             ^
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS a
+	COLUMNS (
+		b int,
+		NESTED PATH '$' AS n_a
+		COLUMNS (
+			c int
+		)
+	)
+) jt;
+ b | c 
+---+---
+   |  
+(1 row)
+
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$'
+	COLUMNS (
+		b int,
+		NESTED PATH '$' AS b
+		COLUMNS (
+			c int
+		)
+	)
+) jt;
+ERROR:  duplicate JSON_TABLE column or path name: b
+LINE 5:   NESTED PATH '$' AS b
+                             ^
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$'
+	COLUMNS (
+		NESTED PATH '$' AS a
+		COLUMNS (
+			b int
+		),
+		NESTED PATH '$'
+		COLUMNS (
+			NESTED PATH '$' AS a
+			COLUMNS (
+				c int
+			)
+		)
+	)
+) jt;
+ERROR:  duplicate JSON_TABLE column or path name: a
+LINE 10:    NESTED PATH '$' AS a
+                               ^
+-- JSON_TABLE: plan execution
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+INSERT INTO jsonb_table_test
+VALUES (
+	'[
+		{"a":  1,  "b": [], "c": []},
+		{"a":  2,  "b": [1, 2, 3], "c": [10, null, 20]},
+		{"a":  3,  "b": [1, 2], "c": []},
+		{"x": "4", "b": [1, 2], "c": 123}
+	 ]'
+);
+-- unspecified plan (outer, union)
+select
+	jt.*
+from
+	jsonb_table_test jtt,
+	json_table (
+		jtt.js,'strict $[*]' as p
+		columns (
+			n for ordinality,
+			a int path 'lax $.a' default -1 on empty,
+			nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+			nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+		)
+	) jt;
+ n | a  | b | c  
+---+----+---+----
+ 1 |  1 |   |   
+ 2 |  2 | 1 |   
+ 2 |  2 | 2 |   
+ 2 |  2 | 3 |   
+ 2 |  2 |   | 10
+ 2 |  2 |   |   
+ 2 |  2 |   | 20
+ 3 |  3 | 1 |   
+ 3 |  3 | 2 |   
+ 4 | -1 | 1 |   
+ 4 | -1 | 2 |   
+(11 rows)
+
+-- Should succeed (JSON arguments are passed to root and nested paths)
+SELECT *
+FROM
+	generate_series(1, 4) x,
+	generate_series(1, 3) y,
+	JSON_TABLE(jsonb
+		'[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+		'strict $[*] ? (@[*] < $x)'
+		PASSING x AS x, y AS y
+		COLUMNS (
+			y text FORMAT JSON PATH '$',
+			NESTED PATH 'strict $[*] ? (@ >= $y)'
+			COLUMNS (
+				z int PATH '$'
+			)
+		)
+	) jt;
+ x | y |      y       | z 
+---+---+--------------+---
+ 2 | 1 | [1, 2, 3]    | 1
+ 2 | 1 | [1, 2, 3]    | 2
+ 2 | 1 | [1, 2, 3]    | 3
+ 3 | 1 | [1, 2, 3]    | 1
+ 3 | 1 | [1, 2, 3]    | 2
+ 3 | 1 | [1, 2, 3]    | 3
+ 3 | 1 | [2, 3, 4, 5] | 2
+ 3 | 1 | [2, 3, 4, 5] | 3
+ 3 | 1 | [2, 3, 4, 5] | 4
+ 3 | 1 | [2, 3, 4, 5] | 5
+ 4 | 1 | [1, 2, 3]    | 1
+ 4 | 1 | [1, 2, 3]    | 2
+ 4 | 1 | [1, 2, 3]    | 3
+ 4 | 1 | [2, 3, 4, 5] | 2
+ 4 | 1 | [2, 3, 4, 5] | 3
+ 4 | 1 | [2, 3, 4, 5] | 4
+ 4 | 1 | [2, 3, 4, 5] | 5
+ 4 | 1 | [3, 4, 5, 6] | 3
+ 4 | 1 | [3, 4, 5, 6] | 4
+ 4 | 1 | [3, 4, 5, 6] | 5
+ 4 | 1 | [3, 4, 5, 6] | 6
+ 2 | 2 | [1, 2, 3]    | 2
+ 2 | 2 | [1, 2, 3]    | 3
+ 3 | 2 | [1, 2, 3]    | 2
+ 3 | 2 | [1, 2, 3]    | 3
+ 3 | 2 | [2, 3, 4, 5] | 2
+ 3 | 2 | [2, 3, 4, 5] | 3
+ 3 | 2 | [2, 3, 4, 5] | 4
+ 3 | 2 | [2, 3, 4, 5] | 5
+ 4 | 2 | [1, 2, 3]    | 2
+ 4 | 2 | [1, 2, 3]    | 3
+ 4 | 2 | [2, 3, 4, 5] | 2
+ 4 | 2 | [2, 3, 4, 5] | 3
+ 4 | 2 | [2, 3, 4, 5] | 4
+ 4 | 2 | [2, 3, 4, 5] | 5
+ 4 | 2 | [3, 4, 5, 6] | 3
+ 4 | 2 | [3, 4, 5, 6] | 4
+ 4 | 2 | [3, 4, 5, 6] | 5
+ 4 | 2 | [3, 4, 5, 6] | 6
+ 2 | 3 | [1, 2, 3]    | 3
+ 3 | 3 | [1, 2, 3]    | 3
+ 3 | 3 | [2, 3, 4, 5] | 3
+ 3 | 3 | [2, 3, 4, 5] | 4
+ 3 | 3 | [2, 3, 4, 5] | 5
+ 4 | 3 | [1, 2, 3]    | 3
+ 4 | 3 | [2, 3, 4, 5] | 3
+ 4 | 3 | [2, 3, 4, 5] | 4
+ 4 | 3 | [2, 3, 4, 5] | 5
+ 4 | 3 | [3, 4, 5, 6] | 3
+ 4 | 3 | [3, 4, 5, 6] | 4
+ 4 | 3 | [3, 4, 5, 6] | 5
+ 4 | 3 | [3, 4, 5, 6] | 6
+(52 rows)
+
 -- Should fail (JSON arguments are not passed to column paths)
 SELECT *
 FROM JSON_TABLE(
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 86cb2e7d83..22629188ba 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -118,6 +118,30 @@ FROM json_table_test vals
 
 -- JSON_TABLE: Test backward parsing
 
+CREATE VIEW jsonb_table_view1 AS
+SELECT * FROM
+	JSON_TABLE(
+		jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+		COLUMNS (
+			id FOR ORDINALITY,
+			NESTED PATH '$[1]' AS p1 COLUMNS (
+				a1 int,
+				NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+					a11 text
+				),
+				b1 text
+			),
+			NESTED PATH '$[2]' AS p2 COLUMNS (
+				NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+					a21 text
+				),
+				NESTED PATH '$[*]' AS p22 COLUMNS (
+					a22 text
+				)
+			)
+		)
+	);
+
 CREATE VIEW jsonb_table_view2 AS
 SELECT * FROM
 	JSON_TABLE(
@@ -172,12 +196,14 @@ SELECT * FROM
 			ta text[] PATH '$',
 			jba jsonb[] PATH '$'));
 
+\sv jsonb_table_view1
 \sv jsonb_table_view2
 \sv jsonb_table_view3
 \sv jsonb_table_view4
 \sv jsonb_table_view5
 \sv jsonb_table_view6
 
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view1;
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
@@ -205,6 +231,7 @@ SELECT * FROM
 			"text" text PATH '$'
 	)) json_table_func;
 
+DROP VIEW jsonb_table_view1;
 DROP VIEW jsonb_table_view2;
 DROP VIEW jsonb_table_view3;
 DROP VIEW jsonb_table_view4;
@@ -267,6 +294,108 @@ SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PAT
 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES));
 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER OMIT QUOTES));
 
+-- JSON_TABLE: nested paths
+
+-- Duplicate path names
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS a
+	COLUMNS (
+		b int,
+		NESTED PATH '$' AS a
+		COLUMNS (
+			c int
+		)
+	)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS a
+	COLUMNS (
+		b int,
+		NESTED PATH '$' AS n_a
+		COLUMNS (
+			c int
+		)
+	)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$'
+	COLUMNS (
+		b int,
+		NESTED PATH '$' AS b
+		COLUMNS (
+			c int
+		)
+	)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$'
+	COLUMNS (
+		NESTED PATH '$' AS a
+		COLUMNS (
+			b int
+		),
+		NESTED PATH '$'
+		COLUMNS (
+			NESTED PATH '$' AS a
+			COLUMNS (
+				c int
+			)
+		)
+	)
+) jt;
+
+
+-- JSON_TABLE: plan execution
+
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+
+INSERT INTO jsonb_table_test
+VALUES (
+	'[
+		{"a":  1,  "b": [], "c": []},
+		{"a":  2,  "b": [1, 2, 3], "c": [10, null, 20]},
+		{"a":  3,  "b": [1, 2], "c": []},
+		{"x": "4", "b": [1, 2], "c": 123}
+	 ]'
+);
+
+-- unspecified plan (outer, union)
+select
+	jt.*
+from
+	jsonb_table_test jtt,
+	json_table (
+		jtt.js,'strict $[*]' as p
+		columns (
+			n for ordinality,
+			a int path 'lax $.a' default -1 on empty,
+			nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
+			nested path 'strict $.c[*]' as pc columns ( c int path '$' )
+		)
+	) jt;
+
+
+-- Should succeed (JSON arguments are passed to root and nested paths)
+SELECT *
+FROM
+	generate_series(1, 4) x,
+	generate_series(1, 3) y,
+	JSON_TABLE(jsonb
+		'[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+		'strict $[*] ? (@[*] < $x)'
+		PASSING x AS x, y AS y
+		COLUMNS (
+			y text FORMAT JSON PATH '$',
+			NESTED PATH 'strict $[*] ? (@ >= $y)'
+			COLUMNS (
+				z int PATH '$'
+			)
+		)
+	) jt;
+
 -- Should fail (JSON arguments are not passed to column paths)
 SELECT *
 FROM JSON_TABLE(
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 206ed990c7..f305ee6e64 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1325,6 +1325,7 @@ JsonPathMutableContext
 JsonPathParseItem
 JsonPathParseResult
 JsonPathPredicateCallback
+JsonPathSpec
 JsonPathString
 JsonPathVariable
 JsonQuotes
@@ -1341,6 +1342,7 @@ JsonTablePath
 JsonTablePathSpec
 JsonTablePlan
 JsonTablePlanState
+JsonTableSiblingJoin
 JsonTokenType
 JsonTransformStringValuesAction
 JsonTypeCategory
-- 
2.43.0