How to convert a JSON (JavaScript Object Notation) quoted string into a regular character string using the JSON_UNQUOTE() function?

JSON_UNQUOTE(json) is a MySQL built-in function that converts a JSON string into a regular character string, by removing the enclosing double-quotes and restoring any escaped characters within the string. For example:

SELECT JSON_UNQUOTE('"null"');
  -- +------------------------+
  -- | JSON_UNQUOTE('"null"') |
  -- +------------------------+
  -- | null                   |
  -- +------------------------+

SELECT JSON_QUOTE('"null"'), JSON_UNQUOTE(JSON_QUOTE('"null"'));
  -- +----------------------+------------------------------------+
  -- | JSON_QUOTE('"null"') | JSON_UNQUOTE(JSON_QUOTE('"null"')) |
  -- +----------------------+------------------------------------+
  -- | "\"null\""           | "null"                             |
  -- +----------------------+------------------------------------+

JSON uses the backslash "\" as the escape character. And "\" itself needs to be quoted. But "\" is also the escape character for MySQL string literals. So entering a "\" into a JSON string in MySQL statement becomes very tricky as shown below:

SELECT '"1\\t\\u0032"', JSON_UNQUOTE('"1\\t\\u0032"');
  -- +-------------+-------------------------------+
  -- | "1\t\u0032" | JSON_UNQUOTE('"1\\t\\u0032"') |
  -- +-------------+-------------------------------+
  -- | "1\t\u0032" | 1    2                           |
  -- +-------------+-------------------------------+

SELECT '"C:\Windows"', JSON_UNQUOTE('"C:\Windows"');
  -- +-------------+------------------------------+
  -- | "C:Windows" | JSON_UNQUOTE('"C:\Windows"') |
  -- +-------------+------------------------------+
  -- | "C:Windows" | C:Windows                    |
  -- +-------------+------------------------------+

SELECT '"C:\\\\Windows"', JSON_UNQUOTE('"C:\\\\Windows"');
  -- +---------------+---------------------------------+
  -- | "C:\\Windows" | JSON_UNQUOTE('"C:\\\\Windows"') |
  -- +---------------+---------------------------------+
  -- | "C:\\Windows" | C:\Windows                      |
  -- +---------------+---------------------------------+

SELECT JSON_UNQUOTE('"C:\\Windows"');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function
json_unquote: "Invalid escape character in string." at position 3.

Note that if the argument is not double quoted string, JSON_UNQUOTE() returns it as is without any changes.

SELECT JSON_UNQUOTE('null'), JSON_UNQUOTE('"null');
  -- +----------------------+-----------------------+
  -- | JSON_UNQUOTE('null') | JSON_UNQUOTE('"null') |
  -- +----------------------+-----------------------+
  -- | null                 | "null                 |
  -- +----------------------+-----------------------+

SELECT 'C:\Windows', JSON_UNQUOTE('C:\Windows');
  -- +-----------+----------------------------+
  -- | C:Windows | JSON_UNQUOTE('C:\Windows') |
  -- +-----------+----------------------------+
  -- | C:Windows | C:Windows                  |
  -- +-----------+----------------------------+
1 row in set (0.00 sec)

SELECT 'C:\\Windows', JSON_UNQUOTE('C:\\Windows');
  -- +------------+-----------------------------+
  -- | C:\Windows | JSON_UNQUOTE('C:\\Windows') |
  -- +------------+-----------------------------+
  -- | C:\Windows | C:\Windows                  |
  -- +------------+-----------------------------+
1 row in set (0.00 sec)

SELECT 'C:\\\Windows', JSON_UNQUOTE('C:\\\Windows');
  -- +------------+------------------------------+
  -- | C:\Windows | JSON_UNQUOTE('C:\\\Windows') |
  -- +------------+------------------------------+
  -- | C:\Windows | C:\Windows                   |
  -- +------------+------------------------------+
1 row in set (0.00 sec)

SELECT 'C:\\\\Windows', JSON_UNQUOTE('C:\\\\Windows');
  -- +-------------+-------------------------------+
  -- | C:\\Windows | JSON_UNQUOTE('C:\\\\Windows') |
  -- +-------------+-------------------------------+
  -- | C:\\Windows | C:\\Windows                   |
  -- +-------------+-------------------------------+
1 row in set (0.00 sec)

SELECT 'C:\\\\\Windows', JSON_UNQUOTE('C:\\\\\Windows');
  -- +-------------+--------------------------------+
  -- | C:\\Windows | JSON_UNQUOTE('C:\\\\\Windows') |
  -- +-------------+--------------------------------+
  -- | C:\\Windows | C:\\Windows                    |
  -- +-------------+--------------------------------+

Reference information of the JSON_UNQUOTE() function:

JSON_UNQUOTE(json): str
  Converts a JSON string into a regular character string,
  by removing the enclosing double-quotes and restoring
  any escaped characters within the string.
  If the argument is not double quoted string,
  it returns argument as is without any changes.

Arguments, return value and availability:
  json: Required. The string or JSON string be converted.
  str: Return value. The converted regular character string.
  Available since MySQL 5.7.

⇒ JSON_VALID() - Validating JSON Value

⇐ JSON_TYPE() - Detecting Type of JSON Value

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References