In MariaDB, JSON_QUOTE() is a built-in function that produces a valid JSON string literal that can be included in a JSON document. 

It wraps the string with double quote characters and escapes interior quotes and other special characters, returning a utf8mb4 string.

Syntax

The syntax goes like this:

JSON_QUOTE(json_value)

Example

Here’s an example to demonstrate.

SELECT JSON_QUOTE('Small') AS 'Result';

Result:

+---------+
| Result  |
+---------+
| "Small" |
+---------+

Escape Characters

In addition to wrapping the string in double quotes, JSON_QUOTE() also escapes interior quotes and other special characters.

Example:

SELECT JSON_QUOTE('By "small", she meant "tiny"') AS 'Result';

Result:

+------------------------------------+
| Result                             |
+------------------------------------+
| "By \"small\", she meant \"tiny\"" |
+------------------------------------+

In this case, the original string contains double quotes and so the resulting string escapes each double quote with a backslash.

If the double quotes weren’t escaped, they would interfere with the outer double quotes, and inadvertently terminate the string early.

JSON Objects

Here’s an example of quoting a string representation of a JSON object:

SELECT JSON_QUOTE('{ "name": "Brandy"}');

Result:

+-----------------------------------+
| JSON_QUOTE('{ "name": "Brandy"}') |
+-----------------------------------+
| "{ \"name\": \"Brandy\"}"         |
+-----------------------------------+

Arrays

Here’s an example of quoting a string representation of an array:

SELECT JSON_QUOTE('[ "Small", "Medium", "Large" ]');

Result:

+----------------------------------------------+
| JSON_QUOTE('[ "Small", "Medium", "Large" ]') |
+----------------------------------------------+
| "[ \"Small\", \"Medium\", \"Large\" ]"       |
+----------------------------------------------+

Numbers

Passing a number returns NULL:

SELECT JSON_QUOTE(10) AS 'Result';

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

The number would need to be provided as a string. It would then be returned as a quoted string.

Example:

SELECT JSON_QUOTE('10') AS 'Result';

Result:

+--------+
| Result |
+--------+
| "10"   |
+--------+

Null Arguments

If the argument is NULL, the result is NULL:

SELECT JSON_QUOTE(null);

Result:

+------------------+
| JSON_QUOTE(null) |
+------------------+
| NULL             |
+------------------+

Incorrect Parameter Count

Calling JSON_QUOTE() without an argument results in an error:

SELECT JSON_QUOTE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUOTE'

It’s the same when you provide too many arguments:

SELECT JSON_QUOTE('a', 'b');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUOTE'

More Examples

MariaDB JSON_QUOTE() works the same as the MySQL function of the same name. See JSON_QUOTE() – How to Escape Characters in Strings used as JSON Values in MySQL for more examples.