SQL/JSON: Fix JSON_QUERY(... WITH CONDITIONAL WRAPPER)

amitlan · amitlan · commit e6c45d85dc16 · 2024-09-12T09:39:56.000+09:00
Currently, when WITH CONDITIONAL WRAPPER is specified, array wrappers are applied even to a single SQL/JSON item if it is a scalar JSON value, but this behavior does not comply with the standard. To fix, apply wrappers only when there are multiple SQL/JSON items in the result. Reported-by: Peter Eisentraut <peter@eisentraut.org> Author: Peter Eisentraut <peter@eisentraut.org> Author: Amit Langote <amitlangote09@gmail.com> Reviewed-by: Andrew Dunstan <andrew@dunslane.net> Discussion: https://postgr.es/m/8022e067-818b-45d3-8fab-6e0d94d03626%40eisentraut.org Backpatch-through: 17
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
@@ -3947,7 +3947,24 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 		return (Datum) 0;
 	}
 
-	/* WRAP or not? */
+	/*
+	 * Determine whether to wrap the result in a JSON array or not.
+	 *
+	 * First, count the number of SQL/JSON items in the returned
+	 * JsonValueList. If the list is empty (singleton == NULL), no wrapping is
+	 * necessary.
+	 *
+	 * If the wrapper mode is JSW_NONE or JSW_UNSPEC, wrapping is explicitly
+	 * disabled. This enforces a WITHOUT WRAPPER clause, which is also the
+	 * default when no WRAPPER clause is specified.
+	 *
+	 * If the mode is JSW_UNCONDITIONAL, wrapping is enforced regardless of
+	 * the number of SQL/JSON items, enforcing a WITH WRAPPER or WITH
+	 * UNCONDITIONAL WRAPPER clause.
+	 *
+	 * For JSW_CONDITIONAL, wrapping occurs only if there is more than one
+	 * SQL/JSON item in the list, enforcing a WITH CONDITIONAL WRAPPER clause.
+	 */
 	count = JsonValueListLength(&found);
 	singleton = count > 0 ? JsonValueListHead(&found) : NULL;
 	if (singleton == NULL)
@@ -3957,10 +3974,7 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
 	else if (wrapper == JSW_UNCONDITIONAL)
 		wrap = true;
 	else if (wrapper == JSW_CONDITIONAL)
-		wrap = count > 1 ||
-			IsAJsonbScalar(singleton) ||
-			(singleton->type == jbvBinary &&
-			 JsonContainerIsScalar(singleton->val.binary.data));
+		wrap = count > 1;
 	else
 	{
 		elog(ERROR, "unrecognized json wrapper %d", (int) wrapper);
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -541,11 +541,11 @@ SELECT JSON_VALUE(NULL::jsonb, '$');
 (1 row)
 
 SELECT
-	JSON_QUERY(js, '$'),
-	JSON_QUERY(js, '$' WITHOUT WRAPPER),
-	JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
-	JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
-	JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
+	JSON_QUERY(js, '$') AS "unspec",
+	JSON_QUERY(js, '$' WITHOUT WRAPPER) AS "without",
+	JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER) AS "with cond",
+	JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
+	JSON_QUERY(js, '$' WITH ARRAY WRAPPER) AS "with"
 FROM
 	(VALUES
 		(jsonb 'null'),
@@ -555,12 +555,12 @@ FROM
 		('[1, null, "2"]'),
 		('{"a": 1, "b": [2]}')
 	) foo(js);
-     json_query     |     json_query     |     json_query     |      json_query      |      json_query      
+       unspec       |      without       |     with cond      |     with uncond      |         with         
 --------------------+--------------------+--------------------+----------------------+----------------------
- null               | null               | [null]             | [null]               | [null]
- 12.3               | 12.3               | [12.3]             | [12.3]               | [12.3]
- true               | true               | [true]             | [true]               | [true]
- "aaa"              | "aaa"              | ["aaa"]            | ["aaa"]              | ["aaa"]
+ null               | null               | null               | [null]               | [null]
+ 12.3               | 12.3               | 12.3               | [12.3]               | [12.3]
+ true               | true               | true               | [true]               | [true]
+ "aaa"              | "aaa"              | "aaa"              | ["aaa"]              | ["aaa"]
  [1, null, "2"]     | [1, null, "2"]     | [1, null, "2"]     | [[1, null, "2"]]     | [[1, null, "2"]]
  {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
 (6 rows)
@@ -587,10 +587,10 @@ FROM
 --------------------+--------------------+---------------------+----------------------+----------------------
                     |                    |                     |                      | 
                     |                    |                     |                      | 
- null               | null               | [null]              | [null]               | [null]
- 12.3               | 12.3               | [12.3]              | [12.3]               | [12.3]
- true               | true               | [true]              | [true]               | [true]
- "aaa"              | "aaa"              | ["aaa"]             | ["aaa"]              | ["aaa"]
+ null               | null               | null                | [null]               | [null]
+ 12.3               | 12.3               | 12.3                | [12.3]               | [12.3]
+ true               | true               | true                | [true]               | [true]
+ "aaa"              | "aaa"              | "aaa"               | ["aaa"]              | ["aaa"]
  [1, 2, 3]          | [1, 2, 3]          | [1, 2, 3]           | [[1, 2, 3]]          | [[1, 2, 3]]
  {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]}  | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
                     |                    | [1, "2", null, [3]] | [1, "2", null, [3]]  | [1, "2", null, [3]]
