2 files changed, 422 insertions, 31 deletions

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 13fa5bea87a..7e3f4a5d275 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -683,6 +683,18 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 					json_table_formatted_column_definition
 					json_table_exists_column_definition
 					json_table_nested_columns
+					json_table_plan_clause_opt
+					json_table_specific_plan
+					json_table_plan
+					json_table_plan_simple
+					json_table_plan_parent_child
+					json_table_plan_outer
+					json_table_plan_inner
+					json_table_plan_sibling
+					json_table_plan_union
+					json_table_plan_cross
+					json_table_plan_primary
+					json_table_default_plan
 
 %type <list>		json_name_and_value_list
 					json_value_expr_list
@@ -698,6 +710,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type <ival>		json_encoding
 					json_encoding_clause_opt
+					json_table_default_plan_choices
+					json_table_default_plan_inner_outer
+					json_table_default_plan_union_cross
 					json_wrapper_clause_opt
 					json_wrapper_behavior
 					json_conditional_or_unconditional_opt
@@ -812,7 +827,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	ORDER ORDINALITY OTHERS OUT_P OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
-	PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLANS POLICY
+	PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLAN PLANS POLICY
 	POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
 
@@ -15928,13 +15943,15 @@ json_table:
 			JSON_TABLE '('
 				json_api_common_syntax
 				json_table_columns_clause
