'mysql - avoid escaping double quotes in json functions

When I issue...

select JSON_REPLACE('{"tbl" : "cnf"}', '$', '{"tbl":"cnf4"}');

I get the following :

+--------------------------------------------------------+
| JSON_REPLACE('{"tbl" : "cnf"}', '$', '{"tbl":"cnf4"}') |
+--------------------------------------------------------+
| "{\"tbl\":\"cnf4\"}"                                   |
+--------------------------------------------------------+

And it gets stored in my database the same say, with backslashes. I want to have no backslashes in my database. How can I achieve that?

I expect a reponse like: {"tbl":"cnf4"}

mysqljson


Solution 1:[1]

Wrap in JSON_UNQUOTE

select JSON_UNQUOTE(JSON_REPLACE('{"tbl" : "cnf"}', '$', '{"tbl":"cnf4"}'));
+----------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_REPLACE('{"tbl" : "cnf"}', '$', '{"tbl":"cnf4"}')) |
+----------------------------------------------------------------------+
| {"tbl":"cnf4"}                                                       |
+----------------------------------------------------------------------+
1 row in set (0.0005 sec)

Solution 2:[2]

This helped me to unescape when I pushed new object to an existing array

json_array_append(data, '$', cast(? as json))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dave Stokes
Solution 2 Eugene

Related Questions