v8-0004-SQL-JSON-functions.patch

From 3865c772ea329c60d84fd936b4d8024969874808 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <andrew@dunslane.net>
Date: Mon, 26 Dec 2022 16:55:15 +0900
Subject: [PATCH v8 4/9] SQL JSON functions

This Patch introduces three SQL standard JSON functions:

JSON()
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
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
---
 doc/src/sgml/keywords/sql2016-02-reserved.txt |   3 +
 src/backend/executor/execExpr.c               |  45 +++
 src/backend/executor/execExprInterp.c         |  46 ++-
 src/backend/nodes/nodeFuncs.c                 |  14 +
 src/backend/parser/gram.y                     |  62 +++-
 src/backend/parser/parse_expr.c               | 169 +++++++++-
 src/backend/parser/parse_target.c             |   9 +
 src/backend/utils/adt/format_type.c           |   4 +
 src/backend/utils/adt/json.c                  |  37 +-
 src/backend/utils/adt/jsonb.c                 |  66 ++--
 src/backend/utils/adt/ruleutils.c             |  13 +-
 src/include/nodes/parsenodes.h                |  35 ++
 src/include/nodes/primnodes.h                 |   5 +-
 src/include/parser/kwlist.h                   |   4 +-
 src/include/utils/json.h                      |  19 ++
 src/include/utils/jsonb.h                     |  21 ++
 src/test/regress/expected/jsonb_sqljson.out   |  16 +-
 src/test/regress/expected/sqljson.out         | 319 ++++++++++++++++++
 src/test/regress/sql/jsonb_sqljson.sql        |   8 +-
 src/test/regress/sql/sqljson.sql              |  83 +++++
 src/tools/pgindent/typedefs.list              |   1 +
 21 files changed, 889 insertions(+), 90 deletions(-)

diff --git a/doc/src/sgml/keywords/sql2016-02-reserved.txt b/doc/src/sgml/keywords/sql2016-02-reserved.txt
index f65dd4d577..3ee9492024 100644
--- a/doc/src/sgml/keywords/sql2016-02-reserved.txt
+++ b/doc/src/sgml/keywords/sql2016-02-reserved.txt
@@ -157,12 +157,15 @@ INTERVAL
 INTO
 IS
 JOIN
+JSON
 JSON_ARRAY
 JSON_ARRAYAGG
 JSON_EXISTS
 JSON_OBJECT
 JSON_OBJECTAGG
 JSON_QUERY
+JSON_SCALAR
+JSON_SERIALIZE
 JSON_TABLE
 JSON_TABLE_PRIMITIVE
 JSON_VALUE
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index c8ec4d78b2..cd48bc6a04 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -48,6 +48,8 @@
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
+#include "utils/json.h"
+#include "utils/jsonb.h"
 #include "utils/jsonpath.h"
 #include "utils/lsyscache.h"
 #include "utils/typcache.h"
