5

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"}');
3

Let's create the table:

mysql> create table jsontest (id serial, value json);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table jsontest\G
*************************** 1. row ***************************
Table: jsontest
Create Table: CREATE TABLE `jsontest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And insert some JSON data/objects:

mysql> INSERT INTO jsontest(value) VALUES (JSON_OBJECT('test', 'string with "escaped quotes" does not work'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO jsontest(value) VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');
Query OK, 1 row affected (0.01 sec)

mysql> select id, value->>"$.test" from jsontest;
+----+--------------------------------------------+
| id | value->>"$.test"                           |
+----+--------------------------------------------+
|  2 | string with "escaped quotes" does not work |
|  9 | string with "escaped quotes" does not work |
+----+--------------------------------------------+
2 rows in set (0.00 sec)

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

| improve this answer | |
0

I would like to add that the value that gets inserted in the table using double slashes

INSERT INTO jsontest(value) 
VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');

...would still be {"test": "string with \"escaped quotes\" does not work"} which is a valid Json format.

It's the way we are querying the inserted data that's making the difference here:

Example

mysql> select id, value->>"$.test" from jsontest;

gives:

"string with "escaped quotes" does not work"

as output, but if we query:

mysql>select value from jsontest;

...it will still reflect {"test": "string with \"escaped quotes\" does not work"} as output. The good thing is using Json type we can directly query and extract values against the corresponding keys:

mysql> SELECT JSON_EXTRACT('{"id": 14, "SampleKey": "SampleValue"}', '$.SampleKey');

Gives "SampleValue" as output.

To use Json datatype the string being inserted should be a valid and properly formatted Json.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.