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

From 234947aea76cc9c3dfa5a200cf3bc395e52f582b Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Thu, 18 Jan 2024 18:00:06 +0900
Subject: [PATCH v49 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                        |  94 +++++-
 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          | 127 +++++++-
 src/backend/utils/adt/jsonpath_exec.c         | 148 +++++++++-
 src/backend/utils/adt/ruleutils.c             |  49 +++-
 src/include/nodes/parsenodes.h                |   2 +
 src/include/nodes/primnodes.h                 |  22 ++
 src/include/parser/kwlist.h                   |   1 +
 .../test/expected/sql-sqljson_jsontable.c     |  14 +-
 .../expected/sql-sqljson_jsontable.stderr     |   8 +
 .../expected/sql-sqljson_jsontable.stdout     |   1 +
 .../ecpg/test/sql/sqljson_jsontable.pgc       |   8 +
 .../regress/expected/sqljson_jsontable.out    | 274 ++++++++++++++++++
 src/test/regress/sql/sqljson_jsontable.sql    | 129 +++++++++
 src/tools/pgindent/typedefs.list              |   2 +
 17 files changed, 895 insertions(+), 26 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1467791666..feace8c773 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18893,6 +18893,22 @@ DETAIL:  Missing "]" after array dimensions.
    a given output row.
   </para>
 
+  <para>
+   JSON data stored at a nested level of the row pattern can be extracted using
+   the <literal>NESTED PATH</literal> clause.  Each
+   <literal>NESTED PATH</literal> clause can be used to generate one or more
+   columns using the data from a nested level of the row pattern, which can be
+   specified using a <literal>COLUMNS</literal> clause.  Rows constructed from
+   such columns are called <firstterm>child rows</firstterm> and are joined
+   agaist the row constructed from the columns specified in the parent
+   <literal>COLUMNS</literal> clause to get the row in the final view.  Child
+   columns may themselves contain a <literal>NESTED PATH</literal>
+   specifification thus allowing to extract data located at arbitrary nesting
+   levels.  Columns produced by <literal>NESTED PATH</literal>s at the same
+   level are considered to be <firstterm>siblings</firstterm> and are joined
+   with each other before joining to the parent row.
+  </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
@@ -18924,6 +18940,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>path_expression</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>
@@ -18971,7 +18989,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
     <listitem>
     <para>
      Adds an ordinality column that provides sequential row numbering starting
-     from 1.
+     from 1.  Each <literal>NESTED PATH</literal> (see below) gets its own
+     counter for any nested ordinality columns.
     </para>
     </listitem>
    </varlistentry>
@@ -19058,6 +19077,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>
 
    <note>
@@ -19173,6 +19219,52 @@ SELECT jt.* FROM
   1 | horror   | Psycho  | "Alfred Hitchcock"
   2 | thriller | Vertigo | "Alfred Hitchcock"
 (2 rows)
+</screen>
+     </para>
+     <para>
+      The following is a modified version of the above query to show the usage
+      of <literal>NESTED PATH</literal> for populating title and director
+      columns, illustrating how they are joined to the parent columns id and
+      kind:
+<screen>
+SELECT jt.* FROM
+ my_films,
+ JSON_TABLE ( js, '$.favorites[*] ? (@.films[*].director == $filter)'
+   PASSING 'Alfred Hitchcock' AS filter
+   COLUMNS (
+    id FOR ORDINALITY,
+    kind text PATH '$.kind',
+    NESTED PATH '$.films[*]' COLUMNS (
+      title text FORMAT JSON PATH '$.title' OMIT QUOTES,
+      director text PATH '$.director' KEEP QUOTES))) AS jt;
+ id |   kind   |  title  |      director
+----+----------+---------+--------------------
+  1 | horror   | Psycho  | "Alfred Hitchcock"
+  2 | thriller | Vertigo | "Alfred Hitchcock"
+(2 rows)
+</screen>
+     </para>
+     <para>
+      The following is the same query but without the filter in the root
+      path:
+<screen>
+SELECT jt.* FROM
+ my_films,
+ JSON_TABLE ( js, '$.favorites[*]'
+   COLUMNS (
+    id FOR ORDINALITY,
+    kind text PATH '$.kind',
+    NESTED PATH '$.films[*]' COLUMNS (
+      title text FORMAT JSON PATH '$.title' OMIT QUOTES,
+      director text PATH '$.director' KEEP QUOTES))) AS jt;
+ id |   kind   |      title      |      director
+----+----------+-----------------+--------------------
+  1 | comedy   | Bananas         | "Woody Allen"
+  1 | comedy   | The Dinner Game | "Francis Veber"
+  2 | horror   | Psycho          | "Alfred Hitchcock"
+  3 | thriller | Vertigo         | "Alfred Hitchcock"
+  4 | drama    | Yojimbo         | "Akira Kurosawa"
+(5 rows)
 </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 fcd0d834b2..e1df1894b6 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4159,6 +4159,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 6ea68722e3..6dcf5cceb8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -752,7 +752,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
@@ -881,8 +881,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 */
@@ -14218,6 +14221,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:
@@ -17636,6 +17668,7 @@ unreserved_keyword:
 			| MOVE
 			| NAME_P
 			| NAMES
+			| NESTED
 			| NEW
 			| NEXT
 			| NFC
@@ -18250,6 +18283,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 e0e2c7d61e..b4e6afbdc9 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -44,16 +44,23 @@ static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt,
 												List *columns,
 												List *passingArgs,
 												JsonTablePathSpec *pathspec);
