|
I run this script:
-- Note, to reproduce in 10.2 change CAST(..VARCHAR(30)) to CAST(..CHAR(30))
|
SET NAMES utf8;
|
SELECT json_object('a',CAST(json_object('b','c') AS VARCHAR(30)));
|
+------------------------------------------------------------+
|
| json_object('a',CAST(json_object('b','c') AS VARCHAR(30))) |
|
+------------------------------------------------------------+
|
| {"a": "{\"b\": \"c\"}"} |
|
+------------------------------------------------------------+
|
The output looks good:
- The result json_object('b','c') is a JSON value
- Then it's passed to CAST(). The result of CAST() is a general purpose string, it's not a JSON value any more.
- The result of CAST() is passed to the outer JSON_OBJECT(), and it correctly gets escaped.
Now I run this script:
SET NAMES utf8;
|
SELECT json_object('a',CONVERT(json_object('b','c') USING utf8));
|
+-----------------------------------------------------------+
|
| json_object('a',CONVERT(json_object('b','c') USING utf8)) |
|
+-----------------------------------------------------------+
|
| {"a": {"b": "c"}} |
|
+-----------------------------------------------------------+
|
Looks wrong. It should return the same result with the previous script, because the result of CONVERT() is a general purpose string, so it must be escaped when passed to the outer json_object().
What other databases do
If I run equivalent scripts in Oracle 21c, they both escape, which looks correct:
SELECT json_object('a' VALUE CAST(json_object('b' VALUE 'c') AS VARCHAR(30))) FROM DUAL;
|
JSON_OBJECT('A'VALUECAST(JSON_OBJECT('B'VALUE'C')ASVARCHAR(30)))
|
{"a":"{\"b\":\"c\"}"}
|
SELECT json_object('a' VALUE CONVERT(json_object('b' VALUE 'c'),'US7ASCII', 'WE8ISO8859P1')) FROM DUAL;
|
JSON_OBJECT('A'VALUECONVERT(JSON_OBJECT('B'VALUE'C'),'US7ASCII','WE8ISO8859P1'))
|
{"a":"{\"b\":\"c\"}"}
|
|