v1-0004-refactor-json_table-wrapper-and-quotes-behavior.no-cfbot
application/octet-stream
Filename: v1-0004-refactor-json_table-wrapper-and-quotes-behavior.no-cfbot
Type: application/octet-stream
Part: 2
Message:
Re: remaining sql/json patches
From df0e2575491e13106ec02685efb0681168cefcae Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 3 Jan 2024 16:19:32 +0800
Subject: [PATCH v1 4/4] refactor json_table wrapper and quotes behavior.
After refactor, quotes, wrapper behavior will consistent with json_value, json_query.
WITH WRAPPER and QUOTES behavior are mutually exclusive, if both occurs, then raise a error,
also make the error message consistent with json_query.
If wrapper is not none, then make the json_table column type to JTC_FORMATTED,
so under the hood, it will call json_query, not json_value.
I also add some tests to it.
---
src/backend/parser/parse_jsontable.c | 21 +++----------
src/test/regress/expected/jsonb_sqljson.out | 34 ++++++++++++++++-----
src/test/regress/sql/jsonb_sqljson.sql | 5 ++-
3 files changed, 35 insertions(+), 25 deletions(-)
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index ee9123c3..62dda4ac 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -478,20 +478,8 @@ appendJsonTableColumns(JsonTableParseContext * cxt, List *columns)
* For scalar columns, require an explicit FORMAT JSON clause
* to get the specified WRAPPER and QUOTES behavior.
*/
- if (typeIsComposite(typid))
+ if (typeIsComposite(typid) || rawc->wrapper != JSW_NONE)
rawc->coltype = JTC_FORMATTED;
- else if (rawc->wrapper != JSW_NONE)
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot use WITH WRAPPER clause for scalar columns"
- " without also specifying FORMAT clause"),
- parser_errposition(pstate, rawc->location)));
- else if (rawc->quotes != JS_QUOTES_UNSPEC)
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot use OMIT/KEEP QUOTES clause for scalar columns"
- " without also specifying FORMAT clause"),
- parser_errposition(pstate, rawc->location)));
/* FALLTHROUGH */
case JTC_EXISTS:
@@ -507,10 +495,9 @@ appendJsonTableColumns(JsonTableParseContext * cxt, List *columns)
if (rawc->wrapper != JSW_NONE &&
rawc->quotes != JS_QUOTES_UNSPEC)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot use WITH WRAPPER clause for formatted colunmns"
- " without also specifying OMIT/KEEP QUOTES"),
- parser_errposition(pstate, rawc->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used"),
+ parser_errposition(pstate, rawc->location));
je = transformJsonTableColumn(rawc, (Node *) param,
NIL, errorOnError);
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index f6a2c1fc..c04a2e09 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -1499,28 +1499,48 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
ERROR: cannot cast type boolean to jsonb
LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
^
--- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns don't work without an
+-- JSON_TABLE: WRAPPER/QUOTES behavior
-- explicit FORMAT clause
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
-ERROR: cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
-LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
- ^
+ item
+---------
+ "world"
+(1 row)
+
+SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' OMIT QUOTES ON SCALAR STRING));
+ item
+-------
+ world
+(1 row)
+
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' KEEP QUOTES ON SCALAR STRING));
item
---------
"world"
(1 row)
+SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' OMIT QUOTES ON SCALAR STRING));
+ item
+-------
+ world
+(1 row)
+
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER));
-ERROR: cannot use WITH WRAPPER clause for scalar columns without also specifying FORMAT clause
-LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
- ^
+ item
+-----------
+ ["world"]
+(1 row)
+
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER));
item
-----------
["world"]
(1 row)
+SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER KEEP QUOTES));
+ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
+LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
+ ^
-- JSON_TABLE: nested paths and plans
-- Should fail (JSON_TABLE columns must contain explicit AS path
-- specifications if explicit PLAN clause is used)
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index bc028398..6f5d91db 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -549,12 +549,15 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'))
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
--- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns don't work without an
+-- JSON_TABLE: WRAPPER/QUOTES behavior
-- explicit FORMAT clause
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
+SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' OMIT QUOTES ON SCALAR STRING));
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' KEEP QUOTES ON SCALAR STRING));
+SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' OMIT QUOTES ON SCALAR STRING));
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER));
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER));
+SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER KEEP QUOTES));
-- JSON_TABLE: nested paths and plans
--
2.34.1