@@ -2449,6 +2451,12 @@ ExecInitExprRec(Expr *node, ExprState *state,
 				{
 					ExecInitExprRec(ctor->func, state, resv, resnull);
 				}
+				else if ((ctor->type == JSCTOR_JSON_PARSE && !ctor->unique) ||
+						 ctor->type == JSCTOR_JSON_SERIALIZE)
+				{
+					/* Use the value of the first argument as a result */
+					ExecInitExprRec(linitial(args), state, resv, resnull);
+				}
 				else
 				{
 					JsonConstructorExprState *jcstate;
@@ -2487,6 +2495,43 @@ ExecInitExprRec(Expr *node, ExprState *state,
 						argno++;
 					}
 
+					/* prepare type cache for datum_to_json[b]() */
+					if (ctor->type == JSCTOR_JSON_SCALAR)
+					{
+						bool		is_jsonb =
+						ctor->returning->format->format_type == JS_FORMAT_JSONB;
+
+						jcstate->arg_type_cache =
+							palloc(sizeof(*jcstate->arg_type_cache) * nargs);
+
+						for (int i = 0; i < nargs; i++)
+						{
+							int			category;
+							Oid			outfuncid;
+							Oid			typid = jcstate->arg_types[i];
+
+							if (is_jsonb)
+							{
+								JsonbTypeCategory jbcat;
+
+								jsonb_categorize_type(typid, &jbcat, &outfuncid);
+
+								category = (int) jbcat;
+							}
+							else
+							{
+								JsonTypeCategory jscat;
+
+								json_categorize_type(typid, &jscat, &outfuncid);
+
+								category = (int) jscat;
+							}
+
+							jcstate->arg_type_cache[i].outfuncid = outfuncid;
+							jcstate->arg_type_cache[i].category = category;
+						}
+					}
+
 					ExprEvalPushStep(state, &scratch);
 				}
 
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 94b68780e9..09ca68c369 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4607,7 +4607,7 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 										 jcstate->arg_values,
 										 jcstate->arg_nulls,
 										 jcstate->arg_types,
-										 jcstate->constructor->absent_on_null);
+										 ctor->absent_on_null);
 	else if (ctor->type == JSCTOR_JSON_OBJECT)
 		res = (is_jsonb ?
 			   jsonb_build_object_worker :
@@ -4615,8 +4615,48 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 										  jcstate->arg_values,
 										  jcstate->arg_nulls,
 										  jcstate->arg_types,
-										  jcstate->constructor->absent_on_null,
-										  jcstate->constructor->unique);
+										  ctor->absent_on_null,
+										  ctor->unique);
+	else if (ctor->type == JSCTOR_JSON_SCALAR)
+	{
+		if (jcstate->arg_nulls[0])
+		{
+			res = (Datum) 0;
+			isnull = true;
+		}
+		else
+		{
+			Datum		value = jcstate->arg_values[0];
+			int			category = jcstate->arg_type_cache[0].category;
+			Oid			outfuncid = jcstate->arg_type_cache[0].outfuncid;
+
+			if (is_jsonb)
+				res = to_jsonb_worker(value, category, outfuncid);
+			else
+				res = to_json_worker(value, category, outfuncid);
+		}
+	}
+	else if (ctor->type == JSCTOR_JSON_PARSE)
+	{
+		if (jcstate->arg_nulls[0])
+		{
+			res = (Datum) 0;
+			isnull = true;
+		}
+		else
+		{
+			Datum		value = jcstate->arg_values[0];
+			text	   *js = DatumGetTextP(value);
+
+			if (is_jsonb)
+				res = jsonb_from_text(js, true);
+			else
+			{
+				(void) json_validate(js, true, true);
+				res = value;
+			}
+		}
+	}
 	else
 	{
 		res = (Datum) 0;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index f5726a3ac3..b7a101cfcc 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4332,6 +4332,20 @@ raw_expression_tree_walker_impl(Node *node,
 					return true;
 			}
 			break;
+		case T_JsonParseExpr:
+			return WALK(((JsonParseExpr *) node)->expr);
+		case T_JsonScalarExpr:
+			return WALK(((JsonScalarExpr *) node)->expr);
+		case T_JsonSerializeExpr:
+			{
+				JsonSerializeExpr *jse = (JsonSerializeExpr *) node;
+
+				if (WALK(jse->expr))
+					return true;
+				if (WALK(jse->output))
+					return true;
+			}
+			break;
 		case T_JsonConstructorExpr:
 			{
 				JsonConstructorExpr *ctor = (JsonConstructorExpr *) node;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a386f247f9..ef5d45dfad 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -571,7 +571,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	copy_options
 
 %type <typnam>	Typename SimpleTypename ConstTypename
-				GenericType Numeric opt_float
+				GenericType Numeric opt_float JsonType
 				Character ConstCharacter
 				CharacterWithLength CharacterWithoutLength
 				ConstDatetime ConstInterval
@@ -659,6 +659,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 					json_value_func_expr
 					json_query_expr
 					json_exists_predicate
+					json_parse_expr
+					json_scalar_expr
+					json_serialize_expr
 					json_api_common_syntax
 					json_context_item
 					json_argument
@@ -778,7 +781,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
 
 	JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
-	JSON_QUERY JSON_VALUE
+	JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_VALUE
 
 	KEY KEYS KEEP
 
@@ -14056,6 +14059,7 @@ SimpleTypename:
 					$$->typmods = list_make2(makeIntConst(INTERVAL_FULL_RANGE, -1),
 											 makeIntConst($3, @3));
 				}
+			| JsonType								{ $$ = $1; }
 		;
 
 /* We have a separate ConstTypename to allow defaulting fixed-length
@@ -14074,6 +14078,7 @@ ConstTypename:
 			| ConstBit								{ $$ = $1; }
 			| ConstCharacter						{ $$ = $1; }
 			| ConstDatetime							{ $$ = $1; }
+			| JsonType								{ $$ = $1; }
 		;
 
 /*
@@ -14442,6 +14447,13 @@ interval_second:
 				}
 		;
 
+JsonType:
+			JSON
+				{
+					$$ = SystemTypeName("json");
+					$$->location = @1;
+				}
+		;
 
 /*****************************************************************************
  *
@@ -16421,8 +16433,45 @@ json_func_expr:
 			| json_value_func_expr
 			| json_query_expr
 			| json_exists_predicate
+			| json_parse_expr
+			| json_scalar_expr
+			| json_serialize_expr
+		;
+
+json_parse_expr:
+			JSON '(' json_value_expr json_key_uniqueness_constraint_opt ')'
+				{
+					JsonParseExpr *n = makeNode(JsonParseExpr);
+
+					n->expr = (JsonValueExpr *) $3;
+					n->unique_keys = $4;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
+
+json_scalar_expr:
+			JSON_SCALAR '(' a_expr ')'
+				{
+					JsonScalarExpr *n = makeNode(JsonScalarExpr);
+
+					n->expr = (Expr *) $3;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
 		;
 
+json_serialize_expr:
+			JSON_SERIALIZE '(' json_value_expr json_output_clause_opt ')'
+				{
+					JsonSerializeExpr *n = makeNode(JsonSerializeExpr);
+
+					n->expr = (JsonValueExpr *) $3;
+					n->output = (JsonOutput *) $4;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
 
 json_value_func_expr:
 			JSON_VALUE '('
@@ -16432,6 +16481,7 @@ json_value_func_expr:
 			')'
 				{
 					JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
 					n->op = JSON_VALUE_OP;
 					n->common = (JsonCommon *) $3;
 					n->output = (JsonOutput *) $4;
@@ -16448,6 +16498,7 @@ json_api_common_syntax:
 			json_passing_clause_opt
 				{
 					JsonCommon *n = makeNode(JsonCommon);
+
 					n->expr = (JsonValueExpr *) $1;
 					n->pathspec = $3;
 					n->pathname = $4;
@@ -16488,6 +16539,7 @@ json_argument:
 			json_value_expr AS ColLabel
 			{
 				JsonArgument *n = makeNode(JsonArgument);
+
 				n->val = (JsonValueExpr *) $1;
 				n->name = $3;
 				$$ = (Node *) n;
@@ -17498,7 +17550,6 @@ unreserved_keyword:
 			| INSTEAD
 			| INVOKER
 			| ISOLATION
-			| JSON
 			| KEEP
 			| KEY
 			| KEYS
@@ -17718,12 +17769,15 @@ col_name_keyword:
 			| INT_P
 			| INTEGER
 			| INTERVAL
+			| JSON
 			| JSON_ARRAY
 			| JSON_ARRAYAGG
 			| JSON_EXISTS
 			| JSON_OBJECT
 			| JSON_OBJECTAGG
 			| JSON_QUERY
+			| JSON_SCALAR
+			| JSON_SERIALIZE
 			| JSON_VALUE
 			| LEAST
 			| NATIONAL
@@ -18089,6 +18143,8 @@ bare_label_keyword:
 			| JSON_OBJECT
 			| JSON_OBJECTAGG
 			| JSON_QUERY
+			| JSON_SCALAR
+			| JSON_SERIALIZE
 			| JSON_VALUE
 			| KEEP
 			| KEY
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9288f7b2a1..dae159b220 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -86,6 +86,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,
@@ -340,6 +344,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));
@@ -3172,7 +3188,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;
@@ -3246,17 +3263,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 ?
@@ -3265,6 +3282,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)
 		{
@@ -3272,6 +3291,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,
@@ -3282,11 +3304,20 @@ 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;
 
@@ -3314,7 +3345,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);
 }
 
 /*
@@ -3323,7 +3355,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);
 }
 
 /*
@@ -3965,7 +3998,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);
 
@@ -4361,3 +4394,111 @@ 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 compatibility 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);
+
+		if (returning->typid != BYTEAOID)
+		{
+			char		typcategory;
+			bool		typispreferred;
+
+			get_type_category_preferred(returning->typid, &typcategory,
+										&typispreferred);
+			if (typcategory != TYPCATEGORY_STRING)
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("cannot use RETURNING type %s in %s",
+								format_type_be(returning->typid),
+								"JSON_SERIALIZE()"),
+						 errhint("Try returning a string type or bytea.")));
+		}
+	}
+	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);
+}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index bc7e44d8a9..34e7094acf 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1909,6 +1909,15 @@ FigureColnameInternal(Node *node, char **name)
 		case T_XmlSerialize:
 			*name = "xmlserialize";
 			return 2;
+		case T_JsonParseExpr:
+			*name = "json";
+			return 2;
+		case T_JsonScalarExpr:
+			*name = "json_scalar";
+			return 2;
+		case T_JsonSerializeExpr:
+			*name = "json_serialize";
+			return 2;
 		case T_JsonObjectConstructor:
 			*name = "json_object";
 			return 2;
diff --git a/src/backend/utils/adt/format_type.c b/src/backend/utils/adt/format_type.c
index 12402a0637..36c45a3978 100644
--- a/src/backend/utils/adt/format_type.c
+++ b/src/backend/utils/adt/format_type.c
@@ -294,6 +294,10 @@ format_type_extended(Oid type_oid, int32 typemod, bits16 flags)
 			else
 				buf = pstrdup("character varying");
 			break;
+
+		case JSONOID:
+			buf = pstrdup("json");
+			break;
 	}
 
 	if (buf == NULL)
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index da4b2a9d1b..dd58044116 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -30,21 +30,6 @@
 #include "utils/lsyscache.h"
 #include "utils/typcache.h"
 
-typedef enum					/* type categories for datum_to_json */
-{
-	JSONTYPE_NULL,				/* null, so we didn't bother to identify */
-	JSONTYPE_BOOL,				/* boolean (built-in types only) */
-	JSONTYPE_NUMERIC,			/* numeric (ditto) */
-	JSONTYPE_DATE,				/* we use special formatting for datetimes */
-	JSONTYPE_TIMESTAMP,
-	JSONTYPE_TIMESTAMPTZ,
-	JSONTYPE_JSON,				/* JSON itself (and JSONB) */
-	JSONTYPE_ARRAY,				/* array */
-	JSONTYPE_COMPOSITE,			/* composite */
-	JSONTYPE_CAST,				/* something with an explicit cast to JSON */
-	JSONTYPE_OTHER				/* all else */
-} JsonTypeCategory;
-
 /* Common context for key uniqueness check */
 typedef struct HTAB *JsonUniqueCheckState;	/* hash table for key names */
 
@@ -99,9 +84,6 @@ static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
 							  bool use_line_feeds);
 static void array_to_json_internal(Datum array, StringInfo result,
 								   bool use_line_feeds);
