Add SQL/JSON query functions

This introduces the following SQL/JSON functions for querying JSON
data using jsonpath expressions:

JSON_EXISTS(), which can be used to apply a jsonpath expression to a
JSON value to check if it yields any values.

JSON_QUERY(), which can be used to to apply a jsonpath expression to
a JSON value to get a JSON object, an array, or a string.  There are
various options to control whether multi-value result uses array
wrappers and whether the singleton scalar strings are quoted or not.

JSON_VALUE(), which can be used to apply a jsonpath expression to a
JSON value to return a single scalar value, producing an error if it
multiple values are matched.

Both JSON_VALUE() and JSON_QUERY() functions have options for
handling EMPTY and ERROR conditions, which can be used to specify
the behavior when no values are matched and when an error occurs
during jsonpath evaluation, respectively.

Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Peter Eisentraut <peter@eisentraut.org>
Author: Jian He <jian.universality@gmail.com>

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, Álvaro Herrera, Jian He, Anton A. Melnikov,
Nikita Malakhov, Peter Eisentraut, Tomas Vondra

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
Discussion: https://postgr.es/m/CA+HiwqHROpf9e644D8BRqYvaAPmgBZVup-xKMDPk-nd4EpgzHw@mail.gmail.com
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com

3 files changed, 735 insertions, 24 deletions

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 39a801a1c38..c247eefb0cc 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -653,10 +653,19 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				json_returning_clause_opt
 				json_name_and_value
 				json_aggregate_func
+				json_argument
+				json_behavior
+				json_on_error_clause_opt
 %type <list>	json_name_and_value_list
 				json_value_expr_list
 				json_array_aggregate_order_by_clause_opt
-%type <ival>	json_predicate_type_constraint
+				json_arguments
+				json_behavior_clause_opt
+				json_passing_clause_opt
+%type <ival>	json_behavior_type
+				json_predicate_type_constraint
+				json_quotes_clause_opt
+				json_wrapper_behavior
 %type <boolean>	json_key_uniqueness_constraint_opt
 				json_object_constructor_null_clause_opt
 				json_array_constructor_null_clause_opt
@@ -697,7 +706,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	CACHE CALL CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P
 	CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
 	CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT
-	COMMITTED COMPRESSION CONCURRENTLY CONFIGURATION CONFLICT
+	COMMITTED COMPRESSION CONCURRENTLY CONDITIONAL CONFIGURATION CONFLICT
 	CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY
 	COST CREATE CROSS CSV CUBE CURRENT_P
 	CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
@@ -708,8 +717,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
 	DOUBLE_P DROP
 
-	EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT
-	EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
+	EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ERROR_P ESCAPE
+	EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
 	EXTENSION EXTERNAL EXTRACT
 
 	FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR
@@ -724,10 +733,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
 	INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
 
-	JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_OBJECT JSON_OBJECTAGG
-	JSON_SCALAR JSON_SERIALIZE
+	JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
+	JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_VALUE
 
-	KEY KEYS
+	KEEP KEY KEYS
 
 	LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
 	LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
@@ -741,7 +750,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
 	NULLS_P NUMERIC
 
-	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
+	OBJECT_P OF OFF OFFSET OIDS OLD OMIT ON ONLY OPERATOR OPTION OPTIONS OR
 	ORDER ORDINALITY OTHERS OUT_P OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
@@ -750,7 +759,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
 	PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
 
-	QUOTE
+	QUOTE QUOTES
 
 	RANGE READ REAL REASSIGN RECHECK RECURSIVE REF_P REFERENCES REFERENCING
 	REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
@@ -761,7 +770,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	SEQUENCE SEQUENCES
 	SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
 	SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
-	START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
+	START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRING_P STRIP_P
 	SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
 
 	TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
@@ -769,7 +778,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	TREAT TRIGGER TRIM TRUE_P
 	TRUNCATE TRUSTED TYPE_P TYPES_P
 
-	UESCAPE UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
+	UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
 	UNLISTEN UNLOGGED UNTIL UPDATE USER USING
 
 	VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
@@ -15805,6 +15814,62 @@ func_expr_common_subexpr:
 					m->location = @1;
 					$$ = (Node *) m;
 				}
