v17-0006-JSON-TABLE-PLAN-clause.patch

application/octet-stream
Filename: v17-0006-JSON-TABLE-PLAN-clause.patch
Type: application/octet-stream
Part: 0
Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: format-patch
Series: patch v17-0006
Subject: Add the PLAN clauses for JSON_TABLE
File	+	−
src/backend/catalog/sql_features.txt	2	2
src/backend/nodes/makefuncs.c	54	0
src/backend/nodes/nodeFuncs.c	1	1
src/backend/parser/gram.y	96	1
src/backend/parser/parse_jsontable.c	199	23
src/backend/utils/adt/ruleutils.c	47	0
src/include/nodes/makefuncs.h	5	0
src/include/nodes/parsenodes.h	60	17
src/include/nodes/primnodes.h	2	0
src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c	5	5
src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr	1	1
src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc	2	1
src/test/regress/expected/sqljson_jsontable.out	585	49
src/test/regress/sql/sqljson_jsontable.sql	354	8
src/tools/pgindent/typedefs.list	4	0
From ffcb858988f37b87b7abd11c913998cbfc7ea0ae Mon Sep 17 00:00:00 2001
From: Nikita Malakhov <n.malakhov@postgrespro.ru>
Date: Sun, 3 Nov 2024 21:28:34 +0300
Subject: [PATCH 1/2] Add the PLAN clauses for JSON_TABLE
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds the PLAN clauses for JSON_TABLE, which allow the user
to specify how data from nested paths are joined, allowing
considerable freedom in shaping the tabular output of JSON_TABLE.
PLAN DEFAULT allows the user to specify the global strategies when
dealing with sibling or child nested paths. The is often sufficient
to achieve the necessary goal, and is considerably simpler than the
full PLAN clause, which allows the user to specify the strategy to be
used for each named nested path.

This is a part of the v45-0001-JSON_TABLE.patch from
https://www.postgresql.org/message-id/CA%2BHiwqE1gcPkQhBko%2BUbvVvAtRBaLfOpmHbFrK79pW_5F51Oww%40mail.gmail.com

Author: Nikita Glukhov
Author: Teodor Sigaev
Author: Oleg Bartunov
Author: Alexander Korotkov
Author: Andrew Dunstan
Author: Amit Langote
Author: Anton A. Melnikov
Author: Nikita Malakhov

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
---
 src/backend/catalog/sql_features.txt          |   4 +-
 src/backend/nodes/makefuncs.c                 |  54 +++
 src/backend/nodes/nodeFuncs.c                 |   2 +-
 src/backend/parser/gram.y                     |  96 ++++-
 src/backend/parser/parse_jsontable.c          |  47 ++-
 src/backend/utils/adt/ruleutils.c             |  47 +++
 src/include/nodes/makefuncs.h                 |   5 +
 src/include/nodes/parsenodes.h                |  77 +++-
 src/include/nodes/primnodes.h                 |   2 +
 .../test/expected/sql-sqljson_jsontable.c     |  10 +-
 .../expected/sql-sqljson_jsontable.stderr     |   2 +-
 .../ecpg/test/sql/sqljson_jsontable.pgc       |   3 +-
 src/test/regress/sql/sqljson_jsontable.sql    | 362 +++++++++++++++++-
 src/tools/pgindent/typedefs.list              |   4 +
 14 files changed, 665 insertions(+), 50 deletions(-)

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index c002f37202..a0e63f454e 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -550,7 +550,7 @@ T814	Colon in JSON_OBJECT or JSON_OBJECTAGG			YES
 T821	Basic SQL/JSON query operators			YES	
 T822	SQL/JSON: IS JSON WITH UNIQUE KEYS predicate			YES	
 T823	SQL/JSON: PASSING clause			YES	
-T824	JSON_TABLE: specific PLAN clause			NO	
+T824	JSON_TABLE: specific PLAN clause			YES	
 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			YES	
@@ -564,7 +564,7 @@ T834	SQL/JSON path language: wildcard member accessor			YES
 T835	SQL/JSON path language: filter expressions			YES	
 T836	SQL/JSON path language: starts with predicate			YES	
 T837	SQL/JSON path language: regex_like predicate			YES	
-T838	JSON_TABLE: PLAN DEFAULT clause			NO	
+T838	JSON_TABLE: PLAN DEFAULT clause			YES	
 T839	Formatted cast of datetimes to/from character strings			NO	
 T840	Hex integer literals in SQL/JSON path language			YES	
 T851	SQL/JSON: optional keywords for default syntax			YES	
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 9cac3c1c27..24e2f5ce3b 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -890,6 +890,60 @@ makeJsonBehavior(JsonBehaviorType btype, Node *expr, int location)
 	return behavior;
 }
 
+/*
+ * makeJsonTableDefaultPlan -
+ *	   creates a JsonTablePlanSpec node to represent a "default" JSON_TABLE plan
+ *	   with given join strategy
+ */
+Node *
+makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type, int location)
+{
+	JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec);
+
+	n->plan_type = JSTP_DEFAULT;
+	n->join_type = join_type;
+	n->location = location;
+
+	return (Node *) n;
+}
+
+/*
+ * makeJsonTableSimplePlan -
+ *	   creates a JsonTablePlanSpec node to represent a "simple" JSON_TABLE plan
+ *	   for given PATH
+ */
+Node *
+makeJsonTableSimplePlan(char *pathname, int location)
+{
+	JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec);
+
+	n->plan_type = JSTP_SIMPLE;
+	n->pathname = pathname;
+	n->location = location;
+
+	return (Node *) n;
+}
+
+/*
+ * makeJsonTableJoinedPlan -
+ *	   creates a JsonTablePlanSpec node to represent join between the given
+ *	   pair of plans
+ */
+Node *
+makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2,
+						int location)
+{
+	JsonTablePlanSpec *n = makeNode(JsonTablePlanSpec);
+
+	n->plan_type = JSTP_JOINED;
+	n->join_type = type;
+	n->plan1 = castNode(JsonTablePlanSpec, plan1);
+	n->plan2 = castNode(JsonTablePlanSpec, plan2);
+	n->location = location;
+
+	return (Node *) n;
+}
+
 /*
  * makeJsonKeyValue -
  *	  creates a JsonKeyValue node
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 3060847b13..1509a735ce 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4177,7 +4177,7 @@ raw_expression_tree_walker_impl(Node *node,
 					return true;
 				if (WALK(jtc->on_error))
 					return true;
-				if (WALK(jtc->columns))
+				if (jtc->coltype == JTC_NESTED && WALK(jtc->columns))
 					return true;
 			}
 			break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 89fdb94c23..80d3bfb1da 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -648,6 +648,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				json_table
 				json_table_column_definition
 				json_table_column_path_clause_opt
+				json_table_plan_clause_opt
+				json_table_plan
+				json_table_plan_simple
+				json_table_plan_outer
+				json_table_plan_inner
+				json_table_plan_union
+				json_table_plan_cross
+				json_table_plan_primary
 %type <list>	json_name_and_value_list
 				json_value_expr_list
 				json_array_aggregate_order_by_clause_opt
@@ -659,6 +667,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival>	json_behavior_type
 				json_predicate_type_constraint
 				json_quotes_clause_opt
+				json_table_default_plan_choices
+				json_table_default_plan_inner_outer
+				json_table_default_plan_union_cross
 				json_wrapper_behavior
 %type <boolean>	json_key_uniqueness_constraint_opt
 				json_object_constructor_null_clause_opt
@@ -14200,6 +14211,7 @@ json_table:
 				json_value_expr ',' a_expr json_table_path_name_opt
 				json_passing_clause_opt
 				COLUMNS '(' json_table_column_definition_list ')'
+				json_table_plan_clause_opt
 				json_on_error_clause_opt
 			')'
 				{
@@ -14217,7 +14229,13 @@ json_table:
 					n->pathspec = makeJsonTablePathSpec(pathstring, $6, @5, @6);
 					n->passing = $7;
 					n->columns = $10;
-					n->on_error = (JsonBehavior *) $12;
+					if($12 != NULL && $6 == NULL)
+						ereport(ERROR,
+								errcode(ERRCODE_SYNTAX_ERROR),
+								errmsg("PLAN clause requires explicit AS expression"),
+								parser_errposition(@6));
+					n->planspec = (JsonTablePlanSpec *) $12;
+					n->on_error = (JsonBehavior *) $13;
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -14340,6 +14358,82 @@ json_table_column_path_clause_opt:
 			| /* EMPTY */
 				{ $$ = NULL; }
 		;
