v9-0007-Remove-subtransaction-in-SQL-JSON-execution.patch

From 9154ff35f22fe7e8ce65ffdad757ff94abd79440 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Tue, 30 Aug 2022 00:10:44 +0300
Subject: [PATCH v9 7/9] Remove subtransaction in SQL/JSON execution

---
 src/backend/executor/execExpr.c             | 180 ++--
 src/backend/executor/execExprInterp.c       | 946 ++++++++++++--------
 src/backend/jit/llvm/llvmjit_expr.c         |   2 +-
 src/backend/jit/llvm/llvmjit_types.c        |   2 +-
 src/backend/nodes/nodeFuncs.c               |  91 +-
 src/backend/optimizer/util/clauses.c        |  73 +-
 src/backend/parser/parse_expr.c             | 226 ++---
 src/backend/utils/adt/jsonpath.c            |   8 +-
 src/include/executor/execExpr.h             |  49 +-
 src/include/nodes/primnodes.h               |  36 +-
 src/include/optimizer/clauses.h             |   2 +
 src/include/utils/jsonpath.h                |   3 +-
 src/test/regress/expected/jsonb_sqljson.out | 327 +++++--
 src/test/regress/sql/jsonb_sqljson.sql      |  72 +-
 14 files changed, 1131 insertions(+), 886 deletions(-)

diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index d0a57c7aaee..f50e0d703f8 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -86,7 +86,9 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
 								  FunctionCallInfo fcinfo, AggStatePerTrans pertrans,
 								  int transno, int setno, int setoff, bool ishash,
 								  bool nullcheck);
-
+static void ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state,
+							 ExprEvalStep *scratch,
+							 Datum *resv, bool *resnull);
 
 static ExprState *
 ExecInitExprInternal(Expr *node, PlanState *parent, ParamListInfo ext_params,
@@ -2559,114 +2561,9 @@ ExecInitExprRec(Expr *node, ExprState *state,
 			}
 
 		case T_JsonExpr:
-			{
-				JsonExpr   *jexpr = castNode(JsonExpr, node);
-				JsonExprState *jsestate = palloc0(sizeof(JsonExprState));
-				ListCell   *argexprlc;
-				ListCell   *argnamelc;
-
-				scratch.opcode = EEOP_JSONEXPR;
-				scratch.d.jsonexpr.jsestate = jsestate;
-
-				jsestate->jsexpr = jexpr;
-
-				jsestate->formatted_expr =
-					palloc(sizeof(*jsestate->formatted_expr));
-
-				ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
-								&jsestate->formatted_expr->value,
-								&jsestate->formatted_expr->isnull);
-
-				jsestate->pathspec =
-					palloc(sizeof(*jsestate->pathspec));
-
-				ExecInitExprRec((Expr *) jexpr->path_spec, state,
-								&jsestate->pathspec->value,
-								&jsestate->pathspec->isnull);
-
-				jsestate->res_expr =
-					palloc(sizeof(*jsestate->res_expr));
-
-				jsestate->result_expr = jexpr->result_coercion
-					? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr,
-												state->parent,
-												&jsestate->res_expr->value,
-												&jsestate->res_expr->isnull)
-					: NULL;
-
-				jsestate->default_on_empty = !jexpr->on_empty ? NULL :
-					ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
-								 state->parent);
-
-				jsestate->default_on_error =
-					ExecInitExpr((Expr *) jexpr->on_error->default_expr,
-								 state->parent);
-
-				if (jexpr->omit_quotes ||
-					(jexpr->result_coercion && jexpr->result_coercion->via_io))
-				{
-					Oid			typinput;
-
-					/* lookup the result type's input function */
-					getTypeInputInfo(jexpr->returning->typid, &typinput,
-									 &jsestate->input.typioparam);
-					fmgr_info(typinput, &jsestate->input.func);
-				}
-
-				jsestate->args = NIL;
-
-				forboth(argexprlc, jexpr->passing_values,
-						argnamelc, jexpr->passing_names)
-				{
-					Expr	   *argexpr = (Expr *) lfirst(argexprlc);
-					String	   *argname = lfirst_node(String, argnamelc);
-					JsonPathVariableEvalContext *var = palloc(sizeof(*var));
-
-					var->name = pstrdup(argname->sval);
-					var->typid = exprType((Node *) argexpr);
-					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;
-
-					jsestate->args =
-						lappend(jsestate->args, var);
-				}
-
-				jsestate->cache = NULL;
-
-				if (jexpr->coercions)
-				{
-					JsonCoercion **coercion;
-					struct JsonCoercionState *cstate;
-					Datum	   *caseval;
-					bool	   *casenull;
-
-					jsestate->coercion_expr =
-						palloc(sizeof(*jsestate->coercion_expr));
-
-					caseval = &jsestate->coercion_expr->value;
-					casenull = &jsestate->coercion_expr->isnull;
-
-					for (cstate = &jsestate->coercions.null,
-						 coercion = &jexpr->coercions->null;
-						 coercion <= &jexpr->coercions->composite;
-						 coercion++, cstate++)
-					{
-						cstate->coercion = *coercion;
-						cstate->estate = *coercion ?
-							ExecInitExprWithCaseValue((Expr *) (*coercion)->expr,
-													  state->parent,
-													  caseval, casenull) : NULL;
-					}
-				}
-
-				ExprEvalPushStep(state, &scratch);
-				break;
-			}
+			ExecInitJsonExpr(castNode(JsonExpr, node), state, &scratch,
+							 resv, resnull);
+			break;
 
 		default:
 			elog(ERROR, "unrecognized node type: %d",
@@ -4257,3 +4154,68 @@ ExecBuildParamSetEqual(TupleDesc desc,
 
 	return state;
 }
+
+static void
+ExecInitJsonExpr(JsonExpr *jexpr, ExprState *state, ExprEvalStep *scratch,
+				 Datum *resv, bool *resnull)
+{
+	JsonExprState *jsestate;
+	ListCell   *argexprlc;
+	ListCell   *argnamelc;
+
+	/* JSON_TABLE preudo-function returns context item as a result */
+	if (jexpr->op == JSON_TABLE_OP)
+	{
+		ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+						resv, resnull);
+		return;
+	}
+
+	jsestate = palloc0(sizeof(JsonExprState));
+	jsestate->jsexpr = jexpr;
+
+	scratch->opcode = EEOP_JSONEXPR;
+	scratch->d.jsonexpr.jsestate = jsestate;
+
+	ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+					&jsestate->formatted_expr.value,
+					&jsestate->formatted_expr.isnull);
+
+	ExecInitExprRec((Expr *) jexpr->path_spec, state,
+					&jsestate->pathspec.value,
+					&jsestate->pathspec.isnull);
+
+	if (jexpr->on_empty)
+		jsestate->default_on_empty =
+			ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
+						 state->parent);
+
+	jsestate->default_on_error =
+		ExecInitExpr((Expr *) jexpr->on_error->default_expr,
+					 state->parent);
+
+	jsestate->args = NIL;
+
+	forboth(argexprlc, jexpr->passing_values,
+			argnamelc, jexpr->passing_names)
+	{
+		Expr	   *argexpr = (Expr *) lfirst(argexprlc);
+		String	   *argname = lfirst_node(String, argnamelc);
+		JsonPathVariableEvalContext *var = palloc(sizeof(*var));
+
+		var->name = pstrdup(argname->sval);
+		var->typid = exprType((Node *) argexpr);
+		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;
+
+		jsestate->args =
+			lappend(jsestate->args, var);
+	}
+
+	ExprEvalPushStep(state, scratch);
+}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 636794ca6f1..45c648817ba 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -75,6 +75,7 @@
 #include "utils/date.h"
 #include "utils/datum.h"
 #include "utils/expandedrecord.h"
+#include "utils/float.h"
 #include "utils/json.h"
 #include "utils/jsonb.h"
 #include "utils/jsonfuncs.h"
@@ -1844,7 +1845,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
 		EEO_CASE(EEOP_JSONEXPR)
 		{
 			/* too complex for an inline implementation */
-			ExecEvalJson(state, op, econtext);
+			ExecEvalJsonExpr(state, op, econtext);
 			EEO_NEXT();
 		}
 
@@ -4700,96 +4701,6 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 	*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:
-		case JSON_BEHAVIOR_EMPTY:
-			*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;
-	JsonExprState *jsestate;
-
-	if (estate)					/* coerce using specified expression */
-		return ExecEvalExpr(estate, econtext, isNull);
-
-	jsestate = op->d.jsonexpr.jsestate;
-
-	if (jsestate->jsexpr->op != JSON_EXISTS_OP)
-	{
-		JsonCoercion *coercion = jsestate->jsexpr->result_coercion;
-		JsonExpr   *jexpr = jsestate->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(&jsestate->input.func, str,
-									 jsestate->input.typioparam,
-									 jexpr->returning->typmod);
-		}
-		else if (coercion && coercion->via_populate)
-			return json_populate_type(res, JSONBOID,
-									  jexpr->returning->typid,
-									  jexpr->returning->typmod,
-									  &jsestate->cache,
-									  econtext->ecxt_per_query_memory,
-									  isNull);
-	}
-
-	if (jsestate->result_expr)
-	{
-		jsestate->res_expr->value = res;
-		jsestate->res_expr->isnull = *isNull;
-
-		res = ExecEvalExpr(jsestate->result_expr, econtext, isNull);
-	}
-
-	return res;
-}
-
 /*
  * Evaluate a JSON path variable caching computed value.
  */
@@ -4844,84 +4755,438 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
 }
 
 /*
- * Prepare SQL/JSON item coercion to the output type. Returned a datum of the
- * corresponding SQL type and a pointer to the coercion state.
+ * Check whether we need to override default coercion in
+ * JSON_QUERY(OMIT QUOTES) case.
  */
-Datum
-ExecPrepareJsonItemCoercion(JsonbValue *item,
-							JsonReturning *returning,
-							struct JsonCoercionsState *coercions,
-							struct JsonCoercionState **pcoercion)
+static bool
+ExecJsonQueryNeedsIOCoercion(JsonExpr *jsexpr, Datum res, bool isnull)
 {
-	struct JsonCoercionState *coercion;
-	Datum		res;
-	JsonbValue	buf;
+	if (jsexpr->omit_quotes && !isnull)
+	{
+		Jsonb	   *jb = DatumGetJsonbP(res);
+		JsonbValue	jbv;
+
+		/* Coerce string items via I/O in OMIT QUOTES case */
+		return JsonbExtractScalar(&jb->root, &jbv) &&
+			jbv.type == jbvString;
+	}
+
+	return false;
+}
 
