JSON_UNQUOTE()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Transforms a JSON string into the string value that it represents.
USAGE
JSON_UNQUOTE(json_string)
Argument Name | Description |
|---|---|
| The JSON string to unquote |
DETAILS
JSON_UNQUOTE() is a JSON manipulation function that transforms a JSON string into the string value that it represents.
In addition to removing the beginning and ending double-quote character from the JSON string, any backslash escaped letters are unescaped.
When the specified value is not a JSON string, the argument value is returned unchanged.
A NULL is returned if the argument is NULL.
EXAMPLES
With Literal Values
In the following example, JSON_UNQUOTE() is called with a string literal value that contains escaped special characters. The result demonstrates how to unquote and unescape values from JSON:
SET @str = '"Now \\"is\\" the \\\\ time"';
SELECT @str, JSON_UNQUOTE(@str)\G
*************************** 1. row ***************************
@str: "Now \"is\" the \\ time"
JSON_UNQUOTE(@str): Now "is" the \ time
SELECT JSON_UNQUOTE(NULL);
+--------------------+
| JSON_UNQUOTE(NULL) |
+--------------------+
| NULL |
+--------------------+
Example Schema and Data
Some of the examples are based on the contacts table:
CREATE TABLE contacts (
contact JSON
);
INSERT INTO contacts VALUES
('{"email": "john.smith@example.com", "name1": "John", "name2": "Smith"}'),
('{"email": "jon.smith@example.com", "name1": "Jon", "name2": "Smith"}'),
('{"email": "johnny.smith@example.com", "name1": "Johnny", "name2": "Smith"}');
Per-row Values
SELECT JSON_EXTRACT(contact, '$.email') AS email,
JSON_UNQUOTE(
JSON_EXTRACT(contact, '$.email')
) AS u_email
FROM contacts
WHERE JSON_TYPE(JSON_EXTRACT(contact, '$.email')) = 'STRING'\G
*************************** 1. row ***************************
email: "john.smith@example.com"
u_email: john.smith@example.com
*************************** 2. row ***************************
email: "jon.smith@example.com"
u_email: jon.smith@example.com
*************************** 3. row ***************************
email: "johnny.smith@example.com"
u_email: johnny.smith@example.com
Unescaping Special Characters from JSON String
When a JSON string contains special characters that are escaped with the backslash character (\), JSON_UNQUOTE() removes the escape characters:
SELECT JSON_UNQUOTE('{\\1\t: "\"John\""}') AS unquoted_result\G
*************************** 1. row ***************************
unquoted_result: {\1 : ""John""}