Skip to content

Commit 561b74d

Browse files
amitlanamitlan
committed
Fix restriction on specifying KEEP QUOTES in JSON_QUERY()
Currently, transformJsonFuncExpr() enforces some restrictions on the combinations of QUOTES and WRAPPER clauses that can be specified in JSON_QUERY(). The intent was to only prevent the useless combination WITH WRAPPER OMIT QUOTES, but the coding prevented KEEP QUOTES too, which is not helpful. Fix that.
1 parent b453a7a commit 561b74d

File tree

5 files changed

+41
-26
lines changed

5 files changed

+41
-26
lines changed

‎src/backend/parser/parse_expr.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4300,7 +4300,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
43004300

43014301
/* OMIT QUOTES is meaningless when strings are wrapped. */
43024302
if (func->op == JSON_QUERY_OP &&
4303-
func->quotes != JS_QUOTES_UNSPEC &&
4303+
func->quotes == JS_QUOTES_OMIT &&
43044304
(func->wrapper == JSW_CONDITIONAL ||
43054305
func->wrapper == JSW_UNCONDITIONAL))
43064306
ereport(ERROR,

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

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -591,15 +591,18 @@ SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PAT
591591
["world"]
592592
(1 row)
593593

594-
-- Error: QUOTES clause meaningless when WITH WRAPPER is present
595-
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES));
596-
ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
597-
LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
598-
^
594+
-- Error: OMIT QUOTES should not be specified when WITH WRAPPER is present
599595
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER OMIT QUOTES));
600596
ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
601597
LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
602598
^
599+
-- But KEEP QUOTES (the default) is fine
600+
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES));
601+
item
602+
-----------
603+
["world"]
604+
(1 row)
605+
603606
-- Test PASSING args
604607
SELECT *
605608
FROM JSON_TABLE(

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

Lines changed: 23 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -656,35 +656,45 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"b
656656
"b
657657
(1 row)
658658

659-
-- QUOTES behavior should not be specified when WITH WRAPPER used:
659+
-- OMIT QUOTES behavior should not be specified when WITH WRAPPER used:
660660
-- Should fail
661661
SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
662662
ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
663663
LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES)...
664664
^
665-
SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
666-
ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
667-
LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES)...
668-
^
669-
SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
670-
ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
671-
LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER ...
672-
^
673665
SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
674666
ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
675667
LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER ...
676668
^
677669
-- Should succeed
678-
SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
670+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH CONDITIONAL WRAPPER KEEP QUOTES);
671+
json_query
672+
------------
673+
["1"]
674+
(1 row)
675+
676+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH UNCONDITIONAL WRAPPER KEEP QUOTES);
677+
json_query
678+
------------
679+
["1"]
680+
(1 row)
681+
682+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH WRAPPER KEEP QUOTES);
683+
json_query
684+
------------
685+
["1"]
686+
(1 row)
687+
688+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITHOUT WRAPPER OMIT QUOTES);
679689
json_query
680690
------------
681-
[1]
691+
1
682692
(1 row)
683693

684-
SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
694+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITHOUT WRAPPER KEEP QUOTES);
685695
json_query
686696
------------
687-
[1]
697+
"1"
688698
(1 row)
689699

690700
-- test QUOTES behavior.

‎src/test/regress/sql/sqljson_jsontable.sql

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -264,9 +264,10 @@ SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITHOU
264264

265265
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER));
266266

267-
-- Error: QUOTES clause meaningless when WITH WRAPPER is present
268-
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES));
267+
-- Error: OMIT QUOTES should not be specified when WITH WRAPPER is present
269268
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER OMIT QUOTES));
269+
-- But KEEP QUOTES (the default) is fine
270+
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES));
270271

271272
-- Test PASSING args
272273
SELECT *

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

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -193,15 +193,16 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2) OMIT QUOTES);
193193
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bbb' ON EMPTY);
194194
SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bbb"'::jsonb ON EMPTY);
195195

196-
-- QUOTES behavior should not be specified when WITH WRAPPER used:
196+
-- OMIT QUOTES behavior should not be specified when WITH WRAPPER used:
197197
-- Should fail
198198
SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
199-
SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
200-
SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
201199
SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
202200
-- Should succeed
203-
SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
204-
SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
201+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH CONDITIONAL WRAPPER KEEP QUOTES);
202+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH UNCONDITIONAL WRAPPER KEEP QUOTES);
203+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITH WRAPPER KEEP QUOTES);
204+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITHOUT WRAPPER OMIT QUOTES);
205+
SELECT JSON_QUERY(jsonb '["1"]', '$[*]' WITHOUT WRAPPER KEEP QUOTES);
205206

206207
-- test QUOTES behavior.
207208
SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);

0 commit comments

Comments
 (0)