JSON_UNQUOTE()

Function Description

The JSON_UNQUOTE() function is used to extract a JSON value without quotes from a JSON string and return it as a string.

Specifically, the JSON_UNQUOTE() function takes a JSON string as input, and then extracts a JSON value from it and returns it as a string. If the entered JSON string does not contain a valid JSON value or the parameter is NULL, the function returns NULL. If the parameter is NULL, NULL is returned. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.

Grammar Structure

select JSON_UNQUOTE(string_value);

In a string, some sequences have special meanings, and these sequences start with a backslash (), called escape characters, and the rules are as follows. For all other escape sequences, the backslash will be ignored. That is, escape characters are interpreted as having no escape. For example, \x is x. These sequences are case sensitive. For example, \b is interpreted as a backspace, while \B is interpreted as B.

Escape sequence	Characters represented
“	Double Quotes (“)
\b	Backspace symbol
\f	Page change
\n	Line Newlines
\r	Carriage Return
\t	Tabs
|Backslash ()
\uXXXX	Unicode UTF-8 bytes with XXXX

Example

mysql> SET @j = '"abc"';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_UNQUOTE(@j);
+-----------------------------------+
| @j | json_unquote(@j) |
+-----------------------------------+
| "abc" | abc |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SET @j = '[1, 2, 3]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_UNQUOTE(@j);
+----------------------------------------+
| @j | json_unquote(@j) |
+----------------------------------------+
| [1, 2, 3] | [1, 2, 3] |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
+----------------------------------+
| json_unquote("\\t\\u0032") |
+----------------------------------+
| 2 |
+----------------------------------+
1 row in set (0.00 sec)