-	if (item->type == jbvBinary &&
-		JsonContainerIsScalar(item->val.binary.data))
+/* Coerce C string to text, varchar(N), or bpchar(N) */
+static Datum
+ExecJsonStringCoercion(const char *str, int32 len, Oid typid, int32 typmod)
+{
+	if (typid == BYTEAOID)
 	{
-		bool		res PG_USED_FOR_ASSERTS_ONLY;
+		text	   *txt = cstring_to_text_with_len(str, len);
+		NameData	encoding = {0};
+
+		/* UTF8 is the only one supported encoding */
+		strcpy(NameStr(encoding), "UTF8");
 
-		res = JsonbExtractScalar(item->val.binary.data, &buf);
-		item = &buf;
-		Assert(res);
+		return DirectFunctionCall2(pg_convert_to,
+								   PointerGetDatum(txt),
+								   PointerGetDatum(&encoding));
 	}
+	else if (typid == TEXTOID || typid == JSONOID || typmod <= VARHDRSZ)
+		return PointerGetDatum(cstring_to_text_with_len(str, len));
+	else
+	{
+		char	   *txt;
+		int32		size;
+
+		len = VARHDRSZ + len;
+
+		if (typid == BPCHAROID)
+			size = typmod;
+		else
+			size = Min(len, typmod);
+
+		txt = palloc(size);
+		SET_VARSIZE(txt, size);
+
+		memcpy(VARDATA(txt), str, Min(size, len) - VARHDRSZ);
+
+		if (len < size)
+			memset(txt + len, ' ', size - len);
 
-	/* get coercion state reference and datum of the corresponding SQL type */
+		return PointerGetDatum(txt);
+	}
+}
+
+/* Coerce SQL/JSON item to text */
+static Datum
+ExecJsonCoercionToText(PGFunction outfunc, Datum value, Oid typid, int32 typmod)
+{
+	char	   *str = DatumGetCString(DirectFunctionCall1(outfunc, value));
+
+	return ExecJsonStringCoercion(str, strlen(str), typid, typmod);
+}
+
+/* Coerce datetime SQL/JSON item to the output typid */
+static Datum
+ExecJsonDatetimeCoercion(Datum val, Oid val_typid, Oid typid, int32 typmod,
+						 bool *isnull, bool *error)
+{
+	if (val_typid == typid)
+		return val;
+
+	/*
+	 * XXX coercion to text is done using output functions, and they
+	 * are mutable for non-time[tz] types due to using of DateStyle.
+	 * We can pass USE_ISO_DATES, which is used inside jsonpath, to
+	 * make these coercions and JSON_VALUE(RETURNING text) immutable.
+	 *
+	 * XXX Also timestamp[tz] output functions can throw "out of range"
+	 * error, but this error seem to be not possible.
+	 */
+	switch (val_typid)
+	{
+		case DATEOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(date_out, val, typid, typmod);
+			else if (typid == DATEOID)
+				return val;
+			else if (typid == TIMESTAMPOID)
+			{
+				int			overflow = 0;
+				Timestamp	ts =
+					date2timestamp_opt_overflow(DatumGetDateADT(val),
+												error ? &overflow : NULL);
+
+				if (overflow)
+				{
+					*error = true;
+					return (Datum) 0;
+				}
+
+				return TimestampGetDatum(ts);
+			}
+			else if (typid == TIMESTAMPTZOID)
+			{
+				int			overflow = 0;
+				TimestampTz	res =
+					date2timestamptz_opt_overflow(DatumGetDateADT(val),
+												  error ? &overflow : NULL);
+
+				if (overflow)
+				{
+					*error = true;
+					return (Datum) 0;
+				}
+
+				return TimestampTzGetDatum(res);
+			}
+			else
+				break; /* No cast */
+
+		case TIMEOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(time_out, val, typid, typmod);
+			else if (typid == TIMEOID)
+				return val;
+			else if (typid == TIMETZOID)
+				return DirectFunctionCall1(time_timetz, val);
+			else
+				break;	/* No cast */
+
+		case TIMETZOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(timetz_out, val, typid, typmod);
+			else if (typid == TIMETZOID)
+				return val;
+			else if (typid == TIMEOID)
+				return DirectFunctionCall1(timetz_time, val);
+			else
+				break;
+
+		case TIMESTAMPOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(timestamp_out, val, typid, typmod);
+			else if (typid == TIMESTAMPOID)
+				return val;
+			else if (typid == DATEOID)
+				return timestamp_date_opt_error(DatumGetTimestamp(val), error);
+			else if (typid == TIMEOID)
+			{
+				TimeADT		time =
+					timestamp_time_opt_error(DatumGetTimestamp(val),
+											 isnull, error);
+
+				if ((error && *error) || *isnull)
+					return (Datum) 0;
+				else
+					return TimeADTGetDatum(time);
+			}
+			else if (typid == TIMESTAMPTZOID)
+			{
+				int			overflow = 0;
+				TimestampTz tstz =
+					timestamp2timestamptz_opt_overflow(DatumGetTimestamp(val),
+													   error ? &overflow : NULL);
+
+				if (overflow)
+				{
+					*error = true;
+					return (Datum) 0;
+				}
+
+				return TimestampTzGetDatum(tstz);
+			}
+			else
+				break;	/* No cast */
+
+		case TIMESTAMPTZOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(timestamptz_out, val, typid, typmod);
+			else if (typid == TIMESTAMPTZOID)
+				return val;
+			else if (typid == DATEOID)
+				return timestamptz_date_opt_error(DatumGetTimestampTz(val), error);
+			else if (typid == TIMEOID)
+			{
+				TimeADT		time =
+					timestamptz_time_opt_error(DatumGetTimestampTz(val),
+											   isnull, error);
+
+				if ((error && *error) || *isnull)
+					return (Datum) 0;
+				else
+					return TimeADTGetDatum(time);
+			}
+			else if (typid == TIMETZOID)
+			{
+				TimeTzADT  *result =
+					timestamptz_timetz_opt_error(DatumGetTimestampTz(val), error);
+
+				if ((error && *error) || !result)
+				{
+					*isnull = true;
+					return (Datum) 0;
+				}
+
+				return TimeTzADTPGetDatum(result);
+			}
+			else if (typid == TIMESTAMPOID)
+			{
+				Timestamp	ts =
+					timestamptz2timestamp_opt_error(DatumGetTimestampTz(val),
+													error);
+
+				if (error && *error)
+					return (Datum) 0;
+
+				return TimestampGetDatum(ts);
+			}
+			else
+				break;	/* No cast */
+
+		default:
+			elog(ERROR, "unexpected jsonb datetime type oid %u", val_typid);
+			break;
+	}
+
+	if (!error)
+		ereport(ERROR,
+			(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+			 errmsg("SQL/JSON item cannot be cast to target type")));
+
+	*error = true;
+	*isnull = true;
+
+	return (Datum) 0;
+}
+
+/* Coerce boolean SQL/JSON item or JSON_EXISTS result to the output type */
+static bool
+ExecJsonBoolCoercion(bool val, Oid typid, int32 typmod, Datum *res)
+{
+	if (typid == BOOLOID)
+		*res = BoolGetDatum(val);
+	else if (typid == INT4OID)
+		/* We only have cast bool::int4 in the catalog. */
+		*res = Int32GetDatum(val ? 1 : 0);
+	else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+		/*
+		 * bool::text returns 'true' / 'false',
+		 * boolout() returns 't' / 'f'.
+		 */
+		*res = ExecJsonStringCoercion(val ? "true" : "false", val ? 4 : 5,
+									  typid, typmod);
+	else
+		return false;
+
+	return true;
+}
+
+static Datum
+JsonbPGetTextDatum(Jsonb *jb)
+{
+	char	   *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+	Datum		res = CStringGetTextDatum(str);
+
+	pfree(str);
+	return res;
+}
+
+/* Coerce SQL/JSON item to the output typid */
+static Datum
+ExecJsonValueCoercion(JsonbValue *item, Oid typid, int32 typmod,
+					  bool *isnull, bool *error)
+{
+	*isnull = false;
+
+	/* Special case for json and jsonb output types */
+	if (typid == JSONBOID)
+		return JsonbPGetDatum(JsonbValueToJsonb(item));
+
+	if (typid == JSONOID)
+		return JsonbPGetTextDatum(JsonbValueToJsonb(item));
+
+	/*
+	 * Coercion method and set of supported output types are determined
+	 * by the item type.
+	 */
 	switch (item->type)
 	{
 		case jbvNull:
-			coercion = &coercions->null;
-			res = (Datum) 0;
-			break;
+			Assert(0); /* must be handled by the caller */
+			*isnull = true;
+			return (Datum) 0;
 
 		case jbvString:
-			coercion = &coercions->string;
-			res = PointerGetDatum(cstring_to_text_with_len(item->val.string.val,
-														   item->val.string.len));
-			break;
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+			{
+				return ExecJsonStringCoercion(item->val.string.val,
+											  item->val.string.len,
+											  typid, typmod);
+			}
+			else if (typid == INT2OID || typid == INT4OID || typid == INT8OID)
+			{
+				char	   *str = pnstrdup(item->val.string.val,
+										   item->val.string.len);
+
+				if (error)
+				{
+					int64		val = pg_strtoint64_opt_error(str, error);
+
+					if (*error)
+						return (Datum) 0;
+
+					if (typid == INT2OID)
+					{
+						if (val <= PG_INT16_MAX || val >= PG_INT16_MIN)
+							return Int16GetDatum((int16) val);
+					}
+					else if (typid == INT4OID)
+					{
+						if (val <= PG_INT32_MAX || val >= PG_INT32_MIN)
+							return Int32GetDatum((int32) val);
+					}
+					else
+						return Int64GetDatum(val);
+
+					*error = true;
+					return (Datum) 0;
+				}
+				else if (typid == INT2OID)
+					return Int16GetDatum(pg_strtoint16(str));
+				else if (typid == INT4OID)
+					return Int32GetDatum(pg_strtoint32(str));
+				else
+					return Int64GetDatum(pg_strtoint64(str));
+			}
+			else if (typid == BOOLOID)
+			{
+				return BoolGetDatum(boolin_opt_error(item->val.string.val,
+													 item->val.string.len,
+													 error));
+			}
+			else
+			{
+				char	   *str = pnstrdup(item->val.string.val,
+										   item->val.string.len);
+
+				if (typid == FLOAT4OID)
+					return Float4GetDatum(float4in_opt_error(str, error));
+				else if (typid == FLOAT8OID)
+					return Float8GetDatum(
+						float8in_internal_opt_error(str, NULL,
+													"double precision",
+													str, error));
+				else if (typid == NUMERICOID)
+					return NumericGetDatum(numeric_in_opt_error(str, typmod, error));
+				else if (typid == DATEOID)
+					return DateADTGetDatum(date_in_opt_error(str, error));
+				else if (typid == TIMEOID)
+					return TimeADTGetDatum(time_in_opt_error(str, typmod, error));
+				else if (typid == TIMETZOID)
+					return TimeTzADTPGetDatum(timetz_in_opt_error(str, typmod, error));
+				else if (typid == TIMESTAMPOID)
+					return TimestampGetDatum(timestamp_in_opt_error(str, typmod, error));
+				else if (typid == TIMESTAMPTZOID)
+					return TimestampTzGetDatum(timestamptz_in_opt_error(str, typmod, error));
+				else
+					break;	/* No cast */
+			}
 
 		case jbvNumeric:
-			coercion = &coercions->numeric;
-			res = NumericGetDatum(item->val.numeric);
-			break;
+			{
+				Numeric		num = item->val.numeric;
+
+				if (typid == NUMERICOID)
+					return NumericGetDatum(num);
+				else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+					return ExecJsonCoercionToText(numeric_out, NumericGetDatum(num), typid, typmod);
+				else if (typid == INT2OID)
+					return Int16GetDatum(numeric_int2_opt_error(num, error));
+				else if (typid == INT4OID)
+					return Int32GetDatum(numeric_int4_opt_error(num, error));
+				else if (typid == INT8OID)
+					return Int64GetDatum(numeric_int8_opt_error(num, error));
+				else if (typid == FLOAT4OID || typid == FLOAT8OID)
+				{
+					/*
+					 * XXX numeric_float8() also uses I/O coercion, but
+					 * it has special handling of Inf and NaN.
+					 */
+					Datum		cstr = DirectFunctionCall1(numeric_out,
+														   NumericGetDatum(num));
+					char	   *str = DatumGetCString(cstr);
 
-		case jbvBool:
-			coercion = &coercions->boolean;
-			res = BoolGetDatum(item->val.boolean);
-			break;
+					if (typid == FLOAT4OID)
+						return Float4GetDatum(float4in_opt_error(str, error));
+					else
+						return Float8GetDatum(
+							float8in_internal_opt_error(str, NULL,
+														"double precision",
+														str, error));
+				}
+				else
+					break;	/* No cast */
+			}
 
-		case jbvDatetime:
-			res = item->val.datetime.value;
-			switch (item->val.datetime.typid)
+		case jbvBool:
 			{
-				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 %u",
-						 item->val.datetime.typid);
-					return (Datum) 0;
+				Datum		res;
+
+				if (ExecJsonBoolCoercion(item->val.boolean, typid, typmod, &res))
+					return res;
+
+				break; /* No cast */
 			}
-			break;
+
+		case jbvDatetime:
+			return ExecJsonDatetimeCoercion(item->val.datetime.value,
+											item->val.datetime.typid,
+											typid, typmod, isnull, error);
 
 		case jbvArray:
 		case jbvObject:
 		case jbvBinary:
-			coercion = &coercions->composite;
-			res = JsonbPGetDatum(JsonbValueToJsonb(item));
+			Assert(0);	/* non-scalars must be rejected by JsonPathValue() */
 			break;
 
 		default:
@@ -4929,99 +5194,100 @@ ExecPrepareJsonItemCoercion(JsonbValue *item,
 			return (Datum) 0;
 	}
 
-	*pcoercion = coercion;
+	if (!error)
+		ereport(ERROR,
+			(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+			 errmsg("SQL/JSON item cannot be cast to target type")));
 
-	return res;
-}
+	*error = true;
+	*isnull = true;
 
