In MySQL, we can use the -> operator to extract data from a JSON document.

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 an alias for the two-argument form of the JSON_EXTRACT() function.

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.

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 use the -> operator to extract data from one of the fields within those JSON documents:

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

Result:

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

Here’s another example that also extracts the weight for each pet:

SELECT 
    PetId,
    PetName -> '$.name',
    PetName -> '$.weight'
FROM Pets;

Result:

+-------+---------------------+-----------------------+
| PetId | PetName -> '$.name' | PetName -> '$.weight' |
+-------+---------------------+-----------------------+
|     1 | "Wag"               | 20                    |
|     2 | "Bark"              | 10                    |
|     3 | "Meow"              | 7                     |
+-------+---------------------+-----------------------+

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' = 'Wag';

Result:

+-------+----------------------------------------------+
| PetId | PetName                                      |
+-------+----------------------------------------------+
|     1 | {"name": "Wag", "type": "Dog", "weight": 20} |
+-------+----------------------------------------------+

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.

Let’s order by weight:

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

Result:

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

Remove Quotes

We have a couple of options for removing the quotes from strings that we return from the JSON document.

We can use the JSON_UNQUOTE() function:

SELECT 
    PetName -> '$.name' AS "Quoted",
    JSON_UNQUOTE(PetName -> '$.name') AS "Unquoted"
FROM Pets;

Result:

+--------+----------+
| Quoted | Unquoted |
+--------+----------+
| "Wag"  | Wag      |
| "Bark" | Bark     |
| "Meow" | Meow     |
+--------+----------+

Or we can use the ->> operator:

SELECT 
    PetName -> '$.name' AS "Quoted",
    PetName ->> '$.name' AS "Unquoted"
FROM Pets;

Result:

+--------+----------+
| Quoted | Unquoted |
+--------+----------+
| "Wag"  | Wag      |
| "Bark" | Bark     |
| "Meow" | Meow     |
+--------+----------+

The ->> operator provides us with a more concise way of returning the same data with less code.

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_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  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Now let’s look at the warning:

SHOW WARNINGS;

Result:

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

The same applies to the ->> operator.