SQL/JSON query functions

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

JSON_EXISTS()
JSON_QUERY()
JSON_VALUE()

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

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

Nikita Glukhov

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

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

4 files changed, 176 insertions, 0 deletions

diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 380940968bd..872f2f0828f 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -109,6 +109,7 @@ extern VacuumRelation *makeVacuumRelation(RangeVar *relation, Oid oid, List *va_
 extern JsonFormat *makeJsonFormat(JsonFormatType type, JsonEncoding encoding,
 								  int location);
 extern JsonValueExpr *makeJsonValueExpr(Expr *expr, JsonFormat *format);
+extern JsonBehavior *makeJsonBehavior(JsonBehaviorType type, Node *expr);
 extern Node *makeJsonKeyValue(Node *key, Node *value);
 extern Node *makeJsonIsPredicate(Node *expr, JsonFormat *format,
 								 JsonValueType vtype, bool unique_keys,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index e8f30367a48..d48147abeef 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -206,6 +206,9 @@ typedef enum NodeTag
 	T_JsonReturning,
 	T_JsonValueExpr,
 	T_JsonConstructorExpr,
+	T_JsonExpr,
+	T_JsonCoercion,
+	T_JsonItemCoercions,
 
 	/*
 	 * TAGS FOR EXPRESSION STATE NODES (execnodes.h)
@@ -505,8 +508,12 @@ typedef enum NodeTag
 	T_JsonAggConstructor,
 	T_JsonObjectAgg,
 	T_JsonArrayAgg,
+	T_JsonFuncExpr,
 	T_JsonIsPredicate,
+	T_JsonCommon,
+	T_JsonArgument,
 	T_JsonKeyValue,
+	T_JsonBehavior,
 	T_JsonOutput,
 
 	/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6bf212b01a1..0ff4ba08846 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1595,6 +1595,23 @@ typedef struct TriggerTransition
 /* Nodes for SQL/JSON support */
 
 /*
+ * JsonQuotes -
+ *		representation of [KEEP|OMIT] QUOTES clause for JSON_QUERY()
+ */
+typedef enum JsonQuotes
+{
+	JS_QUOTES_UNSPEC,			/* unspecified */
+	JS_QUOTES_KEEP,				/* KEEP QUOTES */
+	JS_QUOTES_OMIT				/* OMIT QUOTES */
+} JsonQuotes;
+
+/*
+ * JsonPathSpec -
+ *		representation of JSON path constant
+ */
+typedef char *JsonPathSpec;
+
+/*
  * JsonOutput -
  *		representation of JSON output clause (RETURNING type [FORMAT format])
  */
@@ -1606,6 +1623,48 @@ typedef struct JsonOutput
 } JsonOutput;
 
 /*
+ * JsonArgument -
+ *		representation of argument from JSON PASSING clause
+ */
+typedef struct JsonArgument
+{
+	NodeTag		type;
+	JsonValueExpr *val;			/* argument value expression */
+	char	   *name;			/* argument name */
+} JsonArgument;
+
+/*
+ * JsonCommon -
+ *		representation of common syntax of functions using JSON path
+ */
+typedef struct JsonCommon
+{
+	NodeTag		type;
+	JsonValueExpr *expr;		/* context item expression */
+	Node	   *pathspec;		/* JSON path specification expression */
+	char	   *pathname;		/* path name, if any */
+	List	   *passing;		/* list of PASSING clause arguments, if any */
+	int			location;		/* token location, or -1 if unknown */
+} JsonCommon;
+
+/*
+ * JsonFuncExpr -
+ *		untransformed representation of JSON function expressions
+ */
+typedef struct JsonFuncExpr
+{
+	NodeTag		type;
+	JsonExprOp	op;				/* expression type */
+	JsonCommon *common;			/* common syntax */
+	JsonOutput *output;			/* output clause, if specified */
+	JsonBehavior *on_empty;		/* ON EMPTY behavior, if specified */
+	JsonBehavior *on_error;		/* ON ERROR behavior, if specified */
+	JsonWrapper	wrapper;		/* array wrapper behavior (JSON_QUERY only) */
+	bool		omit_quotes;	/* omit or keep quotes? (JSON_QUERY only) */
+	int			location;		/* token location, or -1 if unknown */
+} JsonFuncExpr;
+
+/*
  * JsonKeyValue -
  *		untransformed representation of JSON object key-value pair for
  *		JSON_OBJECT() and JSON_OBJECTAGG()
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index f4a39653ac6..7ebe868af91 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1234,6 +1234,17 @@ typedef struct XmlExpr
 } XmlExpr;
 
 /*
+ * JsonExprOp -
+ *		enumeration of JSON functions using JSON path
+ */
+typedef enum JsonExprOp
+{
+	JSON_VALUE_OP,				/* JSON_VALUE() */
+	JSON_QUERY_OP,				/* JSON_QUERY() */
+	JSON_EXISTS_OP				/* JSON_EXISTS() */
+} JsonExprOp;
+
+/*
  * JsonEncoding -
  *		representation of JSON ENCODING clause
  */
@@ -1257,6 +1268,37 @@ typedef enum JsonFormatType
 } JsonFormatType;
 
 /*
+ * JsonBehaviorType -
+ *		enumeration of behavior types used in JSON ON ... BEHAVIOR clause
+ *
+ * 		If enum members are reordered, get_json_behavior() from ruleutils.c
+ * 		must be updated accordingly.
+ */
+typedef enum JsonBehaviorType
+{
+	JSON_BEHAVIOR_NULL = 0,
+	JSON_BEHAVIOR_ERROR,
+	JSON_BEHAVIOR_EMPTY,
+	JSON_BEHAVIOR_TRUE,
+	JSON_BEHAVIOR_FALSE,
+	JSON_BEHAVIOR_UNKNOWN,
+	JSON_BEHAVIOR_EMPTY_ARRAY,
+	JSON_BEHAVIOR_EMPTY_OBJECT,
+	JSON_BEHAVIOR_DEFAULT
+} JsonBehaviorType;
+
+/*
+ * JsonWrapper -
+ *		representation of WRAPPER clause for JSON_QUERY()
+ */
+typedef enum JsonWrapper
+{
+	JSW_NONE,
+	JSW_CONDITIONAL,
+	JSW_UNCONDITIONAL,
+} JsonWrapper;
+
+/*
  * JsonFormat -
  *		representation of JSON FORMAT clause
  */