@@ -681,7 +681,7 @@ LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER ...
 SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH CONDITIONAL WRAPPER KEEP QUOTES);
  json_query 
 ------------
- ["1"]
+ "1"
 (1 row)
 
 SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH UNCONDITIONAL WRAPPER KEEP QUOTES);
@@ -940,30 +940,30 @@ FROM
  x | y |     list     
 ---+---+--------------
  0 | 0 | []
- 0 | 1 | [1]
+ 0 | 1 | 1
  0 | 2 | [1, 2]
  0 | 3 | [1, 2, 3]
  0 | 4 | [1, 2, 3, 4]
  1 | 0 | []
- 1 | 1 | [1]
+ 1 | 1 | 1
  1 | 2 | [1, 2]
  1 | 3 | [1, 2, 3]
  1 | 4 | [1, 2, 3, 4]
  2 | 0 | []
  2 | 1 | []
- 2 | 2 | [2]
+ 2 | 2 | 2
  2 | 3 | [2, 3]
  2 | 4 | [2, 3, 4]
  3 | 0 | []
  3 | 1 | []
  3 | 2 | []
- 3 | 3 | [3]
+ 3 | 3 | 3
  3 | 4 | [3, 4]
  4 | 0 | []
  4 | 1 | []
  4 | 2 | []
  4 | 3 | []
- 4 | 4 | [4]
+ 4 | 4 | 4
 (25 rows)
 
 -- record type returning with quotes behavior.
@@ -1088,7 +1088,7 @@ CREATE TABLE test_jsonb_constraints (
 	CONSTRAINT test_jsonb_constraint3
 		CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
 	CONSTRAINT test_jsonb_constraint4
-		CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
+		CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) = jsonb '[10]')
 	CONSTRAINT test_jsonb_constraint5
 		CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) >  'a' COLLATE "C")
 );
@@ -1103,7 +1103,7 @@ Check constraints:
     "test_jsonb_constraint1" CHECK (js IS JSON)
     "test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
     "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
-    "test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
+    "test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) = '[10]'::jsonb)
     "test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) WITHOUT WRAPPER OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
 
 SELECT check_clause
@@ -1113,7 +1113,7 @@ ORDER BY 1;
                                                               check_clause                                                              
 ----------------------------------------------------------------------------------------------------------------------------------------
  (JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) WITHOUT WRAPPER OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
- (JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
+ (JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) = '[10]'::jsonb)
  (JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT 12 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)
@@ -1143,9 +1143,6 @@ DETAIL:  Failing row contains ({"b": 1}, 1, [1, 2]).
 INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
 ERROR:  new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint3"
 DETAIL:  Failing row contains ({"a": 1}, 1, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
-ERROR:  new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint5"
-DETAIL:  Failing row contains ({"a": 7}, 1, [1, 2]).
 INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
 ERROR:  new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4"
 DETAIL:  Failing row contains ({"a": 10}, 1, [1, 2]).
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -146,11 +146,11 @@ select json_value('{"a": "1.234"}', '$.a' returning int error on error);
 SELECT JSON_VALUE(NULL::jsonb, '$');
 
 SELECT
-	JSON_QUERY(js, '$'),
-	JSON_QUERY(js, '$' WITHOUT WRAPPER),
-	JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
-	JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
-	JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
+	JSON_QUERY(js, '$') AS "unspec",
+	JSON_QUERY(js, '$' WITHOUT WRAPPER) AS "without",
+	JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER) AS "with cond",
+	JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
+	JSON_QUERY(js, '$' WITH ARRAY WRAPPER) AS "with"
 FROM
 	(VALUES
 		(jsonb 'null'),
@@ -331,7 +331,7 @@ CREATE TABLE test_jsonb_constraints (
 	CONSTRAINT test_jsonb_constraint3
 		CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
 	CONSTRAINT test_jsonb_constraint4
-		CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
+		CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) = jsonb '[10]')
 	CONSTRAINT test_jsonb_constraint5
 		CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) >  'a' COLLATE "C")
 );
@@ -353,7 +353,6 @@ INSERT INTO test_jsonb_constraints VALUES ('1', 1);
 INSERT INTO test_jsonb_constraints VALUES ('[]');
 INSERT INTO test_jsonb_constraints VALUES ('{"b": 1}', 1);
 INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
 INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
 
 DROP TABLE test_jsonb_constraints;
