Collections:
JSON_UNQUOTE() - Unquoting JSON String
How to convert a JSON (JavaScript Object Notation) quoted string into a regular character string using the JSON_UNQUOTE() function?
✍: FYIcenter.com
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
2025-03-12, 5832🔥, 0💬
Popular Posts:
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
How To Install Oracle Database 10g XE in Oracle? To install 10g universal edition, double click, Ora...
How to check if two JSON values have overlaps using the JSON_OVERLAPS() function? JSON_OVERLAPS(json...
Where to find answers to frequently asked questions in general areas of Microsoft SQL Server Transac...
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...