MySQL JSON_UNQUOTE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_UNQUOTE() function to remove quotes from a JSON string.

Introduction to MySQL JSON_UNQUOTE() function

The JSON_UNQUOTE() function allows you to remove double quotes from a JSON string. Here’s the syntax of the JSON_UNQUOTE() function:

JSON_UNQUOTE(json_val)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_val: The JSON string from which you want to remove the quotes.

The JSON_QUOTE() function returns a raw value without surrounding quotes. The function returns NULL if the json_val is NULL.

If the json_val starts and ends with double quotes but is not a valid JSON string literal, the JSON_UNQUOTE() function will raise an error.

MySQL JSON_UNQUOTE() function example

Let’s take an example of using the JSON_UNQUOTE() function.

First, create a table called persons:

CREATE TABLE persons (
    id INT PRIMARY KEY,
    info JSON
);Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the persons table:

INSERT INTO persons (id, info) 
VALUES
(1, '{"name": "John", "age": 25, "city": "New York"}'),
(2, '{"name": "Alice", "age": 30, "city": "San Francisco"}');Code language: SQL (Structured Query Language) (sql)

Third, query the city of each person:

SELECT 
  info -> "$.city" city 
FROM 
  persons;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| city            |
+-----------------+
| "New York"      |
| "San Francisco" |
+-----------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The city names are surrounded by double quotes. To remove the double quotes, you use the JSON_UNQUOTE() function:

SELECT 
  JSON_UNQUOTE(info -> "$.city") city 
FROM 
  persons;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------+
| city          |
+---------------+
| New York      |
| San Francisco |
+---------------+
2 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

MySQL makes it more concise by providing the ->> operator that both extracts and removes quotes from a JSON value:

SELECT 
  info ->> "$.city" city 
FROM 
  persons;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------+
| city          |
+---------------+
| New York      |
| San Francisco |
+---------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_UNQUOTE() function to remove quotes from a JSON string.