-typedef Datum (*JsonFunc) (ExprEvalStep *op, ExprContext *econtext,
-						   Datum item, bool *resnull, void *p, bool *error);
+	return (Datum) 0;
+}
 
+/*
+ * Evaluate a JSON error/empty behavior and coerce result to the output
+ * type.
+ */
 static Datum
-ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
-						 ExprContext *econtext,
-						 Datum res, bool *resnull,
-						 void *p, bool *error, bool subtrans)
+ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
+					 ExprState *default_estate,
+					 Oid ret_typid, int32 ret_typmod, bool *is_null)
 {
-	MemoryContext oldcontext;
-	ResourceOwner oldowner;
-
-	if (!subtrans)
-		/* No need to use subtransactions. */
-		return func(op, econtext, res, resnull, p, error);
+	*is_null = false;
 
-	/*
-	 * We should catch exceptions of category ERRCODE_DATA_EXCEPTION and
-	 * execute the corresponding ON ERROR behavior then.
-	 */
-	oldcontext = CurrentMemoryContext;
-	oldowner = CurrentResourceOwner;
+	switch (behavior->btype)
+	{
+		case JSON_BEHAVIOR_EMPTY_ARRAY:
+			if (ret_typid == JSONBOID)
+				return JsonbPGetDatum(JsonbMakeEmptyArray());
+			else
+			{
+				Assert(ret_typid == JSONOID || ret_typid == TEXTOID ||
+					   ret_typid == VARCHAROID || ret_typid == BPCHAROID ||
+					   ret_typid == BYTEAOID);
 
-	Assert(error);
+				return ExecJsonStringCoercion("[]", 2, ret_typid, ret_typmod);
+			}
 
-	BeginInternalSubTransaction(NULL);
-	/* Want to execute expressions inside function's memory context */
-	MemoryContextSwitchTo(oldcontext);
+		case JSON_BEHAVIOR_EMPTY_OBJECT:
+			if (ret_typid == JSONBOID)
+				return JsonbPGetDatum(JsonbMakeEmptyObject());
+			else
+			{
+				Assert(ret_typid == JSONOID || ret_typid == TEXTOID ||
+					   ret_typid == VARCHAROID || ret_typid == BPCHAROID ||
+					   ret_typid == BYTEAOID);
 
-	PG_TRY();
-	{
-		res = func(op, econtext, res, resnull, p, error);
+				return ExecJsonStringCoercion("{}", 2, ret_typid, ret_typmod);
+			}
 
-		/* Commit the inner transaction, return to outer xact context */
-		ReleaseCurrentSubTransaction();
-		MemoryContextSwitchTo(oldcontext);
-		CurrentResourceOwner = oldowner;
-	}
-	PG_CATCH();
-	{
-		ErrorData  *edata;
-		int			ecategory;
+		case JSON_BEHAVIOR_TRUE:
+		case JSON_BEHAVIOR_FALSE:
+			{
+				Datum		res;
+				bool		ok =
+					ExecJsonBoolCoercion(behavior->btype == JSON_BEHAVIOR_TRUE,
+										 ret_typid, ret_typmod, &res);
 
-		/* Save error info in oldcontext */
-		MemoryContextSwitchTo(oldcontext);
-		edata = CopyErrorData();
-		FlushErrorState();
+				Assert(ok); /* returning type must be checked in parser */
 
-		/* Abort the inner transaction */
-		RollbackAndReleaseCurrentSubTransaction();
-		MemoryContextSwitchTo(oldcontext);
-		CurrentResourceOwner = oldowner;
+				return res;
+			}
 
-		ecategory = ERRCODE_TO_CATEGORY(edata->sqlerrcode);
+		case JSON_BEHAVIOR_NULL:
+		case JSON_BEHAVIOR_UNKNOWN:
+		case JSON_BEHAVIOR_EMPTY:
+			*is_null = true;
+			return (Datum) 0;
 
-		if (ecategory != ERRCODE_DATA_EXCEPTION &&	/* jsonpath and other data
-													 * errors */
-			ecategory != ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION)	/* domain errors */
-			ReThrowError(edata);
+		case JSON_BEHAVIOR_DEFAULT:
+			/*
+			 * Execute DEFAULT expression.
+			 * Coercion is not needed here, because expression is
+			 * already coerced to the target type by the parser.
+			 */
+			return ExecEvalExpr(default_estate, econtext, is_null);
 
-		res = (Datum) 0;
-		*error = true;
+		default:
+			elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
+			return (Datum) 0;
 	}
-	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)
+ExecEvalJsonExprInternal(ExprState *state,
+						 JsonExprState *jsestate, ExprContext *econtext,
+						 JsonPath *path, Datum item, bool *resnull,
+						 bool *error)
 {
-	ExecEvalJsonExprContext *cxt = pcxt;
-	JsonPath   *path = cxt->path;
-	JsonExprState *jsestate = op->d.jsonexpr.jsestate;
 	JsonExpr   *jexpr = jsestate->jsexpr;
-	ExprState  *estate = NULL;
 	bool		empty = false;
 	Datum		res = (Datum) 0;
+	Oid			ret_typid = jexpr->returning->typid;
+	int32		ret_typmod = jexpr->returning->typmod;
+
+	*resnull = true;
 
 	switch (jexpr->op)
 	{
@@ -5029,70 +5295,96 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
 			res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error,
 								jsestate->args);
 			if (error && *error)
-			{
-				*resnull = true;
 				return (Datum) 0;
-			}
+
+			if (empty)
+				break;
+
 			*resnull = !DatumGetPointer(res);
-			break;
+
+			/* Override default coercion in OMIT QUOTES case */
+			if (ExecJsonQueryNeedsIOCoercion(jexpr, res, *resnull))
+			{
+				char	   *str = JsonbUnquote(DatumGetJsonbP(res));
+
+				if (ret_typid == JSONBOID)
+				{
+					res = jsonb_from_cstring(str, strlen(str), false, error);
+
+					if (error && *error)
+						return (Datum) 0;
+
+					return res;
+				}
+				else if (ret_typid == JSONOID)
+				{
+					text	   *json = cstring_to_text(str);
+					JsonLexContext *lex;
+					JsonParseErrorType result;
+
+					/* validate it */
+					lex = makeJsonLexContext(json, false);
+					result = pg_parse_json(lex, &nullSemAction);
+
+					if (result != JSON_SUCCESS)
+					{
+						if (error)
+						{
+							*error = true;
+							return (Datum) 0;
+						}
+
+						json_ereport_error(result, lex);
+					}
+
+					return PointerGetDatum(json);
+				}
+				else if (ret_typid == TEXTOID ||
+						 ret_typid == VARCHAROID ||
+						 ret_typid == BPCHAROID ||
+						 ret_typid == BYTEAOID)
+					return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod);
+			}
+			else if (ret_typid == JSONBOID)
+				return res;
+			else if (ret_typid == JSONOID || ret_typid == TEXTOID)
+				return JsonbPGetTextDatum(DatumGetJsonbP(res));
+			else if (ret_typid == VARCHAROID || ret_typid == BPCHAROID ||
+					 ret_typid == BYTEAOID)
+			{
+				Jsonb	   *jb = DatumGetJsonbP(res);
+				char	   *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+
+				return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod);
+			}
+
+			Assert(0);	/* unsupported output type */
+			*error = *resnull = true;
+			return (Datum) 0;
 
 		case JSON_VALUE_OP:
 			{
-				struct JsonCoercionState *jcstate;
 				JsonbValue *jbv = JsonPathValue(item, path, &empty, error,
 												jsestate->args);
 
 				if (error && *error)
 					return (Datum) 0;
 
-				if (!jbv)		/* NULL or empty */
+				if (empty)
 					break;
 
-				Assert(!empty);
-
-				*resnull = false;
+				if (!jbv)
+					return (Datum) 0;	/* NULL */
 
-				/* 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;
-				}
+				Assert(jbv->type != jbvNull);
 
-				/* Use coercion from SQL/JSON item type to the output type */
-				res = ExecPrepareJsonItemCoercion(jbv,
-												  jsestate->jsexpr->returning,
-												  &jsestate->coercions,
-												  &jcstate);
+				res = ExecJsonValueCoercion(jbv, ret_typid, ret_typmod,
+											resnull, error);
 
-				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,
-							(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
-							 errmsg("SQL/JSON item cannot be cast to target type")));
-				}
-				else if (!jcstate->estate)
-					return res; /* no coercion */
+				if (error && *error)
+					return (Datum) 0;
 