-static void json_categorize_type(Oid typoid,
-								 JsonTypeCategory *tcategory,
-								 Oid *outfuncoid);
 static void datum_to_json(Datum val, bool is_null, StringInfo result,
 						  JsonTypeCategory tcategory, Oid outfuncoid,
 						  bool key_scalar);
@@ -181,7 +163,7 @@ json_recv(PG_FUNCTION_ARGS)
  * output function OID.  If the returned category is JSONTYPE_CAST, we
  * return the OID of the type->JSON cast function instead.
  */
-static void
+void
 json_categorize_type(Oid typoid,
 					 JsonTypeCategory *tcategory,
 					 Oid *outfuncoid)
@@ -763,6 +745,16 @@ row_to_json_pretty(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len));
 }
 
+Datum
+to_json_worker(Datum val, JsonTypeCategory tcategory, Oid outfuncoid)
+{
+	StringInfo	result = makeStringInfo();
+
+	datum_to_json(val, false, result, tcategory, outfuncoid, false);
+
+	return PointerGetDatum(cstring_to_text_with_len(result->data, result->len));
+}
+
 bool
 to_json_is_immutable(Oid typoid)
 {
@@ -803,7 +795,6 @@ to_json(PG_FUNCTION_ARGS)
 {
 	Datum		val = PG_GETARG_DATUM(0);
 	Oid			val_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
-	StringInfo	result;
 	JsonTypeCategory tcategory;
 	Oid			outfuncoid;
 
@@ -815,11 +806,7 @@ to_json(PG_FUNCTION_ARGS)
 	json_categorize_type(val_type,
 						 &tcategory, &outfuncoid);
 
-	result = makeStringInfo();
-
-	datum_to_json(val, false, result, tcategory, outfuncoid, false);
-
-	PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len));
+	PG_RETURN_DATUM(to_json_worker(val, tcategory, outfuncoid));
 }
 
 /*
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 2ddb3d8a58..4e37b7500a 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -34,26 +34,10 @@ typedef struct JsonbInState
 {
 	JsonbParseState *parseState;
 	JsonbValue *res;
+	bool		unique_keys;
 	Node	   *escontext;
 } JsonbInState;
 
-/* unlike with json categories, we need to treat json and jsonb differently */
-typedef enum					/* type categories for datum_to_jsonb */
-{
-	JSONBTYPE_NULL,				/* null, so we didn't bother to identify */
-	JSONBTYPE_BOOL,				/* boolean (built-in types only) */
-	JSONBTYPE_NUMERIC,			/* numeric (ditto) */
-	JSONBTYPE_DATE,				/* we use special formatting for datetimes */
-	JSONBTYPE_TIMESTAMP,		/* we use special formatting for timestamp */
-	JSONBTYPE_TIMESTAMPTZ,		/* ... and timestamptz */
-	JSONBTYPE_JSON,				/* JSON */
-	JSONBTYPE_JSONB,			/* JSONB */
-	JSONBTYPE_ARRAY,			/* array */
-	JSONBTYPE_COMPOSITE,		/* composite */
-	JSONBTYPE_JSONCAST,			/* something with an explicit cast to JSON */
-	JSONBTYPE_OTHER				/* all else */
-} JsonbTypeCategory;
-
 typedef struct JsonbAggState
 {
 	JsonbInState *res;
@@ -63,7 +47,8 @@ typedef struct JsonbAggState
 	Oid			val_output_func;
 } JsonbAggState;
 
