v2-0001-WIP-Fix-JSON_QUERY-WITH-CONDITIONAL-WRAPPER.patch
application/octet-stream
Filename: v2-0001-WIP-Fix-JSON_QUERY-WITH-CONDITIONAL-WRAPPER.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v2-0001
Subject: WIP: Fix JSON_QUERY WITH CONDITIONAL WRAPPER
| File | + | − |
|---|---|---|
| src/backend/utils/adt/jsonpath_exec.c | 19 | 5 |
| src/test/regress/expected/sqljson_queryfuncs.out | 21 | 21 |
| src/test/regress/sql/sqljson_queryfuncs.sql | 5 | 5 |
From 106f39fded2bcdfd9a7ff47bb56f35c806b94d24 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 4 Sep 2024 12:09:35 +0200
Subject: [PATCH v2] WIP: Fix JSON_QUERY WITH CONDITIONAL WRAPPER
---
src/backend/utils/adt/jsonpath_exec.c | 24 ++++++++---
.../regress/expected/sqljson_queryfuncs.out | 42 +++++++++----------
src/test/regress/sql/sqljson_queryfuncs.sql | 10 ++---
3 files changed, 45 insertions(+), 31 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index e3ee0093d4..e569c7efb8 100644
--- 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
index 73d7d2117e..378cd9f4cc 100644
--- 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.
@@ -1147,7 +1147,7 @@ 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"
+ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint5"
DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
DROP TABLE test_jsonb_constraints;
-- Test mutabilily of query functions
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index 21ff7787a2..a7342416a9 100644
--- 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'),
--
2.43.0