-				/* coerce using specific expression */
-				estate = jcstate->estate;
-				jsestate->coercion_expr->value = res;
-				jsestate->coercion_expr->isnull = *resnull;
-				break;
+				return res;
 			}
 
 		case JSON_EXISTS_OP:
@@ -5101,113 +5393,68 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
 													jsestate->args,
 													error);
 
-				*resnull = error && *error;
-				res = BoolGetDatum(exists);
-
-				if (!jsestate->result_expr)
-					return res;
+				if (!error || !*error)
+				{
+					/* Should succeed, output type is checked by parser */
+					(void) ExecJsonBoolCoercion(exists, ret_typid, ret_typmod, &res);
+					*resnull = false;
+				}
 
-				/* coerce using result expression */
-				estate = jsestate->result_expr;
-				jsestate->res_expr->value = res;
-				jsestate->res_expr->isnull = *resnull;
-				break;
+				return res;
 			}
 
-		case JSON_TABLE_OP:
-			*resnull = false;
-			return item;
-
 		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 */
+	Assert(empty);
+	Assert(jexpr->on_empty);	/* it is not JSON_EXISTS */
 
-		if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+	if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+	{
+		if (error)
 		{
-			if (error)
-			{
-				*error = true;
-				return (Datum) 0;
-			}
-
-			ereport(ERROR,
-					(errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					 errmsg("no SQL/JSON item")));
+			*error = true;
+			return (Datum) 0;
 		}
 
-		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 = jsestate->default_on_empty;
-		else
-			/* Execute ON EMPTY behavior */
-			res = ExecEvalJsonBehavior(econtext, jexpr->on_empty,
-									   jsestate->default_on_empty,
-									   resnull);
+		ereport(ERROR,
+				(errcode(ERRCODE_NO_SQL_JSON_ITEM),
+				 errmsg("no SQL/JSON item")));
 	}
 
-	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;
+	/* Execute ON EMPTY behavior */
+	return ExecEvalJsonBehavior(econtext, jexpr->on_empty,
+								jsestate->default_on_empty,
+								ret_typid, ret_typmod, resnull);
 }
 
 /* ----------------------------------------------------------------
- *		ExecEvalJson
+ *		ExecEvalJsonExpr
  * ----------------------------------------------------------------
  */
 void
-ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
 {
-	ExecEvalJsonExprContext cxt;
 	JsonExprState *jsestate = op->d.jsonexpr.jsestate;
 	JsonExpr   *jexpr = jsestate->jsexpr;
 	Datum		item;
-	Datum		res = (Datum) 0;
+	Datum		res;
 	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 (jsestate->formatted_expr->isnull || jsestate->pathspec->isnull)
+	if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull)
 	{
-		/* execute domain checks for NULLs */
-		(void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull,
-										NULL, NULL);
-
-		Assert(*op->resnull);
+		*op->resnull = true;
+		*op->resvalue = (Datum) 0;
 		return;
 	}
 
-	item = jsestate->formatted_expr->value;
-	path = DatumGetJsonPathP(jsestate->pathspec->value);
+	item = jsestate->formatted_expr.value;
+	path = DatumGetJsonPathP(jsestate->pathspec.value);
 
 	/* reset JSON path variable contexts */
 	foreach(lc, jsestate->args)
@@ -5218,29 +5465,20 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
 		var->evaluated = false;
 	}
 
