| Bug #80093 | JSON_EXTRACT returns quoted string | ||
|---|---|---|---|
| Submitted: | 21 Jan 2016 9:10 | Modified: | 22 Feb 2016 10:35 |
| Reporter: | Alexey Nick | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 5.7.10, 5.7.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | json, json_extract, quotes | ||
[11 Feb 2016 13:52]
MySQL Verification Team
Hello Alexey, Thank you for the report. In order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html. If you have any questions, please contact the MySQL community team. -- Workadound is to use https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-unq... mysql> set @json = '{"a" : "{\\"d\\": \\"some inner json\\"}"}'; Query OK, 0 rows affected (0.00 sec) mysql> select json_extract(@json, "$.a"); +--------------------------------+ | json_extract(@json, "$.a") | +--------------------------------+ | "{\"d\": \"some inner json\"}" | +--------------------------------+ 1 row in set (0.00 sec) mysql> select json_unquote(json_extract(@json, "$.a")); +------------------------------------------+ | json_unquote(json_extract(@json, "$.a")) | +------------------------------------------+ | {"d": "some inner json"} | +------------------------------------------+ 1 row in set (0.00 sec) mysql> Thanks, Umesh
[22 Feb 2016 10:35]
Knut Anders Hatlen
This is essentially the same feature request as bug#78736, although the suggested solutions are slightly different, so I'm closing this bug report as a duplicate. The current behaviour is intentional. The quotes are kept around the string to ensure that it is still a valid JSON document after the conversion from the JSON type to a string type. Since applications may rely on this behaviour and expect a parsable JSON text to be returned, one must be careful about changing it. The approach suggested in bug#78736, where a shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) is added, is safer because it doesn't modify existing behaviour.
Description: When selecting single value using json_extract, if the target value is string it remains quoted. For example set @json = '{"a" : "{\\"d\\": \\"some inner json\\"}"}'; select json_extract(@json, "$.a"); returns "{\"d\": \"some inner json\"}" It would be nice if single value would always returned unquoted (or there was new SQL Mode that could change that behaviour) So the above query would return {"d": "some inner json"} How to repeat: set @json = '{"a" : "{\\"d\\": \\"some inner json\\"}"}'; select json_extract(@json, "$.a"); Suggested fix: Change to_string of json_wrapper --- sql/json_dom.cc.orig 2015-12-16 20:47:41 UTC +++ sql/json_dom.cc @@ -2107,7 +2107,11 @@ const char *data= wr.get_data(); size_t length= wr.get_data_length(); - if (print_string(buffer, json_quoted, data, length)) + bool shouldQuote = json_quoted; + if (depth == 1) { + shouldQuote = false; + } + if (print_string(buffer, shouldQuote, data, length)) return true; /* purecov: inspected */ break; }