+			| JSON_QUERY '('
+				json_value_expr ',' a_expr json_passing_clause_opt
+				json_returning_clause_opt
+				json_wrapper_behavior
+				json_quotes_clause_opt
+				json_behavior_clause_opt
+			')'
+				{
+					JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
+					n->op = JSON_QUERY_OP;
+					n->context_item = (JsonValueExpr *) $3;
+					n->pathspec = $5;
+					n->passing = $6;
+					n->output = (JsonOutput *) $7;
+					n->wrapper = $8;
+					n->quotes = $9;
+					n->on_empty = (JsonBehavior *) linitial($10);
+					n->on_error = (JsonBehavior *) lsecond($10);
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| JSON_EXISTS '('
+				json_value_expr ',' a_expr json_passing_clause_opt
+				json_on_error_clause_opt
+			')'
+				{
+					JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
+					n->op = JSON_EXISTS_OP;
+					n->context_item = (JsonValueExpr *) $3;
+					n->pathspec = $5;
+					n->passing = $6;
+					n->output = NULL;
+					n->on_error = (JsonBehavior *) $7;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+			| JSON_VALUE '('
+				json_value_expr ',' a_expr json_passing_clause_opt
+				json_returning_clause_opt
+				json_behavior_clause_opt
+			')'
+				{
+					JsonFuncExpr *n = makeNode(JsonFuncExpr);
+
+					n->op = JSON_VALUE_OP;
+					n->context_item = (JsonValueExpr *) $3;
+					n->pathspec = $5;
+					n->passing = $6;
+					n->output = (JsonOutput *) $7;
+					n->on_empty = (JsonBehavior *) linitial($8);
+					n->on_error = (JsonBehavior *) lsecond($8);
+					n->location = @1;
+					$$ = (Node *) n;
+				}
 			;
 
 
@@ -16531,6 +16596,77 @@ opt_asymmetric: ASYMMETRIC
 		;
 
 /* SQL/JSON support */
+json_passing_clause_opt:
+			PASSING json_arguments					{ $$ = $2; }
+			| /*EMPTY*/								{ $$ = NIL; }
+		;
+
+json_arguments:
+			json_argument							{ $$ = list_make1($1); }
+			| json_arguments ',' json_argument		{ $$ = lappend($1, $3); }
+		;
+
+json_argument:
+			json_value_expr AS ColLabel
+			{
+				JsonArgument *n = makeNode(JsonArgument);
+
+				n->val = (JsonValueExpr *) $1;
+				n->name = $3;
+				$$ = (Node *) n;
+			}
+		;
+
+/* ARRAY is a noise word */
+json_wrapper_behavior:
+			  WITHOUT WRAPPER					{ $$ = JSW_NONE; }
+			| WITHOUT ARRAY	WRAPPER				{ $$ = JSW_NONE; }
+			| WITH WRAPPER						{ $$ = JSW_UNCONDITIONAL; }
+			| WITH ARRAY WRAPPER				{ $$ = JSW_UNCONDITIONAL; }
+			| WITH CONDITIONAL ARRAY WRAPPER	{ $$ = JSW_CONDITIONAL; }
+			| WITH UNCONDITIONAL ARRAY WRAPPER	{ $$ = JSW_UNCONDITIONAL; }
+			| WITH CONDITIONAL WRAPPER			{ $$ = JSW_CONDITIONAL; }
+			| WITH UNCONDITIONAL WRAPPER		{ $$ = JSW_UNCONDITIONAL; }
+			| /* empty */						{ $$ = JSW_UNSPEC; }
+		;
+
+json_behavior:
+			DEFAULT a_expr
+				{ $$ = (Node *) makeJsonBehavior(JSON_BEHAVIOR_DEFAULT, $2, @1); }
+			| json_behavior_type
+				{ $$ = (Node *) makeJsonBehavior($1, NULL, @1); }
+		;
+
+json_behavior_type:
+			ERROR_P		{ $$ = JSON_BEHAVIOR_ERROR; }
+			| NULL_P	{ $$ = JSON_BEHAVIOR_NULL; }
+			| TRUE_P	{ $$ = JSON_BEHAVIOR_TRUE; }
+			| FALSE_P	{ $$ = JSON_BEHAVIOR_FALSE; }
+			| UNKNOWN	{ $$ = JSON_BEHAVIOR_UNKNOWN; }
+			| EMPTY_P ARRAY	{ $$ = JSON_BEHAVIOR_EMPTY_ARRAY; }
+			| EMPTY_P OBJECT_P	{ $$ = JSON_BEHAVIOR_EMPTY_OBJECT; }
+			/* non-standard, for Oracle compatibility only */
+			| EMPTY_P	{ $$ = JSON_BEHAVIOR_EMPTY_ARRAY; }
+		;
+
+json_behavior_clause_opt:
+			json_behavior ON EMPTY_P
+				{ $$ = list_make2($1, NULL); }
+			| json_behavior ON ERROR_P
+				{ $$ = list_make2(NULL, $1); }
+			| json_behavior ON EMPTY_P json_behavior ON ERROR_P
+				{ $$ = list_make2($1, $4); }
+			| /* EMPTY */
+				{ $$ = list_make2(NULL, NULL); }
+		;
+
+json_on_error_clause_opt:
+			json_behavior ON ERROR_P
+				{ $$ = $1; }
+			| /* EMPTY */
+				{ $$ = NULL; }
+		;
+
 json_value_expr:
 			a_expr json_format_clause_opt
 			{
@@ -16575,6 +16711,14 @@ json_format_clause_opt:
 				}
 		;
 
+json_quotes_clause_opt:
+			KEEP QUOTES ON SCALAR STRING_P		{ $$ = JS_QUOTES_KEEP; }
+			| KEEP QUOTES						{ $$ = JS_QUOTES_KEEP; }
+			| OMIT QUOTES ON SCALAR STRING_P	{ $$ = JS_QUOTES_OMIT; }
+			| OMIT QUOTES						{ $$ = JS_QUOTES_OMIT; }
+			| /* EMPTY */						{ $$ = JS_QUOTES_UNSPEC; }
+		;
+
 json_returning_clause_opt:
 			RETURNING Typename json_format_clause_opt
 				{
@@ -17191,6 +17335,7 @@ unreserved_keyword:
 			| COMMIT
 			| COMMITTED
 			| COMPRESSION
+			| CONDITIONAL
 			| CONFIGURATION
 			| CONFLICT
 			| CONNECTION
@@ -17227,10 +17372,12 @@ unreserved_keyword:
 			| DOUBLE_P
 			| DROP
 			| EACH
+			| EMPTY_P
 			| ENABLE_P
 			| ENCODING
 			| ENCRYPTED
 			| ENUM_P
+			| ERROR_P
 			| ESCAPE
 			| EVENT
 			| EXCLUDE
@@ -17280,6 +17427,7 @@ unreserved_keyword:
 			| INSTEAD
 			| INVOKER
 			| ISOLATION
+			| KEEP
 			| KEY
 			| KEYS
 			| LABEL
@@ -17326,6 +17474,7 @@ unreserved_keyword:
 			| OFF
 			| OIDS
 			| OLD
+			| OMIT
 			| OPERATOR
 			| OPTION
 			| OPTIONS
@@ -17356,6 +17505,7 @@ unreserved_keyword:
 			| PROGRAM
 			| PUBLICATION
 			| QUOTE
+			| QUOTES
 			| RANGE
 			| READ
 			| REASSIGN
@@ -17415,6 +17565,7 @@ unreserved_keyword:
 			| STORAGE
 			| STORED
 			| STRICT_P
+			| STRING_P
 			| STRIP_P
 			| SUBSCRIPTION
 			| SUPPORT
@@ -17437,6 +17588,7 @@ unreserved_keyword:
 			| UESCAPE
 			| UNBOUNDED
 			| UNCOMMITTED
+			| UNCONDITIONAL
 			| UNENCRYPTED
 			| UNKNOWN
 			| UNLISTEN
@@ -17497,10 +17649,13 @@ col_name_keyword:
 			| JSON
 			| JSON_ARRAY
 			| JSON_ARRAYAGG
+			| JSON_EXISTS
 			| JSON_OBJECT
 			| JSON_OBJECTAGG
+			| JSON_QUERY
 			| JSON_SCALAR
 			| JSON_SERIALIZE
+			| JSON_VALUE
 			| LEAST
 			| MERGE_ACTION
 			| NATIONAL
@@ -17734,6 +17889,7 @@ bare_label_keyword:
 			| COMMITTED
 			| COMPRESSION
 			| CONCURRENTLY
+			| CONDITIONAL
 			| CONFIGURATION
 			| CONFLICT
 			| CONNECTION
@@ -17786,11 +17942,13 @@ bare_label_keyword:
 			| DROP
 			| EACH
 			| ELSE
+			| EMPTY_P
 			| ENABLE_P
 			| ENCODING
 			| ENCRYPTED
 			| END_P
 			| ENUM_P
+			| ERROR_P
 			| ESCAPE
 			| EVENT
 			| EXCLUDE
@@ -17860,10 +18018,14 @@ bare_label_keyword:
 			| JSON
 			| JSON_ARRAY
 			| JSON_ARRAYAGG
+			| JSON_EXISTS
 			| JSON_OBJECT
 			| JSON_OBJECTAGG
+			| JSON_QUERY
 			| JSON_SCALAR
 			| JSON_SERIALIZE
+			| JSON_VALUE
+			| KEEP
 			| KEY
 			| KEYS
 			| LABEL
@@ -17925,6 +18087,7 @@ bare_label_keyword:
 			| OFF
 			| OIDS
 			| OLD
+			| OMIT
 			| ONLY
 			| OPERATOR
 			| OPTION
@@ -17962,6 +18125,7 @@ bare_label_keyword:
 			| PROGRAM
 			| PUBLICATION
 			| QUOTE
+			| QUOTES
 			| RANGE
 			| READ
 			| REAL
@@ -18030,6 +18194,7 @@ bare_label_keyword:
 			| STORAGE
 			| STORED
 			| STRICT_P
+			| STRING_P
 			| STRIP_P
 			| SUBSCRIPTION
 			| SUBSTRING
@@ -18064,6 +18229,7 @@ bare_label_keyword:
 			| UESCAPE
 			| UNBOUNDED
 			| UNCOMMITTED
+			| UNCONDITIONAL
 			| UNENCRYPTED
 			| UNIQUE
 			| UNKNOWN
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d44b1f2ab2f..7166138bf76 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -37,6 +37,7 @@
 #include "utils/builtins.h"
 #include "utils/date.h"
 #include "utils/fmgroids.h"
+#include "utils/jsonb.h"
 #include "utils/lsyscache.h"
 #include "utils/timestamp.h"
 #include "utils/xml.h"
@@ -91,6 +92,15 @@ static Node *transformJsonParseExpr(ParseState *pstate, JsonParseExpr *expr);
 static Node *transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *expr);
 static Node *transformJsonSerializeExpr(ParseState *pstate,
 										JsonSerializeExpr *expr);
+static Node *transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *p);
+static void transformJsonPassingArgs(ParseState *pstate, const char *constructName,
+									 JsonFormatType format, List *args,
+									 List **passing_values, List **passing_names);
+static void coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr);
+static JsonBehavior *transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
+										   JsonBehaviorType default_behavior,
+										   JsonReturning *returning);
+static Node *GetJsonBehaviorConst(JsonBehaviorType btype, int location);
 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,
