From 106f39fded2bcdfd9a7ff47bb56f35c806b94d24 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut 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