+json_table_plan_clause_opt:
+			PLAN '(' json_table_plan ')'
+				{ $$ = $3; }
+			| PLAN DEFAULT '(' json_table_default_plan_choices ')'
+				{ $$ = makeJsonTableDefaultPlan($4, @1); }
+			| /* EMPTY */
+				{ $$ = NULL; }
+		;
+
+json_table_plan:
+			json_table_plan_simple
+			| json_table_plan_outer
+			| json_table_plan_inner
+			| json_table_plan_union
+			| json_table_plan_cross
+		;
+
+json_table_plan_simple:
+			name
+				{ $$ = makeJsonTableSimplePlan($1, @1); }
+		;
+
+json_table_plan_outer:
+			json_table_plan_simple OUTER_P json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTP_JOIN_OUTER, $1, $3, @1); }
+		;
+
+json_table_plan_inner:
+			json_table_plan_simple INNER_P json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTP_JOIN_INNER, $1, $3, @1); }
+		;
+
+json_table_plan_union:
+			json_table_plan_primary UNION json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); }
+			| json_table_plan_union UNION json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTP_JOIN_UNION, $1, $3, @1); }
+		;
+
+json_table_plan_cross:
+			json_table_plan_primary CROSS json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); }
+			| json_table_plan_cross CROSS json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTP_JOIN_CROSS, $1, $3, @1); }
+		;
+
+json_table_plan_primary:
+			json_table_plan_simple
+				{ $$ = $1; }
+			| '(' json_table_plan ')'
+				{
+					castNode(JsonTablePlanSpec, $2)->location = @1;
+					$$ = $2;
+				}
+		;
+
+json_table_default_plan_choices:
+			json_table_default_plan_inner_outer
+				{ $$ = $1 | JSTP_JOIN_UNION; }
+			| json_table_default_plan_union_cross
+				{ $$ = $1 | JSTP_JOIN_OUTER; }
+			| json_table_default_plan_inner_outer ',' json_table_default_plan_union_cross
+				{ $$ = $1 | $3; }
+			| json_table_default_plan_union_cross ',' json_table_default_plan_inner_outer
+				{ $$ = $1 | $3; }
+		;
+
+json_table_default_plan_inner_outer:
+			INNER_P						{ $$ = JSTP_JOIN_INNER; }
+			| OUTER_P					{ $$ = JSTP_JOIN_OUTER; }
+		;
+
+json_table_default_plan_union_cross:
+			UNION						{ $$ = JSTP_JOIN_UNION; }
+			| CROSS						{ $$ = JSTP_JOIN_CROSS; }
+		;
 
 /*****************************************************************************
  *
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index ecb140e6e7..3516e2adef 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -41,10 +41,12 @@ typedef struct JsonTableParseContext
 static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt,
 												List *columns,
 												List *passingArgs,
-												JsonTablePathSpec *pathspec);
+												JsonTablePathSpec *pathspec,
+												JsonTablePlanSpec *planspec);
 static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt,
 													  List *passingArgs,
-													  List *columns);
+													  List *columns,
+													  JsonTablePlanSpec *planspec);
 static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc,
 											  Node *contextItemExpr,
 											  List *passingArgs);
@@ -52,13 +54,15 @@ static bool isCompositeType(Oid typid);
 static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec,
 											bool errorOnError,
 											int colMin, int colMax,
-											JsonTablePlan *childplan);
+											JsonTablePlan *childplan,
+											JsonTablePlanSpec *planspec);
 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);
+											   JsonTablePlan *rplan,
+											   bool cross);
 
 /*
  * transformJsonTable -
@@ -77,12 +81,20 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	JsonFuncExpr *jfe;
 	JsonExpr   *je;
 	JsonTablePathSpec *rootPathSpec = jt->pathspec;
+	JsonTablePlanSpec *rootPlanSpec = jt->planspec;
 	bool		is_lateral;
 	JsonTableParseContext cxt = {pstate};
 
 	Assert(IsA(rootPathSpec->string, A_Const) &&
 		   castNode(A_Const, rootPathSpec->string)->val.node.type == T_String);
 
+	if(rootPlanSpec && !rootPathSpec)
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+				errmsg("JSON_TABLE must contain explicit AS path"),
+				errdetail("PLAN clause requires explicit AS path."),
+				parser_errposition(pstate, jt->on_error->location));
+
 	if (jt->on_error &&
 		jt->on_error->btype != JSON_BEHAVIOR_ERROR &&
 		jt->on_error->btype != JSON_BEHAVIOR_EMPTY &&
@@ -137,7 +149,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	cxt.tf = tf;
 	tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns,
 												  jt->passing,
-												  rootPathSpec);
+												  rootPathSpec, rootPlanSpec);
 
 	/*
 	 * Copy the transformed PASSING arguments into the TableFunc node, because
@@ -248,7 +260,7 @@ generateJsonTablePathName(JsonTableParseContext *cxt)
 static JsonTablePlan *
 transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 						  List *passingArgs,
-						  JsonTablePathSpec *pathspec)
+						  JsonTablePathSpec *pathspec, JsonTablePlanSpec *planspec)
 {
 	ParseState *pstate = cxt->pstate;
 	JsonTable  *jt = cxt->jt;
@@ -360,11 +372,11 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 		colMax = list_length(tf->colvalexprs) - 1;
 
 	/* Recursively transform nested columns */