@@ -359,6 +369,10 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			result = transformJsonSerializeExpr(pstate, (JsonSerializeExpr *) expr);
 			break;
 
+		case T_JsonFuncExpr:
+			result = transformJsonFuncExpr(pstate, (JsonFuncExpr *) expr);
+			break;
+
 		default:
 			/* should not reach here */
 			elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
@@ -3263,7 +3277,7 @@ makeJsonByteaToTextConversion(Node *expr, JsonFormat *format, int location)
 static Node *
 transformJsonValueExpr(ParseState *pstate, const char *constructName,
 					   JsonValueExpr *ve, JsonFormatType default_format,
-					   Oid targettype)
+					   Oid targettype, bool isarg)
 {
 	Node	   *expr = transformExprRecurse(pstate, (Node *) ve->raw_expr);
 	Node	   *rawexpr;
@@ -3295,6 +3309,41 @@ transformJsonValueExpr(ParseState *pstate, const char *constructName,
 		else
 			format = ve->format->format_type;
 	}
+	else if (isarg)
+	{
+		/*
+		 * Special treatment for PASSING arguments.
+		 *
+		 * Pass types supported by GetJsonPathVar() / JsonItemFromDatum()
+		 * directly without converting to json[b].
+		 */
+		switch (exprtype)
+		{
+			case BOOLOID:
+			case NUMERICOID:
+			case INT2OID:
+			case INT4OID:
+			case INT8OID:
+			case FLOAT4OID:
+			case FLOAT8OID:
+			case TEXTOID:
+			case VARCHAROID:
+			case DATEOID:
+			case TIMEOID:
+			case TIMETZOID:
+			case TIMESTAMPOID:
+			case TIMESTAMPTZOID:
+				return expr;
+
+			default:
+				if (typcategory == TYPCATEGORY_STRING)
+					return expr;
+				/* else convert argument to json[b] type */
+				break;
+		}
+
+		format = default_format;
+	}
 	else if (exprtype == JSONOID || exprtype == JSONBOID)
 		format = JS_FORMAT_DEFAULT; /* do not format json[b] types */
 	else
@@ -3306,7 +3355,12 @@ transformJsonValueExpr(ParseState *pstate, const char *constructName,
 		Node	   *coerced;
 		bool		only_allow_cast = OidIsValid(targettype);
 
-		if (!only_allow_cast &&
+		/*
+		 * PASSING args are handled appropriately by GetJsonPathVar() /
+		 * JsonItemFromDatum().
+		 */
+		if (!isarg &&
+			!only_allow_cast &&
 			exprtype != BYTEAOID && typcategory != TYPCATEGORY_STRING)
 			ereport(ERROR,
 					errcode(ERRCODE_DATATYPE_MISMATCH),
@@ -3459,6 +3513,11 @@ transformJsonOutput(ParseState *pstate, const JsonOutput *output,
 				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				errmsg("returning SETOF types is not supported in SQL/JSON functions"));
 
+	if (get_typtype(ret->typid) == TYPTYPE_PSEUDO)
+		ereport(ERROR,
+				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("returning pseudo-types is not supported in SQL/JSON functions"));
+
 	if (ret->format->format_type == JS_FORMAT_DEFAULT)
 		/* assign JSONB format when returning jsonb, or JSON format otherwise */
 		ret->format->format_type =
@@ -3555,7 +3614,6 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr,
 	/* try to coerce expression to the output type */
 	res = coerce_to_target_type(pstate, expr, exprtype,
 								returning->typid, returning->typmod,
-	/* XXX throwing errors when casting to char(N) */
 								COERCION_EXPLICIT,
 								COERCE_EXPLICIT_CAST,
 								location);
@@ -3655,7 +3713,7 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor)
 			Node	   *val = transformJsonValueExpr(pstate, "JSON_OBJECT()",
 													 kv->value,
 													 JS_FORMAT_DEFAULT,
-													 InvalidOid);
+													 InvalidOid, false);
 
 			args = lappend(args, key);
 			args = lappend(args, val);
@@ -3842,7 +3900,7 @@ transformJsonObjectAgg(ParseState *pstate, JsonObjectAgg *agg)
 	val = transformJsonValueExpr(pstate, "JSON_OBJECTAGG()",
 								 agg->arg->value,
 								 JS_FORMAT_DEFAULT,
-								 InvalidOid);
+								 InvalidOid, false);
 	args = list_make2(key, val);
 
 	returning = transformJsonConstructorOutput(pstate, agg->constructor->output,
@@ -3898,9 +3956,8 @@ transformJsonArrayAgg(ParseState *pstate, JsonArrayAgg *agg)
 	Oid			aggfnoid;
 	Oid			aggtype;
 
-	arg = transformJsonValueExpr(pstate, "JSON_ARRAYAGG()",
-								 agg->arg,
-								 JS_FORMAT_DEFAULT, InvalidOid);
+	arg = transformJsonValueExpr(pstate, "JSON_ARRAYAGG()", agg->arg,
+								 JS_FORMAT_DEFAULT, InvalidOid, false);
 
 	returning = transformJsonConstructorOutput(pstate, agg->constructor->output,
 											   list_make1(arg));
@@ -3947,9 +4004,8 @@ transformJsonArrayConstructor(ParseState *pstate, JsonArrayConstructor *ctor)
 		{
 			JsonValueExpr *jsval = castNode(JsonValueExpr, lfirst(lc));
 			Node	   *val = transformJsonValueExpr(pstate, "JSON_ARRAY()",
-													 jsval,
-													 JS_FORMAT_DEFAULT,
-													 InvalidOid);
+													 jsval, JS_FORMAT_DEFAULT,
+													 InvalidOid, false);
 
 			args = lappend(args, val);
 		}
@@ -4108,7 +4164,7 @@ transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr)
 		 * function-like CASTs.
 		 */
 		arg = transformJsonValueExpr(pstate, "JSON()", jsexpr->expr,
-									 JS_FORMAT_JSON, returning->typid);
+									 JS_FORMAT_JSON, returning->typid, false);
 	}
 
 	return makeJsonConstructorExpr(pstate, JSCTOR_JSON_PARSE, list_make1(arg), NULL,
@@ -4153,7 +4209,7 @@ transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr)
 	Node	   *arg = transformJsonValueExpr(pstate, "JSON_SERIALIZE()",
 											 expr->expr,
 											 JS_FORMAT_JSON,
-											 InvalidOid);
+											 InvalidOid, false);
 
 	if (expr->output)
 	{
@@ -4187,3 +4243,474 @@ transformJsonSerializeExpr(ParseState *pstate, JsonSerializeExpr *expr)
 	return makeJsonConstructorExpr(pstate, JSCTOR_JSON_SERIALIZE, list_make1(arg),
 								   NULL, returning, false, false, expr->location);
 }
