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.