-	needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &jsestate->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);
+	res = ExecEvalJsonExprInternal(state, jsestate, econtext,
+								   path, item, op->resnull,
+								   throwErrors ? NULL : &error);
 
 	if (error)
 	{
+		Assert(!throwErrors);
+
 		/* Execute ON ERROR behavior */
 		res = ExecEvalJsonBehavior(econtext, jexpr->on_error,
 								   jsestate->default_on_error,
+								   jexpr->returning->typid,
+								   jexpr->returning->typmod,
 								   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;
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index bd3965143da..fd72630f5e6 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2408,7 +2408,7 @@ llvm_compile_expr(ExprState *state)
 				break;
 
 			case EEOP_JSONEXPR:
-				build_EvalXFunc(b, mod, "ExecEvalJson",
+				build_EvalXFunc(b, mod, "ExecEvalJsonExpr",
 								v_state, op, v_econtext);
 				LLVMBuildBr(b, opblocks[opno + 1]);
 				break;
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index 373471ad27f..37fe64654b6 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -135,7 +135,7 @@ void	   *referenced_functions[] =
 	ExecEvalXmlExpr,
 	ExecEvalJsonConstructor,
 	ExecEvalJsonIsPredicate,
-	ExecEvalJson,
+	ExecEvalJsonExpr,
 	MakeExpandedObjectReadOnlyInternal,
 	slot_getmissingattrs,
 	slot_getsomeattrs_int,
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c334daae392..e0240beeeab 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -266,9 +266,6 @@ exprType(const Node *expr)
 		case T_JsonExpr:
 			type = ((const JsonExpr *) expr)->returning->typid;
 			break;
-		case T_JsonCoercion:
-			type = exprType(((const JsonCoercion *) expr)->expr);
-			break;
 		default:
 			elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
 			type = InvalidOid;	/* keep compiler quiet */
@@ -507,8 +504,6 @@ exprTypmod(const Node *expr)
 			return ((const JsonConstructorExpr *) expr)->returning->typmod;
 		case T_JsonExpr:
 			return ((JsonExpr *) expr)->returning->typmod;
-		case T_JsonCoercion:
-			return exprTypmod(((const JsonCoercion *) expr)->expr);
 		default:
 			break;
 	}
@@ -1010,14 +1005,12 @@ exprCollation(const Node *expr)
 		case T_JsonExpr:
 			{
 				JsonExpr   *jexpr = (JsonExpr *) expr;
-				JsonCoercion *coercion = jexpr->result_coercion;
 
-				if (!coercion)
-					coll = InvalidOid;
-				else if (coercion->expr)
-					coll = exprCollation(coercion->expr);
-				else if (coercion->via_io || coercion->via_populate)
-					coll = coercion->collation;
+				if (jexpr->returning->typid == TEXTOID ||
+					jexpr->returning->typid == CHAROID ||
+					jexpr->returning->typid == VARCHAROID ||
+					jexpr->returning->typid == BPCHAROID)
+					coll = jexpr->collation;
 				else
 					coll = InvalidOid;
 			}
@@ -1255,14 +1248,12 @@ exprSetCollation(Node *expr, Oid collation)
 		case T_JsonExpr:
 			{
 				JsonExpr   *jexpr = (JsonExpr *) expr;
-				JsonCoercion *coercion = jexpr->result_coercion;
 
-				if (!coercion)
-					Assert(!OidIsValid(collation));
-				else if (coercion->expr)
-					exprSetCollation(coercion->expr, collation);
-				else if (coercion->via_io || coercion->via_populate)
-					coercion->collation = collation;
+				if (jexpr->returning->typid == TEXTOID ||
+					jexpr->returning->typid == CHAROID ||
+					jexpr->returning->typid == VARCHAROID ||
+					jexpr->returning->typid == BPCHAROID)
+					jexpr->collation = collation;
 				else
 					Assert(!OidIsValid(collation));
 			}
@@ -2507,8 +2498,6 @@ expression_tree_walker(Node *node,
 
 				if (walker(jexpr->formatted_expr, context))
 					return true;
-				if (walker(jexpr->result_coercion, context))
-					return true;
 				if (walker(jexpr->passing_values, context))
 					return true;
 				/* we assume walker doesn't care about passing_names */
@@ -2517,36 +2506,6 @@ expression_tree_walker(Node *node,
 					return true;
 				if (walker(jexpr->on_error->default_expr, context))
 					return true;
-				if (walker(jexpr->coercions, context))
-					return true;
-			}
-			break;
-		case T_JsonCoercion:
-			return walker(((JsonCoercion *) node)->expr, context);
-		case T_JsonItemCoercions:
-			{
-				JsonItemCoercions *coercions = (JsonItemCoercions *) node;
-
-				if (walker(coercions->null, context))
-					return true;
-				if (walker(coercions->string, context))
-					return true;
-				if (walker(coercions->numeric, context))
-					return true;
-				if (walker(coercions->boolean, context))
-					return true;
-				if (walker(coercions->date, context))
-					return true;
-				if (walker(coercions->time, context))
-					return true;
-				if (walker(coercions->timetz, context))
-					return true;
-				if (walker(coercions->timestamp, context))
-					return true;
-				if (walker(coercions->timestamptz, context))
-					return true;
-				if (walker(coercions->composite, context))
-					return true;
 			}
 			break;
 		default:
@@ -3576,7 +3535,6 @@ expression_tree_mutator(Node *node,
 				FLATCOPY(newnode, jexpr, JsonExpr);
 				MUTATE(newnode->path_spec, jexpr->path_spec, Node *);
 				MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *);
-				MUTATE(newnode->result_coercion, jexpr->result_coercion, JsonCoercion *);
 				MUTATE(newnode->passing_values, jexpr->passing_values, List *);
 				/* assume mutator does not care about passing_names */
 				if (newnode->on_empty)
@@ -3587,35 +3545,6 @@ expression_tree_mutator(Node *node,
 				return (Node *) newnode;
 			}
 			break;
-		case T_JsonCoercion:
-			{
-				JsonCoercion *coercion = (JsonCoercion *) node;
-				JsonCoercion *newnode;
-
-				FLATCOPY(newnode, coercion, JsonCoercion);
-				MUTATE(newnode->expr, coercion->expr, Node *);
-				return (Node *) newnode;
-			}
-			break;
-		case T_JsonItemCoercions:
-			{
-				JsonItemCoercions *coercions = (JsonItemCoercions *) node;
-				JsonItemCoercions *newnode;
-
-				FLATCOPY(newnode, coercions, JsonItemCoercions);
-				MUTATE(newnode->null, coercions->null, JsonCoercion *);
-				MUTATE(newnode->string, coercions->string, JsonCoercion *);
-				MUTATE(newnode->numeric, coercions->numeric, JsonCoercion *);
-				MUTATE(newnode->boolean, coercions->boolean, JsonCoercion *);
-				MUTATE(newnode->date, coercions->date, JsonCoercion *);
-				MUTATE(newnode->time, coercions->time, JsonCoercion *);
-				MUTATE(newnode->timetz, coercions->timetz, JsonCoercion *);
-				MUTATE(newnode->timestamp, coercions->timestamp, JsonCoercion *);
-				MUTATE(newnode->timestamptz, coercions->timestamptz, JsonCoercion *);
-				MUTATE(newnode->composite, coercions->composite, JsonCoercion *);
-				return (Node *) newnode;
-			}
-			break;
 		default:
 			elog(ERROR, "unrecognized node type: %d",
 				 (int) nodeTag(node));
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 533df86ff77..6eb923892f4 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -411,6 +411,26 @@ contain_mutable_functions_walker(Node *node, void *context)
 	{
 		JsonExpr   *jexpr = castNode(JsonExpr, node);
 		Const	   *cnst;
+		bool		returns_datetime;
+
+		/*
+		 * Input fuctions for datetime types are stable.  They can be
+		 * called in JSON_VALUE(), when the resulting SQL/JSON is a
+		 * string.
+		 */
+		if (jexpr->returning->typid == DATEOID ||
+			jexpr->returning->typid == TIMEOID ||
+			jexpr->returning->typid == TIMETZOID ||
+			jexpr->returning->typid == TIMESTAMPOID ||
+			jexpr->returning->typid == TIMESTAMPTZOID)
+			return true;
+
+		/*
+		 * pg_convert_to(), which is used for implementation of
+		 * JSON_QUERY(RETURNING bytea FORMAT JSON), is stable.
+		 */
+		if (jexpr->returning->typid == BYTEAOID)
+			return true;
 
 		if (!IsA(jexpr->path_spec, Const))
 			return true;
@@ -421,8 +441,32 @@ contain_mutable_functions_walker(Node *node, void *context)
 		if (cnst->constisnull)
 			return false;
 
-		return jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
-							jexpr->passing_names, jexpr->passing_values);
+		if (jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
+						 jexpr->passing_names, jexpr->passing_values,
+						 &returns_datetime))
+			return true;
+
+		if (returns_datetime && jexpr->op == JSON_VALUE_OP)
+		{
+			/*
+			 * Some datetime types have mutable output functions,
+			 * so if returning string types whole expression is mutable.
+			 * TODO check individual datetime type if it is known.
+			 *
+			 * Other non-JSON output types have no conversion from
+			 * datetime types.
+			 *
+			 * Datetime returning types were checked above, no need to
+			 * check them here.  But conversion between them also can
+			 * be mutable.
+			 */
+			if (jexpr->returning->typid == TEXTOID ||
+				jexpr->returning->typid == VARCHAROID ||
+				jexpr->returning->typid == BPCHAROID)
+				return true;
+		}
+
+		return false;
 	}
 
 	if (IsA(node, SQLValueFunction))
@@ -896,18 +940,6 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
 								 context, 0);
 	}
 
-	/* JsonExpr is parallel-unsafe if subtransactions can be used. */
-	else if (IsA(node, JsonExpr))
-	{
-		JsonExpr   *jsexpr = (JsonExpr *) node;
-
-		if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL))
-		{
-			context->max_hazard = PROPARALLEL_UNSAFE;
-			return true;
-		}
-	}
-
 	/* Recurse to check arguments */
 	return expression_tree_walker(node,
 								  max_parallel_hazard_walker,
@@ -5323,3 +5355,16 @@ pull_paramids_walker(Node *node, Bitmapset **context)
 	return expression_tree_walker(node, pull_paramids_walker,
 								  (void *) context);
 }
+
+bool
+expr_can_throw_errors(Node *expr)
+{
+	if (!expr)
+		return false;
+
+	if (IsA(expr, Const))
+		return false;
+
+	/* TODO consider more cases */
+	return true;
+}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index fabb5f72076..c02530a3671 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -22,6 +22,7 @@
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
 #include "optimizer/optimizer.h"
 #include "parser/analyze.h"
 #include "parser/parse_agg.h"
@@ -4108,7 +4109,6 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func)
 	/* format is determined by context item type */
 	format = exprType(jsexpr->formatted_expr) == JSONBOID ? JS_FORMAT_JSONB : JS_FORMAT_JSON;
 
-	jsexpr->result_coercion = NULL;
 	jsexpr->omit_quotes = false;
 
 	jsexpr->format = func->common->expr->format;
@@ -4169,40 +4169,6 @@ assignDefaultJsonReturningType(Node *context_item, JsonFormat *context_format,
 	ret->typmod = -1;
 }
 
-/*
- * Try to coerce expression to the output type or
- * use json_populate_type() for composite, array and domain types or
- * use coercion via I/O.
- */
-static JsonCoercion *
-coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning)
-{
-	char		typtype;
-	JsonCoercion *coercion = makeNode(JsonCoercion);
-
-	coercion->expr = coerceJsonFuncExpr(pstate, expr, returning, false);
-
-	if (coercion->expr)
-	{
-		if (coercion->expr == expr)
-			coercion->expr = NULL;
-
-		return coercion;
-	}
-
-	typtype = get_typtype(returning->typid);
-
-	if (returning->typid == RECORDOID ||
-		typtype == TYPTYPE_COMPOSITE ||
-		typtype == TYPTYPE_DOMAIN ||
-		type_is_array(returning->typid))
-		coercion->via_populate = true;
-	else
-		coercion->via_io = true;
-
-	return coercion;
-}
-
 /*
  * Transform a JSON output clause of JSON_VALUE and JSON_QUERY.
  */
@@ -4210,8 +4176,6 @@ static void
 transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
 							JsonExpr *jsexpr)
 {
-	Node	   *expr = jsexpr->formatted_expr;
-
 	jsexpr->returning = transformJsonOutput(pstate, func->output, false);
 
 	/* JSON_VALUE returns text by default */
@@ -4225,14 +4189,41 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
 	{
 		JsonReturning ret;
 
-		if (func->op == JSON_VALUE_OP &&
-			jsexpr->returning->typid != JSONOID &&
-			jsexpr->returning->typid != JSONBOID)
+		if (func->op == JSON_VALUE_OP)
 		{
-			/* Forced coercion via I/O for JSON_VALUE for non-JSON types */
-			jsexpr->result_coercion = makeNode(JsonCoercion);
-			jsexpr->result_coercion->expr = NULL;
-			jsexpr->result_coercion->via_io = true;
+			/*
+			 * Only a limited list of output types is supported in
+			 * JSON_VALUE() now.
+			 */
+			switch (jsexpr->returning->typid)
+			{
+				case JSONBOID:
+				case JSONOID:
+				case TEXTOID:
+				case VARCHAROID:
+				case BPCHAROID:
+				case BOOLOID:
+				case NUMERICOID:
+				case INT2OID:
+				case INT4OID:
+				case INT8OID:
+				case FLOAT4OID:
+				case FLOAT8OID:
+				case DATEOID:
+				case TIMEOID:
+				case TIMETZOID:
+				case TIMESTAMPOID:
+				case TIMESTAMPTZOID:
+					break; /* Ok */
+
+				default:
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("returning type %s is not supported in JSON_VALUE()",
+									format_type_be(jsexpr->returning->typid)),
+							 parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+			}
+
 			return;
 		}
 
@@ -4241,13 +4232,23 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
 		if (ret.typid != jsexpr->returning->typid ||
 			ret.typmod != jsexpr->returning->typmod)
 		{
-			Node	   *placeholder = makeCaseTestExpr(expr);
-
-			Assert(((CaseTestExpr *) placeholder)->typeId == ret.typid);
-			Assert(((CaseTestExpr *) placeholder)->typeMod == ret.typmod);
+			switch (jsexpr->returning->typid)
+			{
+				case JSONBOID:
+				case JSONOID:
+				case TEXTOID:
+				case VARCHAROID:
+				case BPCHAROID:
+				case BYTEAOID:
+					break;
 
-			jsexpr->result_coercion = coerceJsonExpr(pstate, placeholder,
-													 jsexpr->returning);
+				default:
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("returning type %s is not supported in JSON_QUERY()",
+									format_type_be(jsexpr->returning->typid)),
+							 parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+			}
 		}
 	}
 	else
@@ -4259,8 +4260,10 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
  * Coerce an expression in JSON DEFAULT behavior to the target output type.
  */
 static Node *
-coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
+coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr,
+					  bool is_on_empty, Node *defexpr)
 {
+	Node	   *orig_defexpr = defexpr;
 	int			location;
 	Oid			exprtype;
 
@@ -4290,63 +4293,31 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
 						format_type_be(jsexpr->returning->typid)),
 				 parser_errposition(pstate, location)));
 