+				json_table_plan_clause_opt
 				json_table_error_clause_opt
 			')'
 				{
 					JsonTable *n = makeNode(JsonTable);
 					n->common = (JsonCommon *) $3;
 					n->columns = $4;
-					n->on_error = $5;
+					n->plan = (JsonTablePlan *) $5;
+					n->on_error = $6;
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -16055,12 +16072,15 @@ json_table_formatted_column_definition:
 		;
 
 json_table_nested_columns:
-			NESTED path_opt Sconst json_table_columns_clause
+			NESTED path_opt Sconst
+							json_as_path_name_clause_opt
+							json_table_columns_clause
 				{
 					JsonTableColumn *n = makeNode(JsonTableColumn);
 					n->coltype = JTC_NESTED;
 					n->pathspec = $3;
-					n->columns = $4;
+					n->pathname = $4;
+					n->columns = $5;
 					n->location = @1;
 					$$ = (Node *) n;
 				}
@@ -16071,6 +16091,106 @@ path_opt:
 			| /* EMPTY */							{ }
 		;
 
+json_table_plan_clause_opt:
+			json_table_specific_plan				{ $$ = $1; }
+			| json_table_default_plan				{ $$ = $1; }
+			| /* EMPTY */							{ $$ = NULL; }
+		;
+
+json_table_specific_plan:
+			PLAN '(' json_table_plan ')'			{ $$ = $3; }
+		;
+
+json_table_plan:
+			json_table_plan_simple
+			| json_table_plan_parent_child
+			| json_table_plan_sibling
+		;
+
+json_table_plan_simple:
+			json_table_path_name
+				{
+					JsonTablePlan *n = makeNode(JsonTablePlan);
+					n->plan_type = JSTP_SIMPLE;
+					n->pathname = $1;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
+
+json_table_plan_parent_child:
+			json_table_plan_outer
+			| json_table_plan_inner
+		;
+
+json_table_plan_outer:
+			json_table_plan_simple OUTER_P json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTPJ_OUTER, $1, $3, @1); }
+		;
+
+json_table_plan_inner:
+			json_table_plan_simple INNER_P json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTPJ_INNER, $1, $3, @1); }
+		;
+
+json_table_plan_sibling:
+			json_table_plan_union
+			| json_table_plan_cross
+		;
+
+json_table_plan_union:
+			json_table_plan_primary UNION json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); }
+			| json_table_plan_union UNION json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); }
+		;
+
+json_table_plan_cross:
+			json_table_plan_primary CROSS json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); }
+			| json_table_plan_cross CROSS json_table_plan_primary
+				{ $$ = makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); }
+		;
+
+json_table_plan_primary:
+			json_table_plan_simple						{ $$ = $1; }
+			| '(' json_table_plan ')'
+				{
+					castNode(JsonTablePlan, $2)->location = @1;
+					$$ = $2;
+				}
+		;
+
+json_table_default_plan:
+			PLAN DEFAULT '(' json_table_default_plan_choices ')'
+			{
+				JsonTablePlan *n = makeNode(JsonTablePlan);
+				n->plan_type = JSTP_DEFAULT;
+				n->join_type = $4;
+				n->location = @1;
+				$$ = (Node *) n;
+			}
+		;
+
+json_table_default_plan_choices:
+			json_table_default_plan_inner_outer			{ $$ = $1 | JSTPJ_UNION; }
+			| json_table_default_plan_inner_outer ','
+			  json_table_default_plan_union_cross		{ $$ = $1 | $3; }
+			| json_table_default_plan_union_cross		{ $$ = $1 | JSTPJ_OUTER; }
+			| json_table_default_plan_union_cross ','
+			  json_table_default_plan_inner_outer		{ $$ = $1 | $3; }
+		;
+
+json_table_default_plan_inner_outer:
+			INNER_P										{ $$ = JSTPJ_INNER; }
+			| OUTER_P									{ $$ = JSTPJ_OUTER; }
+		;
+
+json_table_default_plan_union_cross:
+			UNION										{ $$ = JSTPJ_UNION; }
+			| CROSS										{ $$ = JSTPJ_CROSS; }
+		;
+
 json_returning_clause_opt:
 			RETURNING Typename
 				{
@@ -16951,6 +17071,7 @@ unreserved_keyword:
 			| PASSING
 			| PASSWORD
 			| PATH
+			| PLAN
 			| PLANS
 			| POLICY
 			| PRECEDING
@@ -17568,6 +17689,7 @@ bare_label_keyword:
 			| PASSWORD
 			| PATH
 			| PLACING
+			| PLAN
 			| PLANS
 			| POLICY
 			| POSITION
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index dd75a40bf6f..c7dcefa11cb 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -37,13 +37,16 @@ typedef struct JsonTableContext
 	JsonTable  *table;				/* untransformed node */
 	TableFunc  *tablefunc;			/* transformed node	*/
 	List	   *pathNames;			/* list of all path and columns names */
+	int			pathNameId;			/* path name id counter */
 	Oid			contextItemTypid;	/* type oid of context item (json/jsonb) */
 } JsonTableContext;
 
 static JsonTableParent * transformJsonTableColumns(JsonTableContext *cxt,
-													   List *columns,
-													   char *pathSpec,
-													   int location);
+												   JsonTablePlan *plan,
+												   List *columns,
+												   char *pathSpec,
+												   char **pathName,
+												   int location);
 
 static Node *
 makeStringConst(char *str, int location)
@@ -154,62 +157,239 @@ registerAllJsonTableColumns(JsonTableContext *cxt, List *columns)
 		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
 
 		if (jtc->coltype == JTC_NESTED)
+		{
+			if (jtc->pathname)
+				registerJsonTableColumn(cxt, jtc->pathname);
+
 			registerAllJsonTableColumns(cxt, jtc->columns);
+		}
 		else
