Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2c27346

Browse files
committed
SQL/JSON: Fix JSON_QUERY(... WITH CONDITIONAL WRAPPER)
Currently, when WITH CONDITIONAL WRAPPER is specified, array wrappersare applied even to a single SQL/JSON item if it is a scalar JSONvalue, but this behavior does not comply with the standard.To fix, apply wrappers only when there are multiple SQL/JSON itemsin 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.orgBackpatch-through: 17
1 parent7f88e50 commit2c27346

File tree

3 files changed

+48
-38
lines changed

3 files changed

+48
-38
lines changed

‎src/backend/utils/adt/jsonpath_exec.c

Lines changed: 19 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3947,7 +3947,24 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
39473947
return (Datum)0;
39483948
}
39493949

3950-
/* WRAP or not? */
3950+
/*
3951+
* Determine whether to wrap the result in a JSON array or not.
3952+
*
3953+
* First, count the number of SQL/JSON items in the returned
3954+
* JsonValueList. If the list is empty (singleton == NULL), no wrapping is
3955+
* necessary.
3956+
*
3957+
* If the wrapper mode is JSW_NONE or JSW_UNSPEC, wrapping is explicitly
3958+
* disabled. This enforces a WITHOUT WRAPPER clause, which is also the
3959+
* default when no WRAPPER clause is specified.
3960+
*
3961+
* If the mode is JSW_UNCONDITIONAL, wrapping is enforced regardless of
3962+
* the number of SQL/JSON items, enforcing a WITH WRAPPER or WITH
3963+
* UNCONDITIONAL WRAPPER clause.
3964+
*
3965+
* For JSW_CONDITIONAL, wrapping occurs only if there is more than one
3966+
* SQL/JSON item in the list, enforcing a WITH CONDITIONAL WRAPPER clause.
3967+
*/
39513968
count=JsonValueListLength(&found);
39523969
singleton=count>0 ?JsonValueListHead(&found) :NULL;
39533970
if (singleton==NULL)
@@ -3957,10 +3974,7 @@ JsonPathQuery(Datum jb, JsonPath *jp, JsonWrapper wrapper, bool *empty,
39573974
elseif (wrapper==JSW_UNCONDITIONAL)
39583975
wrap= true;
39593976
elseif (wrapper==JSW_CONDITIONAL)
3960-
wrap=count>1||
3961-
IsAJsonbScalar(singleton)||
3962-
(singleton->type==jbvBinary&&
3963-
JsonContainerIsScalar(singleton->val.binary.data));
3977+
wrap=count>1;
39643978
else
39653979
{
39663980
elog(ERROR,"unrecognized json wrapper %d", (int)wrapper);

‎src/test/regress/expected/sqljson_queryfuncs.out

Lines changed: 23 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -541,11 +541,11 @@ SELECT JSON_VALUE(NULL::jsonb, '$');
541541
(1 row)
542542

543543
SELECT
544-
JSON_QUERY(js, '$'),
545-
JSON_QUERY(js, '$' WITHOUT WRAPPER),
546-
JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
547-
JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
548-
JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
544+
JSON_QUERY(js, '$') AS "unspec",
545+
JSON_QUERY(js, '$' WITHOUT WRAPPER) AS "without",
546+
JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER) AS "with cond",
547+
JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
548+
JSON_QUERY(js, '$' WITH ARRAY WRAPPER) AS "with"
549549
FROM
550550
(VALUES
551551
(jsonb 'null'),
@@ -555,12 +555,12 @@ FROM
555555
('[1, null, "2"]'),
556556
('{"a": 1, "b": [2]}')
557557
) foo(js);
558-
json_query |json_query |json_query | json_query |json_query
558+
unspec| without|with cond|with uncond | with
559559
--------------------+--------------------+--------------------+----------------------+----------------------
560-
null | null |[null] | [null] | [null]
561-
12.3 | 12.3 |[12.3] | [12.3] | [12.3]
562-
true | true |[true] | [true] | [true]
563-
"aaa" | "aaa" |["aaa"] | ["aaa"] | ["aaa"]
560+
null | null | null | [null] | [null]
561+
12.3 | 12.3 | 12.3 | [12.3] | [12.3]
562+
true | true | true | [true] | [true]
563+
"aaa" | "aaa" | "aaa" | ["aaa"] | ["aaa"]
564564
[1, null, "2"] | [1, null, "2"] | [1, null, "2"] | [[1, null, "2"]] | [[1, null, "2"]]
565565
{"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
566566
(6 rows)
@@ -587,10 +587,10 @@ FROM
587587
--------------------+--------------------+---------------------+----------------------+----------------------
588588
| | | |
589589
| | | |
590-
null | null |[null] | [null] | [null]
591-
12.3 | 12.3 |[12.3] | [12.3] | [12.3]
592-
true | true |[true] | [true] | [true]
593-
"aaa" | "aaa" |["aaa"] | ["aaa"] | ["aaa"]
590+
null | null | null | [null] | [null]
591+
12.3 | 12.3 | 12.3 | [12.3] | [12.3]
592+
true | true | true | [true] | [true]
593+
"aaa" | "aaa" | "aaa" | ["aaa"] | ["aaa"]
594594
[1, 2, 3] | [1, 2, 3] | [1, 2, 3] | [[1, 2, 3]] | [[1, 2, 3]]
595595
{"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
596596
| | [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 ...
681681
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH CONDITIONAL WRAPPER KEEP QUOTES);
682682
json_query
683683
------------
684-
["1"]
684+
"1"
685685
(1 row)
686686

687687
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH UNCONDITIONAL WRAPPER KEEP QUOTES);
@@ -940,30 +940,30 @@ FROM
940940
x | y | list
941941
---+---+--------------
942942
0 | 0 | []
943-
0 | 1 |[1]
943+
0 | 1 |1
944944
0 | 2 | [1, 2]
945945
0 | 3 | [1, 2, 3]
946946
0 | 4 | [1, 2, 3, 4]
947947
1 | 0 | []
948-
1 | 1 |[1]
948+
1 | 1 |1
949949
1 | 2 | [1, 2]
950950
1 | 3 | [1, 2, 3]
951951
1 | 4 | [1, 2, 3, 4]
952952
2 | 0 | []
953953
2 | 1 | []
954-
2 | 2 |[2]
954+
2 | 2 |2
955955
2 | 3 | [2, 3]
956956
2 | 4 | [2, 3, 4]
957957
3 | 0 | []
958958
3 | 1 | []
959959
3 | 2 | []
960-
3 | 3 |[3]
960+
3 | 3 |3
961961
3 | 4 | [3, 4]
962962
4 | 0 | []
963963
4 | 1 | []
964964
4 | 2 | []
965965
4 | 3 | []
966-
4 | 4 |[4]
966+
4 | 4 |4
967967
(25 rows)
968968

969969
-- record type returning with quotes behavior.
@@ -1088,7 +1088,7 @@ CREATE TABLE test_jsonb_constraints (
10881088
CONSTRAINT test_jsonb_constraint3
10891089
CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
10901090
CONSTRAINT test_jsonb_constraint4
1091-
CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR)< jsonb '[10]')
1091+
CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR)= jsonb '[10]')
10921092
CONSTRAINT test_jsonb_constraint5
10931093
CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C")
10941094
);
@@ -1103,7 +1103,7 @@ Check constraints:
11031103
"test_jsonb_constraint1" CHECK (js IS JSON)
11041104
"test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
11051105
"test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
1106-
"test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR)< '[10]'::jsonb)
1106+
"test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR)= '[10]'::jsonb)
11071107
"test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) WITHOUT WRAPPER OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
11081108

