In MySQL, we have several options when it comes to removing the quotes from a JSON value.

For example, normally if we return a string value from a JSON document, we would get that value along with its quotes. But sometimes we might not want the quotes. Below are three methods we can use to unquote a JSON value in MySQL.

The JSON_UNQUOTE() Function

Our first option is to use the JSON_UNQUOTE() function. This name of this function pretty much tells us what it does.

Suppose we create the following table and populate one of its columns with JSON data:

CREATE TABLE Pets ( PetId INT, PetName JSON);

INSERT INTO Pets
VALUES 
    (1, '{ "name": "Wag", "type": "Dog", "weight": 20 }'), 
    (2, '{ "name": "Bark", "type": "Dog", "weight": 10 }'), 
    (3, '{ "name": "Meow", "type": "Cat", "weight": 7 }');

We can now run queries against that table and extract values from the JSON documents stored in the second column.

Here’s an example of extracting some of the string values from the JSON document, and removing the quotes:

SELECT 
    JSON_UNQUOTE( 
        JSON_EXTRACT(
            PetName, 
            "$.name"
            ) 
        ) AS PetName
FROM Pets;

Result:

+---------+
| PetName |
+---------+
| Wag     |
| Bark    |
| Meow    |
+---------+

We can see that the pet names were returned without the double quotes.

This was all made possible by the JSON_UNQUOTE() function. Here’s what happens when we remove the JSON_UNQUOTE() function:

SELECT
    JSON_EXTRACT(
        PetName, 
        "$.name"
        ) AS PetName
FROM Pets;

Result:

+---------+
| PetName |
+---------+
| "Wag"   |
| "Bark"  |
| "Meow"  |
+---------+

As expected, the double quotes are included with the values.

The -> Operator

We can alternatively use the -> operator instead of the JSON_EXTRACT() function. This operator serves as an alias for the JSON_EXTRACT() function when used with two arguments; a column identifier on the left and a JSON path (a string literal) on the right that is evaluated against the JSON document (the column value).

In other words, the -> operator can be used as shorthand for the JSON_EXTRACT() function.

Here’s how we can use the -> operator to simplify the previous example:

SELECT 
    JSON_UNQUOTE( PetName->"$.name" ) AS PetName
FROM Pets;

Result:

+---------+
| PetName |
+---------+
| Wag     |
| Bark    |
| Meow    |
+---------+

This example still requires the use of JSON_UNQUOTE() if we want to remove the quotes from each value. But the code required is more concise, due to the use of the -> operator.

The ->> Operator

Starting with MySQL 8.0, we can use the ->> operator to replace both the JSON_EXTRACT() function and the JSON_UNQUOTE() function. So, this operator is similar to the -> operator, but performs JSON unquoting of the value as well.

So, we can use the following code to do exactly what we did in the previous examples:

SELECT 
    PetName->>"$.name" AS PetName
FROM Pets;

Result:

+---------+
| PetName |
+---------+
| Wag     |
| Bark    |
| Meow    |
+---------+

We can see that this is the most concise way of extracting and unquoting JSON values from our JSON column.