In MariaDB, JSON_UNQUOTE() is a built-in function that removes quotes from a JSON value. In other words, it “unquotes” a JSON value.

Syntax

The syntax goes like this:

JSON_UNQUOTE(val)

Example

Here’s an example to demonstrate.

SELECT JSON_UNQUOTE('"Eggs"');

Result:

+------------------------+
| JSON_UNQUOTE('"Eggs"') |
+------------------------+
| Eggs                   |
+------------------------+

Escape Characters

If the value contains a backslash character (\), it’s usually ignored. However, certain sequences involving the backslash can have special meaning, and can affect the outcome of JSON_UNQUOTE().

This will depend on whether or not SQL mode is set to NO_BACKSLASH_ESCAPES. When SQL mode is set to NO_BACKSLASH_ESCAPES, this disables using the backslash character as an escape character within strings, making it equivalent to an ordinary character.

Otherwise, the following sequences will hold true:

Escape sequence	Character
\"	Double quote (")
\b	Backslash
\f	Formfeed
\n	Newline (linefeed)
\r	Carriage return
\t	Tab
\\	Backslash (\)
\uXXXX	UTF-8 bytes for Unicode value XXXX

Example:

SELECT JSON_UNQUOTE('Feeling\tGood');

Result:

+-------------------------------+
| JSON_UNQUOTE('Feeling\tGood') |
+-------------------------------+
| Feeling	Good                  |
+-------------------------------+

Here, I used the \t sequence to add a tab character within the string.

If I set SQL mode to NO_BACKSLASH_ESCAPES, the sequence has no special meaning, and the literal sequence is included in the output without interpretation:

SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';

SELECT JSON_UNQUOTE('Feeling\tGood');

Result:

+-------------------------------+
| JSON_UNQUOTE('Feeling\tGood') |
+-------------------------------+
| Feeling\tGood                 |
+-------------------------------+

Null Arguments

If the argument is NULL, the result is NULL:

SELECT JSON_UNQUOTE(null);

Result:

+--------------------+
| JSON_UNQUOTE(null) |
+--------------------+
| NULL               |
+--------------------+

Incorrect Parameter Count

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

SELECT JSON_UNQUOTE();

Result:

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

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

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

Result:

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