-	childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns);
+	childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns, (!planspec ? NULL : planspec->plan2));
 
 	/* Create a "parent" scan responsible for all columns handled above. */
 	return makeJsonTablePathScan(pathspec, errorOnError, colMin, colMax,
-								 childplan);
+								 childplan, planspec);
 }
 
 /*
@@ -451,7 +463,8 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
 static JsonTablePlan *
 transformJsonTableNestedColumns(JsonTableParseContext *cxt,
 								List *passingArgs,
-								List *columns)
+								List *columns,
+								JsonTablePlanSpec *planSpec)
 {
 	JsonTablePlan *plan = NULL;
 	ListCell   *lc;
@@ -473,11 +486,15 @@ transformJsonTableNestedColumns(JsonTableParseContext *cxt,
 		if (jtc->pathspec->name == NULL)
 			jtc->pathspec->name = generateJsonTablePathName(cxt);
 
-		nested = transformJsonTableColumns(cxt, jtc->columns, passingArgs,
-										   jtc->pathspec);
+		nested = transformJsonTableColumns(cxt,
+										   jtc->columns,
+										   passingArgs,
+										   jtc->pathspec,
+										   (!planSpec || planSpec->plan_type == JSTP_JOINED ? NULL : planSpec->plan2));
 
 		if (plan)
-			plan = makeJsonTableSiblingJoin(plan, nested);
+			plan = makeJsonTableSiblingJoin(plan, nested,
+				(!planSpec ? false : planSpec->join_type == JSTP_JOIN_CROSS));
 		else
 			plan = nested;
 	}
@@ -496,7 +513,7 @@ transformJsonTableNestedColumns(JsonTableParseContext *cxt,
 static JsonTablePlan *
 makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
 					  int colMin, int colMax,
-					  JsonTablePlan *childplan)
+					  JsonTablePlan *childplan, JsonTablePlanSpec *planspec)
 {
 	JsonTablePathScan *scan = makeNode(JsonTablePathScan);
 	char	   *pathstring;
@@ -514,6 +531,7 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
 	scan->errorOnError = errorOnError;
 
 	scan->child = childplan;
+	scan->outerJoin = (!planspec ? false : planspec->join_type == JSTP_JOIN_OUTER);
 
 	scan->colMin = colMin;
 	scan->colMax = colMax;
@@ -529,13 +547,14 @@ makeJsonTablePathScan(JsonTablePathSpec *pathspec, bool errorOnError,
  * sets of rows from 'lplan' and 'rplan'.
  */
 static JsonTablePlan *
-makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan)
+makeJsonTableSiblingJoin(JsonTablePlan *lplan, JsonTablePlan *rplan, bool cross)
 {
 	JsonTableSiblingJoin *join = makeNode(JsonTableSiblingJoin);
 
 	join->plan.type = T_JsonTableSiblingJoin;
 	join->lplan = lplan;
 	join->rplan = rplan;
+	join->cross = cross;
 
 	return (JsonTablePlan *) join;
 }
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 2177d17e27..5c8874120f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11880,6 +11880,49 @@ get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 	}
 }
 
+/*
+ * get_json_table_plan - Parse back a JSON_TABLE plan
+ */
+static void
+get_json_table_plan(TableFunc *tf, Node *node, deparse_context *context,
+					bool parenthesize)
+{
+	if (parenthesize)
+		appendStringInfoChar(context->buf, '(');
+
+	if (IsA(node, JsonTableSiblingJoin))
+	{
+		JsonTableSiblingJoin *n = (JsonTableSiblingJoin *) node;
+
+		get_json_table_plan(tf, (Node *) n->lplan, context,
+							IsA(n->lplan, JsonTableSiblingJoin) ||
+							castNode(JsonTablePathScan, n->lplan)->child);
+
+		appendStringInfoString(context->buf, n->cross ? " CROSS " : " UNION ");
+
+		get_json_table_plan(tf, (Node *) n->rplan, context,
+							IsA(n->rplan, JsonTableSiblingJoin) ||
+							castNode(JsonTablePathScan, n->rplan)->child);
+	}
+	else
+	{
+		JsonTablePathScan *n = castNode(JsonTablePathScan, node);
+
+		appendStringInfoString(context->buf, quote_identifier(n->path->name));
+
+		if (n->child)
+		{
+			appendStringInfoString(context->buf,
+								   n->outerJoin ? " OUTER " : " INNER ");
+			get_json_table_plan(tf, (Node *) n->child, context,
+								IsA(n->child, JsonTableSiblingJoin));
+		}
+	}
+
+	if (parenthesize)
+		appendStringInfoChar(context->buf, ')');
+}
+
 /*
  * get_json_table_columns - Parse back JSON_TABLE columns
  */
@@ -12045,6 +12088,10 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
 	get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context,
 						   showimplicit);
 
+	appendStringInfoChar(buf, ' ');
+	appendContextKeyword(context, "PLAN ", 0, 0, 0);
+	get_json_table_plan(tf, (Node *) root, context, true);
+
 	if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY_ARRAY)
 		get_json_behavior(jexpr->on_error, context, "ERROR");
 
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 0765e5c57b..caf5ce843e 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -123,5 +123,10 @@ extern JsonTablePath *makeJsonTablePath(Const *pathvalue, char *pathname);
 extern JsonTablePathSpec *makeJsonTablePathSpec(char *string, char *name,
 												int string_location,
 												int name_location);
+extern Node *makeJsonTableDefaultPlan(JsonTablePlanJoinType join_type,
+									  int location);
+extern Node *makeJsonTableSimplePlan(char *pathname, int location);
+extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type,
+									 Node *plan1, Node *plan2, int location);
 
 #endif							/* MAKEFUNC_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0d96db5638..f48a792fe4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1814,23 +1814,6 @@ typedef struct JsonTablePathSpec
 	ParseLoc	location;		/* location of 'string' */
 } JsonTablePathSpec;
 
-/*
- * JsonTable -
- *		untransformed representation of JSON_TABLE
- */
-typedef struct JsonTable
-{
-	NodeTag		type;
-	JsonValueExpr *context_item;	/* context item expression */
-	JsonTablePathSpec *pathspec;	/* JSON path specification */
-	List	   *passing;		/* list of PASSING clause arguments, if any */
-	List	   *columns;		/* list of JsonTableColumn */
-	JsonBehavior *on_error;		/* ON ERROR behavior */
-	Alias	   *alias;			/* table alias in FROM clause */
-	bool		lateral;		/* does it have LATERAL prefix? */
-	ParseLoc	location;		/* token location, or -1 if unknown */
-} JsonTable;
-
 /*
  * JsonTableColumnType -
  *		enumeration of JSON_TABLE column types
@@ -1864,6 +1847,66 @@ typedef struct JsonTableColumn
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } JsonTableColumn;
 
+/*
+ * JsonTablePlanType -
+ *		flags for JSON_TABLE plan node types representation
+ */
+typedef enum JsonTablePlanType
+{
+	JSTP_DEFAULT,
+	JSTP_SIMPLE,
+	JSTP_JOINED,
+} JsonTablePlanType;
+
+/*
+ * JsonTablePlanJoinType -
+ *		JSON_TABLE join types for JSTP_JOINED plans
+ */
+typedef enum JsonTablePlanJoinType
+{
+	JSTP_JOIN_INNER,
+	JSTP_JOIN_OUTER,
+	JSTP_JOIN_CROSS,
+	JSTP_JOIN_UNION,
+} JsonTablePlanJoinType;
+
+/*
+ * JsonTablePlanSpec -
+ *		untransformed representation of JSON_TABLE's PLAN clause
+ */
+typedef struct JsonTablePlanSpec
+{
+	NodeTag		type;
+
+	JsonTablePlanType plan_type;	/* plan type */
+	JsonTablePlanJoinType join_type;	/* join type (for joined plan only) */
+	char	   *pathname;		/* path name (for simple plan only) */
+
+	/* For joined plans */
+	struct JsonTablePlanSpec *plan1;	/* first joined plan */
+	struct JsonTablePlanSpec *plan2;	/* second joined plan */
+
+	int			location;		/* token location, or -1 if unknown */
+} JsonTablePlanSpec;
+
+/*
+ * JsonTable -
+ *		untransformed representation of JSON_TABLE
+ */
+typedef struct JsonTable
+{
+	NodeTag		type;
+	JsonValueExpr *context_item;	/* context item expression */
+	JsonTablePathSpec *pathspec;	/* JSON path specification */
+	List	   *passing;		/* list of PASSING clause arguments, if any */
+	List	   *columns;		/* list of JsonTableColumn */
+	JsonTablePlanSpec *planspec;	/* join plan, if specified */
+	JsonBehavior *on_error;		/* ON ERROR behavior */
+	Alias	   *alias;			/* table alias in FROM clause */
+	bool		lateral;		/* does it have LATERAL prefix? */
+	ParseLoc	location;		/* token location, or -1 if unknown */
+} JsonTable;
+
 /*
  * JsonKeyValue -
  *		untransformed representation of JSON object key-value pair for
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index b0ef1952e8..7465558e1c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1905,6 +1905,7 @@ typedef struct JsonTablePathScan
 
 	/* Plan(s) for nested columns, if any. */
 	JsonTablePlan *child;
