MariaDB JSON_QUOTE(): Quote a String as JSON

MariaDB JSON_QUOTE() wraps a string in double quotes and escapes special characters so the result is a JSON string literal. It returns a utf8mb4 string. See the official MariaDB JSON_QUOTE reference.

MariaDB JSON_QUOTE() Syntax

Here is the syntax for the MariaDB JSON_QUOTE() function:

JSON_QUOTE(str)

Parameters

str

Required. a string.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUOTE'.

Return value

The MariaDB JSON_QUOTE() function returns a JSON string value surrounded by double quotes. The result uses the utf8mb4 character set.

If the argument is NULL, the JSON_QUOTE() function returns NULL.

Special characters in the following table will be escaped with a backslash:

The \uXXXX form can represent a Basic Multilingual Plane code point. JSON uses it for control characters that do not have a shorter escape; characters outside that plane use a surrogate pair. See RFC 8259, section 7.

MariaDB JSON_QUOTE() Examples

Here are examples of the MariaDB JSON_QUOTE() function.

Basic example

SELECT
    JSON_QUOTE('123'),
    JSON_QUOTE('NULL'),
    JSON_QUOTE('"NULL"');

Output:

+-------------------+--------------------+----------------------+
| JSON_QUOTE('123') | JSON_QUOTE('NULL') | JSON_QUOTE('"NULL"') |
+-------------------+--------------------+----------------------+
| "123"             | "NULL"             | "\"NULL\""           |
+-------------------+--------------------+----------------------+

Escape character

In addition to enclosing a string in double quotes, JSON_QUOTE() escapes inner quotes and other special characters.

SELECT JSON_QUOTE('I am "strong"');

Output:

+-----------------------------+
| JSON_QUOTE('I am "strong"') |
+-----------------------------+
| "I am \"strong\""           |
+-----------------------------+

Numbers

JSON_QUOTE() expects a string argument. A numeric SQL value returns NULL; cast it to CHAR when you want the number’s text form quoted as a JSON string:

SELECT
    JSON_QUOTE(123) AS numeric_argument,
    JSON_QUOTE(CAST(123 AS CHAR)) AS text_argument;

Output:

+------------------+--------------+
| numeric_argument | text_argument |
+------------------+--------------+
| NULL             | "123"        |
+------------------+--------------+

NULL parameter

If the argument is NULL, JSON_QUOTE() will return NULL:

SELECT JSON_QUOTE(NULL);

Output:

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

Conclusion

In MariaDB, JSON_QUOTE() is a built-in function that wraps a value in double quotes, making it a JSON string value.