In MySQL, we can use the ->> operator to extract a value from a JSON document, and unquote that value.

We provide the column that contains the JSON document to the left of the operator, and the path to the value that we want to extract to the right.

The ->> operator is equivalent to JSON_UNQUOTE(JSON_EXTRACT()), and it provides the same results.

Syntax

The syntax goes like this:

column->>path

Where column is the column that contains the JSON document, and path is the path to the value we want to extract (and unquote).

The following three statements are equivalent:

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

The JSON_EXTRACT() function extracts a value from a JSON document. The JSON_UNQUOTE() function removes any quotes from the extracted value.

The -> operator is an alias for the JSON_EXTRACT() function.

And so the ->> operator can be used in place of the above functions/operators.

Example

Suppose we have the following table called Pets:

SELECT * 
FROM Pets;

Result:

+-------+-----------------------------------------------+
| PetId | PetName                                       |
+-------+-----------------------------------------------+
|     1 | {"name": "Wag", "type": "Dog", "weight": 20}  |
|     2 | {"name": "Bark", "type": "Dog", "weight": 10} |
|     3 | {"name": "Meow", "type": "Cat", "weight": 7}  |
+-------+-----------------------------------------------+

We can see that the PetName column contains JSON. We can also see that each string value is surrounded by double quotes.

We can use the ->> operator to extract data from one of the fields within those JSON documents, and unquote the value:

SELECT PetName ->> '$.name'
FROM Pets;

Result:

+----------------------+
| PetName ->> '$.name' |
+----------------------+
| Wag                  |
| Bark                 |
| Meow                 |
+----------------------+

We can see that the values have been extracted and unquoted (i.e. the double quotes have been removed).

Here’s what happens when we use the -> operator instead:

SELECT PetName -> '$.name'
FROM Pets;

Result:

+---------------------+
| PetName -> '$.name' |
+---------------------+
| "Wag"               |
| "Bark"              |
| "Meow"              |
+---------------------+

This time, the same values were extracted, but they weren’t unquoted. The returned values are still surrounded by double quotes.

In the WHERE Clause

We’re not limited to just the SELECT list. We can use the ->> operator in place of any column reference.

Here’s an example of using ->> in a WHERE clause:

SELECT *
FROM Pets
WHERE PetName ->> '$.name' = 'Bark';

Result:

+-------+-----------------------------------------------+
| PetId | PetName                                       |
+-------+-----------------------------------------------+
|     2 | {"name": "Bark", "type": "Dog", "weight": 10} |
+-------+-----------------------------------------------+

In the ORDER BY Clause

Here’s an example of using the ->> operator in an ORDER BY clause:

SELECT *
FROM Pets
ORDER BY PetName ->> '$.name';

Result:

+-------+-----------------------------------------------+
| PetId | PetName                                       |
+-------+-----------------------------------------------+
|     2 | {"name": "Bark", "type": "Dog", "weight": 10} |
|     3 | {"name": "Meow", "type": "Cat", "weight": 7}  |
|     1 | {"name": "Wag", "type": "Dog", "weight": 20}  |
+-------+-----------------------------------------------+

We can see that the pets are now ordered based on their names.

How EXPLAIN & DESCRIBE Present the ->> Operator

When we use the EXPLAIN or DESCRIBE statements to return information about our query’s execution, they present the query as if we’d used JSON_QUOTE() and JSON_EXTRACT() instead of the ->> operator.

EXPLAIN SELECT PetName ->> '$.name' FROM Pets;

Result:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | Pets  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

Now let’s look at the warning:

SHOW WARNINGS;

Result:

+-------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                         |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select json_unquote(json_extract(`test`.`pets`.`PetName`,'$.name')) AS `PetName ->> '$.name'` from `test`.`pets` |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------+

The same treatment applies to the -> operator (although it obviously doesn’t include the JSON_QUOTE() function).