-static inline Datum jsonb_from_cstring(char *json, int len, Node *escontext);
+static inline Datum jsonb_from_cstring(char *json, int len, bool unique_keys,
+									   Node *escontext);
 static bool checkStringLen(size_t len, Node *escontext);
 static JsonParseErrorType jsonb_in_object_start(void *pstate);
 static JsonParseErrorType jsonb_in_object_end(void *pstate);
@@ -72,17 +57,11 @@ static JsonParseErrorType jsonb_in_array_end(void *pstate);
 static JsonParseErrorType jsonb_in_object_field_start(void *pstate, char *fname, bool isnull);
 static void jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal);
 static JsonParseErrorType jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype);
-static void jsonb_categorize_type(Oid typoid,
-								  JsonbTypeCategory *tcategory,
-								  Oid *outfuncoid);
 static void composite_to_jsonb(Datum composite, JsonbInState *result);
 static void array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims,
 							   Datum *vals, bool *nulls, int *valcount,
 							   JsonbTypeCategory tcategory, Oid outfuncoid);
 static void array_to_jsonb_internal(Datum array, JsonbInState *result);
-static void jsonb_categorize_type(Oid typoid,
-								  JsonbTypeCategory *tcategory,
-								  Oid *outfuncoid);
 static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
 						   JsonbTypeCategory tcategory, Oid outfuncoid,
 						   bool key_scalar);
@@ -100,7 +79,7 @@ jsonb_in(PG_FUNCTION_ARGS)
 {
 	char	   *json = PG_GETARG_CSTRING(0);
 
-	return jsonb_from_cstring(json, strlen(json), fcinfo->context);
+	return jsonb_from_cstring(json, strlen(json), false, fcinfo->context);
 }
 
 /*
@@ -124,7 +103,7 @@ jsonb_recv(PG_FUNCTION_ARGS)
 	else
 		elog(ERROR, "unsupported jsonb version number %d", version);
 
-	return jsonb_from_cstring(str, nbytes, NULL);
+	return jsonb_from_cstring(str, nbytes, false, NULL);
 }
 
 /*
@@ -165,6 +144,15 @@ jsonb_send(PG_FUNCTION_ARGS)
 	PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
 }
 
+Datum
+jsonb_from_text(text *js, bool unique_keys)
+{
+	return jsonb_from_cstring(VARDATA_ANY(js),
+							  VARSIZE_ANY_EXHDR(js),
+							  unique_keys,
+							  NULL);
+}
+
 /*
  * Get the type name of a jsonb container.
  */
@@ -258,7 +246,7 @@ jsonb_typeof(PG_FUNCTION_ARGS)
  * instead of being thrown.
  */
 static inline Datum
-jsonb_from_cstring(char *json, int len, Node *escontext)
+jsonb_from_cstring(char *json, int len, bool unique_keys, Node *escontext)
 {
 	JsonLexContext *lex;
 	JsonbInState state;
@@ -268,7 +256,9 @@ jsonb_from_cstring(char *json, int len, Node *escontext)
 	memset(&sem, 0, sizeof(sem));
 	lex = makeJsonLexContextCstringLen(json, len, GetDatabaseEncoding(), true);
 
+	state.unique_keys = unique_keys;
 	state.escontext = escontext;
+
 	sem.semstate = (void *) &state;
 
 	sem.object_start = jsonb_in_object_start;
@@ -304,6 +294,7 @@ jsonb_in_object_start(void *pstate)
 	JsonbInState *_state = (JsonbInState *) pstate;
 
 	_state->res = pushJsonbValue(&_state->parseState, WJB_BEGIN_OBJECT, NULL);
+	_state->parseState->unique_keys = _state->unique_keys;
 
 	return JSON_SUCCESS;
 }
@@ -640,7 +631,7 @@ add_indent(StringInfo out, bool indent, int level)
  * output function OID.  If the returned category is JSONBTYPE_JSONCAST,
  * we return the OID of the relevant cast function instead.
  */