+	bool		outerJoin;		/* outer or inner join for nested columns? */
 
 	/*
 	 * 0-based index in TableFunc.colvalexprs of the 1st and the last column
@@ -1926,6 +1927,7 @@ typedef struct JsonTableSiblingJoin
 
 	JsonTablePlan *lplan;
 	JsonTablePlan *rplan;
+	bool		cross;			/* cross or union join? */
 } JsonTableSiblingJoin;
 
 /* ----------------
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
index b2a0f11eb6..d315382801 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.c
@@ -132,21 +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, 
+  { 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 ) ) ) plan ( p1 ) ) 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"
+#line 32 "sqljson_jsontable.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 31 "sqljson_jsontable.pgc"
+#line 32 "sqljson_jsontable.pgc"
 
   printf("Found foo=%d\n", foo);
 
   { ECPGdisconnect(__LINE__, "CURRENT");
-#line 34 "sqljson_jsontable.pgc"
+#line 35 "sqljson_jsontable.pgc"
 
 if (sqlca.sqlcode < 0) sqlprint();}
-#line 34 "sqljson_jsontable.pgc"
+#line 35 "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 9262cf71a1..af60eaf5cc 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson_jsontable.stderr
@@ -12,7 +12,7 @@
 [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]: 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 ) ) ) plan ( p1 ) ) 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
diff --git a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
index aa2b4494bb..6412d8d0ff 100644
--- a/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson_jsontable.pgc
@@ -28,7 +28,8 @@ EXEC SQL END DECLARE SECTION;
 		NESTED '$' AS p1 COLUMNS (
 			NESTED PATH '$' AS p11 COLUMNS ( foo int )
 		)
-	)) jt (foo);
+	)
+	PLAN (p1)) jt (foo);
   printf("Found foo=%d\n", foo);
 
   EXEC SQL DISCONNECT;
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 154eea79c7..b88e1b03a7 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -323,25 +323,42 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 -- JsonPathQuery() error message mentioning column name
 SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
 
--- JSON_TABLE: nested paths
+-- JSON_TABLE: nested paths and plans
 
--- Duplicate path names
+-- Should fail (JSON_TABLE columns must contain explicit AS path
+-- specifications if explicit PLAN clause is used)
 SELECT * FROM JSON_TABLE(
-	jsonb '[]', '$' AS a
+	jsonb '[]', '$' -- AS <path name> required here
 	COLUMNS (
-		b int,
-		NESTED PATH '$' AS a
-		COLUMNS (
-			c int
+		foo int PATH '$'
+	)
+	PLAN DEFAULT (UNION)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS path1
+	COLUMNS (
+		NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+			foo int PATH '$'
 		)
 	)
+	PLAN DEFAULT (UNION)
 ) jt;
 
+-- Should fail (column names must be distinct)
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS a
+	COLUMNS (
+		a int
+	)
+) jt;
+
+-- Duplicate path names
 SELECT * FROM JSON_TABLE(
 	jsonb '[]', '$' AS a
 	COLUMNS (
 		b int,
-		NESTED PATH '$' AS n_a
+		NESTED PATH '$' AS a
 		COLUMNS (
 			c int
 		)
@@ -376,6 +393,161 @@ SELECT * FROM JSON_TABLE(
 	)
 ) jt;
 
+-- JSON_TABLE: plan validation
+
+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;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER p3)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 UNION p1 UNION p11)
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p13))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 UNION p11) CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 INNER p11) CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', 'strict $[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)))
+) jt;
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', 'strict $[*]' -- without root path name
+	COLUMNS (
+		NESTED PATH '$' 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 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))
+) jt;
 
 -- JSON_TABLE: plan execution
 
@@ -405,6 +577,180 @@ from
 		)
 	) jt;
 
+-- default 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 '$' )
+		)
+		plan default (outer, union)
+	) jt;
+
+-- specific plan (p outer (pb union pc))
+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 '$' )
+		)
+		plan (p outer (pb union pc))
+	) jt;
+
+-- specific plan (p outer (pc union pb))
+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 '$' )
+		)
+		plan (p outer (pc union pb))
+	) jt;
+
+-- default plan (inner, 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 '$' )
+		)
+		plan default (inner)
+	) jt;
+
+-- specific plan (p inner (pb union pc))
+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 '$' )
+		)
+		plan (p inner (pb union pc))
+	) jt;
+
+-- default plan (inner, cross)
+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 '$' )
+		)
+		plan default (cross, inner)
+	) jt;
+
+-- specific plan (p inner (pb cross pc))
+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 '$' )
+		)
+		plan (p inner (pb cross pc))
+	) jt;
+
+-- default plan (outer, cross)
+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 '$' )
+		)
+		plan default (outer, cross)
+	) jt;
+
+-- specific plan (p outer (pb cross pc))
+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 '$' )
+		)
+		plan (p outer (pb cross pc))
+	) jt;
+
+
+select
+	jt.*, b1 + 100 as b
+from
+	json_table (jsonb
+		'[
+			{"a":  1,  "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
+			{"a":  2,  "b": [10, 20], "c": [1, null, 2]},
+			{"x": "3", "b": [11, 22, 33, 44]}
+		 ]',
+		'$[*]' as p
+		columns (
+			n for ordinality,
+			a int path 'lax $.a' default -1 on error,
+			nested path 'strict $.b[*]' as pb columns (
+				b text format json path '$',
+				nested path 'strict $[*]' as pb1 columns (
+					b1 int path '$'
+				)
+			),
+			nested path 'strict $.c[*]' as pc columns (
+				c text format json path '$',
+				nested path 'strict $[*]' as pc1 columns (
+					c1 int path '$'
+				)
+			)
+		)
+		--plan default(outer, cross)
+		plan(p outer ((pb inner pb1) cross (pc outer pc1)))
+	) jt;
 
 -- PASSING arguments are passed to nested paths and their columns' paths
 SELECT *
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 1847bbfa95..b21560e3b0 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1378,7 +1378,11 @@ JsonTablePathScan
 JsonTablePathSpec
 JsonTablePlan
 JsonTablePlanRowSource
+JsonTablePlanSpec
 JsonTablePlanState
+JsonTablePlanStateType
+JsonTablePlanJoinType
+JsonTablePlanType
 JsonTableSiblingJoin
 JsonTokenType
 JsonTransformStringValuesAction
-- 
2.25.1


From fb1aab3b2050fe517e5221d218a33bcbce6853a5 Mon Sep 17 00:00:00 2001
From: Nikita Malakhov <n.malakhov@postgrespro.ru>
Date: Sun, 3 Nov 2024 22:24:53 +0300
Subject: [PATCH 2/2] Add the PLAN clauses for JSON_TABLE - Part 2
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Plan validation code adapted to changes in master branch,
invalid tests corrected, but should be reviewed.
Some code from old patch left commented.

This is a part of the v45-0001-JSON_TABLE.patch from
https://www.postgresql.org/message-id/CA%2BHiwqE1gcPkQhBko%2BUbvVvAtRBaLfOpmHbFrK79pW_5F51Oww%40mail.gmail.com

Author: Nikita Glukhov
Author: Teodor Sigaev
Author: Oleg Bartunov
Author: Alexander Korotkov
Author: Andrew Dunstan
Author: Amit Langote
Author: Anton A. Melnikov
Author: Nikita Malakhov

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
---
 src/backend/parser/gram.y                     |   1 +
 src/backend/parser/parse_jsontable.c          | 175 ++++-
 .../regress/expected/sqljson_jsontable.out    | 634 ++++++++++++++++--
 3 files changed, 752 insertions(+), 58 deletions(-)

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 80d3bfb1da..aa60cc407b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -14233,6 +14233,7 @@ json_table:
 						ereport(ERROR,
 								errcode(ERRCODE_SYNTAX_ERROR),
 								errmsg("PLAN clause requires explicit AS expression"),
+								errdetail("JSON_TABLE path must contain explicit AS pathname specification if explicit PLAN clause is used"),
 								parser_errposition(@6));
 					n->planspec = (JsonTablePlanSpec *) $12;
 					n->on_error = (JsonBehavior *) $13;
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 3516e2adef..7f106a0cc1 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -42,7 +42,8 @@ static JsonTablePlan *transformJsonTableColumns(JsonTableParseContext *cxt,
 												List *columns,
 												List *passingArgs,
 												JsonTablePathSpec *pathspec,
-												JsonTablePlanSpec *planspec);
+												JsonTablePlanSpec *planspec,
+												bool isroot);
 static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt,
 													  List *passingArgs,
 													  List *columns,
@@ -57,12 +58,15 @@ static JsonTablePlan *makeJsonTablePathScan(JsonTablePathSpec *pathspec,
 											JsonTablePlan *childplan,
 											JsonTablePlanSpec *planspec);
 static void CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
-											List *columns);
+											List *columns, bool plancheck);
 static bool LookupPathOrColumnName(JsonTableParseContext *cxt, char *name);
 static char *generateJsonTablePathName(JsonTableParseContext *cxt);
 static JsonTablePlan *makeJsonTableSiblingJoin(JsonTablePlan *lplan,
 											   JsonTablePlan *rplan,
 											   bool cross);
+static void validateJsonTableChildPlan(ParseState *pstate, JsonTablePlanSpec *plan,
+						   List *columns);
+static void collectSiblingPathsInJsonTablePlan(JsonTablePlanSpec *plan, List **paths);
 
 /*
  * transformJsonTable -
@@ -88,7 +92,9 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	Assert(IsA(rootPathSpec->string, A_Const) &&
 		   castNode(A_Const, rootPathSpec->string)->val.node.type == T_String);
 
-	if(rootPlanSpec && !rootPathSpec)
+	cxt.jt = jt;
+
+	if(rootPlanSpec && (!rootPathSpec || rootPathSpec->name == NULL))
 		ereport(ERROR,
 				errcode(ERRCODE_SYNTAX_ERROR),
 				errmsg("JSON_TABLE must contain explicit AS path"),
@@ -107,9 +113,17 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 
 	cxt.pathNameId = 0;
 	if (rootPathSpec->name == NULL)
+	{
+		if(rootPlanSpec)
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("PLAN clause requires explicit AS path expression for nested columns"),
+					 parser_errposition(pstate, jt->planspec->location)));
+
 		rootPathSpec->name = generateJsonTablePathName(&cxt);
+	}
 	cxt.pathNames = list_make1(rootPathSpec->name);
-	CheckDuplicateColumnOrPathNames(&cxt, jt->columns);
+	CheckDuplicateColumnOrPathNames(&cxt, jt->columns, rootPlanSpec != NULL);
 
 	/*
 	 * We make lateral_only names of this level visible, whether or not the
@@ -149,7 +163,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	cxt.tf = tf;
 	tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns,
 												  jt->passing,
-												  rootPathSpec, rootPlanSpec);
+												  rootPathSpec, rootPlanSpec, true);
 
 	/*
 	 * Copy the transformed PASSING arguments into the TableFunc node, because
@@ -181,7 +195,7 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
  */
 static void
 CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
-								List *columns)
+								List *columns, bool plancheck)
 {
 	ListCell   *lc1;
 
@@ -202,8 +216,13 @@ CheckDuplicateColumnOrPathNames(JsonTableParseContext *cxt,
 											   jtc->pathspec->name_location));
 				cxt->pathNames = lappend(cxt->pathNames, jtc->pathspec->name);
 			}
+			else if(plancheck)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("PLAN clause requires explicit AS path expression for nested columns"),
+						 parser_errposition(cxt->pstate, jtc->location)));
 
-			CheckDuplicateColumnOrPathNames(cxt, jtc->columns);
+			CheckDuplicateColumnOrPathNames(cxt, jtc->columns, false);
 		}
 		else
 		{
@@ -252,6 +271,107 @@ generateJsonTablePathName(JsonTableParseContext *cxt)
 	return name;
 }
 