+static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt,
+													  List *passingArgs,
+													  List *columns);
 static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc,
 											  Node *contextItemExpr,
 											  List *passingArgs);
 static bool isCompositeType(Oid typid);
 static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec,
-											bool errorOnError);
+											bool errorOnError,
+											int colMin, int colMax,
+											JsonTablePlan *childplan);
 static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
 											List *columns);
 static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name);
 static char *generateJsonTablePathName(JsonTableParseContext *cxt);
+static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan,
+											   JsonTablePlan *rplan);
 
 /*
  * transformJsonTable -
@@ -170,13 +177,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);
+		}
 	}
 }
 
@@ -232,6 +258,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;
+	JsonTablePlan *childplan;
+
+	/* Start of column range */
+	colMin = list_length(tf->colvalexprs);
 
 	foreach(col, columns)
 	{
@@ -241,9 +273,12 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 		Oid			typcoll = InvalidOid;
 		Node	   *colexpr;
 
-		Assert(rawc->name);
-		tf->colnames = lappend(tf->colnames,
-							   makeString(pstrdup(rawc->name)));
+		if (rawc->coltype != JTC_NESTED)
+		{
+			Assert(rawc->name);
+			tf->colnames = lappend(tf->colnames,
+								   makeString(pstrdup(rawc->name)));
+		}
 
 		/*
 		 * Determine the type and typmod for the new column. FOR ORDINALITY
@@ -301,6 +336,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 					break;
 				}
 
+			case JTC_NESTED:
+				continue;
+
 			default:
 				elog(ERROR, "unknown JSON_TABLE column type: %d", (int) rawc->coltype);
 				break;
@@ -312,7 +350,14 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 		tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
 	}
 
-	return makeJsonTablePathScan(pathspec, errorOnError);
+	/* End of column range */
+	colMax = list_length(tf->colvalexprs) - 1;
+
+	/* Transform recursively nested columns */
+	childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns);
+
+	return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax,
+								 childplan);
 }
 
 /*
@@ -395,11 +440,50 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 	return jfexpr;
 }
 
+/*
+ * Recursively transform nested columns and create child plan(s) that will be
+ * used to evaluate their row patterns.
+ */
+static JsonTablePlan *
+transformJsonTableNestedColumns(JsonTableParseContext *cxt,
+								List *passingArgs,
+								List *columns)
+{
+	JsonTablePlan *plan = NULL;
+	ListCell   *lc;
+
+	/* transform all nested columns into UNION join */
+	foreach(lc, columns)
+	{
+		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+		JsonTablePlan *nested;
+
+		if (jtc->coltype != JTC_NESTED)
+			continue;
+
+		if (jtc->pathspec->name == NULL)
+			jtc->pathspec->name = generateJsonTablePathName(cxt);
+
+		nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs,
+										   jtc->pathspec);
+
+		/* Join nested plan with previous sibling nested plans. */
+		if (plan)
+			plan = makeJsonTableSiblingJoin(plan, nested);
+		else
+			plan = nested;
+	}
+
+	return plan;
+}
+
 /*
  * Create a JsonTablePlan for given path and ON ERROR behavior.
  */
 static JsonTablePlan *
-makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
+makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
+					  int colMin, int colMax,
+					  JsonTablePlan *childplan)
 {
 	JsonTablePathScan *scan = makeNode(JsonTablePathScan);
 	char	   *pathstring;
@@ -416,5 +500,22 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
 	scan->path = makeJsonTablePath(value, pathspec->name);
 	scan->errorOnError = errorOnError;
 
+	scan->colMin = colMin;
+	scan->colMax = colMax;
+
+	scan->child = childplan;
+
 	return (JsonTablePlan *) scan;
 }
+
+static JsonTablePlan *
+makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan)
+{
+	JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin);
+
+	join->plan.type = T_JsonTableSiblingJoin;
+	join->lplan = lplan;
+	join->rplan = rplan;
+
+	return (JsonTablePlan *) join;
+}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 75c468bc08..d9ad80b077 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -202,6 +202,23 @@ typedef struct JsonTablePlanState
 
 	/* Counter for ORDINAL columns */
 	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 */
@@ -213,6 +230,12 @@ typedef struct JsonTableExecContext
 
 	/* State of the plan providing a row evaluated from "root" jsonpath */
 	JsonTablePlanState *rootplanstate;
+
+	/*
+	 * Per-column JsonTablePlanStates for all columns including the nested
+	 * ones.
+	 */
+	JsonTablePlanState **colplanstates;
 } JsonTableExecContext;
 
 /* strict/lax flags is decomposed into four [un]wrap/error flags */
@@ -337,15 +360,18 @@ static void checkTimezoneIsUsedForCast(bool useTz, const char *type1,
 static void JsonTableInitOpaque(TableFuncScanState *state, int natts);
 static JsonTablePlanState *JsonTableInitPlan(JsonTableExecContext *cxt,
 											 JsonTablePlan *plan,
+											 JsonTablePlanState *parentstate,
 											 List *args,
 											 MemoryContext mcxt);
 static void JsonTableSetDocument(TableFuncScanState *state, Datum value);
 static void JsonTableResetRowPattern(JsonTablePlanState *plan, Datum item);
+static void JsonTablePlanReset(JsonTablePlanState *planstate);
 static bool JsonTableFetchRow(TableFuncScanState *state);
 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);
 
 const TableFuncRoutine JsonbTableRoutine =
 {
@@ -4087,8 +4113,11 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts)
 		}
 	}
 
+	cxt->colplanstates = palloc(sizeof(JsonTablePlanState *) *
+								list_length(tf->colvalexprs));
+
 	/* Initialize plan */
-	cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, args,
+	cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, NULL, args,
 										   CurrentMemoryContext);
 
 	state->opaque = cxt;
@@ -4113,19 +4142,22 @@ JsonTableDestroyOpaque(TableFuncScanState *state)
 /*
  * JsonTableInitPlan
  *		Initialize information for evaluating jsonpath in the given
- *		JsonTablePlan
+ *		JsonTablePlan and, recursively, in any child plans
  */
 static JsonTablePlanState *
 JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
+				  JsonTablePlanState *parentstate,
 				  List *args, MemoryContext mcxt)
 {
 	JsonTablePlanState *planstate = palloc0(sizeof(*planstate));
 
 	planstate->plan = plan;
+	planstate->parent = parentstate;
 
 	if (IsA(plan, JsonTablePathScan))
 	{
 		JsonTablePathScan *scan = (JsonTablePathScan *) plan;
+		int			i;
 
 		planstate->path = DatumGetJsonPathP(scan->path->value->constvalue);
 		planstate->args = args;
@@ -4135,6 +4167,21 @@ JsonTableInitPlan(JsonTableExecContext *cxt, JsonTablePlan *plan,
 		/* No row pattern evaluated yet. */
 		planstate->current.value = PointerGetDatum(NULL);
 		planstate->current.isnull = true;
+
+		for (i = scan->colMin; i <= scan->colMax; i++)
+			cxt->colplanstates[i] = planstate;
+
+		planstate->nested = scan->child ?
+			JsonTableInitPlan(cxt, scan->child, planstate, args, mcxt) : NULL;
+	}
+	else if (IsA(plan, JsonTableSiblingJoin))
+	{
+		JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan;
+
+		planstate->left = JsonTableInitPlan(cxt, join->lplan, parentstate,
+											args, mcxt);
+		planstate->right = JsonTableInitPlan(cxt, join->rplan, parentstate,
+											 args, mcxt);
 	}
 
 	return planstate;
@@ -4192,13 +4239,33 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item)
 	planstate->ordinal = 0;
 }
 
