MySQL’s JSON_UNQUOTE function is used to remove quotation marks from a JSON string and return the unquoted result. This can be useful when you want to extract a specific value from a JSON string or manipulate the data within a JSON object.

Syntax

The basic syntax for the JSON_UNQUOTE function is as follows:

JSON_UNQUOTE(json_string)

json_string: This is the JSON string from which you want to remove the quotation marks.

Example

Let’s consider a simple example where you have a JSON string stored in a MySQL column, and you want to extract a specific value from that JSON string using JSON_UNQUOTE.

Assume you have a table named employee with a column named info containing JSON data:

CREATE TABLE employee (
    id INT,
    info JSON
);

INSERT INTO employee VALUES
    (1, '{"name": "John", "age": 30, "department": "IT"}'),
    (2, '{"name": "Jane", "age": 28, "department": "HR"}'),
    (3, '{"name": "Bob", "age": 35, "department": "Finance"}');

Now, let’s use JSON_UNQUOTE() to extract the “name” from the JSON strings:

SELECT id,
JSON_UNQUOTE(info->"$.name") AS employee_name
FROM employee;

In this example, the -> operator is used to access the “name” key within the JSON object. JSON_UNQUOTE() is then applied to remove the quotation marks from the extracted value. The result of the query will be:

+----+---------------+
| id | employee_name |
+----+---------------+
|  1 | John          |
|  2 | Jane          |
|  3 | Bob           |
+----+---------------+

This demonstrates how JSON_UNQUOTE() can be used to extract and display unquoted values from a JSON string in MySQL.