@@ -1343,6 +1385,73 @@ typedef struct JsonIsPredicate
 	int			location;		/* token location, or -1 if unknown */
 } JsonIsPredicate;
 
+/*
+ * JsonBehavior -
+ *		representation of JSON ON ... BEHAVIOR clause
+ */
+typedef struct JsonBehavior
+{
+	NodeTag		type;
+	JsonBehaviorType btype;		/* behavior type */
+	Node	   *default_expr;	/* default expression, if any */
+} JsonBehavior;
+
+/*
+ * JsonCoercion -
+ *		coercion from SQL/JSON item types to SQL types
+ */
+typedef struct JsonCoercion
+{
+	NodeTag		type;
+	Node	   *expr;			/* resulting expression coerced to target type */
+	bool		via_populate;	/* coerce result using json_populate_type()? */
+	bool		via_io;			/* coerce result using type input function? */
+	Oid			collation;		/* collation for coercion via I/O or populate */
+} JsonCoercion;
+
+/*
+ * JsonItemCoercions -
+ *		expressions for coercion from SQL/JSON item types directly to the
+ *		output SQL type
+ */
+typedef struct JsonItemCoercions
+{
+	NodeTag		type;
+	JsonCoercion *null;
+	JsonCoercion *string;
+	JsonCoercion *numeric;
+	JsonCoercion *boolean;
+	JsonCoercion *date;
+	JsonCoercion *time;
+	JsonCoercion *timetz;
+	JsonCoercion *timestamp;
+	JsonCoercion *timestamptz;
+	JsonCoercion *composite;	/* arrays and objects */
+} JsonItemCoercions;
+
+/*
+ * JsonExpr -
+ *		transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS()
+ */
+typedef struct JsonExpr
+{
+	Expr		xpr;
+	JsonExprOp	op;				/* json function ID */
+	Node	   *formatted_expr;	/* formatted context item expression */
+	JsonCoercion *result_coercion;	/* resulting coercion to RETURNING type */
+	JsonFormat *format;			/* context item format (JSON/JSONB) */
+	Node	   *path_spec;		/* JSON path specification expression */
+	List	   *passing_names;	/* PASSING argument names */
+	List	   *passing_values;	/* PASSING argument values */
+	JsonReturning *returning;	/* RETURNING clause type/format info */
+	JsonBehavior *on_empty;		/* ON EMPTY behavior */
+	JsonBehavior *on_error;		/* ON ERROR behavior */
+	JsonItemCoercions *coercions; /* coercions for JSON_VALUE */
+	JsonWrapper	wrapper;		/* WRAPPER for JSON_QUERY */
+	bool		omit_quotes;	/* KEEP/OMIT QUOTES for JSON_QUERY */
+	int			location;		/* token location, or -1 if unknown */
+} JsonExpr;
+
 /* ----------------
  * NullTest
  *