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

1 files changed, 88 insertions, 30 deletions

diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index c55b3aae027..ee9b5089b92 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -175,6 +175,7 @@ struct JsonTableScanState
 	Datum		current;
 	int			ordinal;
 	bool		currentIsNull;
+	bool		outerJoin;
 	bool		errorOnError;
 	bool		advanceNested;
 	bool		reset;
@@ -188,6 +189,7 @@ struct JsonTableJoinState
 		{
 			JsonTableJoinState *left;
 			JsonTableJoinState *right;
+			bool		cross;
 			bool		advanceRight;
 		}			join;
 		JsonTableScanState scan;
@@ -3166,6 +3168,7 @@ JsonTableInitScanState(JsonTableContext *cxt, JsonTableScanState *scan,
 	int			i;
 
 	scan->parent = parent;
+	scan->outerJoin = node->outerJoin;
 	scan->errorOnError = node->errorOnError;
 	scan->path = DatumGetJsonPathP(node->path->constvalue);
 	scan->args = args;
@@ -3192,6 +3195,7 @@ JsonTableInitPlanState(JsonTableContext *cxt, Node *plan,
 		JsonTableSibling *join = castNode(JsonTableSibling, plan);
 
 		state->is_join = true;
+		state->u.join.cross = join->cross;
 		state->u.join.left = JsonTableInitPlanState(cxt, join->larg, parent);
 		state->u.join.right = JsonTableInitPlanState(cxt, join->rarg, parent);
 	}
@@ -3328,8 +3332,26 @@ JsonTableSetDocument(TableFuncScanState *state, Datum value)
 	JsonTableResetContextItem(&cxt->root, value);
 }
 
+/* Recursively reset scan and its child nodes */
+static void
+JsonTableRescanRecursive(JsonTableJoinState *state)
+{
+	if (state->is_join)
+	{
+		JsonTableRescanRecursive(state->u.join.left);
+		JsonTableRescanRecursive(state->u.join.right);
+		state->u.join.advanceRight = false;
+	}
+	else
+	{
+		JsonTableRescan(&state->u.scan);
+		if (state->u.scan.nested)
+			JsonTableRescanRecursive(state->u.scan.nested);
+	}
+}
+
 /*
- * Fetch next row from a union joined scan.
+ * Fetch next row from a cross/union joined scan.
  *
  * Returns false at the end of a scan, true otherwise.
  */
@@ -3339,17 +3361,48 @@ JsonTableNextJoinRow(JsonTableJoinState *state)
 	if (!state->is_join)
 		return JsonTableNextRow(&state->u.scan);
 
-	if (!state->u.join.advanceRight)
+	if (state->u.join.advanceRight)
 	{
-		/* fetch next outer row */
-		if (JsonTableNextJoinRow(state->u.join.left))
+		/* fetch next inner row */
+		if (JsonTableNextJoinRow(state->u.join.right))
 			return true;
 
-		state->u.join.advanceRight = true;	/* next inner row */
+		/* inner rows are exhausted */
+		if (state->u.join.cross)
+			state->u.join.advanceRight = false;	/* next outer row */
+		else
+			return false;	/* end of scan */
+	}
+
+	while (!state->u.join.advanceRight)
+	{
+		/* fetch next outer row */
+		bool		left = JsonTableNextJoinRow(state->u.join.left);
+
+		if (state->u.join.cross)
+		{
+			if (!left)
+				return false;	/* end of scan */
+
+			JsonTableRescanRecursive(state->u.join.right);
+
+			if (!JsonTableNextJoinRow(state->u.join.right))
+				continue;	/* next outer row */
+
+			state->u.join.advanceRight = true;	/* next inner row */
+		}
+		else if (!left)
+		{
+			if (!JsonTableNextJoinRow(state->u.join.right))
+				return false;	/* end of scan */
+
+			state->u.join.advanceRight = true;	/* next inner row */
+		}
+
+		break;
 	}
 
-	/* fetch next inner row */
-	return JsonTableNextJoinRow(state->u.join.right);
+	return true;
 }
 
 /* Recursively set 'reset' flag of scan and its child nodes */
@@ -3373,16 +3426,13 @@ JsonTableJoinReset(JsonTableJoinState *state)
 }
 
 /*
- * Fetch next row from a simple scan with outer joined nested subscans.
+ * Fetch next row from a simple scan with outer/inner joined nested subscans.
  *
  * Returns false at the end of a scan, true otherwise.
  */
 static bool
 JsonTableNextRow(JsonTableScanState *scan)
 {
-	JsonbValue *jbv;
-	MemoryContext oldcxt;
-
 	/* reset context item if requested */
 	if (scan->reset)
 	{
@@ -3394,34 +3444,42 @@ JsonTableNextRow(JsonTableScanState *scan)
 	if (scan->advanceNested)
 	{
 		/* fetch next nested row */
-		if (JsonTableNextJoinRow(scan->nested))
-			return true;
+		scan->advanceNested = JsonTableNextJoinRow(scan->nested);
 
-		scan->advanceNested = false;
+		if (scan->advanceNested)
+			return true;
 	}
 
-	/* fetch next row */
-	jbv = JsonValueListNext(&scan->found, &scan->iter);
-
-	if (!jbv)
+	for (;;)
 	{
-		scan->current = PointerGetDatum(NULL);
-		scan->currentIsNull = true;
-		return false;	/* end of scan */
-	}
+		/* fetch next row */
+		JsonbValue *jbv = JsonValueListNext(&scan->found, &scan->iter);
+		MemoryContext oldcxt;
 
-	/* set current row item */
-	oldcxt = MemoryContextSwitchTo(scan->mcxt);
-	scan->current = JsonbPGetDatum(JsonbValueToJsonb(jbv));
-	scan->currentIsNull = false;
-	MemoryContextSwitchTo(oldcxt);
+		if (!jbv)
+		{
+			scan->current = PointerGetDatum(NULL);
+			scan->currentIsNull = true;
+			return false;	/* end of scan */
+		}
 
-	scan->ordinal++;
+		/* set current row item */
+		oldcxt = MemoryContextSwitchTo(scan->mcxt);
+		scan->current = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+		scan->currentIsNull = false;
+		MemoryContextSwitchTo(oldcxt);
+
+		scan->ordinal++;
+
+		if (!scan->nested)
+			break;
 
-	if (scan->nested)
-	{
 		JsonTableJoinReset(scan->nested);
+
 		scan->advanceNested = JsonTableNextJoinRow(scan->nested);
+
+		if (scan->advanceNested || scan->outerJoin)
+			break;
 	}
 
 	return true;