+
+/*
+ * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node.
+ */
+static Node *
+transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
+{
+	JsonExpr   *jsexpr;
+	Node	   *path_spec;
+	const char *func_name = NULL;
+	JsonFormatType default_format;
+
+	switch (func->op)
+	{
+		case JSON_EXISTS_OP:
+			func_name = "JSON_EXISTS";
+			default_format = JS_FORMAT_DEFAULT;
+			break;
+		case JSON_QUERY_OP:
+			func_name = "JSON_QUERY";
+			default_format = JS_FORMAT_JSONB;
+			break;
+		case JSON_VALUE_OP:
+			func_name = "JSON_VALUE";
+			default_format = JS_FORMAT_DEFAULT;
+			break;
+		default:
+			elog(ERROR, "invalid JsonFuncExpr op %d", (int) func->op);
+			break;
+	}
+
+	/*
+	 * Even though the syntax allows it, FORMAT JSON specification in
+	 * RETURNING is meaningless except for JSON_QUERY().  Flag if not
+	 * JSON_QUERY().
+	 */
+	if (func->output && func->op != JSON_QUERY_OP)
+	{
+		JsonFormat *format = func->output->returning->format;
+
+		if (format->format_type != JS_FORMAT_DEFAULT ||
+			format->encoding != JS_ENC_DEFAULT)
+			ereport(ERROR,
+					errcode(ERRCODE_SYNTAX_ERROR),
+					errmsg("cannot specify FORMAT JSON in RETURNING clause of %s()",
+						   func_name),
+					parser_errposition(pstate, format->location));
+	}
+
+	/* OMIT QUOTES is meaningless when strings are wrapped. */
+	if (func->op == JSON_QUERY_OP &&
+		func->quotes != JS_QUOTES_UNSPEC &&
+		(func->wrapper == JSW_CONDITIONAL ||
+		 func->wrapper == JSW_UNCONDITIONAL))
+		ereport(ERROR,
+				errcode(ERRCODE_SYNTAX_ERROR),
+				errmsg("SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used"),
+				parser_errposition(pstate, func->location));
+
+	jsexpr = makeNode(JsonExpr);
+	jsexpr->location = func->location;
+	jsexpr->op = func->op;
+
+	/*
+	 * jsonpath machinery can only handle jsonb documents, so coerce the input
+	 * if not already of jsonb type.
+	 */
+	jsexpr->formatted_expr = transformJsonValueExpr(pstate, func_name,
+													func->context_item,
+													default_format,
+													JSONBOID,
+													false);
+	jsexpr->format = func->context_item->format;
+
+	path_spec = transformExprRecurse(pstate, func->pathspec);
+	path_spec = coerce_to_target_type(pstate, path_spec, exprType(path_spec),
+									  JSONPATHOID, -1,
+									  COERCION_EXPLICIT, COERCE_IMPLICIT_CAST,
+									  exprLocation(path_spec));
+	if (path_spec == NULL)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("JSON path expression must be of type %s, not of type %s",
+						"jsonpath", format_type_be(exprType(path_spec))),
+				 parser_errposition(pstate, exprLocation(path_spec))));
+	jsexpr->path_spec = path_spec;
+
+	/* Transform and coerce the PASSING arguments to to jsonb. */
+	transformJsonPassingArgs(pstate, func_name,
+							 JS_FORMAT_JSONB,
+							 func->passing,
+							 &jsexpr->passing_values,
+							 &jsexpr->passing_names);
+
+	/* Transform the JsonOutput into JsonReturning. */
+	jsexpr->returning = transformJsonOutput(pstate, func->output, false);
+
+	switch (func->op)
+	{
+		case JSON_EXISTS_OP:
+			/* JSON_EXISTS returns boolean by default. */
+			if (!OidIsValid(jsexpr->returning->typid))
+			{
+				jsexpr->returning->typid = BOOLOID;
+				jsexpr->returning->typmod = -1;
+			}
+
+			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+													 JSON_BEHAVIOR_FALSE,
+													 jsexpr->returning);
+			break;
+
+		case JSON_QUERY_OP:
+			/* JSON_QUERY returns jsonb by default. */
+			if (!OidIsValid(jsexpr->returning->typid))
+			{
+				JsonReturning *ret = jsexpr->returning;
+
+				ret->typid = JSONBOID;
+				ret->typmod = -1;
+			}
+
+			/*
+			 * Keep quotes on scalar strings by default, omitting them only if
+			 * OMIT QUOTES is specified.
+			 */
+			jsexpr->omit_quotes = (func->quotes == JS_QUOTES_OMIT);
+			jsexpr->wrapper = func->wrapper;
+
+			coerceJsonExprOutput(pstate, jsexpr);
+
+			if (func->on_empty)
+				jsexpr->on_empty = transformJsonBehavior(pstate,
+														 func->on_empty,
+														 JSON_BEHAVIOR_NULL,
+														 jsexpr->returning);
+			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+													 JSON_BEHAVIOR_NULL,
+													 jsexpr->returning);
+			break;
+
+		case JSON_VALUE_OP:
+			/* JSON_VALUE returns text by default. */
+			if (!OidIsValid(jsexpr->returning->typid))
+			{
+				jsexpr->returning->typid = TEXTOID;
+				jsexpr->returning->typmod = -1;
+			}
+
+			/*
+			 * Override whatever transformJsonOutput() set these to, which
+			 * assumes that output type to be jsonb.
+			 */
+			jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT;
+			jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
+
+			/* Always omit quotes from scalar strings. */
+			jsexpr->omit_quotes = true;
+
+			coerceJsonExprOutput(pstate, jsexpr);
+
+			if (func->on_empty)
+				jsexpr->on_empty = transformJsonBehavior(pstate,
+														 func->on_empty,
+														 JSON_BEHAVIOR_NULL,
+														 jsexpr->returning);
+			jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
+													 JSON_BEHAVIOR_NULL,
+													 jsexpr->returning);
+			break;
+
+		default:
+			elog(ERROR, "invalid JsonFuncExpr op %d", (int) func->op);
+			break;
+	}
+
+	return (Node *) jsexpr;
+}
+
+/*
+ * Transform a SQL/JSON PASSING clause.
+ */
+static void
+transformJsonPassingArgs(ParseState *pstate, const char *constructName,
+						 JsonFormatType format, List *args,
+						 List **passing_values, List **passing_names)
+{
+	ListCell   *lc;
+
+	*passing_values = NIL;
+	*passing_names = NIL;
+
+	foreach(lc, args)
+	{
+		JsonArgument *arg = castNode(JsonArgument, lfirst(lc));
+		Node	   *expr = transformJsonValueExpr(pstate, constructName,
+												  arg->val, format,
+												  InvalidOid, true);
+
+		*passing_values = lappend(*passing_values, expr);
+		*passing_names = lappend(*passing_names, makeString(arg->name));
+	}
+}
+
+/*
+ * Set up to coerce the result value of JSON_VALUE() / JSON_QUERY() to the
+ * RETURNING type (default or user-specified), if needed.
+ */
+static void
+coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr)
+{
+	JsonReturning *returning = jsexpr->returning;
+	Node	   *context_item = jsexpr->formatted_expr;
+	int			default_typmod;
+	Oid			default_typid;
+	bool		omit_quotes =
+		jsexpr->op == JSON_QUERY_OP && jsexpr->omit_quotes;
+	Node	   *coercion_expr = NULL;
+
+	Assert(returning);
+
+	/*
+	 * Check for cases where the coercion should be handled at runtime, that
+	 * is, without using a cast expression.
+	 */
+	if (jsexpr->op == JSON_VALUE_OP)
+	{
+		/*
+		 * Use cast expressions for types with typmod and domain types.
+		 */
+		if (returning->typmod == -1 &&
+			get_typtype(returning->typid) != TYPTYPE_DOMAIN)
+		{
+			jsexpr->use_io_coercion = true;
+			return;
+		}
+	}
+	else if (jsexpr->op == JSON_QUERY_OP)
+	{
+		/*
+		 * Cast functions from jsonb to the following types (jsonb_bool() et
+		 * al) don't handle errors softly, so coerce either by calling
+		 * json_populate_type() or the type's input function so that any
+		 * errors are handled appropriately. The latter only if OMIT QUOTES is
+		 * true.
+		 */
+		switch (returning->typid)
+		{
+			case BOOLOID:
+			case NUMERICOID:
+			case INT2OID:
+			case INT4OID:
+			case INT8OID:
+			case FLOAT4OID:
+			case FLOAT8OID:
+				if (jsexpr->omit_quotes)
+					jsexpr->use_io_coercion = true;
+				else
+					jsexpr->use_json_coercion = true;
+				return;
+			default:
+				break;
+		}
+	}
+
+	/* Look up a cast expression. */
+
+	/*
+	 * For JSON_VALUE() and for JSON_QUERY() when OMIT QUOTES is true,
+	 * ExecEvalJsonExprPath() will convert a quote-stripped source value to
+	 * its text representation, so use TEXTOID as the source type.
+	 */
+	if (omit_quotes || jsexpr->op == JSON_VALUE_OP)
+	{
+		default_typid = TEXTOID;
+		default_typmod = -1;
+	}
+	else
+	{
+		default_typid = exprType(context_item);
+		default_typmod = exprTypmod(context_item);
+	}
+
+	if (returning->typid != default_typid ||
+		returning->typmod != default_typmod)
+	{
+		/*
+		 * We abuse CaseTestExpr here as placeholder to pass the result of
+		 * jsonpath evaluation as input to the coercion expression.
+		 */
+		CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+
+		placeholder->typeId = default_typid;
+		placeholder->typeMod = default_typmod;
+
+		coercion_expr = coerceJsonFuncExpr(pstate, (Node *) placeholder,
+										   returning, false);
+		if (coercion_expr == (Node *) placeholder)
+			coercion_expr = NULL;
+	}
+
+	jsexpr->coercion_expr = coercion_expr;
+
+	if (coercion_expr == NULL)
+	{
+		/*
+		 * Either no cast was found or coercion is unnecessary but still must
+		 * convert the string value to the output type.
+		 */
+		if (omit_quotes || jsexpr->op == JSON_VALUE_OP)
+			jsexpr->use_io_coercion = true;
+		else
+			jsexpr->use_json_coercion = true;
+	}
+
+	Assert(jsexpr->coercion_expr != NULL ||
+		   (jsexpr->use_io_coercion != jsexpr->use_json_coercion));
+}
+
+/*
+ * Transform a JSON BEHAVIOR clause.
+ */
+static JsonBehavior *
+transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
+					  JsonBehaviorType default_behavior,
+					  JsonReturning *returning)
+{
+	JsonBehaviorType btype = default_behavior;
+	Node	   *expr = NULL;
+	bool		coerce_at_runtime = false;
+	int			location = -1;
+
+	if (behavior)
+	{
+		btype = behavior->btype;
+		location = behavior->location;
+		if (btype == JSON_BEHAVIOR_DEFAULT)
+		{
+			expr = transformExprRecurse(pstate, behavior->expr);
+			if (!IsA(expr, Const) && !IsA(expr, FuncExpr) &&
+				!IsA(expr, OpExpr))
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("can only specify constant, non-aggregate"
+								" function, or operator expression for"
+								" DEFAULT"),
+						 parser_errposition(pstate, exprLocation(expr))));
+			if (contain_var_clause(expr))
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("DEFAULT expression must not contain column references"),
+						 parser_errposition(pstate, exprLocation(expr))));
+			if (expression_returns_set(expr))
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("DEFAULT expression must not return a set"),
+						 parser_errposition(pstate, exprLocation(expr))));
+		}
+	}
+
+	if (expr == NULL && btype != JSON_BEHAVIOR_ERROR)
+		expr = GetJsonBehaviorConst(btype, location);
+
+	if (expr)
+	{
+		Node	   *coerced_expr = expr;
+		bool		isnull = (IsA(expr, Const) && ((Const *) expr)->constisnull);
+
+		/*
+		 * Coerce NULLs and "internal" (that is, not specified by the user)
+		 * jsonb-valued expressions at runtime using json_populate_type().
+		 *
+		 * For other (user-specified) non-NULL values, try to find a cast and
+		 * error out if one is not found.
+		 */
+		if (isnull ||
+			(exprType(expr) == JSONBOID &&
+			 btype == default_behavior))
+			coerce_at_runtime = true;
+		else
+			coerced_expr =
+				coerce_to_target_type(pstate, expr, exprType(expr),
+									  returning->typid, returning->typmod,
+									  COERCION_EXPLICIT, COERCE_EXPLICIT_CAST,
+									  exprLocation((Node *) behavior));
+
+		if (coerced_expr == NULL)
+			ereport(ERROR,
+					errcode(ERRCODE_CANNOT_COERCE),
+					errmsg("cannot cast behavior expression of type %s to %s",
+						   format_type_be(exprType(expr)),
+						   format_type_be(returning->typid)),
+					parser_errposition(pstate, exprLocation(expr)));
+		else
+			expr = coerced_expr;
+	}
+
+	if (behavior)
+		behavior->expr = expr;
+	else
+		behavior = makeJsonBehavior(btype, expr, location);
+
+	behavior->coerce = coerce_at_runtime;
+
+	return behavior;
+}
+
+/*
+ * Returns a Const node holding the value for the given non-ERROR
+ * JsonBehaviorType.
+ */
+static Node *
+GetJsonBehaviorConst(JsonBehaviorType btype, int location)
+{
+	Datum		val = (Datum) 0;
+	Oid			typid = JSONBOID;
+	int			len = -1;
+	bool		isbyval = false;
+	bool		isnull = false;
+	Const	   *con;
+
+	switch (btype)
+	{
+		case JSON_BEHAVIOR_EMPTY_ARRAY:
+			val = DirectFunctionCall1(jsonb_in, CStringGetDatum("[]"));
+			break;
+
+		case JSON_BEHAVIOR_EMPTY_OBJECT:
+			val = DirectFunctionCall1(jsonb_in, CStringGetDatum("{}"));
+			break;
+
+		case JSON_BEHAVIOR_TRUE:
+			val = BoolGetDatum(true);
+			typid = BOOLOID;
+			len = sizeof(bool);
+			isbyval = true;
+			break;
+
+		case JSON_BEHAVIOR_FALSE:
+			val = BoolGetDatum(false);
+			typid = BOOLOID;
+			len = sizeof(bool);
+			isbyval = true;
+			break;
+
+		case JSON_BEHAVIOR_NULL:
+		case JSON_BEHAVIOR_UNKNOWN:
+		case JSON_BEHAVIOR_EMPTY:
+			val = (Datum) 0;
+			isnull = true;
+			typid = INT4OID;
+			len = sizeof(int32);
+			isbyval = true;
+			break;
+
+			/* These two behavior types are handled by the caller. */
+		case JSON_BEHAVIOR_DEFAULT:
+		case JSON_BEHAVIOR_ERROR:
+			Assert(false);
+			break;
+
+		default:
+			elog(ERROR, "unrecognized SQL/JSON behavior %d", btype);
+			break;
+	}
+
+	con = makeConst(typid, -1, InvalidOid, len, val, isnull, isbyval);
+	con->location = location;
+
+	return (Node *) con;
+}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index ea522b932b2..1276f336041 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -2006,6 +2006,24 @@ FigureColnameInternal(Node *node, char **name)
 			/* make JSON_ARRAYAGG act like a regular function */
 			*name = "json_arrayagg";
 			return 2;
+		case T_JsonFuncExpr:
+			/* make SQL/JSON functions act like a regular function */
+			switch (((JsonFuncExpr *) node)->op)
+			{
+				case JSON_EXISTS_OP:
+					*name = "json_exists";
+					return 2;
+				case JSON_QUERY_OP:
+					*name = "json_query";
+					return 2;
+				case JSON_VALUE_OP:
+					*name = "json_value";
+					return 2;
+				default:
+					elog(ERROR, "unrecognized JsonExpr op: %d",
+						 (int) ((JsonFuncExpr *) node)->op);
+			}
+			break;
 		default:
 			break;
 	}