PLAN clauses for JSON_TABLE

These clauses 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.

Nikita Glukhov

Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby.

Discussion: https://postgr.es/m/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru

4 files changed, 48 insertions, 0 deletions

diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 872f2f0828f..c717468eb39 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -110,6 +110,8 @@ extern JsonFormat *makeJsonFormat(JsonFormatType type, JsonEncoding encoding,
 								  int location);
 extern JsonValueExpr *makeJsonValueExpr(Expr *expr, JsonFormat *format);
 extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr);
+extern Node *makeJsonTableJoinedPlan(JsonTablePlanJoinType type,
+									 Node *plan1, Node *plan2, int location);
 extern Node *makeJsonKeyValue(Node *key, Node *value);
 extern Node *makeJsonIsPredicate(Node *expr, JsonFormat *format,
 								 JsonValueType vtype, bool unique_keys,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index aefce33e284..300824258ed 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -518,6 +518,7 @@ typedef enum NodeTag
 	T_JsonIsPredicate,
 	T_JsonTable,
 	T_JsonTableColumn,
+	T_JsonTablePlan,
 	T_JsonCommon,
 	T_JsonArgument,
 	T_JsonKeyValue,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e58211eac1b..4a2ca81f3c0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1688,6 +1688,7 @@ typedef struct JsonTableColumn
 	char	   *name;				/* column name */
 	TypeName   *typeName;			/* column type name */
 	JsonPathSpec pathspec;			/* path specification, if any */
+	char	   *pathname;			/* path name, if any */
 	JsonFormat *format;				/* JSON format clause, if specified */
 	JsonWrapper	wrapper;			/* WRAPPER behavior for formatted columns */
 	bool		omit_quotes;		/* omit or keep quotes on scalar strings? */
@@ -1698,6 +1699,46 @@ typedef struct JsonTableColumn
 } JsonTableColumn;
 
 /*
+ * JsonTablePlanType -
+ *		flags for JSON_TABLE plan node types representation
+ */
+typedef enum JsonTablePlanType
+{
+	JSTP_DEFAULT,
+	JSTP_SIMPLE,
+	JSTP_JOINED,
+} JsonTablePlanType;
+
+/*
+ * JsonTablePlanJoinType -
+ *		flags for JSON_TABLE join types representation
+ */
+typedef enum JsonTablePlanJoinType
+{
+	JSTPJ_INNER = 0x01,
+	JSTPJ_OUTER = 0x02,
+	JSTPJ_CROSS = 0x04,
+	JSTPJ_UNION = 0x08,
+} JsonTablePlanJoinType;
+
+typedef struct JsonTablePlan JsonTablePlan;
+
+/*
+ * JsonTablePlan -
+ *		untransformed representation of JSON_TABLE plan node
+ */
+struct JsonTablePlan
+{
+	NodeTag		type;
+	JsonTablePlanType plan_type;		/* plan type */
+	JsonTablePlanJoinType join_type;	/* join type (for joined plan only) */
+	JsonTablePlan *plan1;				/* first joined plan */
+	JsonTablePlan *plan2;				/* second joined plan */
+	char	   *pathname;				/* path name (for simple plan only) */
+	int			location;				/* token location, or -1 if unknown */
+};
+
+/*
  * JsonTable -
  *		untransformed representation of JSON_TABLE
  */
@@ -1706,6 +1747,7 @@ typedef struct JsonTable
 	NodeTag		type;
 	JsonCommon *common;					/* common JSON path syntax fields */
 	List	   *columns;				/* list of JsonTableColumn */
+	JsonTablePlan *plan;				/* join plan, if specified */
 	JsonBehavior *on_error;				/* ON ERROR behavior, if specified */
 	Alias	   *alias;					/* table alias in FROM clause */
 	bool		lateral;				/* does it have LATERAL prefix? */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 290898cfd75..66d32fc0062 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1473,7 +1473,9 @@ typedef struct JsonTableParent
 {
 	NodeTag		type;
 	Const	   *path;		/* jsonpath constant */
+	char	   *name;		/* path name */
 	Node	   *child;		/* nested columns, if any */
+	bool		outerJoin;	/* outer or inner join for nested columns? */
 	int			colMin;		/* min column index in the resulting column list */
 	int			colMax;		/* max column index in the resulting column list */
 	bool		errorOnError; /* ERROR/EMPTY ON ERROR behavior */
@@ -1488,6 +1490,7 @@ typedef struct JsonTableSibling
 	NodeTag		type;
 	Node	   *larg;		/* left join node */
 	Node	   *rarg;		/* right join node */
+	bool		cross;		/* cross or union join? */
 } JsonTableSibling;
 
 /* ----------------