-static void
+void
 jsonb_categorize_type(Oid typoid,
 					  JsonbTypeCategory *tcategory,
 					  Oid *outfuncoid)
@@ -1150,6 +1141,18 @@ add_jsonb(Datum val, bool is_null, JsonbInState *result,
 	datum_to_jsonb(val, is_null, result, tcategory, outfuncoid, key_scalar);
 }
 
+Datum
+to_jsonb_worker(Datum val, JsonbTypeCategory tcategory, Oid outfuncoid)
+{
+	JsonbInState result;
+
+	memset(&result, 0, sizeof(JsonbInState));
+
+	datum_to_jsonb(val, false, &result, tcategory, outfuncoid, false);
+
+	return JsonbPGetDatum(JsonbValueToJsonb(result.res));
+}
+
 bool
 to_jsonb_is_immutable(Oid typoid)
 {
@@ -1191,7 +1194,6 @@ to_jsonb(PG_FUNCTION_ARGS)
 {
 	Datum		val = PG_GETARG_DATUM(0);
 	Oid			val_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
-	JsonbInState result;
 	JsonbTypeCategory tcategory;
 	Oid			outfuncoid;
 
@@ -1203,11 +1205,7 @@ to_jsonb(PG_FUNCTION_ARGS)
 	jsonb_categorize_type(val_type,
 						  &tcategory, &outfuncoid);
 
-	memset(&result, 0, sizeof(JsonbInState));
-
-	datum_to_jsonb(val, false, &result, tcategory, outfuncoid, false);
-
-	PG_RETURN_POINTER(JsonbValueToJsonb(result.res));
+	PG_RETURN_DATUM(to_jsonb_worker(val, tcategory, outfuncoid));
 }
 
 Datum
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 37bc74b658..8cc79776b4 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10067,7 +10067,9 @@ get_json_constructor_options(JsonConstructorExpr *ctor, StringInfo buf)
 	if (ctor->unique)
 		appendStringInfoString(buf, " WITH UNIQUE KEYS");
 
-	get_json_returning(ctor->returning, buf, true);
+	if (ctor->type != JSCTOR_JSON_PARSE &&
+		ctor->type != JSCTOR_JSON_SCALAR)
+		get_json_returning(ctor->returning, buf, true);
 }
 
 static void
@@ -10081,6 +10083,15 @@ get_json_constructor(JsonConstructorExpr *ctor, deparse_context *context,
 
 	switch (ctor->type)
 	{
+		case JSCTOR_JSON_PARSE:
+			funcname = "JSON";
+			break;
+		case JSCTOR_JSON_SCALAR:
+			funcname = "JSON_SCALAR";
+			break;
+		case JSCTOR_JSON_SERIALIZE:
+			funcname = "JSON_SERIALIZE";
+			break;
 		case JSCTOR_JSON_OBJECT:
 			funcname = "JSON_OBJECT";
 			break;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e99a83532e..44af6c1ebd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1797,6 +1797,41 @@ typedef struct JsonKeyValue
 	JsonValueExpr *value;		/* JSON value expression */
 } JsonKeyValue;
 
+/*
+ * JsonParseExpr -
+ *		untransformed representation of JSON()
+ */
+typedef struct JsonParseExpr
+{
+	NodeTag		type;
+	JsonValueExpr *expr;		/* string expression */
+	bool		unique_keys;	/* WITH UNIQUE KEYS? */
+	int			location;		/* token location, or -1 if unknown */
+} JsonParseExpr;
+
+/*
+ * JsonScalarExpr -
+ *		untransformed representation of JSON_SCALAR()
+ */
+typedef struct JsonScalarExpr
+{
+	NodeTag		type;
+	Expr	   *expr;			/* scalar expression */
+	int			location;		/* token location, or -1 if unknown */
+} JsonScalarExpr;
+
+/*
+ * JsonSerializeExpr -
+ *		untransformed representation of JSON_SERIALIZE() function
+ */
+typedef struct JsonSerializeExpr
+{
+	NodeTag		type;
+	JsonValueExpr *expr;		/* json value expression */
+	JsonOutput *output;			/* RETURNING clause, if specified  */
+	int			location;		/* token location, or -1 if unknown */
+}			JsonSerializeExpr;
+
 /*
  * JsonObjectConstructor -
  *		untransformed representation of JSON_OBJECT() constructor
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bbc8f1307a..796efbc0e1 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1600,7 +1600,10 @@ typedef enum JsonConstructorType
 	JSCTOR_JSON_OBJECT = 1,
 	JSCTOR_JSON_ARRAY = 2,
 	JSCTOR_JSON_OBJECTAGG = 3,
-	JSCTOR_JSON_ARRAYAGG = 4
+	JSCTOR_JSON_ARRAYAGG = 4,
+	JSCTOR_JSON_SCALAR = 5,
+	JSCTOR_JSON_SERIALIZE = 6,
+	JSCTOR_JSON_PARSE = 7
 } JsonConstructorType;
 
 /*
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 2db5d3bc00..f01eb61a2f 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -232,13 +232,15 @@ PG_KEYWORD("is", IS, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("isnull", ISNULL, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
 PG_KEYWORD("isolation", ISOLATION, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
-PG_KEYWORD("json", JSON, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json", JSON, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_array", JSON_ARRAY, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_arrayagg", JSON_ARRAYAGG, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_exists", JSON_EXISTS, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_object", JSON_OBJECT, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_objectagg", JSON_OBJECTAGG, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_query", JSON_QUERY, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json_scalar", JSON_SCALAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("json_serialize", JSON_SERIALIZE, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("json_value", JSON_VALUE, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("keep", KEEP, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index 35a9a5545d..4c0e0bd09d 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -16,11 +16,30 @@
 
 #include "lib/stringinfo.h"
 
+typedef enum					/* type categories for datum_to_json */
+{
+	JSONTYPE_NULL,				/* null, so we didn't bother to identify */
+	JSONTYPE_BOOL,				/* boolean (built-in types only) */
+	JSONTYPE_NUMERIC,			/* numeric (ditto) */
+	JSONTYPE_DATE,				/* we use special formatting for datetimes */
+	JSONTYPE_TIMESTAMP,
+	JSONTYPE_TIMESTAMPTZ,
+	JSONTYPE_JSON,				/* JSON itself (and JSONB) */
+	JSONTYPE_ARRAY,				/* array */
+	JSONTYPE_COMPOSITE,			/* composite */
+	JSONTYPE_CAST,				/* something with an explicit cast to JSON */
+	JSONTYPE_OTHER				/* all else */
+} JsonTypeCategory;
+
 /* functions in json.c */
 extern void escape_json(StringInfo buf, const char *str);
 extern char *JsonEncodeDateTime(char *buf, Datum value, Oid typid,
 								const int *tzp);
 extern bool to_json_is_immutable(Oid typoid);
+extern void json_categorize_type(Oid typoid, JsonTypeCategory *tcategory,
+								 Oid *outfuncoid);
+extern Datum to_json_worker(Datum val, JsonTypeCategory tcategory,
+							Oid outfuncoid);
 extern Datum json_build_object_worker(int nargs, Datum *args, bool *nulls,
 									  Oid *types, bool absent_on_null,
 									  bool unique_keys);
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index ac279ee535..d9e28d14ce 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -368,6 +368,22 @@ typedef struct JsonbIterator
 	struct JsonbIterator *parent;
 } JsonbIterator;
 