+		{
 			registerJsonTableColumn(cxt, jtc->name);
+		}
+	}
+}
+
+/* Generate a new unique JSON_TABLE path name. */
+static char *
+generateJsonTablePathName(JsonTableContext *cxt)
+{
+	char		namebuf[32];
+	char	   *name = namebuf;
+
+	do
+	{
+		snprintf(namebuf, sizeof(namebuf), "json_table_path_%d",
+				 ++cxt->pathNameId);
+	} while (isJsonTablePathNameDuplicate(cxt, name));
+
+	name = pstrdup(name);
+	cxt->pathNames = lappend(cxt->pathNames, name);
+
+	return name;
+}
+
+/* Collect sibling path names from plan to the specified list. */
+static void
+collectSiblingPathsInJsonTablePlan(JsonTablePlan *plan, List **paths)
+{
+	if (plan->plan_type == JSTP_SIMPLE)
+		*paths = lappend(*paths, plan->pathname);
+	else if (plan->plan_type == JSTP_JOINED)
+	{
+		if (plan->join_type == JSTPJ_INNER ||
+			plan->join_type == JSTPJ_OUTER)
+		{
+			Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+			*paths = lappend(*paths, plan->plan1->pathname);
+		}
+		else if (plan->join_type == JSTPJ_CROSS ||
+				 plan->join_type == JSTPJ_UNION)
+		{
+			collectSiblingPathsInJsonTablePlan(plan->plan1, paths);
+			collectSiblingPathsInJsonTablePlan(plan->plan2, paths);
+		}
+		else
+			elog(ERROR, "invalid JSON_TABLE join type %d",
+				 plan->join_type);
+	}
+}
+
+/*
+ * Validate child JSON_TABLE plan by checking that:
+ *  - all nested columns have path names specified
+ *  - all nested columns have corresponding node in the sibling plan
+ *  - plan does not contain duplicate or extra nodes
+ */
+static void
+validateJsonTableChildPlan(ParseState *pstate, JsonTablePlan *plan,
+						   List *columns)
+{
+	ListCell   *lc1;
+	List	   *siblings = NIL;
+	int			nchildren = 0;
+
+	if (plan)
+		collectSiblingPathsInJsonTablePlan(plan, &siblings);
+
+	foreach(lc1, columns)
+	{
+		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc1));
+
+		if (jtc->coltype == JTC_NESTED)
+		{
+			ListCell   *lc2;
+			bool		found = false;
+
+			if (!jtc->pathname)
+				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->pathname, lfirst(lc2))))
+					break;
+			}
+
+			if (!found)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("invalid JSON_TABLE plan"),
+						 errdetail("plan node for nested path %s was not found in plan", jtc->pathname),
+						 parser_errposition(pstate, jtc->location)));
+
+			nchildren++;
+		}
+	}
+
+	if (list_length(siblings) > nchildren)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("invalid JSON_TABLE plan"),
+				 errdetail("plan node contains some extra or duplicate sibling nodes"),
+				 parser_errposition(pstate, plan ? plan->location : -1)));
+}
+
+static JsonTableColumn *
+findNestedJsonTableColumn(List *columns, const char *pathname)
+{
+	ListCell   *lc;
+
+	foreach(lc, columns)
+	{
+		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+
+		if (jtc->coltype == JTC_NESTED &&
+			jtc->pathname &&
+			!strcmp(jtc->pathname, pathname))
+			return jtc;
 	}
+
+	return NULL;
 }
 
 static Node *
-transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *jtc)
+transformNestedJsonTableColumn(JsonTableContext *cxt, JsonTableColumn *jtc,
+							   JsonTablePlan *plan)
 {
 	JsonTableParent *node;
+	char	   *pathname = jtc->pathname;
 
-	node = transformJsonTableColumns(cxt, jtc->columns, jtc->pathspec,
-									 jtc->location);
+	node = transformJsonTableColumns(cxt, plan, jtc->columns, jtc->pathspec,
+									 &pathname, jtc->location);
+	node->name = pstrdup(pathname);
 
 	return (Node *) node;
 }
 
 static Node *
-makeJsonTableSiblingJoin(Node *lnode, Node *rnode)
+makeJsonTableSiblingJoin(bool cross, Node *lnode, Node *rnode)
 {
 	JsonTableSibling *join = makeNode(JsonTableSibling);
 
 	join->larg = lnode;
 	join->rarg = rnode;
+	join->cross = cross;
 
 	return (Node *) join;
 }
 
 /*
- * Recursively transform child (nested) JSON_TABLE columns.
+ * Recursively transform child JSON_TABLE plan.
  *
- * Child columns are transformed into a binary tree of union-joined
- * JsonTableSiblings.
+ * Default plan is transformed into a cross/union join of its nested columns.
+ * Simple and outer/inner plans are transformed into a JsonTableParent by
+ * finding and transforming corresponding nested column.
+ * Sibling plans are recursively transformed into a JsonTableSibling.
  */
 static Node *
-transformJsonTableChildColumns(JsonTableContext *cxt, List *columns)
+transformJsonTableChildPlan(JsonTableContext *cxt, JsonTablePlan *plan,
+							List *columns)
 {
-	Node	   *res = NULL;
-	ListCell   *lc;
+	JsonTableColumn *jtc = NULL;
 
-	/* transform all nested columns into union join */
-	foreach(lc, columns)
+	if (!plan || plan->plan_type == JSTP_DEFAULT)
 	{
-		JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
-		Node	   *node;
+		/* unspecified or default plan */
+		Node	   *res = NULL;
+		ListCell   *lc;
+		bool		cross = plan && (plan->join_type & JSTPJ_CROSS);
 
-		if (jtc->coltype != JTC_NESTED)
-			continue;
+		/* transform all nested columns into cross/union join */
+		foreach(lc, columns)
+		{
+			JsonTableColumn *jtc = castNode(JsonTableColumn, lfirst(lc));
+			Node *node;
+
+			if (jtc->coltype != JTC_NESTED)
+				continue;
 
-		node = transformNestedJsonTableColumn(cxt, jtc);
+			node = transformNestedJsonTableColumn(cxt, jtc, plan);
 
-		/* join transformed node with previous sibling nodes */
-		res = res ? makeJsonTableSiblingJoin(res, node) : node;
+			/* join transformed node with previous sibling nodes */
+			res = res ? makeJsonTableSiblingJoin(cross, res, node) : node;
+		}
+
+		return res;
 	}
+	else if (plan->plan_type == JSTP_SIMPLE)
+	{
+		jtc = findNestedJsonTableColumn(columns, plan->pathname);
+	}
+	else if (plan->plan_type == JSTP_JOINED)
+	{
+		if (plan->join_type == JSTPJ_INNER ||
+			plan->join_type == JSTPJ_OUTER)
+		{
+			Assert(plan->plan1->plan_type == JSTP_SIMPLE);
+			jtc = findNestedJsonTableColumn(columns, plan->plan1->pathname);
+		}
+		else
+		{
+			Node	   *node1 =
+				transformJsonTableChildPlan(cxt, plan->plan1, columns);
+			Node	   *node2 =
+				transformJsonTableChildPlan(cxt, plan->plan2, columns);
+
+			return makeJsonTableSiblingJoin(plan->join_type == JSTPJ_CROSS,
+											node1, node2);
+		}
+	}
+	else
+		elog(ERROR, "invalid JSON_TABLE plan type %d", plan->plan_type);
+
+	if (!jtc)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("invalid JSON_TABLE plan"),
+				 errdetail("path name was %s not found in nested columns list",
+						   plan->pathname),
+				 parser_errposition(cxt->pstate, plan->location)));
 
-	return res;
+	return transformNestedJsonTableColumn(cxt, jtc, plan);
 }
 
 /* Check whether type is json/jsonb, array, or record. */
@@ -374,16 +554,80 @@ makeParentJsonTableNode(JsonTableContext *cxt, char *pathSpec, List *columns)
 }
 
 static JsonTableParent *