-	return defexpr;
-}
+	/* Don't allow unsafe DEFAUL ON EMPTY expressions, unless ERROR ON ERROR */
+	if (!is_on_empty ||
+		jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+		return defexpr;
 
-/*
- * Initialize SQL/JSON item coercion from the SQL type "typid" to the target
- * "returning" type.
- */
-static JsonCoercion *
-initJsonItemCoercion(ParseState *pstate, Oid typid,
-					 const JsonReturning *returning)
-{
-	Node	   *expr;
+	if (!expr_can_throw_errors(defexpr))
+		return defexpr;
 
-	if (typid == UNKNOWNOID)
+	/* Try to simplify expression if there is non-empty coercion */
+	if (defexpr != orig_defexpr &&
+		!expr_can_throw_errors(orig_defexpr))
 	{
-		expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid);
-	}
-	else
-	{
-		CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+		defexpr = eval_const_expressions(NULL, defexpr);
 
-		placeholder->typeId = typid;
-		placeholder->typeMod = -1;
-		placeholder->collation = InvalidOid;
-
-		expr = (Node *) placeholder;
+		if (!expr_can_throw_errors(defexpr))
+			return defexpr;
 	}
 
-	return coerceJsonExpr(pstate, expr, returning);
-}
+	ereport(ERROR,
+			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			 errmsg("unsafe DEFAULT ON EMPTY expressions are not supported"),
+			 errhint("Use ERROR ON ERROR clause or try to simplify expression into constant-like form."),
+			 parser_errposition(pstate, location)));
 
-static void
-initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions,
-					  const JsonReturning *returning, Oid contextItemTypeId)
-{
-	struct
-	{
-		JsonCoercion **coercion;
-		Oid			typid;
-	}		   *p,
-				coercionTypids[] =
-	{
-		{&coercions->null, UNKNOWNOID},
-		{&coercions->string, TEXTOID},
-		{&coercions->numeric, NUMERICOID},
-		{&coercions->boolean, BOOLOID},
-		{&coercions->date, DATEOID},
-		{&coercions->time, TIMEOID},
-		{&coercions->timetz, TIMETZOID},
-		{&coercions->timestamp, TIMESTAMPOID},
-		{&coercions->timestamptz, TIMESTAMPTZOID},
-		{&coercions->composite, contextItemTypeId},
-		{NULL, InvalidOid}
-	};
-
-	for (p = coercionTypids; p->coercion; p++)
-		*p->coercion = initJsonItemCoercion(pstate, p->typid, returning);
+	return NULL;
 }
 
 /*
@@ -4370,17 +4341,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
 
 			jsexpr->on_empty->default_expr =
-				coerceDefaultJsonExpr(pstate, jsexpr,
+				coerceDefaultJsonExpr(pstate, jsexpr, true,
 									  jsexpr->on_empty->default_expr);
 
 			jsexpr->on_error->default_expr =
-				coerceDefaultJsonExpr(pstate, jsexpr,
+				coerceDefaultJsonExpr(pstate, jsexpr, false,
 									  jsexpr->on_error->default_expr);
 
-			jsexpr->coercions = makeNode(JsonItemCoercions);
-			initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning,
-								  exprType(contextItemExpr));
-
 			break;
 
 		case JSON_QUERY_OP:
@@ -4389,11 +4356,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			transformJsonFuncExprOutput(pstate, func, jsexpr);
 
 			jsexpr->on_empty->default_expr =
-				coerceDefaultJsonExpr(pstate, jsexpr,
+				coerceDefaultJsonExpr(pstate, jsexpr, true,
 									  jsexpr->on_empty->default_expr);
 
 			jsexpr->on_error->default_expr =
-				coerceDefaultJsonExpr(pstate, jsexpr,
+				coerceDefaultJsonExpr(pstate, jsexpr, false,
 									  jsexpr->on_error->default_expr);
 
 			jsexpr->wrapper = func->wrapper;
@@ -4409,6 +4376,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT;
 			jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
 
+			/* Coerce intermediate boolean result to the output type if needed */
 			if (!OidIsValid(jsexpr->returning->typid))
 			{
 				jsexpr->returning->typid = BOOLOID;
@@ -4416,32 +4384,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			}
 			else if (jsexpr->returning->typid != BOOLOID)
 			{
-				CaseTestExpr *placeholder = makeNode(CaseTestExpr);
-				int			location = exprLocation((Node *) jsexpr);
-
-				placeholder->typeId = BOOLOID;
-				placeholder->typeMod = -1;
-				placeholder->collation = InvalidOid;
-
-				jsexpr->result_coercion = makeNode(JsonCoercion);
-				jsexpr->result_coercion->expr =
-					coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID,
-										  jsexpr->returning->typid,
-										  jsexpr->returning->typmod,
-										  COERCION_EXPLICIT,
-										  COERCE_IMPLICIT_CAST,
-										  location);
-
-				if (!jsexpr->result_coercion->expr)
-					ereport(ERROR,
-							(errcode(ERRCODE_CANNOT_COERCE),
-							 errmsg("cannot cast type %s to %s",
-									format_type_be(BOOLOID),
-									format_type_be(jsexpr->returning->typid)),
-							 parser_coercion_errposition(pstate, location, (Node *) jsexpr)));
+				switch (jsexpr->returning->typid)
+				{
+					case INT4OID:
+					case TEXTOID:
+					case VARCHAROID:
+					case BPCHAROID:
+						break;
 
-				if (jsexpr->result_coercion->expr == (Node *) placeholder)
-					jsexpr->result_coercion->expr = NULL;
+					default:
+						ereport(ERROR,
+								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								 errmsg("returning type %s is not supported in JSON_EXISTS()",
+										format_type_be(jsexpr->returning->typid)),
+								 parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+				}
 			}
 			break;
 
@@ -4457,7 +4414,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 						 errmsg("JSON_TABLE() is not yet implemented for the json type"),
 						 errhint("Try casting the argument to jsonb"),
 						 parser_errposition(pstate, func->location)));
-
 			break;
 	}
 
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index da9df4ae766..9c9f9ad703f 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1318,9 +1318,11 @@ jspIsMutableWalker(JsonPathItem *jpi, JsonPathMutableContext *cxt)
  * Check whether jsonpath expression is immutable or not.
  */
 bool
-jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+jspIsMutable(JsonPath *path, List *varnames, List *varexprs,
+			 bool *returns_datetime)
 {
 	JsonPathMutableContext cxt;
+	JsonPathDatatypeStatus status;
 	JsonPathItem jpi;
 
 	cxt.varnames = varnames;
@@ -1330,7 +1332,9 @@ jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
 	cxt.mutable = false;
 
 	jspInit(&jpi, path);
-	jspIsMutableWalker(&jpi, &cxt);
+	status = jspIsMutableWalker(&jpi, &cxt);
+
+	*returns_datetime = status != jpdsNonDateTime;
 
 	return cxt.mutable;
 }
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index c8ef917ffe0..adabf57c97b 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -762,43 +762,14 @@ typedef struct JsonExprState
 {
 	JsonExpr   *jsexpr;			/* original expression node */
 
-	struct
-	{
-		FmgrInfo	func;		/* typinput function for output type */
-		Oid			typioparam;
-	}			input;			/* I/O info for output type */
-
 	NullableDatum
-			   *formatted_expr, /* formatted context item value */
-			   *res_expr,		/* result item */
-			   *coercion_expr,	/* input for JSON item coercion */
-			   *pathspec;		/* path specification value */
+				formatted_expr,	/* formatted context item value */
+				coercion_expr,	/* input for JSON item coercion */
+				pathspec;		/* path specification value */
 
-	ExprState  *result_expr;	/* coerced to output type */
 	ExprState  *default_on_empty;	/* ON EMPTY DEFAULT expression */
 	ExprState  *default_on_error;	/* ON ERROR DEFAULT expression */
 	List	   *args;			/* passing arguments */
-
-	void	   *cache;			/* cache for json_populate_type() */
-
-	struct JsonCoercionsState
-	{
-		struct JsonCoercionState
-		{
-			JsonCoercion *coercion; /* coercion expression */
-			ExprState  *estate; /* coercion expression state */
-		}			null,
-					string,
-		numeric    ,
-					boolean,
-					date,
-					time,
-					timetz,
-					timestamp,
-					timestamptz,
-					composite;
-	}			coercions;		/* states for coercion from SQL/JSON item
-								 * types directly to the output type */
 } JsonExprState;
 
 /* functions in execExpr.c */
@@ -860,18 +831,8 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
 						   ExprContext *econtext, TupleTableSlot *slot);
 extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 									ExprContext *econtext);
-extern void ExecEvalJson(ExprState *state, ExprEvalStep *op,
-						 ExprContext *econtext);
-extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item,
-										 JsonReturning *returning,
-										 struct JsonCoercionsState *coercions,
-										 struct JsonCoercionState **pjcstate);
-extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
-											struct JsonCoercionsState *);
-extern Datum ExecEvalExprPassingCaseValue(ExprState *estate,
-										  ExprContext *econtext, bool *isnull,
-										  Datum caseval_datum,
-										  bool caseval_isnull);
+extern void ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op,
+							 ExprContext *econtext);
 
 extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
 							 ExprContext *aggcontext);
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 3aa96bb6855..14fbb32ce9c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1551,39 +1551,6 @@ typedef struct JsonBehavior
 	Node	   *default_expr;	/* default expression, if any */
 } JsonBehavior;
 
