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

1 files changed, 257 insertions, 0 deletions

diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 91af0300952..0ac14153aae 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -67,7 +67,9 @@
 #include "lib/stringinfo.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
 #include "utils/builtins.h"
+#include "utils/formatting.h"
 #include "utils/json.h"
 #include "utils/jsonpath.h"
 
@@ -1077,3 +1079,258 @@ jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from, JsonPathItem *to,
 
 	return true;
 }
+
+/* SQL/JSON datatype status: */
+typedef enum JsonPathDatatypeStatus
+{
+	jpdsNonDateTime,			/* null, bool, numeric, string, array, object */
+	jpdsUnknownDateTime,		/* unknown datetime type */
+	jpdsDateTimeZoned,			/* timetz, timestamptz */
+	jpdsDateTimeNonZoned		/* time, timestamp, date */
+} JsonPathDatatypeStatus;
+
+/* Context for jspIsMutableWalker() */
+typedef struct JsonPathMutableContext
+{
+	List	   *varnames;		/* list of variable names */
+	List	   *varexprs;		/* list of variable expressions */
+	JsonPathDatatypeStatus current;	/* status of @ item */
+	bool		lax;			/* jsonpath is lax or strict */
+	bool		mutable;		/* resulting mutability status */
+} JsonPathMutableContext;
+
+/*
+ * Recursive walker for jspIsMutable()
+ */
+static JsonPathDatatypeStatus
+jspIsMutableWalker(JsonPathItem *jpi, JsonPathMutableContext *cxt)
+{
+	JsonPathItem next;
+	JsonPathDatatypeStatus status = jpdsNonDateTime;
+
+	while (!cxt->mutable)
+	{
+		JsonPathItem arg;
+		JsonPathDatatypeStatus leftStatus;
+		JsonPathDatatypeStatus rightStatus;
+
+		switch (jpi->type)
+		{
+			case jpiRoot:
+				Assert(status == jpdsNonDateTime);
+				break;
+
+			case jpiCurrent:
+				Assert(status == jpdsNonDateTime);
+				status = cxt->current;
+				break;
+
+			case jpiFilter:
+				{
+					JsonPathDatatypeStatus prevStatus = cxt->current;
+
+					cxt->current = status;
+					jspGetArg(jpi, &arg);
+					jspIsMutableWalker(&arg, cxt);
+
+					cxt->current = prevStatus;
+					break;
+				}
+
+			case jpiVariable:
+				{
+					int32		len;
+					const char *name = jspGetString(jpi, &len);
+					ListCell   *lc1;
+					ListCell   *lc2;
+
+					Assert(status == jpdsNonDateTime);
+
+					forboth(lc1, cxt->varnames, lc2, cxt->varexprs)
+					{
+						String	   *varname = lfirst_node(String, lc1);
+						Node	   *varexpr = lfirst(lc2);
+
+						if (strncmp(varname->sval, name, len))
+							continue;
+
+						switch (exprType(varexpr))
+						{
+							case DATEOID:
+							case TIMEOID:
+							case TIMESTAMPOID:
+								status = jpdsDateTimeNonZoned;
+								break;
+
+							case TIMETZOID:
+							case TIMESTAMPTZOID:
+								status = jpdsDateTimeZoned;
+								break;
+
+							default:
+								status = jpdsNonDateTime;
+								break;
+						}
+
+						break;
+					}
+					break;
+				}
+
+			case jpiEqual:
+			case jpiNotEqual:
+			case jpiLess:
+			case jpiGreater:
+			case jpiLessOrEqual:
+			case jpiGreaterOrEqual:
+				Assert(status == jpdsNonDateTime);
+				jspGetLeftArg(jpi, &arg);
+				leftStatus = jspIsMutableWalker(&arg, cxt);
+
+				jspGetRightArg(jpi, &arg);
+				rightStatus = jspIsMutableWalker(&arg, cxt);
+
+				/*
+				 * Comparison of datetime type with different timezone status
+				 * is mutable.
+				 */
+				if (leftStatus != jpdsNonDateTime &&
+					rightStatus != jpdsNonDateTime &&
+					(leftStatus == jpdsUnknownDateTime ||
+					 rightStatus == jpdsUnknownDateTime ||
+					 leftStatus != rightStatus))
+					cxt->mutable = true;
+				break;
+
+			case jpiNot:
+			case jpiIsUnknown:
+			case jpiExists:
+			case jpiPlus:
+			case jpiMinus:
+				Assert(status == jpdsNonDateTime);
+				jspGetArg(jpi, &arg);
+				jspIsMutableWalker(&arg, cxt);
+				break;
+
+			case jpiAnd:
+			case jpiOr:
+			case jpiAdd:
+			case jpiSub:
+			case jpiMul:
+			case jpiDiv:
+			case jpiMod:
+			case jpiStartsWith:
+				Assert(status == jpdsNonDateTime);
+				jspGetLeftArg(jpi, &arg);
+				jspIsMutableWalker(&arg, cxt);
+				jspGetRightArg(jpi, &arg);
+				jspIsMutableWalker(&arg, cxt);
+				break;
+
+			case jpiIndexArray:
+				for (int i = 0; i < jpi->content.array.nelems; i++)
+				{
+					JsonPathItem from;
+					JsonPathItem to;
+
+					if (jspGetArraySubscript(jpi, &from, &to, i))
+						jspIsMutableWalker(&to, cxt);
+
+					jspIsMutableWalker(&from, cxt);
+				}
+				/* FALLTHROUGH */
+
+			case jpiAnyArray:
+				if (!cxt->lax)
+					status = jpdsNonDateTime;
+				break;
+
+			case jpiAny:
+				if (jpi->content.anybounds.first > 0)
+					status = jpdsNonDateTime;
+				break;
+
+			case jpiDatetime:
+				if (jpi->content.arg)
+				{
+					char	   *template;
+					int			flags;
+
+					jspGetArg(jpi, &arg);
+					if (arg.type != jpiString)
+					{
+						status = jpdsNonDateTime;
+						break;	/* there will be runtime error */
+					}
+
+					template = jspGetString(&arg, NULL);
+					flags = datetime_format_flags(template, NULL);
+					if (flags & DCH_ZONED)
+						status = jpdsDateTimeZoned;
+					else
+						status = jpdsDateTimeNonZoned;
+				}
+				else
+				{
+					status = jpdsUnknownDateTime;
+				}
+				break;
+
+			case jpiLikeRegex:
+				Assert(status == jpdsNonDateTime);
+				jspInitByBuffer(&arg, jpi->base, jpi->content.like_regex.expr);
+				jspIsMutableWalker(&arg, cxt);
+				break;
+
+			/* literals */
+			case jpiNull:
+			case jpiString:
+			case jpiNumeric:
+			case jpiBool:
+			/* accessors */
+			case jpiKey:
+			case jpiAnyKey:
+			/* special items */
+			case jpiSubscript:
+			case jpiLast:
+			/* item methods */
+			case jpiType:
+			case jpiSize:
+			case jpiAbs:
+			case jpiFloor:
+			case jpiCeiling:
+			case jpiDouble:
+			case jpiKeyValue:
+				status = jpdsNonDateTime;
+				break;
+		}
+
+		if (!jspGetNext(jpi, &next))
+			break;
+
+		jpi = &next;
+	}
+
+	return status;
+}
+
+/*
+ * Check whether jsonpath expression is immutable or not.
+ */
+bool
+jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+{
+	JsonPathMutableContext cxt;
+	JsonPathItem jpi;
+
+	cxt.varnames = varnames;
+	cxt.varexprs = varexprs;
+	cxt.current = jpdsNonDateTime;
+	cxt.lax = (path->header & JSONPATH_LAX) != 0;
+	cxt.mutable = false;
+
+	jspInit(&jpi, path);
+	jspIsMutableWalker(&jpi, &cxt);
+
+	return cxt.mutable;
+}