-transformJsonTableColumns(JsonTableContext *cxt, List *columns, char *pathSpec,
+transformJsonTableColumns(JsonTableContext *cxt, JsonTablePlan *plan,
+						  List *columns, char *pathSpec, char **pathName,
 						  int location)
 {
 	JsonTableParent *node;
+	JsonTablePlan *childPlan;
+	bool		defaultPlan = !plan || plan->plan_type == JSTP_DEFAULT;
+
+	if (!*pathName)
+	{
+		if (cxt->table->plan)
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("invalid JSON_TABLE expression"),
+					 errdetail("JSON_TABLE columns must contain "
+							   "explicit AS pathname specification if "
+							   "explicit PLAN clause is used"),
+					parser_errposition(cxt->pstate, location)));
+
+		*pathName = generateJsonTablePathName(cxt);
+	}
+
+	if (defaultPlan)
+		childPlan = plan;
+	else
+	{
+		/* validate parent and child plans */
+		JsonTablePlan *parentPlan;
+
+		if (plan->plan_type == JSTP_JOINED)
+		{
+			if (plan->join_type != JSTPJ_INNER &&
+				plan->join_type != JSTPJ_OUTER)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("invalid JSON_TABLE plan"),
+						 errdetail("expected INNER or OUTER JSON_TABLE plan node"),
+						 parser_errposition(cxt->pstate, plan->location)));
+
+			parentPlan = plan->plan1;
+			childPlan = plan->plan2;
+
+			Assert(parentPlan->plan_type != JSTP_JOINED);
+			Assert(parentPlan->pathname);
+		}
+		else
+		{
+			parentPlan = plan;
+			childPlan = NULL;
+		}
+
+		if (strcmp(parentPlan->pathname, *pathName))
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("invalid JSON_TABLE plan"),
+					 errdetail("path name mismatch: expected %s but %s is given",
+							   *pathName, parentPlan->pathname),
+					 parser_errposition(cxt->pstate, plan->location)));
+
+		validateJsonTableChildPlan(cxt->pstate, childPlan, columns);
+	}
 
 	/* transform only non-nested columns */
 	node = makeParentJsonTableNode(cxt, pathSpec, columns);
+	node->name = pstrdup(*pathName);
 
-	/* transform recursively nested columns */
-	node->child = transformJsonTableChildColumns(cxt, columns);
+	if (childPlan || defaultPlan)
+	{
+		/* transform recursively nested columns */
+		node->child = transformJsonTableChildPlan(cxt, childPlan, columns);
+		if (node->child)
+			node->outerJoin = !plan || (plan->join_type & JSTPJ_OUTER);
+		/* else: default plan case, no children found */
+	}
 
 	return node;
 }
@@ -401,7 +645,9 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	JsonTableContext cxt;
 	TableFunc  *tf = makeNode(TableFunc);
 	JsonFuncExpr *jfe = makeNode(JsonFuncExpr);
+	JsonTablePlan *plan = jt->plan;
 	JsonCommon *jscommon;
+	char	   *rootPathName = jt->common->pathname;
 	char	   *rootPath;
 	bool		is_lateral;
 
@@ -409,9 +655,31 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 	cxt.table = jt;
 	cxt.tablefunc = tf;
 	cxt.pathNames = NIL;
+	cxt.pathNameId = 0;
+
+	if (rootPathName)
+		registerJsonTableColumn(&cxt, rootPathName);
 
 	registerAllJsonTableColumns(&cxt, jt->columns);
 
+#if 0 /* XXX it' unclear from the standard whether root path name is mandatory or not */
+	if (plan && plan->plan_type != JSTP_DEFAULT && !rootPathName)
+	{
+		/* Assign root path name and create corresponding plan node */
+		JsonTablePlan *rootNode = makeNode(JsonTablePlan);
+		JsonTablePlan *rootPlan = (JsonTablePlan *)
+				makeJsonTableJoinedPlan(JSTPJ_OUTER, (Node *) rootNode,
+										(Node *) plan, jt->location);
+
+		rootPathName = generateJsonTablePathName(&cxt);
+
+		rootNode->plan_type = JSTP_SIMPLE;
+		rootNode->pathname = rootPathName;
+
+		plan = rootPlan;
+	}
+#endif
+
 	jscommon = copyObject(jt->common);
 	jscommon->pathspec = makeStringConst(pstrdup("$"), -1);
 
@@ -447,7 +715,8 @@ transformJsonTable(ParseState *pstate, JsonTable *jt)
 
 	rootPath = castNode(A_Const, jt->common->pathspec)->val.sval.sval;
 
-	tf->plan = (Node *) transformJsonTableColumns(&cxt, jt->columns, rootPath,
+	tf->plan = (Node *) transformJsonTableColumns(&cxt, plan, jt->columns,
+												  rootPath, &rootPathName,
 												  jt->common->location);
 
 	tf->ordinalitycol = -1;		/* undefine ordinality column number */