Add basic JSON_TABLE() functionality

JSON_TABLE() allows JSON data to be converted into a relational view
and thus used, for example, in a FROM clause, like other tabular
data.  Data to show in the view is selected from a source JSON object
using a JSON path expression to get a sequence of JSON objects that's
called a "row pattern", which becomes the source to compute the
SQL/JSON values that populate the view's output columns.  Column
values themselves are computed using JSON path expressions applied to
each of the JSON objects comprising the "row pattern", for which the
SQL/JSON query functions added in 6185c9737cf4 are used.

To implement JSON_TABLE() as a table function, this augments the
TableFunc and TableFuncScanState nodes that are currently used to
support XMLTABLE() with some JSON_TABLE()-specific fields.

Note that the JSON_TABLE() spec includes NESTED COLUMNS and PLAN
clauses, which are required to provide more flexibility to extract
data out of nested JSON objects, but they are not implemented here
to keep this commit of manageable size.

Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Jian He <jian.universality@gmail.com>

Reviewers have included (in no particular order):

Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup,
Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby, Álvaro Herrera, Jian He

Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com

1 files changed, 27 insertions, 14 deletions

diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c
index 72ca34a2287..f483221bb8e 100644
--- a/src/backend/executor/nodeTableFuncscan.c
+++ b/src/backend/executor/nodeTableFuncscan.c
@@ -28,6 +28,7 @@
 #include "miscadmin.h"
 #include "nodes/execnodes.h"
 #include "utils/builtins.h"
+#include "utils/jsonpath.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/xml.h"
@@ -161,8 +162,9 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags)
 	scanstate->ss.ps.qual =
 		ExecInitQual(node->scan.plan.qual, &scanstate->ss.ps);
 
-	/* Only XMLTABLE is supported currently */
-	scanstate->routine = &XmlTableRoutine;
+	/* Only XMLTABLE and JSON_TABLE are supported currently */
+	scanstate->routine =
+		tf->functype == TFT_XMLTABLE ? &XmlTableRoutine : &JsonbTableRoutine;
 
 	scanstate->perTableCxt =
 		AllocSetContextCreate(CurrentMemoryContext,
@@ -182,6 +184,10 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags)
 		ExecInitExprList(tf->colexprs, (PlanState *) scanstate);
 	scanstate->coldefexprs =
 		ExecInitExprList(tf->coldefexprs, (PlanState *) scanstate);
+	scanstate->colvalexprs =
+		ExecInitExprList(tf->colvalexprs, (PlanState *) scanstate);
+	scanstate->passingvalexprs =
+		ExecInitExprList(tf->passingvalexprs, (PlanState *) scanstate);
 
 	scanstate->notnulls = tf->notnulls;
 
@@ -274,11 +280,12 @@ tfuncFetchRows(TableFuncScanState *tstate, ExprContext *econtext)
 
 	/*
 	 * Each call to fetch a new set of rows - of which there may be very many
-	 * if XMLTABLE is being used in a lateral join - will allocate a possibly
-	 * substantial amount of memory, so we cannot use the per-query context
-	 * here. perTableCxt now serves the same function as "argcontext" does in
-	 * FunctionScan - a place to store per-one-call (i.e. one result table)
-	 * lifetime data (as opposed to per-query or per-result-tuple).
+	 * if XMLTABLE or JSON_TABLE is being used in a lateral join - will
+	 * allocate a possibly substantial amount of memory, so we cannot use the
+	 * per-query context here. perTableCxt now serves the same function as
+	 * "argcontext" does in FunctionScan - a place to store per-one-call (i.e.
+	 * one result table) lifetime data (as opposed to per-query or
+	 * per-result-tuple).
 	 */
 	MemoryContextSwitchTo(tstate->perTableCxt);
 
@@ -369,14 +376,20 @@ tfuncInitialize(TableFuncScanState *tstate, ExprContext *econtext, Datum doc)
 		routine->SetNamespace(tstate, ns_name, ns_uri);
 	}
 
-	/* Install the row filter expression into the table builder context */
-	value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull);
-	if (isnull)
-		ereport(ERROR,
-				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-				 errmsg("row filter expression must not be null")));
+	/*
+	 * Install the row filter expression, if any, into the table builder
+	 * context.
+	 */
+	if (routine->SetRowFilter)
+	{
+		value = ExecEvalExpr(tstate->rowexpr, econtext, &isnull);
+		if (isnull)
+			ereport(ERROR,
+					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+					 errmsg("row filter expression must not be null")));
 
-	routine->SetRowFilter(tstate, TextDatumGetCString(value));
+		routine->SetRowFilter(tstate, TextDatumGetCString(value));
+	}
 
 	/*
 	 * Install the column filter expressions into the table builder context.