+static void
+collectSiblingPathsInJsonTablePlan(JsonTablePlanSpec *plan, List **paths)
+{
+	if (plan->plan_type == JSTP_SIMPLE)
+	{
+		*paths = lappend(*paths, plan->pathname);
+	}
+	else /* if (plan->plan_type == JSTP_JOINED) */
+	{
+		if(plan->plan1 && plan->plan1->pathname)
+			*paths = lappend(*paths, plan->plan1->pathname);
+		if(plan->plan2 && plan->plan2->pathname)
+			*paths = lappend(*paths, plan->plan2->pathname);
+
+		if(plan->plan1 && plan->plan1->plan_type == JSTP_JOINED)
+			collectSiblingPathsInJsonTablePlan(plan->plan1, paths);
+
+		if(plan->plan2 && plan->plan2->plan_type == JSTP_JOINED)
+			collectSiblingPathsInJsonTablePlan(plan->plan2, paths);
+/* XXX taken from original PLAN patch v45-0001-JSON_TABLE.patch
+ * caused unexpected errors and was replaced with code above
+ */
+/*
+		if (plan->join_type == JSTP_JOIN_INNER ||
+			plan->join_type == JSTP_JOIN_OUTER)
+		{
+			Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+			if(plan->plan2 && plan->plan2->pathname)
+				*paths = lappend(*paths, plan->plan2->pathname);
+		}
+		else if (plan->join_type == JSTP_JOIN_CROSS ||
+				 plan->join_type == JSTP_JOIN_UNION)
+		{
+			collectSiblingPathsInJsonTablePlan(plan->plan1, paths);
+			collectSiblingPathsInJsonTablePlan(plan->plan2, paths);
+		}
+		else
+			elog(ERROR, "invalid JSON_TABLE join type %d",
+				 plan->join_type);
+*/
+	}
+}
+
+static void
+validateJsonTableChildPlan(ParseState *pstate, JsonTablePlanSpec *plan,
+						   List *columns)
+{
+	ListCell   *lc1;
+	List	   *siblings = NIL;
+	int			nchildren = 0;
+
+	if (plan)
+		collectSiblingPathsInJsonTablePlan(plan, &siblings);
+	else
+		return;
+
+	foreach(lc1, columns)
+	{
+		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
+
+		if (jtc->coltype == JTC_NESTED)
+		{
+			ListCell   *lc2;
+			bool		found = false;
+
+			if (jtc->pathspec->name == NULL)
+				ereport(ERROR,
+						errcode(ERRCODE_SYNTAX_ERROR),
+						errmsg("nested JSON_TABLE columns must contain"
+							   " an explicit AS pathname specification"
+							   " if an explicit PLAN clause is used"),
+						parser_errposition(pstate, jtc->location));
+
+			/* find nested path name in the list of sibling path names */
+			foreach(lc2, siblings)
+			{
+				if ((found = !strcmp(jtc->pathspec->name, lfirst(lc2))))
+					break;
+			}
+
+			if (!found)
+				ereport(ERROR,
+						errcode(ERRCODE_SYNTAX_ERROR),
+						errmsg("invalid JSON_TABLE specification"),
+						errdetail("PLAN clause for nested path %s was not found.",
+								  jtc->pathspec->name),
+						parser_errposition(pstate, jtc->location));
+
+			nchildren++;
+		}
+	}
+/*
+	if (list_length(siblings) > nchildren)
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+				errmsg("invalid JSON_TABLE plan clause"),
+				errdetail("PLAN clause contains some extra or duplicate sibling nodes."),
+				parser_errposition(pstate, plan ? plan->location : -1));
+*/
+}
+
 /*
  * Create a JsonTablePlan that will supply the source row for 'columns'
  * using 'pathspec' and append the columns' transformed JsonExpr nodes and
@@ -260,7 +380,8 @@ generateJsonTablePathName(JsonTableParseContext *cxt)
 static JsonTablePlan *
 transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 						  List *passingArgs,
-						  JsonTablePathSpec *pathspec, JsonTablePlanSpec *planspec)
+						  JsonTablePathSpec *pathspec, JsonTablePlanSpec *planspec,
+						  bool isroot)
 {
 	ParseState *pstate = cxt->pstate;
 	JsonTable  *jt = cxt->jt;
@@ -274,6 +395,29 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 				colMax;
 	JsonTablePlan *childplan;
 
+	if (planspec)
+	{
+		if (planspec->plan_type == JSTP_JOINED &&
+			planspec->join_type != JSTP_JOIN_INNER &&
+			planspec->join_type != JSTP_JOIN_OUTER &&
+			planspec->join_type != JSTP_JOIN_UNION &&
+			planspec->join_type != JSTP_JOIN_CROSS)
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("invalid JSON_TABLE plan clause"),
+					 errdetail("Expected INNER, OUTER, UNION or CROSS."),
+					 parser_errposition(cxt->pstate, planspec->location)));
+
+		if (!pathspec
+			|| (pathspec->name && planspec->pathname && strcmp(planspec->pathname, pathspec->name) != 0))
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("invalid JSON_TABLE specification"),
+					 errdetail("PATH name mismatch: expected %s but %s is given.",
+							   (!pathspec ? "none" : pathspec->name), planspec->pathname),
+					 parser_errposition(cxt->pstate, planspec->location)));
+	}
+
 	/* Start of column range */
 	colMin = list_length(tf->colvalexprs);
 
@@ -291,6 +435,15 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 			tf->colnames = lappend(tf->colnames,
 								   makeString(pstrdup(rawc->name)));
 		}
+		else
+		{
+			if(isroot && planspec && (!pathspec || pathspec->name == NULL))
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("invalid JSON_TABLE expression"),
+						 errdetail("JSON_TABLE path must contain explicit AS pathname specification if explicit PLAN clause is used"),
+						 parser_errposition(pstate, rawc->location)));
+		}
 
 		/*
 		 * Determine the type and typmod for the new column. FOR ORDINALITY
@@ -371,6 +524,9 @@ transformJsonTableColumns(JsonTableParseContext *cxt, List *columns,
 	else
 		colMax = list_length(tf->colvalexprs) - 1;
 
+	if(planspec && planspec->plan_type != JSTP_DEFAULT) /* How to check DEFAULT plan type? */
+		validateJsonTableChildPlan(pstate, planspec, columns);
+
 	/* Recursively transform nested columns */
 	childplan = transformJsonTableNestedColumns(cxt, passingArgs, columns, (!planspec ? NULL : planspec->plan2));
 
@@ -490,7 +646,8 @@ transformJsonTableNestedColumns(JsonTableParseContext *cxt,
 										   jtc->columns,
 										   passingArgs,
 										   jtc->pathspec,
-										   (!planSpec || planSpec->plan_type == JSTP_JOINED ? NULL : planSpec->plan2));
+										   planSpec,
+										   false);
 
 		if (plan)
 			plan = makeJsonTableSiblingJoin(plan, nested,
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index d62d32241d..686db35f8f 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -321,6 +321,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view2 AS
                 "numeric" numeric PATH '$',
                 domain jsonb_test_domain PATH '$'
             )
+            PLAN (json_table_path_0)
         )
 \sv jsonb_table_view3
 CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
@@ -341,6 +342,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
                 jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES,
                 jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES
             )
+            PLAN (json_table_path_0)
         )
 \sv jsonb_table_view4
 CREATE OR REPLACE VIEW public.jsonb_table_view4 AS
@@ -359,6 +361,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view4 AS
                 aaa integer PATH '$."aaa"',
                 aaa1 integer PATH '$."aaa"'
             )
+            PLAN (json_table_path_0)
         )
 \sv jsonb_table_view5
 CREATE OR REPLACE VIEW public.jsonb_table_view5 AS
