SQL/JSON: add standard JSON constructor functions

This commit introduces the SQL/JSON standard-conforming constructors for
JSON types:

JSON_ARRAY()
JSON_ARRAYAGG()
JSON_OBJECT()
JSON_OBJECTAGG()

Most of the functionality was already present in PostgreSQL-specific
functions, but these include some new functionality such as the ability
to skip or include NULL values, and to allow duplicate keys or throw
error when they are found, as well as the standard specified syntax to
specify output type and format.

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: Amit Langote <amitlangote09@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.

Discussion: https://postgr.es/m/CAF4Au4w2x-5LTnN_bxky-mq4=WOqsGsxSpENCzHRAzSnEd8+WQ@mail.gmail.com
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

1 files changed, 41 insertions, 15 deletions

diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index e5b1ebf0c36..eefa429b9c3 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -64,7 +64,8 @@ static int	lengthCompareJsonbStringValue(const void *a, const void *b);
 static int	lengthCompareJsonbString(const char *val1, int len1,
 									 const char *val2, int len2);
 static int	lengthCompareJsonbPair(const void *a, const void *b, void *binequal);
-static void uniqueifyJsonbObject(JsonbValue *object);
+static void uniqueifyJsonbObject(JsonbValue *object, bool unique_keys,
+								 bool skip_nulls);
 static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate,
 										JsonbIteratorToken seq,
 										JsonbValue *scalarVal);
@@ -689,7 +690,9 @@ pushJsonbValueScalar(JsonbParseState **pstate, JsonbIteratorToken seq,
 			appendElement(*pstate, scalarVal);
 			break;
 		case WJB_END_OBJECT:
-			uniqueifyJsonbObject(&(*pstate)->contVal);
+			uniqueifyJsonbObject(&(*pstate)->contVal,
+								 (*pstate)->unique_keys,
+								 (*pstate)->skip_nulls);
 			/* fall through! */
 		case WJB_END_ARRAY:
 			/* Steps here common to WJB_END_OBJECT case */
@@ -732,6 +735,9 @@ pushState(JsonbParseState **pstate)
 	JsonbParseState *ns = palloc(sizeof(JsonbParseState));
 
 	ns->next = *pstate;
+	ns->unique_keys = false;
+	ns->skip_nulls = false;
+
 	return ns;
 }
 
@@ -1936,7 +1942,7 @@ lengthCompareJsonbPair(const void *a, const void *b, void *binequal)
  * Sort and unique-ify pairs in JsonbValue object
  */
 static void
-uniqueifyJsonbObject(JsonbValue *object)
+uniqueifyJsonbObject(JsonbValue *object, bool unique_keys, bool skip_nulls)
 {
 	bool		hasNonUniq = false;
 
@@ -1946,23 +1952,43 @@ uniqueifyJsonbObject(JsonbValue *object)
 		qsort_arg(object->val.object.pairs, object->val.object.nPairs, sizeof(JsonbPair),
 				  lengthCompareJsonbPair, &hasNonUniq);
 
-	if (hasNonUniq)
+	if (hasNonUniq && unique_keys)
+		ereport(ERROR,
+				errcode(ERRCODE_DUPLICATE_JSON_OBJECT_KEY_VALUE),
+				errmsg("duplicate JSON object key"));
+
+	if (hasNonUniq || skip_nulls)
 	{
-		JsonbPair  *ptr = object->val.object.pairs + 1,
-				   *res = object->val.object.pairs;
+		JsonbPair  *ptr,
+				   *res;
+
+		while (skip_nulls && object->val.object.nPairs > 0 &&
+			   object->val.object.pairs->value.type == jbvNull)
+		{
+			/* If skip_nulls is true, remove leading items with null */
+			object->val.object.pairs++;
+			object->val.object.nPairs--;
+		}
 
-		while (ptr - object->val.object.pairs < object->val.object.nPairs)
+		if (object->val.object.nPairs > 0)
 		{
-			/* Avoid copying over duplicate */
-			if (lengthCompareJsonbStringValue(ptr, res) != 0)
+			ptr = object->val.object.pairs + 1;
+			res = object->val.object.pairs;
+
+			while (ptr - object->val.object.pairs < object->val.object.nPairs)
 			{
-				res++;
-				if (ptr != res)
-					memcpy(res, ptr, sizeof(JsonbPair));
+				/* Avoid copying over duplicate or null */
+				if (lengthCompareJsonbStringValue(ptr, res) != 0 &&
+					(!skip_nulls || ptr->value.type != jbvNull))
+				{
+					res++;
+					if (ptr != res)
+						memcpy(res, ptr, sizeof(JsonbPair));
+				}
+				ptr++;
 			}
-			ptr++;
-		}
 
-		object->val.object.nPairs = res + 1 - object->val.object.pairs;
+			object->val.object.nPairs = res + 1 - object->val.object.pairs;
+		}
 	}
 }