1

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);
cristighr
  • 13
  • 5

1 Answers1

1

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.

p-a-o-l-o
  • 9,807
  • 2
  • 22
  • 35
  • the code generates error... i think you can't start a variable's name with a digit, i tried though `SELECT JSON_ARRAY_APPEND(@j, '$."0as@x\\""', 2);` and it worked – cristighr Nov 06 '17 at 14:31
  • ok, yes your answer made me think that json_object escapes the characters and that's why my initial set was doing something different from what I expected. Thank you very much! – cristighr Nov 06 '17 at 15:07