11091109
SELECT check_clause
@@ -1113,7 +1113,7 @@ ORDER BY 1;
11131113
check_clause
11141114
----------------------------------------------------------------------------------------------------------------------------------------
11151115
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) WITHOUT WRAPPER OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
1116-
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR)< '[10]'::jsonb)
1116+
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR)= '[10]'::jsonb)
11171117
(JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
11181118
(js IS JSON)
11191119
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]).
11431143
INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
11441144
ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint3"
11451145
DETAIL: Failing row contains ({"a": 1}, 1, [1, 2]).
1146-
INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
1147-
ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint5"
1148-
DETAIL: Failing row contains ({"a": 7}, 1, [1, 2]).
11491146
INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
11501147
ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4"
11511148
DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).

‎src/test/regress/sql/sqljson_queryfuncs.sql

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -146,11 +146,11 @@ select json_value('{"a": "1.234"}', '$.a' returning int error on error);
146146
SELECT JSON_VALUE(NULL::jsonb,'$');
147147

148148
SELECT
149-
JSON_QUERY(js,'$'),
150-
JSON_QUERY(js,'$' WITHOUT WRAPPER),
151-
JSON_QUERY(js,'$' WITH CONDITIONAL WRAPPER),
152-
JSON_QUERY(js,'$' WITH UNCONDITIONAL ARRAY WRAPPER),
153-
JSON_QUERY(js,'$' WITH ARRAY WRAPPER)
149+
JSON_QUERY(js,'$')AS"unspec",
150+
JSON_QUERY(js,'$' WITHOUT WRAPPER)AS"without",
151+
JSON_QUERY(js,'$' WITH CONDITIONAL WRAPPER)AS"with cond",
152+
JSON_QUERY(js,'$' WITH UNCONDITIONAL ARRAY WRAPPER)AS"with uncond",
153+
JSON_QUERY(js,'$' WITH ARRAY WRAPPER)AS"with"
154154
FROM
155155
(VALUES
156156
(jsonb'null'),
@@ -331,7 +331,7 @@ CREATE TABLE test_jsonb_constraints (
331331
CONSTRAINT test_jsonb_constraint3
332332
CHECK (JSON_VALUE(js::jsonb,'$.a' RETURNINGint DEFAULT'12'ON EMPTY ERRORON ERROR)> i)
333333
CONSTRAINT test_jsonb_constraint4
334-
CHECK (JSON_QUERY(js::jsonb,'$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECTON ERROR)< jsonb'[10]')
334+
CHECK (JSON_QUERY(js::jsonb,'$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECTON ERROR)= jsonb'[10]')
335335
CONSTRAINT test_jsonb_constraint5
336336
CHECK (JSON_QUERY(js::jsonb,'$.a' RETURNINGchar(5) OMIT QUOTES EMPTY ARRAYON EMPTY)>'a' COLLATE"C")
337337
);
@@ -353,7 +353,6 @@ INSERT INTO test_jsonb_constraints VALUES ('1', 1);
353353
INSERT INTO test_jsonb_constraintsVALUES ('[]');
354354
INSERT INTO test_jsonb_constraintsVALUES ('{"b": 1}',1);
355355
INSERT INTO test_jsonb_constraintsVALUES ('{"a": 1}',1);
356-
INSERT INTO test_jsonb_constraintsVALUES ('{"a": 7}',1);
357356
INSERT INTO test_jsonb_constraintsVALUES ('{"a": 10}',1);
358357

359358
DROPTABLE test_jsonb_constraints;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp