SQL/JSON query functions

This introduces the SQL/JSON functions for querying JSON data using
jsonpath expressions. The functions are:

JSON_EXISTS()
JSON_QUERY()
JSON_VALUE()

All of these functions only operate on jsonb. The workaround for now is
to cast the argument to jsonb.

JSON_EXISTS() tests if the jsonpath expression applied to the jsonb
value yields any values. JSON_VALUE() must return a single value, and an
error occurs if it tries to return multiple values. JSON_QUERY() must
return a json object or array, and there are various WRAPPER options for
handling scalar or multi-value results. Both these functions have
options for handling EMPTY and ERROR conditions.

Nikita Glukhov

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.

Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru

1 files changed, 543 insertions, 0 deletions

diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index c0bd9556209..1215f707e28 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -57,14 +57,18 @@
 #include "postgres.h"
 
 #include "access/heaptoast.h"
+#include "access/xact.h"
+#include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "commands/sequence.h"
 #include "executor/execExpr.h"
 #include "executor/nodeSubplan.h"
 #include "funcapi.h"
 #include "miscadmin.h"
+#include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "parser/parsetree.h"
+#include "parser/parse_expr.h"
 #include "pgstat.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
@@ -74,8 +78,10 @@
 #include "utils/json.h"
 #include "utils/jsonb.h"
 #include "utils/jsonfuncs.h"
+#include "utils/jsonpath.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
+#include "utils/resowner.h"
 #include "utils/timestamp.h"
 #include "utils/typcache.h"
 #include "utils/xml.h"
@@ -482,6 +488,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
 		&&CASE_EEOP_SUBPLAN,
 		&&CASE_EEOP_JSON_CONSTRUCTOR,
 		&&CASE_EEOP_IS_JSON,
+		&&CASE_EEOP_JSONEXPR,
 		&&CASE_EEOP_AGG_STRICT_DESERIALIZE,
 		&&CASE_EEOP_AGG_DESERIALIZE,
 		&&CASE_EEOP_AGG_STRICT_INPUT_CHECK_ARGS,
@@ -1805,7 +1812,13 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
 		{
 			/* too complex for an inline implementation */
 			ExecEvalJsonIsPredicate(state, op);
+			EEO_NEXT();
+		}
 
+		EEO_CASE(EEOP_JSONEXPR)
+		{
+			/* too complex for an inline implementation */
+			ExecEvalJson(state, op, econtext);
 			EEO_NEXT();
 		}
 
@@ -4523,3 +4536,533 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 	*op->resvalue = res;
 	*op->resnull = isnull;
 }
+
+/*
+ * Evaluate a JSON error/empty behavior result.
+ */
+static Datum
+ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
+					 ExprState *default_estate, bool *is_null)
+{
+	*is_null = false;
+
+	switch (behavior->btype)
+	{
+		case JSON_BEHAVIOR_EMPTY_ARRAY:
+			return JsonbPGetDatum(JsonbMakeEmptyArray());
+
+		case JSON_BEHAVIOR_EMPTY_OBJECT:
+			return JsonbPGetDatum(JsonbMakeEmptyObject());
+
+		case JSON_BEHAVIOR_TRUE:
+			return BoolGetDatum(true);
+
+		case JSON_BEHAVIOR_FALSE:
+			return BoolGetDatum(false);
+
+		case JSON_BEHAVIOR_NULL:
+		case JSON_BEHAVIOR_UNKNOWN:
+			*is_null = true;
+			return (Datum) 0;
+
+		case JSON_BEHAVIOR_DEFAULT:
+			return ExecEvalExpr(default_estate, econtext, is_null);
+
+		default:
+			elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
+			return (Datum) 0;
+	}
+}
+
+/*
+ * Evaluate a coercion of a JSON item to the target type.
+ */
+static Datum
+ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext,
+						 Datum res, bool *isNull, void *p, bool *error)
+{
+	ExprState *estate = p;
+
+	if (estate)		/* coerce using specified expression */
+		return ExecEvalExpr(estate, econtext, isNull);
+
+	if (op->d.jsonexpr.jsexpr->op != JSON_EXISTS_OP)
+	{
+		JsonCoercion *coercion = op->d.jsonexpr.jsexpr->result_coercion;
+		JsonExpr   *jexpr = op->d.jsonexpr.jsexpr;
+		Jsonb	   *jb = *isNull ? NULL : DatumGetJsonbP(res);
+
+		if ((coercion && coercion->via_io) ||
+			(jexpr->omit_quotes && !*isNull &&
+			 JB_ROOT_IS_SCALAR(jb)))
+		{
+			/* strip quotes and call typinput function */
+			char	   *str = *isNull ? NULL : JsonbUnquote(jb);
+
+			return InputFunctionCall(&op->d.jsonexpr.input.func, str,
+									 op->d.jsonexpr.input.typioparam,
+									 jexpr->returning->typmod);
+		}
+		else if (coercion && coercion->via_populate)
+			return json_populate_type(res, JSONBOID,
+									  jexpr->returning->typid,
+									  jexpr->returning->typmod,
+									  &op->d.jsonexpr.cache,
+									  econtext->ecxt_per_query_memory,
+									  isNull);
+	}
+
+	if (op->d.jsonexpr.result_expr)
+	{
+		op->d.jsonexpr.res_expr->value = res;
+		op->d.jsonexpr.res_expr->isnull = *isNull;
+
+		res = ExecEvalExpr(op->d.jsonexpr.result_expr, econtext, isNull);
+	}
+
+	return res;
+}
+
+/*
+ * Evaluate a JSON path variable caching computed value.
+ */
+int
+EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
+				JsonbValue *val, JsonbValue *baseObject)
+{
+	JsonPathVariableEvalContext *var = NULL;
+	List	   *vars = cxt;
+	ListCell   *lc;
+	int			id = 1;
+
+	if (!varName)
+		return list_length(vars);
+
+	foreach(lc, vars)
+	{
+		var = lfirst(lc);
+
+		if (!strncmp(var->name, varName, varNameLen))
+			break;
+
+		var = NULL;
+		id++;
+	}
+
+	if (!var)
+		return -1;
+
+	if (!var->evaluated)
+	{
+		var->value = ExecEvalExpr(var->estate, var->econtext, &var->isnull);
+		var->evaluated = true;
+	}
+
+	if (var->isnull)
+	{
+		val->type = jbvNull;
+		return 0;
+	}
+
+	JsonItemFromDatum(var->value, var->typid, var->typmod, val);
+
+	*baseObject = *val;
+	return id;
+}
+
+/*
+ * Prepare SQL/JSON item coercion to the output type. Returned a datum of the
+ * corresponding SQL type and a pointer to the coercion state.
+ */
+Datum
+ExecPrepareJsonItemCoercion(JsonbValue *item,
+							JsonReturning *returning,
+							struct JsonCoercionsState *coercions,
+							struct JsonCoercionState **pcoercion)
+{
+	struct JsonCoercionState *coercion;
+	Datum		res;
+	JsonbValue	buf;
+
+	if (item->type == jbvBinary &&
+		JsonContainerIsScalar(item->val.binary.data))
+	{
+		bool		res PG_USED_FOR_ASSERTS_ONLY;
+
+		res = JsonbExtractScalar(item->val.binary.data, &buf);
+		item = &buf;
+		Assert(res);
+	}
+
+	/* get coercion state reference and datum of the corresponding SQL type */
+	switch (item->type)
+	{
+		case jbvNull:
+			coercion = &coercions->null;
+			res = (Datum) 0;
+			break;
+
+		case jbvString:
+			coercion = &coercions->string;
+			res = PointerGetDatum(
+				cstring_to_text_with_len(item->val.string.val,
+										 item->val.string.len));
+			break;
+
+		case jbvNumeric:
+			coercion = &coercions->numeric;
+			res = NumericGetDatum(item->val.numeric);
+			break;
+
+		case jbvBool:
+			coercion = &coercions->boolean;
+			res = BoolGetDatum(item->val.boolean);
+			break;
+
+		case jbvDatetime:
+			res = item->val.datetime.value;
+			switch (item->val.datetime.typid)
+			{
+				case DATEOID:
+					coercion = &coercions->date;
+					break;
+				case TIMEOID:
+					coercion = &coercions->time;
+					break;
+				case TIMETZOID:
+					coercion = &coercions->timetz;
+					break;
+				case TIMESTAMPOID:
+					coercion = &coercions->timestamp;
+					break;
+				case TIMESTAMPTZOID:
+					coercion = &coercions->timestamptz;
+					break;
+				default:
+					elog(ERROR, "unexpected jsonb datetime type oid %d",
+						 item->val.datetime.typid);
+					return (Datum) 0;
+			}
+			break;
+
+		case jbvArray:
+		case jbvObject:
+		case jbvBinary:
+			coercion = &coercions->composite;
+			res = JsonbPGetDatum(JsonbValueToJsonb(item));
+			break;
+
+		default:
+			elog(ERROR, "unexpected jsonb value type %d", item->type);
+			return (Datum) 0;
+	}
+
+	*pcoercion = coercion;
+
+	return res;
+}
+
+typedef Datum (*JsonFunc)(ExprEvalStep *op, ExprContext *econtext,
+						  Datum item, bool *resnull, void *p, bool *error);
+
+static Datum
+ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
+						 ExprContext *econtext,
+						 Datum res, bool *resnull,
+						 void *p, bool *error, bool subtrans)
+{
+	MemoryContext oldcontext;
+	ResourceOwner oldowner;
+
+	if (!subtrans)
+		/* No need to use subtransactions. */
+		return func(op, econtext, res, resnull, p, error);
+
+	/*
+	 * We should catch exceptions of category ERRCODE_DATA_EXCEPTION
+	 * and execute the corresponding ON ERROR behavior then.
+	 */
+	oldcontext = CurrentMemoryContext;
+	oldowner = CurrentResourceOwner;
+
+	Assert(error);
+
+	BeginInternalSubTransaction(NULL);
+	/* Want to execute expressions inside function's memory context */
+	MemoryContextSwitchTo(oldcontext);
+
+	PG_TRY();
+	{
+		res = func(op, econtext, res, resnull, p, error);
+
+		/* Commit the inner transaction, return to outer xact context */
+		ReleaseCurrentSubTransaction();
+		MemoryContextSwitchTo(oldcontext);
+		CurrentResourceOwner = oldowner;
+	}
+	PG_CATCH();
+	{
+		ErrorData  *edata;
+
+		/* Save error info in oldcontext */
+		MemoryContextSwitchTo(oldcontext);
+		edata = CopyErrorData();
+		FlushErrorState();
+
+		/* Abort the inner transaction */
+		RollbackAndReleaseCurrentSubTransaction();
+		MemoryContextSwitchTo(oldcontext);
+		CurrentResourceOwner = oldowner;
+
+		if (ERRCODE_TO_CATEGORY(edata->sqlerrcode) !=
+			ERRCODE_DATA_EXCEPTION)
+			ReThrowError(edata);
+
+		res = (Datum) 0;
+		*error = true;
+	}
+	PG_END_TRY();
+
+	return res;
+}
+
+
+typedef struct
+{
+	JsonPath   *path;
+	bool	   *error;
+	bool		coercionInSubtrans;
+} ExecEvalJsonExprContext;
+
+static Datum
+ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
+				 Datum item, bool *resnull, void *pcxt,
+				 bool *error)
+{
+	ExecEvalJsonExprContext *cxt = pcxt;
+	JsonPath   *path = cxt->path;
+	JsonExpr   *jexpr = op->d.jsonexpr.jsexpr;
+	ExprState  *estate = NULL;
+	bool		empty = false;
+	Datum		res = (Datum) 0;
+
+	switch (jexpr->op)
+	{
+		case JSON_QUERY_OP:
+			res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error,
+								op->d.jsonexpr.args);
+			if (error && *error)
+			{
+				*resnull = true;
+				return (Datum) 0;
+			}
+			*resnull = !DatumGetPointer(res);
+			break;
+
+		case JSON_VALUE_OP:
+			{
+				struct JsonCoercionState *jcstate;
+				JsonbValue *jbv = JsonPathValue(item, path, &empty, error,
+												op->d.jsonexpr.args);
+
+				if (error && *error)
+					return (Datum) 0;
+
+				if (!jbv)	/* NULL or empty */
+					break;
+
+				Assert(!empty);
+
+				*resnull = false;
+
+				/* coerce scalar item to the output type */
+				if (jexpr->returning->typid == JSONOID ||
+					jexpr->returning->typid == JSONBOID)
+				{
+					/* Use result coercion from json[b] to the output type */
+					res = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+					break;
+				}
+
+				/* Use coercion from SQL/JSON item type to the output type */
+				res = ExecPrepareJsonItemCoercion(jbv,
+												  op->d.jsonexpr.jsexpr->returning,
+												  &op->d.jsonexpr.coercions,
+												  &jcstate);
+
+				if (jcstate->coercion &&
+					(jcstate->coercion->via_io ||
+					 jcstate->coercion->via_populate))
+				{
+					if (error)
+					{
+						*error = true;
+						return (Datum) 0;
+					}
+					/*
+					 * Coercion via I/O means here that the cast to the target
+					 * type simply does not exist.
+					 */
+					ereport(ERROR,
+							/*
+							 * XXX Standard says about a separate error code
+							 * ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE
+							 * but does not define its number.
+							 */
+							(errcode(ERRCODE_SQL_JSON_SCALAR_REQUIRED),
+							 errmsg("SQL/JSON item cannot be cast to target type")));
+				}
+				else if (!jcstate->estate)
+					return res;		/* no coercion */
+
+				/* coerce using specific expression */
+				estate = jcstate->estate;
+				op->d.jsonexpr.coercion_expr->value = res;
+				op->d.jsonexpr.coercion_expr->isnull = *resnull;
+				break;
+			}
+
+		case JSON_EXISTS_OP:
+			{
+				bool		exists = JsonPathExists(item, path,
+													op->d.jsonexpr.args,
+													error);
+
+				*resnull = error && *error;
+				res = BoolGetDatum(exists);
+
+				if (!op->d.jsonexpr.result_expr)
+					return res;
+
+				/* coerce using result expression */
+				estate = op->d.jsonexpr.result_expr;
+				op->d.jsonexpr.res_expr->value = res;
+				op->d.jsonexpr.res_expr->isnull = *resnull;
+				break;
+			}
+
+		default:
+			elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op);
+			return (Datum) 0;
+	}
+
+	if (empty)
+	{
+		Assert(jexpr->on_empty);	/* it is not JSON_EXISTS */
+
+		if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+		{
+			if (error)
+			{
+				*error = true;
+				return (Datum) 0;
+			}
+
+			ereport(ERROR,
+					(errcode(ERRCODE_NO_SQL_JSON_ITEM),
+					 errmsg("no SQL/JSON item")));
+		}
+
+		if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT)
+			/*
+			 * Execute DEFAULT expression as a coercion expression, because
+			 * its result is already coerced to the target type.
+			 */
+			estate = op->d.jsonexpr.default_on_empty;
+		else
+			/* Execute ON EMPTY behavior */
+			res = ExecEvalJsonBehavior(econtext, jexpr->on_empty,
+									   op->d.jsonexpr.default_on_empty,
+									   resnull);
+	}
+
+	return ExecEvalJsonExprSubtrans(ExecEvalJsonExprCoercion, op, econtext,
+									res, resnull, estate, error,
+									cxt->coercionInSubtrans);
+}
+
+bool
+ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
+								struct JsonCoercionsState *coercions)
+{
+	if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+		return false;
+
+	if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion)
+		return false;
+
+	if (!coercions)
+		return true;
+
+	return false;
+}
+
+/* ----------------------------------------------------------------
+ *		ExecEvalJson
+ * ----------------------------------------------------------------
+ */
+void
+ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+{
+	ExecEvalJsonExprContext cxt;
+	JsonExpr   *jexpr = op->d.jsonexpr.jsexpr;
+	Datum		item;
+	Datum		res = (Datum) 0;
+	JsonPath   *path;
+	ListCell   *lc;
+	bool		error = false;
+	bool		needSubtrans;
+	bool		throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
+
+	*op->resnull = true;		/* until we get a result */
+	*op->resvalue = (Datum) 0;
+
+	if (op->d.jsonexpr.formatted_expr->isnull || op->d.jsonexpr.pathspec->isnull)
+	{
+		/* execute domain checks for NULLs */
+		(void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull,
+										NULL, NULL);
+
+		Assert(*op->resnull);
+		return;
+	}
+
+	item = op->d.jsonexpr.formatted_expr->value;
+	path = DatumGetJsonPathP(op->d.jsonexpr.pathspec->value);
+
+	/* reset JSON path variable contexts */
+	foreach(lc, op->d.jsonexpr.args)
+	{
+		JsonPathVariableEvalContext *var = lfirst(lc);
+
+		var->econtext = econtext;
+		var->evaluated = false;
+	}
+
+	needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &op->d.jsonexpr.coercions);
+
+	cxt.path = path;
+	cxt.error = throwErrors ? NULL : &error;
+	cxt.coercionInSubtrans = !needSubtrans && !throwErrors;
+	Assert(!needSubtrans || cxt.error);
+
+	res = ExecEvalJsonExprSubtrans(ExecEvalJsonExpr, op, econtext, item,
+								   op->resnull, &cxt, cxt.error,
+								   needSubtrans);
+
+	if (error)
+	{
+		/* Execute ON ERROR behavior */
+		res = ExecEvalJsonBehavior(econtext, jexpr->on_error,
+								   op->d.jsonexpr.default_on_error,
+								   op->resnull);
+
+		/* result is already coerced in DEFAULT behavior case */
+		if (jexpr->on_error->btype != JSON_BEHAVIOR_DEFAULT)
+			res = ExecEvalJsonExprCoercion(op, econtext, res,
+										   op->resnull,
+										   NULL, NULL);
+	}
+
+	*op->resvalue = res;
+}