JSON_TABLE: Add support for NESTED paths and columns

A NESTED path allows to extract data from nested levels of JSON
objects given by the parent path expression, which are projected as
columns specified using a nested COLUMNS clause, just like the parent
COLUMNS clause.  Rows comprised from a NESTED columns are "joined"
to the row comprised from the parent columns.  If a particular NESTED
path evaluates to 0 rows, then the nested COLUMNS will emit NULLs,
making it an OUTER join.

NESTED columns themselves may include NESTED paths to allow
extracting data from arbitrary nesting levels, which are likewise
joined against the rows at the parent level.

Multiple NESTED paths at a given level are called "sibling" paths
and their rows are combined by UNIONing them, that is, after being
joined against the parent row as described above.

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

6 files changed, 394 insertions, 26 deletions

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 80ac59fba46..c002f37202f 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 fcd0d834b20..e1df1894b69 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 ee7a89045c3..0523f7e891e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -755,7 +755,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
@@ -884,8 +884,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 */
@@ -14270,6 +14273,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:
@@ -17688,6 +17720,7 @@ unreserved_keyword:
 			| MOVE
 			| NAME_P
 			| NAMES
+			| NESTED
 			| NEW
 			| NEXT
 			| NFC
@@ -18304,6 +18337,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 060f62170e8..99d3101f6b2 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 -
@@ -172,13 +179,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);
+		}
 	}
 }
 
@@ -234,6 +260,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)
 	{
@@ -243,9 +275,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
@@ -303,6 +338,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 					break;
 				}
 
+			case JTC_NESTED:
+				continue;
+
 			default:
 				elog(ERROR, "unknown JSON_TABLE column type: %d", (int) rawc->coltype);
 				break;
@@ -314,7 +352,21 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 		tf->colvalexprs = lappend(tf->colvalexprs, colexpr);
 	}
 
-	return makeJsonTablePathScan(pathspec, errorOnError);
+	/* End of column range. */
+	if (list_length(tf->colvalexprs) == colMin)
+	{
+		/* No columns in this Scan beside the nested ones. */
+		colMax = colMin = -1;
+	}
+	else
+		colMax = list_length(tf->colvalexprs) - 1;
+
+	/* Recursively transform nested columns */
+	childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns);
+
+	/* Create a "parent" scan responsible for all columns handled above. */
+	return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax,
+								 childplan);
 }
 
 /*
@@ -397,10 +449,58 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 }
 
 /*
+ * 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;
+
+	/*
+	 * If there are multiple NESTED COLUMNS clauses in 'columns', their
+	 * respective plans will be combined using a "sibling join" plan, which
+	 * effectively does a UNION of the sets of rows coming from each nested
+	 * plan.
+	 */
+	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);
+
+		if (plan)
+			plan = makeJsonTableSiblingJoin(plan, nested);
+		else
+			plan = nested;
+	}
+
+	return plan;
+}
+
+/*
  * Create a JsonTablePlan for given path and ON ERROR behavior.
+ *
+ * colMin and colMin give the range of columns computed by this scan in the
+ * global flat list of column expressions that will be passed to the
+ * JSON_TABLE's TableFunc.  Both are -1 when all of columns are nested and
+ * thus computed by 'childplan'.
  */
 static JsonTablePlan *
-makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
+makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
+					  int colMin, int colMax,
+					  JsonTablePlan *childplan)
 {
 	JsonTablePathScan *scan = makeNode(JsonTablePathScan);
 	char	   *pathstring;
@@ -417,5 +517,29 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError)
 	scan->path = makeJsonTablePath(value, pathspec->name);
 	scan->errorOnError = errorOnError;
 
+	scan->child = childplan;
+
+	scan->colMin = colMin;
+	scan->colMax = colMax;
+
 	return (JsonTablePlan *) scan;
 }
+
+/*
+ * Create a JsonTablePlan that will perform a join of the rows coming from
+ * 'lplan' and 'rplan'.
+ *
+ * The default way of "joining" the rows is to perform a UNION between the
+ * sets of rows from 'lplan' and 'rplan'.
+ */
+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 75c468bc085..103572ed932 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -202,6 +202,18 @@ 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;
 } JsonTablePlanState;
 
 /* Random number to identify JsonTableExecContext for sanity checking */
@@ -213,6 +225,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,6 +355,7 @@ 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);
@@ -345,6 +364,9 @@ 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 JsonTablePlanScanNextRow(JsonTablePlanState *planstate);
+static void JsonTableResetNestedPlan(JsonTablePlanState *planstate);
+static bool JsonTablePlanJoinNextRow(JsonTablePlanState *planstate);
 static bool JsonTablePlanNextRow(JsonTablePlanState *planstate);
 
 const TableFuncRoutine JsonbTableRoutine =
@@ -4087,8 +4109,14 @@ JsonTableInitOpaque(TableFuncScanState *state, int natts)
 		}
 	}
 
-	/* Initialize plan */
-	cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, args,
+	cxt->colplanstates = palloc(sizeof(JsonTablePlanState *) *
+								list_length(tf->colvalexprs));
+
+	/*
+	 * Initialize plan for the root path and, recursively, also any child
+	 * plans that compute the NESTED paths.
+	 */
+	cxt->rootplanstate = JsonTableInitPlan(cxt, rootplan, NULL, args,
 										   CurrentMemoryContext);
 
 	state->opaque = cxt;
