Search code examples
mysqljsonrdbmsjson-query

Escape double quotes in json functions mysql

I know it should be simple, but I can't figure out how to escape the double quotes inside mysql query for json manipulation.

I have the following example:

SET @j = '{"4": 1, "0as@x"" : [1, 2, 3, 4], "b": [2, 3]}';

Please pay attention to the double quotes inside the second key: 0as@x"

If i run this query: SELECT JSON_ARRAY_APPEND(@j, '$."0as@x\"', '2'); I get the following error:

Error Code: 3141. Invalid JSON text in argument 1 to function json_array_append: "Missing a colon after a name of object member." at position 16.

All I want is to know how to escape the double quotes inside variable name of json object key.

I also have tried doubling the quotes """, with two backslashes \\"...

Could you please help me?

Thank you!

Later EDIT

In the set statement I escaped the double quotes with \". This is done behind if you use JSON_OBJECT.

In the end I escaped with \\ the double quotes and it worked.

The final code that is working:

SET @j = JSON_OBJECT('4', 1, '0as@x"', '[1, 2, 3, 4]', 'b', '[2, 3]');
SELECT JSON_ARRAY_APPEND(@j, '$."0as@x\\""',  2);
Solution

  • Use the JSON_OBJECT function to build the object, this way:

    SET @j = JSON_OBJECT('4', 1, '0as@x"', '[1, 2, 3, 4]', 'b', '[2, 3]');

    Then the function (no extra quotes around the key name):

    SELECT JSON_ARRAY_APPEND(@j, '$.0as@x"',  2);

    Hope it helped.