In MySQL, the JSON_UNQUOTE() function “unquotes” a JSON document and returns the result as a utf8mb4 string.

You provide the JSON document as an argument, and the function will do the rest.

Syntax

The syntax goes like this:

JSON_UNQUOTE(json_val)

Where json_val is the JSON document you want unquoted.

Example

Here’s an example to demonstrate.

SET @data = '"Homer Simpson"';
SELECT 
  @data Original, 
  JSON_UNQUOTE(@data) Unquoted;

Result:

+-----------------+---------------+
| Original        | Unquoted      |
+-----------------+---------------+
| "Homer Simpson" | Homer Simpson |
+-----------------+---------------+

Escape Characters

The way this function handles escape characters depends on whether NO_BACKSLASH_ESCAPES is enabled or disabled.

When NO_BACKSLASH_ESCAPES is Disabled

The NO_BACKSLASH_ESCAPES mode is disabled by default in MySQL 8.0.

Here’s what happens if we include \t to specify a tab character within a string when NO_BACKSLASH_ESCAPES is disabled, and without using JSON_UNQUOTE().

SET @data = '"Homer \t Simpson"';
SELECT 
  @data Original;

Result:

+-------------------+
| Original          |
+-------------------+
| "Homer 	 Simpson" |
+-------------------+

And here’s what happens if we use JSON_UNQUOTE():

SET @data = '"Homer \t Simpson"';
SELECT JSON_UNQUOTE(@data) Unquoted;

Result:

ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_unquote: "Invalid escape character in string." at position 7.

We can overcome this by using two backslashes (\\t). However, keep in mind that doing this will change the outcome when not using JSON_UNQUOTE().

SET @data = '"Homer \\t Simpson"';
SELECT 
  @data Original, 
  JSON_UNQUOTE(@data) Unquoted;

Result:

+--------------------+-----------------+
| Original           | Unquoted        |
+--------------------+-----------------+
| "Homer \t Simpson" | Homer 	 Simpson |
+--------------------+-----------------+

When NO_BACKSLASH_ESCAPES is Enabled

Here’s what happens when we enable NO_BACKSLASH_ESCAPES before running the previous statement:

SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
SET @data = '"Homer \\t Simpson"';
SELECT 
  @data Original, 
  JSON_UNQUOTE(@data) Unquoted;

Result:

+---------------------+------------------+
| Original            | Unquoted         |
+---------------------+------------------+
| "Homer \\t Simpson" | Homer \t Simpson |
+---------------------+------------------+

And here’s what happens if we remove the first backslash:

SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
SET @data = '"Homer \t Simpson"';
SELECT 
  @data Original, 
  JSON_UNQUOTE(@data) Unquoted;

Result:

+--------------------+-----------------+
| Original           | Unquoted        |
+--------------------+-----------------+
| "Homer \t Simpson" | Homer 	 Simpson |
+--------------------+-----------------+