SQL JSON functions

This Patch introduces three SQL standard JSON functions:

JSON() (incorrectly mentioned in my commit message for f4fb45d15c)
JSON_SCALAR()
JSON_SERIALIZE()

JSON() produces json values from text, bytea, json or jsonb values, and
has facilitites for handling duplicate keys.
JSON_SCALAR() produces a json value from any scalar sql value, including
json and jsonb.
JSON_SERIALIZE() produces text or bytea from input which containis or
represents json or jsonb;

For the most part these functions don't add any significant new
capabilities, but they will be of use to users wanting standard
compliant JSON handling.

Nikita Glukhov

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

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

1 files changed, 138 insertions, 14 deletions

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index ee316a91979..e1400072501 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -88,6 +88,10 @@ static Node *transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg);
 static Node *transformJsonIsPredicate(ParseState *pstate, JsonIsPredicate *p);
 static Node *transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *p);
 static Node *transformJsonValueExpr(ParseState *pstate, JsonValueExpr *jve);
+static Node *transformJsonParseExpr(ParseState *pstate, JsonParseExpr *expr);
+static Node *transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *expr);
+static Node *transformJsonSerializeExpr(ParseState *pstate,
+										JsonSerializeExpr *expr);
 static Node *make_row_comparison_op(ParseState *pstate, List *opname,
 									List *largs, List *rargs, int location);
 static Node *make_row_distinct_op(ParseState *pstate, List *opname,
@@ -347,6 +351,18 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			result = transformJsonValueExpr(pstate, (JsonValueExpr *) expr);
 			break;
 
+		case T_JsonParseExpr:
+			result = transformJsonParseExpr(pstate, (JsonParseExpr *) expr);
+			break;
+
+		case T_JsonScalarExpr:
+			result = transformJsonScalarExpr(pstate, (JsonScalarExpr *) expr);
+			break;
+
+		case T_JsonSerializeExpr:
+			result = transformJsonSerializeExpr(pstate, (JsonSerializeExpr *) expr);
+			break;
+
 		default:
 			/* should not reach here */
 			elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
@@ -3229,7 +3245,8 @@ makeCaseTestExpr(Node *expr)
  */
 static Node *
 transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
-						  JsonFormatType default_format, bool isarg)
+						  JsonFormatType default_format, bool isarg,
+						  Oid targettype)
 {
 	Node	   *expr = transformExprRecurse(pstate, (Node *) ve->raw_expr);
 	Node	   *rawexpr;
@@ -3303,17 +3320,17 @@ transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
 	else
 		format = default_format;
 
-	if (format == JS_FORMAT_DEFAULT)
+	if (format == JS_FORMAT_DEFAULT &&
+		(!OidIsValid(targettype) || exprtype == targettype))
 		expr = rawexpr;
 	else
 	{
-		Oid			targettype = format == JS_FORMAT_JSONB ? JSONBOID : JSONOID;
 		Node	   *orig = makeCaseTestExpr(expr);
 		Node	   *coerced;
+		bool		cast_is_needed = OidIsValid(targettype);
 
-		expr = orig;
-
-		if (!isarg && exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING)
+		if (!isarg && !cast_is_needed &&
+			exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING)
 			ereport(ERROR,
 					(errcode(ERRCODE_DATATYPE_MISMATCH),
 					 errmsg(ve->format->format_type == JS_FORMAT_DEFAULT ?
@@ -3322,6 +3339,8 @@ transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
 					 parser_errposition(pstate, ve->format->location >= 0 ?
 										ve->format->location : location)));
 
+		expr = orig;
+
 		/* Convert encoded JSON text from bytea. */
 		if (format == JS_FORMAT_JSON && exprtype == BYTEAOID)
 		{
@@ -3329,6 +3348,9 @@ transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
 			exprtype = TEXTOID;
 		}
 
+		if (!OidIsValid(targettype))
+			targettype = format == JS_FORMAT_JSONB ? JSONBOID : JSONOID;
+
 		/* Try to coerce to the target type. */
 		coerced = coerce_to_target_type(pstate, expr, exprtype,
 										targettype, -1,
@@ -3339,11 +3361,21 @@ transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
 		if (!coerced)
 		{
 			/* If coercion failed, use to_json()/to_jsonb() functions. */
-			Oid			fnoid = targettype == JSONOID ? F_TO_JSON : F_TO_JSONB;
-			FuncExpr   *fexpr = makeFuncExpr(fnoid, targettype,
-											 list_make1(expr),
-											 InvalidOid, InvalidOid,
-											 COERCE_EXPLICIT_CALL);
+			FuncExpr   *fexpr;
+			Oid			fnoid;
+
+			if (cast_is_needed)		/* only CAST is allowed */
+				ereport(ERROR,
+						(errcode(ERRCODE_CANNOT_COERCE),
+						 errmsg("cannot cast type %s to %s",
+								format_type_be(exprtype),
+								format_type_be(targettype)),
+								parser_errposition(pstate, location)));
+
+			fnoid = targettype == JSONOID ? F_TO_JSON : F_TO_JSONB;
+			fexpr = makeFuncExpr(fnoid, targettype, list_make1(expr),
+								 InvalidOid, InvalidOid, COERCE_EXPLICIT_CALL);
+
 			fexpr->location = location;
 
 			coerced = (Node *) fexpr;
@@ -3370,7 +3402,8 @@ transformJsonValueExprExt(ParseState *pstate, JsonValueExpr *ve,
 static Node *
 transformJsonValueExpr(ParseState *pstate, JsonValueExpr *jve)
 {
-	return transformJsonValueExprExt(pstate, jve, JS_FORMAT_JSON, false);
+	return transformJsonValueExprExt(pstate, jve, JS_FORMAT_JSON, false,
+									 InvalidOid);
 }
 
 /*
@@ -3379,7 +3412,8 @@ transformJsonValueExpr(ParseState *pstate, JsonValueExpr *jve)
 static Node *
 transformJsonValueExprDefault(ParseState *pstate, JsonValueExpr *jve)
 {
-	return transformJsonValueExprExt(pstate, jve, JS_FORMAT_DEFAULT, false);
+	return transformJsonValueExprExt(pstate, jve, JS_FORMAT_DEFAULT, false,
+									 InvalidOid);
 }
 
 /*
@@ -4022,7 +4056,7 @@ transformJsonPassingArgs(ParseState *pstate, JsonFormatType format, List *args,
 	{
 		JsonArgument *arg = castNode(JsonArgument, lfirst(lc));
 		Node	   *expr = transformJsonValueExprExt(pstate, arg->val,
-													 format, true);
+													 format, true, InvalidOid);
 
 		assign_expr_collations(pstate, expr);
 
@@ -4415,3 +4449,93 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 
 	return (Node *) jsexpr;
 }
+
+/*
+ * Transform a JSON() expression.
+ */
+static Node *
+transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr)
+{
+	JsonReturning *returning = makeNode(JsonReturning);
+	Node	   *arg;
+
+	returning->format = makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, -1);
+	returning->typid = JSONOID;
+	returning->typmod = -1;
+
+	if (jsexpr->unique_keys)
+	{
+		/*
+		 * Coerce string argument to text and then to json[b] in the executor
+		 * node with key uniqueness check.
+		 */
+		JsonValueExpr *jve = jsexpr->expr;
+		Oid			arg_type;
+
+		arg = transformJsonParseArg(pstate, (Node *) jve->raw_expr, jve->format,
+									&arg_type);
+
+		if (arg_type != TEXTOID)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("cannot use non-string types with WITH UNIQUE KEYS clause"),
+					 parser_errposition(pstate, jsexpr->location)));
+	}
+	else
+	{
+		/*
+		 * Coerce argument to target type using CAST for compatibilty with PG
+		 * function-like CASTs.
+		 */
+		arg = transformJsonValueExprExt(pstate, jsexpr->expr, JS_FORMAT_JSON,
+										false, returning->typid);
+	}
+
+	return makeJsonConstructorExpr(pstate, JSCTOR_JSON_PARSE, list_make1(arg), NULL,
+							returning, jsexpr->unique_keys, false,
+							jsexpr->location);
+}
+
+/*
+ * Transform a JSON_SCALAR() expression.
+ */
+static Node *
+transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *jsexpr)
+{
+	JsonReturning *returning = makeNode(JsonReturning);
+	Node	   *arg = transformExprRecurse(pstate, (Node *) jsexpr->expr);
+
+	returning->format = makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, -1);
+	returning->typid = JSONOID;
+	returning->typmod = -1;
+
+	if (exprType(arg) == UNKNOWNOID)
+		arg = coerce_to_specific_type(pstate, arg, TEXTOID, "JSON_SCALAR");
+
+	return makeJsonConstructorExpr(pstate, JSCTOR_JSON_SCALAR, list_make1(arg), NULL,
+							returning, false, false, jsexpr->location);
+}
+
+/*
+ * Transform a JSON_SERIALIZE() expression.
+ */
+static Node *
+transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr)
+{
+	Node	   *arg = transformJsonValueExpr(pstate, expr->expr);
+	JsonReturning *returning;
+
+	if (expr->output)
+		returning = transformJsonOutput(pstate, expr->output, true);
+	else
+	{
+		/* RETURNING TEXT FORMAT JSON is by default */
+		returning = makeNode(JsonReturning);
+		returning->format = makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, -1);
+		returning->typid = TEXTOID;
+		returning->typmod = -1;
+	}
+
+	return makeJsonConstructorExpr(pstate, JSCTOR_JSON_SERIALIZE, list_make1(arg),
+							NULL, returning, false, false, expr->location);
+}