I am trying to insert a stringified JSON which looks like this:
'{"test": "string with \"escaped quotes\" does not work"}'
into a MySQL JSON field, but I always get the error:
SQL Error (3140): Invalid JSON text: "Missing a comma or '}' after an object member."
at position 24 in value (or column) '{"test": "string with "escaped quotes" does not work"}'.
Any idea what might be causing the issue?
The full INSERT
statement is:
INSERT INTO test(json_test)
VALUES ('{"test": "string with \"escaped quotes\" does not work"}');
Best Answer
Let's create the table:
And insert some JSON data/objects:
As you can see here you have to use the double backslash escape sequence because you don't want the SQL parser to perform the standard escape sequence processing but instead you want to pass the literal string containing the escape sequence down to the storage engine for the JSON data type processing.
I'll talk to the MySQL docs team about adding some examples and explanation for that here:
12.6 The JSON Data Type - Creating JSON Values