@@ -375,6 +378,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view5 AS
                 exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR,
                 exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR
             )
+            PLAN (json_table_path_0)
         )
 \sv jsonb_table_view6
 CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
@@ -397,45 +401,46 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
                 ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
                 jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES
             )
+            PLAN (json_table_path_0)
         )
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
-                                                                                                                                            QUERY PLAN                                                                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                         QUERY PLAN                                                                                                                                                         
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
    Output: "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$'))
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$') PLAN (json_table_path_0))
 (3 rows)
 
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
-                                                                                                                                                                                                              QUERY PLAN                                                                                                                                                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                                                                                          QUERY PLAN                                                                                                                                                                                                                           
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
    Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES))
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES) PLAN (json_table_path_0))
 (3 rows)
 
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
-                                                                                                                                        QUERY PLAN                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                    QUERY PLAN                                                                                                                                                     
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
    Output: "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"'))
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"') PLAN (json_table_path_0))
 (3 rows)
 
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
-                                                                                                                                       QUERY PLAN                                                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                   QUERY PLAN                                                                                                                                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
    Output: "json_table".exists1, "json_table".exists2, "json_table".exists3
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR))
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR) PLAN (json_table_path_0))
 (3 rows)
 
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
-                                                                                                                                                                                                                    QUERY PLAN                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                                                                                                 QUERY PLAN                                                                                                                                                                                                                                 
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
    Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES))
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES) PLAN (json_table_path_0))
 (3 rows)
 
 -- JSON_TABLE() with alias
@@ -448,11 +453,11 @@ SELECT * FROM
 			"int" int PATH '$',
 			"text" text PATH '$'
 	)) json_table_func;
-                                                                                          QUERY PLAN                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                       QUERY PLAN                                                                                                       
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table" json_table_func
    Output: id, "int", text
-   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, "int" integer PATH '$', text text PATH '$'))
+   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, "int" integer PATH '$', text text PATH '$') PLAN (json_table_path_0))
 (3 rows)
 
 EXPLAIN (COSTS OFF, FORMAT JSON, VERBOSE)
@@ -464,21 +469,21 @@ SELECT * FROM
 			"int" int PATH '$',
 			"text" text PATH '$'
 	)) json_table_func;
-                                                                                                 QUERY PLAN                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- [                                                                                                                                                                                                          +
-   {                                                                                                                                                                                                        +
-     "Plan": {                                                                                                                                                                                              +
-       "Node Type": "Table Function Scan",                                                                                                                                                                  +
-       "Parallel Aware": false,                                                                                                                                                                             +
-       "Async Capable": false,                                                                                                                                                                              +
-       "Table Function Name": "json_table",                                                                                                                                                                 +
-       "Alias": "json_table_func",                                                                                                                                                                          +
-       "Disabled": false,                                                                                                                                                                                   +
-       "Output": ["id", "\"int\"", "text"],                                                                                                                                                                 +
-       "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, \"int\" integer PATH '$', text text PATH '$'))"+
-     }                                                                                                                                                                                                      +
-   }                                                                                                                                                                                                        +
+                                                                                                              QUERY PLAN                                                                                                              
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ [                                                                                                                                                                                                                                   +
+   {                                                                                                                                                                                                                                 +
+     "Plan": {                                                                                                                                                                                                                       +
+       "Node Type": "Table Function Scan",                                                                                                                                                                                           +
+       "Parallel Aware": false,                                                                                                                                                                                                      +
+       "Async Capable": false,                                                                                                                                                                                                       +
+       "Table Function Name": "json_table",                                                                                                                                                                                          +
+       "Alias": "json_table_func",                                                                                                                                                                                                   +
+       "Disabled": false,                                                                                                                                                                                                            +
+       "Output": ["id", "\"int\"", "text"],                                                                                                                                                                                          +
+       "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, \"int\" integer PATH '$', text text PATH '$') PLAN (json_table_path_0))"+
+     }                                                                                                                                                                                                                               +
+   }                                                                                                                                                                                                                                 +
  ]
 (1 row)
 
@@ -712,36 +717,54 @@ LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
 SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
 ERROR:  JSON path expression for column "b" should return single item without wrapper
 HINT:  Use the WITH WRAPPER clause to wrap SQL/JSON items into an array.
--- JSON_TABLE: nested paths
--- Duplicate path names
+-- JSON_TABLE: nested paths and plans
+-- Should fail (JSON_TABLE columns must contain explicit AS path
+-- specifications if explicit PLAN clause is used)
 SELECT * FROM JSON_TABLE(
-	jsonb '[]', '$' AS a
+	jsonb '[]', '$' -- AS <path name> required here
 	COLUMNS (
-		b int,
-		NESTED PATH '$' AS a
-		COLUMNS (
-			c int
+		foo int PATH '$'
+	)
+	PLAN DEFAULT (UNION)
+) jt;
+ERROR:  PLAN clause requires explicit AS expression
+DETAIL:  JSON_TABLE path must contain explicit AS pathname specification if explicit PLAN clause is used
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS path1
+	COLUMNS (
+		NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+			foo int PATH '$'
 		)
 	)
+	PLAN DEFAULT (UNION)
+) jt;
+ERROR:  PLAN clause requires explicit AS path expression for nested columns
+LINE 4:   NESTED PATH '$' COLUMNS ( -- AS <path name> required here
+          ^
+-- Should fail (column names must be distinct)
+SELECT * FROM JSON_TABLE(
+	jsonb '[]', '$' AS a
+	COLUMNS (
+		a int
+	)
 ) jt;
 ERROR:  duplicate JSON_TABLE column or path name: a
-LINE 5:   NESTED PATH '$' AS a
-                             ^
+LINE 4:   a int
+          ^
+-- Duplicate path names
 SELECT * FROM JSON_TABLE(
 	jsonb '[]', '$' AS a
 	COLUMNS (
 		b int,
-		NESTED PATH '$' AS n_a
+		NESTED PATH '$' AS a
 		COLUMNS (
 			c int
 		)
 	)
 ) jt;
- b | c 
----+---
-   |  
-(1 row)
-
+ERROR:  duplicate JSON_TABLE column or path name: a
+LINE 5:   NESTED PATH '$' AS a
+                             ^
 SELECT * FROM JSON_TABLE(
 	jsonb '[]', '$'
 	COLUMNS (
@@ -774,6 +797,192 @@ SELECT * FROM JSON_TABLE(
 ERROR:  duplicate JSON_TABLE column or path name: a
 LINE 10:    NESTED PATH '$' AS a
                                ^
+-- JSON_TABLE: plan validation
+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;
+ERROR:  invalid JSON_TABLE specification
+LINE 12:  PLAN (p1)
+                ^
+DETAIL:  PATH name mismatch: expected p0 but p1 is given.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0)
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 4:   NESTED PATH '$' AS p1 COLUMNS (
+          ^
+DETAIL:  PLAN clause for nested path p1 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER p3)
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 4:   NESTED PATH '$' AS p1 COLUMNS (
+          ^
+DETAIL:  PLAN clause for nested path p1 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 UNION p1 UNION p11)
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 8:   NESTED PATH '$' AS p2 COLUMNS (
+          ^
+DETAIL:  PLAN clause for nested path p2 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p13))
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 8:   NESTED PATH '$' AS p2 COLUMNS (
+          ^
+DETAIL:  PLAN clause for nested path p2 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER (p1 CROSS p2))
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 5:    NESTED PATH '$' AS p11 COLUMNS ( foo int ),
+           ^
+DETAIL:  PLAN clause for nested path p11 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 UNION p11) CROSS p2))
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 6:    NESTED PATH '$' AS p12 COLUMNS ( bar int )
+           ^
+DETAIL:  PLAN clause for nested path p12 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 INNER p11) CROSS p2))
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 6:    NESTED PATH '$' AS p12 COLUMNS ( bar int )
+           ^
+DETAIL:  PLAN clause for nested path p12 was not found.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', '$[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2))
+) jt;
+ERROR:  invalid JSON_TABLE specification
+LINE 12:  PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2))
+                                                           ^
+DETAIL:  PATH name mismatch: expected p11 but p2 is given.
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', 'strict $[*]' AS p0
+	COLUMNS (
+		NESTED PATH '$' 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 (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)))
+) jt;
+ foo | bar | baz 
+-----+-----+-----
+(0 rows)
+
+SELECT * FROM JSON_TABLE(
+	jsonb 'null', 'strict $[*]' -- without root path name
+	COLUMNS (
+		NESTED PATH '$' 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 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))
+) jt;
+ERROR:  PLAN clause requires explicit AS expression
+DETAIL:  JSON_TABLE path must contain explicit AS pathname specification if explicit PLAN clause is used
 -- JSON_TABLE: plan execution
 CREATE TEMP TABLE jsonb_table_test (js jsonb);
 INSERT INTO jsonb_table_test