+/* Recursively set 'reset' flag of planstate and its child nodes */
+static void
+JsonTablePlanReset(JsonTablePlanState *planstate)
+{
+	if (IsA(planstate->plan, JsonTablePathScan))
+	{
+		planstate->reset = true;
+		planstate->advanceNested = false;
+
+		if (planstate->nested)
+			JsonTablePlanReset(planstate->nested);
+	}
+	else if (IsA(planstate->plan, JsonTableSiblingJoin))
+	{
+		JsonTablePlanReset(planstate->left);
+		JsonTablePlanReset(planstate->right);
+		planstate->advanceRight = false;
+	}
+}
+
 /*
  * Fetch next row from a JsonTablePlan's path evaluation result.
  *
  * 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;
@@ -4226,6 +4293,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->current.isnull);
+			JsonTableResetRowPattern(planstate, parent->current.value);
+			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.
@@ -4256,7 +4396,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->colplanstates[colnum];
 	JsonTablePlanRowSource *current = &planstate->current;
 	Datum		result;
 
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c9e3ac88cb..cd1c695f38 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -524,8 +524,13 @@ static char *flatten_reloptions(Oid relid);
 static void get_reloptions(StringInfo buf, Datum reloptions);
 static void get_json_path_spec(Node *path_spec, deparse_context *context,
 							   bool showimplicit);
-static void get_json_table_columns(TableFunc *tf, deparse_context *context,
+static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
+								   deparse_context *context,
 								   bool showimplicit);
+static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
+										  deparse_context *context,
+										  bool showimplicit,
+										  bool needcomma);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -11620,11 +11625,44 @@ 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, JsonTablePlan *plan,
+							  deparse_context *context, bool showimplicit,
+							  bool needcomma)
+{
+	if (IsA(plan, JsonTablePathScan))
+	{
+		JsonTablePathScan *scan = castNode(JsonTablePathScan, plan);
+
+		if (needcomma)
+			appendStringInfoChar(context->buf, ',');
+
+		appendStringInfoChar(context->buf, ' ');
+		appendContextKeyword(context, "NESTED PATH ", 0, 0, 0);
+		get_const_expr(scan->path->value, context, -1);
+		appendStringInfo(context->buf, " AS %s", quote_identifier(scan->path->name));
+		get_json_table_columns(tf, scan, context, showimplicit);
+	}
+	else if (IsA(plan, JsonTableSiblingJoin))
+	{
+		JsonTableSiblingJoin *join = (JsonTableSiblingJoin *) plan;
+
+		get_json_table_nested_columns(tf, join->lplan, context, showimplicit,
+									  needcomma);
+		get_json_table_nested_columns(tf, join->rplan, context, showimplicit,
+									  true);
+	}
+}
+
 /*
  * get_json_table_columns - Parse back JSON_TABLE columns
  */
 static void
-get_json_table_columns(TableFunc *tf, deparse_context *context,
+get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
+					   deparse_context *context,
 					   bool showimplicit)
 {
 	StringInfo	buf = context->buf;
@@ -11705,6 +11743,10 @@ get_json_table_columns(TableFunc *tf, deparse_context *context,
 		get_json_expr_options(colexpr, context, default_behavior);
 	}
 
+	if (scan->child)
+		get_json_table_nested_columns(tf, scan->child, context, showimplicit,
+									  scan->colMax >= scan->colMin);
+
 	if (PRETTY_INDENT(context))
 		context->indentLevel -= PRETTYINDENT_VAR;
 
@@ -11768,7 +11810,8 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
 			context->indentLevel -= PRETTYINDENT_VAR;
 	}
 
-	get_json_table_columns(tf, context, showimplicit);
+	get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context,
+						   showimplicit);
 
 	if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY)
 		get_json_behavior(jexpr->on_error, context, "ERROR");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9946ef6387..0a8e6af42b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1815,6 +1815,7 @@ typedef enum JsonTableColumnType
 	JTC_REGULAR,
 	JTC_EXISTS,
 	JTC_FORMATTED,
+	JTC_NESTED,
 } JsonTableColumnType;
 
 /*
@@ -1831,6 +1832,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 9c44240d0c..36dce9f2bc 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1865,8 +1865,30 @@ typedef struct JsonTablePathScan
 
 	/* 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. */
+	JsonTablePlan *child;
 } JsonTablePathScan;
 