+/* unlike with json categories, we need to treat json and jsonb differently */
+typedef enum					/* type categories for datum_to_jsonb */
+{
+	JSONBTYPE_NULL,				/* null, so we didn't bother to identify */
+	JSONBTYPE_BOOL,				/* boolean (built-in types only) */
+	JSONBTYPE_NUMERIC,			/* numeric (ditto) */
+	JSONBTYPE_DATE,				/* we use special formatting for datetimes */
+	JSONBTYPE_TIMESTAMP,		/* we use special formatting for timestamp */
+	JSONBTYPE_TIMESTAMPTZ,		/* ... and timestamptz */
+	JSONBTYPE_JSON,				/* JSON */
+	JSONBTYPE_JSONB,			/* JSONB */
+	JSONBTYPE_ARRAY,			/* array */
+	JSONBTYPE_COMPOSITE,		/* composite */
+	JSONBTYPE_JSONCAST,			/* something with an explicit cast to JSON */
+	JSONBTYPE_OTHER				/* all else */
+} JsonbTypeCategory;
 
 /* Convenience macros */
 static inline Jsonb *
@@ -418,6 +434,7 @@ extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal,
 										 uint64 *hash, uint64 seed);
 
 /* jsonb.c support functions */
+extern Datum jsonb_from_text(text *js, bool unique_keys);
 extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
 							int estimated_len);
 extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
@@ -433,6 +450,10 @@ extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
 extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
 							   bool *isnull, bool as_text);
 extern bool to_jsonb_is_immutable(Oid typoid);
+extern void jsonb_categorize_type(Oid typoid, JsonbTypeCategory *tcategory,
+								  Oid *outfuncoid);
+extern Datum to_jsonb_worker(Datum val, JsonbTypeCategory tcategory,
+							 Oid outfuncoid);
 extern Datum jsonb_build_object_worker(int nargs, Datum *args, bool *nulls,
 									   Oid *types, bool absent_on_null,
 									   bool unique_keys);
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index 24a1e3eabf..304d135394 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -951,18 +951,22 @@ Check constraints:
 
 SELECT check_clause
 FROM information_schema.check_constraints
-WHERE constraint_name LIKE 'test_jsonb_constraint%';
+WHERE constraint_name LIKE 'test_jsonb_constraint%'
+ORDER BY 1;
                                                        check_clause                                                       
 --------------------------------------------------------------------------------------------------------------------------
+ ((JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2))
+ ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")))
+ ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb))
+ ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i))
  ((js IS JSON))
  (JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr))
- ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i))
- ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb))
- ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")))
- ((JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2))
 (6 rows)
 
-SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'test_jsonb_constraints'::regclass;
+SELECT pg_get_expr(adbin, adrelid)
+FROM pg_attrdef
+WHERE adrelid = 'test_jsonb_constraints'::regclass
+ORDER BY 1;
                                   pg_get_expr                                   
 --------------------------------------------------------------------------------
  JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 439e7faf78..615af42b8a 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1,3 +1,280 @@
+-- JSON()
+SELECT JSON();
+ERROR:  syntax error at or near ")"
+LINE 1: SELECT JSON();
+                    ^
+SELECT JSON(NULL);
+ json 
+------
+ 
+(1 row)
+
+SELECT JSON('{ "a" : 1 } ');
+     json     
+--------------
+ { "a" : 1 } 
+(1 row)
+
+SELECT JSON('{ "a" : 1 } ' FORMAT JSON);
+     json     
+--------------
+ { "a" : 1 } 
+(1 row)
+
+SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8);
+ERROR:  JSON ENCODING clause is only allowed for bytea input type
+LINE 1: SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8);
+                                   ^
+SELECT JSON('{ "a" : 1 } '::bytea FORMAT JSON ENCODING UTF8);
+     json     
+--------------
+ { "a" : 1 } 
+(1 row)
+
+SELECT pg_typeof(JSON('{ "a" : 1 } '));
+ pg_typeof 
+-----------
+ json
+(1 row)
+
+SELECT JSON('   1   '::json);
+  json   
+---------
+    1   
+(1 row)
+
+SELECT JSON('   1   '::jsonb);
+ json 
+------
+ 1
+(1 row)
+
+SELECT JSON('   1   '::json WITH UNIQUE KEYS);
+ERROR:  cannot use non-string types with WITH UNIQUE KEYS clause
+LINE 1: SELECT JSON('   1   '::json WITH UNIQUE KEYS);
+               ^
+SELECT JSON(123);
+ERROR:  cannot cast type integer to json
+LINE 1: SELECT JSON(123);
+                    ^
+SELECT JSON('{"a": 1, "a": 2}');
+       json       
+------------------
+ {"a": 1, "a": 2}
+(1 row)
+
+SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS);
+ERROR:  duplicate JSON object key value
+SELECT JSON('{"a": 1, "a": 2}' WITHOUT UNIQUE KEYS);
+       json       
+------------------
+ {"a": 1, "a": 2}
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
+         QUERY PLAN          
+-----------------------------
+ Result
+   Output: JSON('123'::json)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' FORMAT JSON);
+         QUERY PLAN          
+-----------------------------
+ Result
+   Output: JSON('123'::json)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON);
+                  QUERY PLAN                   
+-----------------------------------------------
+ Result
+   Output: JSON('\x313233'::bytea FORMAT JSON)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8);
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Result
+   Output: JSON('\x313233'::bytea FORMAT JSON ENCODING UTF8)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS);
+                  QUERY PLAN                  
+----------------------------------------------
+ Result
+   Output: JSON('123'::text WITH UNIQUE KEYS)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS);
+         QUERY PLAN          
+-----------------------------
+ Result
+   Output: JSON('123'::json)
+(2 rows)
+
+-- JSON_SCALAR()
+SELECT JSON_SCALAR();
+ERROR:  syntax error at or near ")"
+LINE 1: SELECT JSON_SCALAR();
+                           ^
+SELECT JSON_SCALAR(NULL);
+ json_scalar 
+-------------
+ 
+(1 row)
+
+SELECT JSON_SCALAR(NULL::int);
+ json_scalar 
+-------------
+ 
+(1 row)
+
+SELECT JSON_SCALAR(123);
+ json_scalar 
+-------------
+ 123
+(1 row)
+
+SELECT JSON_SCALAR(123.45);
+ json_scalar 
+-------------
+ 123.45
+(1 row)
+
+SELECT JSON_SCALAR(123.45::numeric);
+ json_scalar 
+-------------
+ 123.45
+(1 row)
+
+SELECT JSON_SCALAR(true);
+ json_scalar 
+-------------
+ true
+(1 row)
+
+SELECT JSON_SCALAR(false);
+ json_scalar 
+-------------
+ false
+(1 row)
+
+SELECT JSON_SCALAR(' 123.45');
+ json_scalar 
+-------------
+ " 123.45"
+(1 row)
+
+SELECT JSON_SCALAR('2020-06-07'::date);
+ json_scalar  
+--------------
+ "2020-06-07"
+(1 row)
+
+SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp);
+      json_scalar      
+-----------------------
+ "2020-06-07T01:02:03"
+(1 row)
+
+SELECT JSON_SCALAR('{}'::json);
+ json_scalar 
+-------------
+ {}
+(1 row)
+
+SELECT JSON_SCALAR('{}'::jsonb);
+ json_scalar 
+-------------
+ {}
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123);
+         QUERY PLAN         
+----------------------------
+ Result
+   Output: JSON_SCALAR(123)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123');
+             QUERY PLAN             
+------------------------------------
+ Result
+   Output: JSON_SCALAR('123'::text)
+(2 rows)
+
+-- JSON_SERIALIZE()
+SELECT JSON_SERIALIZE();
+ERROR:  syntax error at or near ")"
+LINE 1: SELECT JSON_SERIALIZE();
+                              ^
+SELECT JSON_SERIALIZE(NULL);
+ json_serialize 
+----------------
+ 
+(1 row)
+
+SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } '));
+ json_serialize 
+----------------
+ { "a" : 1 } 
+(1 row)
+
+SELECT JSON_SERIALIZE('{ "a" : 1 } ');
+ json_serialize 
+----------------
+ { "a" : 1 } 
+(1 row)
+
+SELECT JSON_SERIALIZE('1');
+ json_serialize 
+----------------
+ 1
+(1 row)
+
+SELECT JSON_SERIALIZE('1' FORMAT JSON);
+ json_serialize 
+----------------
+ 1
+(1 row)
+
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea);
+       json_serialize       
+----------------------------
+ \x7b20226122203a2031207d20
+(1 row)
+
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar);
+ json_serialize 
+----------------
+ { "a" : 1 } 
+(1 row)
+
+SELECT pg_typeof(JSON_SERIALIZE(NULL));
+ pg_typeof 
+-----------
+ text
+(1 row)
+
+-- only string types or bytea allowed
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb);
+ERROR:  cannot use RETURNING type jsonb in JSON_SERIALIZE()
+HINT:  Try returning a string type or bytea.
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}');
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Result
+   Output: JSON_SERIALIZE('{}'::json RETURNING text)
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
+                      QUERY PLAN                      
+------------------------------------------------------
+ Result
+   Output: JSON_SERIALIZE('{}'::json RETURNING bytea)
+(2 rows)
+
 -- JSON_OBJECT()
 SELECT JSON_OBJECT();
  json_object 
@@ -620,6 +897,13 @@ ERROR:  duplicate JSON object key value
 SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
 FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
 ERROR:  duplicate JSON object key value
+SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
+FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v);
+  json_objectagg  
+------------------
+ {"1": 1, "2": 2}
+(1 row)
+
 -- Test JSON_OBJECT deparsing
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
@@ -635,6 +919,41 @@ SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
 CREATE OR REPLACE VIEW public.json_object_view AS
  SELECT JSON_OBJECT('foo' : '1'::text FORMAT JSON, 'bar' : 'baz'::text RETURNING json) AS "json_object"
 DROP VIEW json_object_view;
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
+FROM (VALUES (1,1), (2,2)) a(k,v);
+       a       |    json_objectagg    
+---------------+----------------------
+ {"k":1,"v":1} | { "1" : 1 }
+ {"k":2,"v":2} | { "1" : 1, "2" : 2 }
+(2 rows)
+
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
+FROM (VALUES (1,1), (1,2), (2,2)) a(k,v);
+ERROR:  duplicate JSON key "1"
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL WITH UNIQUE KEYS)
+   OVER (ORDER BY k)
+FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
+ERROR:  duplicate JSON key "1"
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
+OVER (ORDER BY k)
+FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
+        a         |    json_objectagg    
+------------------+----------------------
+ {"k":1,"v":1}    | { "1" : 1 }
+ {"k":1,"v":null} | { "1" : 1 }
+ {"k":2,"v":2}    | { "1" : 1, "2" : 2 }
+(3 rows)
+
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
+OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
+        a         |    json_objectagg    
+------------------+----------------------
+ {"k":1,"v":1}    | { "1" : 1, "2" : 2 }
+ {"k":1,"v":null} | { "1" : 1, "2" : 2 }
+ {"k":2,"v":2}    | { "1" : 1, "2" : 2 }
+(3 rows)
+
 -- Test JSON_ARRAY deparsing
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index 0c3a7cc597..a3e16fe703 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -281,9 +281,13 @@ CREATE TABLE test_jsonb_constraints (
 
 SELECT check_clause
 FROM information_schema.check_constraints
-WHERE constraint_name LIKE 'test_jsonb_constraint%';
+WHERE constraint_name LIKE 'test_jsonb_constraint%'
+ORDER BY 1;
 
-SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef WHERE adrelid = 'test_jsonb_constraints'::regclass;
+SELECT pg_get_expr(adbin, adrelid)
+FROM pg_attrdef
+WHERE adrelid = 'test_jsonb_constraints'::regclass
+ORDER BY 1;
 
 INSERT INTO test_jsonb_constraints VALUES ('', 1);
 INSERT INTO test_jsonb_constraints VALUES ('1', 1);
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 4f3c06dcb3..c8d3b80c9e 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -1,3 +1,65 @@
+-- JSON()
+SELECT JSON();
+SELECT JSON(NULL);
+SELECT JSON('{ "a" : 1 } ');
+SELECT JSON('{ "a" : 1 } ' FORMAT JSON);
+SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8);
+SELECT JSON('{ "a" : 1 } '::bytea FORMAT JSON ENCODING UTF8);
+SELECT pg_typeof(JSON('{ "a" : 1 } '));
+
+SELECT JSON('   1   '::json);
+SELECT JSON('   1   '::jsonb);
+SELECT JSON('   1   '::json WITH UNIQUE KEYS);
+SELECT JSON(123);
+
+SELECT JSON('{"a": 1, "a": 2}');
+SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS);
+SELECT JSON('{"a": 1, "a": 2}' WITHOUT UNIQUE KEYS);
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' FORMAT JSON);
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON);
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8);
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS);
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS);
+
+
+-- JSON_SCALAR()
+SELECT JSON_SCALAR();
+SELECT JSON_SCALAR(NULL);
+SELECT JSON_SCALAR(NULL::int);
+SELECT JSON_SCALAR(123);
+SELECT JSON_SCALAR(123.45);
+SELECT JSON_SCALAR(123.45::numeric);
+SELECT JSON_SCALAR(true);
+SELECT JSON_SCALAR(false);
+SELECT JSON_SCALAR(' 123.45');
+SELECT JSON_SCALAR('2020-06-07'::date);
+SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp);
+SELECT JSON_SCALAR('{}'::json);
+SELECT JSON_SCALAR('{}'::jsonb);
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123);
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123');
+
+-- JSON_SERIALIZE()
+SELECT JSON_SERIALIZE();
+SELECT JSON_SERIALIZE(NULL);
+SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } '));
+SELECT JSON_SERIALIZE('{ "a" : 1 } ');
+SELECT JSON_SERIALIZE('1');
+SELECT JSON_SERIALIZE('1' FORMAT JSON);
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea);
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar);
+SELECT pg_typeof(JSON_SERIALIZE(NULL));
+
+-- only string types or bytea allowed
+SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb);
+
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}');
+EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
+
 -- JSON_OBJECT()
 SELECT JSON_OBJECT();
 SELECT JSON_OBJECT(RETURNING json);
@@ -214,6 +276,9 @@ FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
 SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
 FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
 
+SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
+FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v);
+
 -- Test JSON_OBJECT deparsing
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
@@ -225,6 +290,24 @@ SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
 
 DROP VIEW json_object_view;
 
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
+FROM (VALUES (1,1), (2,2)) a(k,v);
+
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
+FROM (VALUES (1,1), (1,2), (2,2)) a(k,v);
+
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL WITH UNIQUE KEYS)
+   OVER (ORDER BY k)
+FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
+
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
+OVER (ORDER BY k)
+FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
+
+SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
+OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
+
 -- Test JSON_ARRAY deparsing
 EXPLAIN (VERBOSE, COSTS OFF)
 SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 83446e2b8a..1e2d03c54b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1300,6 +1300,7 @@ JsonQuotes
 JsonReturning
 JsonScalarExpr
 JsonSemAction
+JsonSerializeExpr
 JsonTokenType
 JsonTransformStringValuesAction
 JsonTypeCategory
-- 
2.35.3