@@ -813,6 +1022,327 @@ from
  4 | -1 |    2 | 2 |      |   
 (11 rows)
 
+-- default 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 '$' )
+		)
+		plan default (outer, union)
+	) 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)
+
+-- specific plan (p outer (pb union pc))
+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 '$' )
+		)
+		plan (p outer (pb union pc))
+	) 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)
+
+-- specific plan (p outer (pc union pb))
+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 '$' )
+		)
+		plan (p outer (pc union pb))
+	) 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)
+
+-- default plan (inner, 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 '$' )
+		)
+		plan default (inner)
+	) 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)
+
+-- specific plan (p inner (pb union pc))
+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 '$' )
+		)
+		plan (p inner (pb union pc))
+	) 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)
+
+-- default plan (inner, cross)
+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 '$' )
+		)
+		plan default (cross, inner)
+	) 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)
+
+-- specific plan (p inner (pb cross pc))
+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 '$' )
+		)
+		plan (p inner (pb cross pc))
+	) 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)
+
+-- default plan (outer, cross)
+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 '$' )
+		)
+		plan default (outer, cross)
+	) 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)
+
+-- specific plan (p outer (pb cross pc))
+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 '$' )
+		)
+		plan (p outer (pb cross pc))
+	) 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)
+
+select
+	jt.*, b1 + 100 as b
+from
+	json_table (jsonb
+		'[
+			{"a":  1,  "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
+			{"a":  2,  "b": [10, 20], "c": [1, null, 2]},
+			{"x": "3", "b": [11, 22, 33, 44]}
+		 ]',
+		'$[*]' as p
+		columns (
+			n for ordinality,
+			a int path 'lax $.a' default -1 on error,
+			nested path 'strict $.b[*]' as pb columns (
+				b text format json path '$',
+				nested path 'strict $[*]' as pb1 columns (
+					b1 int path '$'
+				)
+			),
+			nested path 'strict $.c[*]' as pc columns (
+				c text format json path '$',
+				nested path 'strict $[*]' as pc1 columns (
+					c1 int path '$'
+				)
+			)
+		)
+		--plan default(outer, cross)
+		plan(p outer ((pb inner pb1) cross (pc outer pc1)))
+	) jt;
+ n | a |      b       | b1  |  c   | c1 |  b  
+---+---+--------------+-----+------+----+-----
+ 1 | 1 | [1, 10]      |   1 |      |    | 101
+ 1 | 1 | [1, 10]      |  10 |      |    | 110
+ 1 | 1 | [2]          |   2 |      |    | 102
+ 1 | 1 | [3, 30, 300] |   3 |      |    | 103
+ 1 | 1 | [3, 30, 300] |  30 |      |    | 130
+ 1 | 1 | [3, 30, 300] | 300 |      |    | 400
+ 1 | 1 |              |     | 1    |    |    
+ 1 | 1 |              |     | null |    |    
+ 1 | 1 |              |     | 2    |    |    
+ 2 | 2 | 10           |     |      |    |    
+ 2 | 2 | 20           |     |      |    |    
+ 2 | 2 |              |     | 1    |    |    
+ 2 | 2 |              |     | null |    |    
+ 2 | 2 |              |     | 2    |    |    
+ 3 |   | 11           |     |      |    |    
+ 3 |   | 22           |     |      |    |    
+ 3 |   | 33           |     |      |    |    
+ 3 |   | 44           |     |      |    |    
+(18 rows)
+
 -- PASSING arguments are passed to nested paths and their columns' paths
 SELECT *
 FROM
@@ -912,6 +1442,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS
                     )
                 )
             )
+            PLAN (json_table_path_0 INNER ((p1 INNER "p1 1") UNION (p2 INNER ("p2:1" UNION p22))))
         )
 DROP VIEW jsonb_table_view_nested;
 CREATE TABLE s (js jsonb);
@@ -1103,6 +1634,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view7 AS
                     )
                 )
             )
+            PLAN (c1 INNER ((((json_table_path_0 INNER z22) UNION json_table_path_1) UNION (json_table_path_2 INNER z1)) UNION (json_table_path_3 INNER z21)))
         ) sub
 DROP VIEW jsonb_table_view7;
 DROP TABLE s;
@@ -1144,6 +1676,7 @@ CREATE OR REPLACE VIEW public.json_table_view8 AS
             COLUMNS (
                 a text PATH '$'
             )
+            PLAN (json_table_path_0)
         )
 CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') ERROR ON ERROR);
 \sv json_table_view9;
@@ -1153,7 +1686,8 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
             '"a"'::text, '$' AS json_table_path_0
             COLUMNS (
                 a text PATH '$'
-            ) ERROR ON ERROR
+            )
+            PLAN (json_table_path_0) ERROR ON ERROR
         )
 DROP VIEW json_table_view8, json_table_view9;
 -- Test JSON_TABLE() deparsing -- don't emit default ON ERROR behavior
@@ -1166,6 +1700,7 @@ CREATE OR REPLACE VIEW public.json_table_view8 AS
             COLUMNS (
                 a text PATH '$'
             )
+            PLAN (json_table_path_0)
         )
 CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ARRAY ON ERROR);
 \sv json_table_view9;
@@ -1176,5 +1711,6 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
             COLUMNS (
                 a text PATH '$'
             )
+            PLAN (json_table_path_0)
         )
 DROP VIEW json_table_view8, json_table_view9;
-- 
2.25.1