JSON_UNQUOTE()

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

Function description

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

Specifically, the JSON_UNQUOTE() function accepts a JSON-typed value or a string containing JSON text, extracts a JSON value from it, and returns it as a string. If the argument is NULL, the function returns NULL; invalid JSON input raises an error. If the value starts and ends with double quotes but is not a valid JSON string literal, an error occurs.

Grammar structure

select JSON_UNQUOTE(string_value);

In strings, certain sequences have special meanings. These sequences start with a backslash () and are called escape characters. The recognized JSON escape sequences are listed below. Unsupported or malformed escape sequences, such as \x, raise an error. The sequences are case-sensitive; \b is recognized, but \B is not.

|The character represented by the escape sequence| |—|—| |”|Double quotes (”) | |\b|Backspace| |\f|form feed character| |\n|Line break| |\r|Carriage return character| |\t|tab| |\|Backslash () | |\uXXXX|Unicode code point XXXX encoded as UTF-8 text|

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)