+/*
+ * JsonTableSiblingJoin -
+ *		Plan to union-join rows of nested paths of the same level
+ */
+typedef struct JsonTableSiblingJoin
+{
+	JsonTablePlan plan;
+
+	JsonTablePlan *lplan;
+	JsonTablePlan *rplan;
+} JsonTableSiblingJoin;
+
 /* ----------------
  * NullTest
  *
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 2d4a0c6a07..6344d7cfc6 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 42a1b176e7..b2a0f11eb6 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
@@ -132,11 +132,21 @@ if (sqlca.sqlcode < 0) sqlprint();}
 
   printf("Found foo=%d\n", foo);
 
+  { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select foo from json_table ( jsonb '[{\"foo\":\"1\"}]' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) ) ) ) jt ( foo )", ECPGt_EOIT, 
+	ECPGt_int,&(foo),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 31 "sqljson_jsontable.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 31 "sqljson_jsontable.pgc"
+
+  printf("Found foo=%d\n", foo);
+
   { ECPGdisconnect(__LINE__, "CURRENT");
-#line 26 "sqljson_jsontable.pgc"
+#line 34 "sqljson_jsontable.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 26 "sqljson_jsontable.pgc"
+#line 34 "sqljson_jsontable.pgc"
 
 
   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 d3713cff5c..9262cf71a1 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
@@ -12,5 +12,13 @@
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ecpg_get_data on line 20: RESULT: 1 offset: -1; array: no
 [NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 26: query: select foo from json_table ( jsonb '[{"foo":"1"}]' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) ) ) ) jt ( foo ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 26: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 26: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 26: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
 [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_jsontable.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
index 615507e602..1e6f358a89 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stdout
@@ -1 +1,2 @@
 Found foo=1
+Found foo=1
diff --git a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
index 6d721bb37f..aa2b4494bb 100644
--- a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
@@ -23,6 +23,14 @@ EXEC SQL END DECLARE SECTION;
 	)) jt (foo);
   printf("Found foo=%d\n", foo);
 
+  EXEC SQL SELECT foo INTO :foo FROM JSON_TABLE(jsonb '[{"foo":"1"}]', '$[*]' AS p0
+	COLUMNS (
+		NESTED '$' AS p1 COLUMNS (
+			NESTED PATH '$' AS p11 COLUMNS ( foo int )
+		)
+	)) jt (foo);
+  printf("Found foo=%d\n", foo);
+
   EXEC SQL DISCONNECT;
 
   return 0;
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 77c04d9763..f11c78c2d0 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;
@@ -634,3 +733,178 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 ERROR:  only string constants are supported in JSON_TABLE path specification
 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
                                                      ^
+-- 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_id for ordinality, b int path '$' ),
+			nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' )
+		)
+	) jt;
+ n | a  | b_id | b | c_id | c  
+---+----+------+---+------+----
+ 1 |  1 |      |   |      |   
+ 2 |  2 |    1 | 1 |      |   
+ 2 |  2 |    2 | 2 |      |   
+ 2 |  2 |    3 | 3 |      |   
+ 2 |  2 |      |   |    1 | 10
+ 2 |  2 |      |   |    2 |   
+ 2 |  2 |      |   |    3 | 20
+ 3 |  3 |    1 | 1 |      |   
+ 3 |  3 |    2 | 2 |      |   
+ 4 | -1 |    1 | 1 |      |   
+ 4 | -1 |    2 | 2 |      |   
+(11 rows)
+
+-- PASSING arguments are passed to nested paths and their columns' 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 '$ ? (@ >= $y)'
+			)
+		)
+	) 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)
+
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index bdce46361d..a00bc030db 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;
@@ -288,3 +315,105 @@ FROM JSON_TABLE(
 
 -- Should fail (not supported)
 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
+
+-- 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_id for ordinality, b int path '$' ),
+			nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' )
+		)
+	) jt;
+
+
+-- PASSING arguments are passed to nested paths and their columns' 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 '$ ? (@ >= $y)'
+			)
+		)
+	) jt;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ba8471b144..3bedea5338 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1327,6 +1327,7 @@ JsonPathMutableContext
 JsonPathParseItem
 JsonPathParseResult
 JsonPathPredicateCallback
+JsonPathSpec
 JsonPathString
 JsonPathVariable
 JsonQuotes
@@ -1345,6 +1346,7 @@ JsonTablePathSpec
 JsonTablePlan
 JsonTablePlanRowSource
 JsonTablePlanState
+JsonTableSiblingJoin
 JsonTokenType
 JsonTransformStringValuesAction
 JsonTypeCategory
-- 
2.43.0