-/*
- * JsonCoercion -
- *		coercion from SQL/JSON item types to SQL types
- */
-typedef struct JsonCoercion
-{
-	NodeTag		type;
-	Node	   *expr;			/* resulting expression coerced to target type */
-	bool		via_populate;	/* coerce result using json_populate_type()? */
-	bool		via_io;			/* coerce result using type input function? */
-	Oid			collation;		/* collation for coercion via I/O or populate */
-} JsonCoercion;
-
-/*
- * JsonItemCoercions -
- *		expressions for coercion from SQL/JSON item types directly to the
- *		output SQL type
- */
-typedef struct JsonItemCoercions
-{
-	NodeTag		type;
-	JsonCoercion *null;
-	JsonCoercion *string;
-	JsonCoercion *numeric;
-	JsonCoercion *boolean;
-	JsonCoercion *date;
-	JsonCoercion *time;
-	JsonCoercion *timetz;
-	JsonCoercion *timestamp;
-	JsonCoercion *timestamptz;
-	JsonCoercion *composite;	/* arrays and objects */
-} JsonItemCoercions;
-
 /*
  * JsonExpr -
  *		transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS()
@@ -1593,7 +1560,6 @@ typedef struct JsonExpr
 	Expr		xpr;
 	JsonExprOp	op;				/* json function ID */
 	Node	   *formatted_expr; /* formatted context item expression */
-	JsonCoercion *result_coercion;	/* resulting coercion to RETURNING type */
 	JsonFormat *format;			/* context item format (JSON/JSONB) */
 	Node	   *path_spec;		/* JSON path specification expression */
 	List	   *passing_names;	/* PASSING argument names */
@@ -1601,8 +1567,8 @@ typedef struct JsonExpr
 	JsonReturning *returning;	/* RETURNING clause type/format info */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
-	JsonItemCoercions *coercions;	/* coercions for JSON_VALUE */
 	JsonWrapper wrapper;		/* WRAPPER for JSON_QUERY */
+	Oid			collation;		/* OID of collation, or InvalidOid if none */
 	bool		omit_quotes;	/* KEEP/OMIT QUOTES for JSON_QUERY */
 	int			location;		/* token location, or -1 if unknown */
 } JsonExpr;
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 6c5203dc448..4d61b88c161 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -55,4 +55,6 @@ extern Query *inline_set_returning_function(PlannerInfo *root,
 
 extern Bitmapset *pull_paramids(Expr *expr);
 
+extern bool expr_can_throw_errors(Node *expr);
+
 #endif							/* CLAUSES_H */
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 8e79b8dc9f0..e9134b7eab4 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -177,7 +177,8 @@ extern bool jspGetBool(JsonPathItem *v);
 extern char *jspGetString(JsonPathItem *v, int32 *len);
 extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
 								 JsonPathItem *to, int i);
-extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs);
+extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs,
+						 bool *returns_datetime);
 
 extern const char *jspOperationName(JsonPathItemType type);
 
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index ef496110af3..0e07bbc858f 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -184,11 +184,11 @@ SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
 (1 row)
 
 SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
-ERROR:  cannot cast type boolean to jsonb
+ERROR:  returning type jsonb is not supported in JSON_EXISTS()
 LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
                ^
 SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
-ERROR:  cannot cast type boolean to real
+ERROR:  returning type real is not supported in JSON_EXISTS()
 LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
                ^
 -- JSON_VALUE
@@ -242,7 +242,9 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
 
 /* jsonb bytea ??? */
 SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
-ERROR:  SQL/JSON item cannot be cast to target type
+ERROR:  returning type bytea is not supported in JSON_VALUE()
+LINE 2: SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ...
+               ^
 SELECT JSON_VALUE(jsonb '1.23', '$');
  json_value 
 ------------
@@ -349,14 +351,129 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
  03-01-2017
 (1 row)
 
--- Test NULL checks execution in domain types
+SELECT JSON_VALUE(jsonb  '1.23',  '$' RETURNING float4);
+ json_value 
+------------
+       1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4);
+ json_value 
+------------
+       1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb  '"nan"', '$' RETURNING float4);
+ json_value 
+------------
+        NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4);
+ json_value 
+------------
+  -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR);
+ json_value 
+------------
+         -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR);
+ERROR:  invalid input syntax for type real: "err"
+SELECT JSON_VALUE(jsonb  '1.23',  '$' RETURNING float8);
+ json_value 
+------------
+       1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8);
+ json_value 
+------------
+       1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb  '"nan"', '$' RETURNING float8);
+ json_value 
+------------
+        NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8);
+ json_value 
+------------
+  -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR);
+ json_value 
+------------
+         -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR);
+ERROR:  invalid input syntax for type double precision: "err"
+SELECT JSON_VALUE(jsonb  '1.23',  '$' RETURNING numeric);
+ json_value 
+------------
+       1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+ json_value 
+------------
+       1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb  '"nan"', '$' RETURNING numeric);
+ json_value 
+------------
+        NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric);
+ json_value 
+------------
+  -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR);
+ json_value 
+------------
+         -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR);
+ERROR:  invalid input syntax for type numeric: "err"
+-- Test for domain types
 CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0');
+CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0');
+-- Test casting to json[b] domains (items casted as is, strings are not unquoted)
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_...
+               ^
+-- Test NULL checks execution in domain types
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
+-- Test returning of non-scalar items
 SELECT JSON_VALUE(jsonb '[]', '$');
  json_value 
 ------------
@@ -478,11 +595,9 @@ SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
 (1 row)
 
 SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
- json_value 
-------------
- (1,2)
-(1 row)
-
+ERROR:  returning type point is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )...
+               ^
 -- Test timestamptz passing and output
 SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
           json_value          
@@ -514,6 +629,36 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
  "2018-02-21T02:34:56+00:00"
 (1 row)
 
+-- test errors in DEFAULT ON EMPTY expressions
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
+                  DEFAULT 1 / x ON EMPTY
+                  DEFAULT 2 ON ERROR)
+FROM (VALUES (1::int), (0)) x(x);
+ERROR:  unsafe DEFAULT ON EMPTY expressions are not supported
+LINE 2:                   DEFAULT 1 / x ON EMPTY
+                                  ^
+HINT:  Use ERROR ON ERROR clause or try to simplify expression into constant-like form.
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
+ json_value 
+------------
+         -1
+(1 row)
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
+ json_value 
+------------
+         -1
+(1 row)
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
+ERROR:  invalid input syntax for type integer: "err"
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON E...
+                                                             ^
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
+ERROR:  unsafe DEFAULT ON EMPTY expressions are not supported
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ...
+                                                             ^
+HINT:  Use ERROR ON ERROR clause or try to simplify expression into constant-like form.
 -- JSON_QUERY
 SELECT
 	JSON_QUERY(js, '$'),
@@ -852,49 +997,35 @@ FROM
 CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
 CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
 SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}},  {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
-                     json_query                      
------------------------------------------------------
- (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",)
-(1 row)
-
+ERROR:  returning type sqljsonb_rec is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "...
+               ^
 SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa":  [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
-         unnest         
-------------------------
- {"a": 1, "b": ["foo"]}
- {"a": 2, "c": {}}
- 123
-(3 rows)
-
+ERROR:  returning type sqljsonb_rec is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa":  [{"a": 1, "...
+                              ^
 SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
- a |      t      | js |     jb     | jsa 
----+-------------+----+------------+-----
- 1 | ["foo", []] |    |            | 
- 2 |             |    | [{}, true] | 
-(2 rows)
-
+ERROR:  returning type sqljsonb_reca is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "...
+                              ^
 -- Extension: array types returning
 SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
-  json_query  
---------------
- {1,2,NULL,3}
-(1 row)
-
+ERROR:  returning type integer[] is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING i...
+               ^
 SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
- a |      t      | js |     jb     | jsa 
----+-------------+----+------------+-----
- 1 | ["foo", []] |    |            | 
- 2 |             |    | [{}, true] | 
-(2 rows)
-
+ERROR:  returning type sqljsonb_rec[] is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo"...
+                             ^
 -- Extension: domain types returning
 SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
- json_query 
-------------
-          1
-(1 row)
-
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb...
+               ^
 SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb...
+               ^
 -- Test timestamptz passing and output
 SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
          json_query          
@@ -1019,6 +1150,25 @@ ERROR:  functions in index expression must be marked IMMUTABLE
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
 ERROR:  functions in index expression must be marked IMMUTABLE
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()'));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**'));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x));
+ERROR:  functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable
 DROP TABLE test_jsonb_mutability;
 -- JSON_TABLE
 -- Should fail (JSON_TABLE can be used only in FROM clause)
@@ -1048,7 +1198,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$'
 (1 row)
 
 -- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
 SELECT *
 FROM
 	(VALUES
@@ -1069,7 +1218,6 @@ FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -1085,29 +1233,26 @@ FROM
 			exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR,
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
-			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$'
+			jsb2q jsonb PATH '$' OMIT QUOTES
 		)
 	) jt
 	ON true;
