JSON_TABLE

This feature allows jsonb data to be treated as a table and thus used in
a FROM clause like other tabular data. Data can be selected from the
jsonb using jsonpath expressions, and hoisted out of nested structures
in the jsonb to form multiple rows, more or less like an outer join.

Nikita Glukhov

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

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

3 files changed, 31 insertions, 11 deletions

diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index d4d3850ec7c..38b94c02767 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -2635,6 +2635,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
 					var->typmod = exprTypmod((Node *) argexpr);
 					var->estate = ExecInitExpr(argexpr, state->parent);
 					var->econtext = NULL;
+					var->mcxt = NULL;
 					var->evaluated = false;
 					var->value = (Datum) 0;
 					var->isnull = true;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 7d4253d970d..7094e7e3f6f 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4602,6 +4602,7 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
 
 		case JSON_BEHAVIOR_NULL:
 		case JSON_BEHAVIOR_UNKNOWN:
+		case JSON_BEHAVIOR_EMPTY:
 			*is_null = true;
 			return (Datum) 0;
 
@@ -4694,8 +4695,14 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
 
 	if (!var->evaluated)
 	{
+		MemoryContext oldcxt = var->mcxt ?
+			MemoryContextSwitchTo(var->mcxt) : NULL;
+
 		var->value = ExecEvalExpr(var->estate, var->econtext, &var->isnull);
 		var->evaluated = true;
+
+		if (oldcxt)
+			MemoryContextSwitchTo(oldcxt);
 	}
 
 	if (var->isnull)
@@ -4843,6 +4850,7 @@ ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
 	PG_CATCH();
 	{
 		ErrorData  *edata;
+		int			ecategory;
 
 		/* Save error info in oldcontext */
 		MemoryContextSwitchTo(oldcontext);
@@ -4854,8 +4862,10 @@ ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
 		MemoryContextSwitchTo(oldcontext);
 		CurrentResourceOwner = oldowner;
 
-		if (ERRCODE_TO_CATEGORY(edata->sqlerrcode) !=
-			ERRCODE_DATA_EXCEPTION)
+		ecategory = ERRCODE_TO_CATEGORY(edata->sqlerrcode);
+
+		if (ecategory != ERRCODE_DATA_EXCEPTION &&	/* jsonpath and other data errors */
+			ecategory != ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION)	/* domain errors */
 			ReThrowError(edata);
 
 		res = (Datum) 0;
@@ -4981,6 +4991,10 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
 				break;
 			}
 
+		case JSON_TABLE_OP:
+			*resnull = false;
+			return item;
+
 		default:
 			elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op);
 			return (Datum) 0;
diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c
index 0db4ed0c2fe..691c3e28cef 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,
@@ -381,14 +383,17 @@ 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")));
+	if (routine->SetRowFilter)
+	{
+		/* 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")));
 
-	routine->SetRowFilter(tstate, TextDatumGetCString(value));
+		routine->SetRowFilter(tstate, TextDatumGetCString(value));
+	}
 
 	/*
 	 * Install the column filter expressions into the table builder context.