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

Last Updated: 12/11/2023, 11:55:42 AM