@@ -4113,19 +4141,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 +4166,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 >= 0 && 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;
@@ -4193,16 +4239,56 @@ JsonTableResetRowPattern(JsonTablePlanState *planstate, Datum item)
 }
 
 /*
- * Fetch next row from a JsonTablePlan's path evaluation result.
+ * Fetch next row from a JsonTablePlan.
  *
  * Returns false if the plan has run out of rows, true otherwise.
  */
 static bool
 JsonTablePlanNextRow(JsonTablePlanState *planstate)
 {
-	JsonbValue *jbv = JsonValueListNext(&planstate->found, &planstate->iter);
+	if (IsA(planstate->plan, JsonTablePathScan))
+		return JsonTablePlanScanNextRow(planstate);
+	else if (IsA(planstate->plan, JsonTableSiblingJoin))
+		return JsonTablePlanJoinNextRow(planstate);
+	else
+		elog(ERROR, "invalid JsonTablePlan %d", (int) planstate->plan->type);
+
+	Assert(false);
+	/* Appease compiler */
+	return false;
+}
+
+/*
+ * Fetch next row from a JsonTablePlan's path evaluation result and from
+ * any child nested path(s).
+ *
+ * Returns true if any of the paths (this or the nested) has more rows to
+ * return.
+ *
+ * By fetching the nested path(s)'s rows based on the parent row at each
+ * level, this essentially joins the rows of different levels.  If a nested
+ * path at a given level has no matching rows, the columns of that level will
+ * compute to NULL, making it an OUTER join.
+ */
+static bool
+JsonTablePlanScanNextRow(JsonTablePlanState *planstate)
+{
+	JsonbValue *jbv;
 	MemoryContext oldcxt;
 
+	/*
+	 * If planstate already has an active row and there is a nested plan,
+	 * check if it has an active row to join with the former.
+	 */
+	if (!planstate->current.isnull)
+	{
+		if (planstate->nested && JsonTablePlanNextRow(planstate->nested))
+			return true;
+	}
+
+	/* Fetch new row from the list of found values to set as active. */
+	jbv = JsonValueListNext(&planstate->found, &planstate->iter);
+
 	/* End of list? */
 	if (jbv == NULL)
 	{
@@ -4223,6 +4309,76 @@ JsonTablePlanNextRow(JsonTablePlanState *planstate)
 	/* Next row! */
 	planstate->ordinal++;
 
+	/* Process nested plan(s), if any. */
+	if (planstate->nested)
+	{
+		/* Re-evaluate the nested path using the above parent row. */
+		JsonTableResetNestedPlan(planstate->nested);
+
+		/*
+		 * Now fetch the nested plan's current row to be joined against the
+		 * parent row.  Any further nested plans' paths will be re-evaluated
+		 * reursively, level at a time, after setting each nested plan's
+		 * current row.
+		 */
+		(void) JsonTablePlanNextRow(planstate->nested);
+	}
+
+	/* There are more rows. */
+	return true;
+}
+
+/*
+ * Re-evaluate the row pattern of a nested plan using the new parent row
+ * pattern.
+ */
+static void
+JsonTableResetNestedPlan(JsonTablePlanState *planstate)
+{
+	/* This better be a child plan. */
+	Assert(planstate->parent != NULL);
+	if (IsA(planstate->plan, JsonTablePathScan))
+	{
+		JsonTablePlanState *parent = planstate->parent;
+
+		if (!parent->current.isnull)
+			JsonTableResetRowPattern(planstate, parent->current.value);
+
+		/*
+		 * If this plan itself has a child nested plan, it will be reset when
+		 * the caller calls JsonTablePlanNextRow() on this plan.
+		 */
+	}
+	else if (IsA(planstate->plan, JsonTableSiblingJoin))
+	{
+		JsonTableResetNestedPlan(planstate->left);
+		JsonTableResetNestedPlan(planstate->right);
+	}
+}
+
+/*
+ * Fetch the next row from a JsonTableSiblingJoin.
+ *
+ * This is essentially a UNION between the rows from left and right siblings.
+ */
+static bool
+JsonTablePlanJoinNextRow(JsonTablePlanState *planstate)
+{
+
+	/* Fetch row from left sibling. */
+	if (!JsonTablePlanNextRow(planstate->left))
+	{
+		/*
+		 * Left sibling ran out of rows, so start fetching from the right
+		 * sibling.
+		 */
+		if (!JsonTablePlanNextRow(planstate->right))
+		{
+			/* Right sibling ran out of row, so there are more rows. */
+			return false;
+		}
+	}
+
 	return true;
 }
 
@@ -4256,7 +4412,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 466d9576a21..52bf87ac55b 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 ")
 
@@ -11627,10 +11632,43 @@ get_xmltable(TableFunc *tf, deparse_context *context, bool showimplicit)
 }
 
 /*
+ * 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;
@@ -11663,7 +11701,16 @@ get_json_table_columns(TableFunc *tf, deparse_context *context,
 		typmod = lfirst_int(lc_coltypmod);
 		colexpr = castNode(JsonExpr, lfirst(lc_colvalexpr));
 
-		if (colnum > 0)
+		/* Skip columns that don't belong to this scan. */
+		if (scan->colMin < 0 || colnum < scan->colMin)
+		{
+			colnum++;
+			continue;
+		}
+		if (colnum > scan->colMax)
+			break;
+
+		if (colnum > scan->colMin)
 			appendStringInfoString(buf, ", ");
 
 		colnum++;
@@ -11711,6 +11758,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->colMin >= 0);
+
 	if (PRETTY_INDENT(context))
 		context->indentLevel -= PRETTYINDENT_VAR;
 
@@ -11774,7 +11825,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");