-                                          js                                           | id | id2 | int |  text   | char(4) | bool | numeric | domain  |      js      |      jb      |     jst      | jsc  | jsv  |     jsb      |     jsbq     | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 |     js2      |     jsb2w      |    jsb2q     | ia | ta | jba 
----------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------+----+----+-----
- 1                                                                                     |  1 |   1 |   1 | 1       | 1       |      |       1 | 1       | 1            | 1            | 1            | 1    | 1    | 1            | 1            |     |      | f       |       0 |         | false   | 1            | [1]            | 1            |    |    | 
- []                                                                                    |    |     |     |         |         |      |         |         |              |              |              |      |      |              |              |     |      |         |         |         |         |              |                |              |    |    | 
- {}                                                                                    |  1 |   1 |     |         |         |      |         |         | {}           | {}           | {}           | {}   | {}   | {}           | {}           |     |      | f       |       0 |         | false   | {}           | [{}]           | {}           |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  1 |   1 |   1 | 1       | 1       |      |       1 | 1       | 1            | 1            | 1            | 1    | 1    | 1            | 1            |     |      | f       |       0 |         | false   | 1            | [1]            | 1            |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  2 |   2 |   1 | 1.23    | 1.23    |      |    1.23 | 1.23    | 1.23         | 1.23         | 1.23         | 1.23 | 1.23 | 1.23         | 1.23         |     |      | f       |       0 |         | false   | 1.23         | [1.23]         | 1.23         |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  3 |   3 |   2 | 2       | 2       |      |       2 | 2       | "2"          | "2"          | "2"          | "2"  | "2"  | "2"          | 2            |     |      | f       |       0 |         | false   | "2"          | ["2"]          | 2            |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  4 |   4 |     | aaaaaaa | aaaa    |      |         | aaaaaaa | "aaaaaaa"    | "aaaaaaa"    | "aaaaaaa"    | "aaa | "aaa | "aaaaaaa"    |              |     |      | f       |       0 |         | false   | "aaaaaaa"    | ["aaaaaaa"]    |              |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  5 |   5 |     | foo     | foo     |      |         |         | "foo"        | "foo"        | "foo"        | "foo | "foo | "foo"        |              |     |      | f       |       0 |         | false   | "foo"        | ["foo"]        |              |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  6 |   6 |     |         |         |      |         |         | null         | null         | null         | null | null | null         | null         |     |      | f       |       0 |         | false   | null         | [null]         | null         |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 |   7 |   0 | false   | fals    | f    |         | false   | false        | false        | false        | fals | fals | false        | false        |     |      | f       |       0 |         | false   | false        | [false]        | false        |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  8 |   8 |   1 | true    | true    | t    |         | true    | true         | true         | true         | true | true | true         | true         |     |      | f       |       0 |         | false   | true         | [true]         | true         |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 |   9 |     |         |         |      |         |         | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 |  123 | t       |       1 |       1 | true    | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 |  10 |     | [1,2]   | [1,2    |      |         | [1,2]   | "[1,2]"      | "[1,2]"      | "[1,2]"      | "[1, | "[1, | "[1,2]"      | [1, 2]       |     |      | f       |       0 |         | false   | "[1,2]"      | ["[1,2]"]      | [1, 2]       |    |    | 
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 |  11 |     | "str"   | "str    |      |         | "str"   | "\"str\""    | "\"str\""    | "\"str\""    | "\"s | "\"s | "\"str\""    | "str"        |     |      | f       |       0 |         | false   | "\"str\""    | ["\"str\""]    | "str"        |    |    | 
+                                          js                                           | id | id2 | int |  text   | char(4) | bool | numeric |      js      |      jb      |     jst      | jsc  | jsv  |     jsb      |     jsbq     | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 |     js2      |     jsb2w      |    jsb2q     
+---------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------
+ 1                                                                                     |  1 |   1 |   1 | 1       | 1       |      |       1 | 1            | 1            | 1            | 1    | 1    | 1            | 1            |     |      | f       |       0 |         | false   | 1            | [1]            | 1
+ []                                                                                    |    |     |     |         |         |      |         |              |              |              |      |      |              |              |     |      |         |         |         |         |              |                | 
+ {}                                                                                    |  1 |   1 |     |         |         |      |         | {}           | {}           | {}           | {}   | {}   | {}           | {}           |     |      | f       |       0 |         | false   | {}           | [{}]           | {}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  1 |   1 |   1 | 1       | 1       |      |       1 | 1            | 1            | 1            | 1    | 1    | 1            | 1            |     |      | f       |       0 |         | false   | 1            | [1]            | 1
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  2 |   2 |   1 | 1.23    | 1.23    |      |    1.23 | 1.23         | 1.23         | 1.23         | 1.23 | 1.23 | 1.23         | 1.23         |     |      | f       |       0 |         | false   | 1.23         | [1.23]         | 1.23
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  3 |   3 |   2 | 2       | 2       |      |       2 | "2"          | "2"          | "2"          | "2"  | "2"  | "2"          | 2            |     |      | f       |       0 |         | false   | "2"          | ["2"]          | 2
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  4 |   4 |     | aaaaaaa | aaaa    |      |         | "aaaaaaa"    | "aaaaaaa"    | "aaaaaaa"    | "aaa | "aaa | "aaaaaaa"    |              |     |      | f       |       0 |         | false   | "aaaaaaa"    | ["aaaaaaa"]    | 
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  5 |   5 |     | foo     | foo     |      |         | "foo"        | "foo"        | "foo"        | "foo | "foo | "foo"        |              |     |      | f       |       0 |         | false   | "foo"        | ["foo"]        | 
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  6 |   6 |     |         |         |      |         | null         | null         | null         | null | null | null         | null         |     |      | f       |       0 |         | false   | null         | [null]         | null
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  7 |   7 |   0 | false   | fals    | f    |         | false        | false        | false        | fals | fals | false        | false        |     |      | f       |       0 |         | false   | false        | [false]        | false
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  8 |   8 |   1 | true    | true    | t    |         | true         | true         | true         | true | true | true         | true         |     |      | f       |       0 |         | false   | true         | [true]         | true
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] |  9 |   9 |     |         |         |      |         | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 |  123 | t       |       1 |       1 | true    | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 |  10 |     | [1,2]   | [1,2    |      |         | "[1,2]"      | "[1,2]"      | "[1,2]"      | "[1, | "[1, | "[1,2]"      | [1, 2]       |     |      | f       |       0 |         | false   | "[1,2]"      | ["[1,2]"]      | [1, 2]
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 |  11 |     | "str"   | "str    |      |         | "\"str\""    | "\"str\""    | "\"str\""    | "\"s | "\"s | "\"str\""    | "str"        |     |      | f       |       0 |         | false   | "\"str\""    | ["\"str\""]    | "str"
 (14 rows)
 
 -- JSON_TABLE: Test backward parsing
@@ -1123,7 +1268,6 @@ SELECT * FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -1139,9 +1283,6 @@ SELECT * FROM
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
 			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$',
 			NESTED PATH '$[1]' AS p1 COLUMNS (
 				a1 int,
 				NESTED PATH '$[*]' AS "p1 1" COLUMNS (
@@ -1168,7 +1309,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
     "json_table"."char(4)",
     "json_table".bool,
     "json_table"."numeric",
-    "json_table".domain,
     "json_table".js,
     "json_table".jb,
     "json_table".jst,
@@ -1184,9 +1324,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
     "json_table".js2,
     "json_table".jsb2w,
     "json_table".jsb2q,
-    "json_table".ia,
-    "json_table".ta,
-    "json_table".jba,
     "json_table".a1,
     "json_table".b1,
     "json_table".a11,
@@ -1205,7 +1342,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
                 "char(4)" character(4) PATH '$',
                 bool boolean PATH '$',
                 "numeric" numeric PATH '$',
-                domain jsonb_test_domain PATH '$',
                 js json PATH '$',
                 jb jsonb PATH '$',
                 jst text FORMAT JSON PATH '$',
@@ -1221,9 +1357,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
                 js2 json PATH '$',
                 jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
                 jsb2q jsonb PATH '$' OMIT QUOTES,
-                ia integer[] PATH '$',
-                ta text[] PATH '$',
-                jba jsonb[] PATH '$',
                 NESTED PATH '$[1]' AS p1
                 COLUMNS (
                     a1 integer PATH '$."a1"',
@@ -1248,15 +1381,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
             PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
         )
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
-                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
-   Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+   Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
 (3 rows)
 
 DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
 -- JSON_TABLE: ON EMPTY/ON ERROR behavior
 SELECT *
 FROM
@@ -1318,15 +1450,15 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to smallint
+ERROR:  returning type smallint is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to bigint
+ERROR:  returning type bigint is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to real
+ERROR:  returning type real is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 E...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
@@ -1336,11 +1468,11 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'))
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to json
+ERROR:  returning type json is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to jsonb
+ERROR:  returning type jsonb is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
                                                              ^
 -- JSON_TABLE: nested paths and plans
@@ -2102,11 +2234,14 @@ set parallel_leader_participation = off;
 -- Should be non-parallel due to subtransactions
 EXPLAIN (COSTS OFF)
 SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
-                 QUERY PLAN                  
----------------------------------------------
- Aggregate
-   ->  Seq Scan on test_parallel_jsonb_value
-(2 rows)
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Finalize Aggregate
+   ->  Gather
+         Workers Planned: 4
+         ->  Partial Aggregate
+               ->  Parallel Seq Scan on test_parallel_jsonb_value
+(5 rows)
 
 SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
     sum     
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index fff25374808..43a194ad09f 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -82,12 +82,43 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
 
 SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
 
--- Test NULL checks execution in domain types
+SELECT JSON_VALUE(jsonb  '1.23',  '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb  '"nan"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb  '1.23',  '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb  '"nan"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb  '1.23',  '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb  '"nan"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR);
+
+-- Test for domain types
 CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0');
+CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0');
+
+-- Test casting to json[b] domains (items casted as is, strings are not unquoted)
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null);
+
+-- Test NULL checks execution in domain types
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
 
+
+-- Test returning of non-scalar items
 SELECT JSON_VALUE(jsonb '[]', '$');
 SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
 SELECT JSON_VALUE(jsonb '{}', '$');
@@ -133,6 +164,17 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
 SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
 SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
 
+-- test errors in DEFAULT ON EMPTY expressions
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
+                  DEFAULT 1 / x ON EMPTY
+                  DEFAULT 2 ON ERROR)
+FROM (VALUES (1::int), (0)) x(x);
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
+
 -- JSON_QUERY
 
 SELECT
@@ -318,6 +360,21 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ?
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
 CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable
+
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable
+
 DROP TABLE test_jsonb_mutability;
 
 -- JSON_TABLE
@@ -338,8 +395,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$'
 	COLUMNS (item int PATH '$', foo int)) bar;
 
 -- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
-
 SELECT *
 FROM
 	(VALUES
@@ -360,7 +415,6 @@ FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -377,10 +431,7 @@ FROM
 
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
-			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$'
+			jsb2q jsonb PATH '$' OMIT QUOTES
 		)
 	) jt
 	ON true;
@@ -399,7 +450,6 @@ SELECT * FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -416,9 +466,6 @@ SELECT * FROM
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
 			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$',
 
 			NESTED PATH '$[1]' AS p1 COLUMNS (
 				a1 int,
@@ -443,7 +490,6 @@ SELECT * FROM
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
 
 DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
 
 -- JSON_TABLE: ON EMPTY/ON ERROR behavior
 SELECT *
-- 
2.17.1