In case you encounter the error "Error 4036: Character not allowed in JSON" when using the JSON data type in Maria DB.

Issue

In Mariadb, when using JSON string with a string type element containing an escape character '' , for example:

REPLACE INTO test2 (ID,Attr) VALUES ('eENvwqzp',json_compact('{"str":"kerokero\n\n\t\"\n\t\"\n\n\tpippi"}'));

You might encounter an error like as follows:

Error 4036: Character disallowed in JSON in argument 1 to function 'json_compact' at position 17,

The argument of json_compact at position 17 is a character \n. Why is the \n disallowed in JSON?

In contrast, in the case of SQLite

With SQLite, with function json() instead of json_compact() the SQL script mentioned above is working well.

REPLACE INTO test2 (ID,Attr) VALUES ('eENvwqzp',json('{"str":"kerokero\n\n\t\"\n\t\"\n\n\tpippi"}'));

'\' is consumed by the MariaDB SQL parser.

The root cause of the error mentioned above is that the '\' is consumed by MariaDB SQL parser**. The MariaDB Knowledge Base provides a solution by using'\\' instead single '\' (opens new window)

However, even though the escaping process required by the JSON specification has been completed by the marshaling process, it is a bit tedious to escape the '\' in the JSON string again.

Another way to use escaped character without double '\'

Another way is blocking the MariaDB parser from consume '\' by using SQL_MODE (opens new window) NO_BACKSLASH_ESCAPES (opens new window). With this SQL_MODE, following script can work well.

REPLACE INTO test2 (ID,Attr) VALUES ('eENvwqzp',json('{"str":"kerokero\n\n\t\"\n\t\"\n\n\tpippi"}'));

---