In MySQL, we have several options when it comes to extracting data from JSON documents. Below are six methods we can use to do this.

The JSON_EXTRACT() Function

As its name suggests, the JSON_EXTRACT() function extracts data from a JSON document. When we do this, we need to pass the JSON document, along with one or more paths for which to extract data from.

Example:

SELECT JSON_EXTRACT(
    '{ "name" : "Wag", "type" : "Dog", "weight" : 25 }',
    '$.name'
);

Result:

"Wag"

We can see that the value at the given path is returned.

We can pass more than one path when doing this:

SELECT JSON_EXTRACT(
    '{ "name" : "Wag", "type" : "Dog", "weight" : 25 }',
    '$.name', '$.weight'
);

Result:

["Wag", 25]

When multiple values are returned, they’re returned in an array.

JSON_UNQUOTE() with JSON_EXTRACT()

We can pass the JSON_EXTRACT() function to the JSON_UNQUOTE() function in order to remove the double quotes from the returned value:

SELECT JSON_UNQUOTE(
    JSON_EXTRACT(
        '{ "name" : "Wag", "type" : "Dog", "weight" : 25 }',
        '$.name'
    )
);

Result:

Wag

The JSON_VALUE() Function

We can alternatively use the JSON_VALUE() function to extract data from a JSON document:

SELECT JSON_VALUE(
    '{ "name" : "Wag", "type" : "Dog", "weight" : 25 }',
    '$.name'
);

Result:

Wag

Notice that this value is returned without double quotes, so we don’t need to use JSON_UNQUOTE() to remove the quotes.

Note that JSON_VALUE() doesn’t allow us to extract multiple paths (unlike the JSON_EXTRACT() function).

However, JSON_VALUE() does allow us to specify a return type:

SELECT JSON_VALUE(
    '{ "name" : "Wag", "type" : "Dog", "weight" : 25 }',
    '$.name'
    RETURNING JSON
);

Result:

"Wag"

In this case I specified that the value should be returned as JSON, and so it now has double quotes.

Here’s another example:

SELECT JSON_VALUE(
    '{ "name" : "Wag", "type" : "Dog", "weight" : 25 }',
    '$.weight'
    RETURNING DECIMAL(6,4)
);

Result:

25.0000

The -> Operator

Another way to do it is to replace JSON_EXTRACT() (or JSON_VALUE()) with the -> operator. The -> operator is an alias for the JSON_EXTRACT() function, and so we can use it to do anything we’d do with that function.

The -> operator accepts a column identifier on the left and a JSON path on the right.

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 fields within the JSON:

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

Result:

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

The ->> Operator

If we don’t want the string values to be enclosed in double quotes, we can use the ->> operator.

The ->> operator does basically the same thing as the -> operator, except that it also unquotes the value:

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

Result:

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

JSON_UNQUOTE() with the -> Operator

If we can’t use the ->> operator for whatever reason, we can use JSON_UNQUOTE() to unquote the value returned by the -> operator:

SELECT 
    PetId,
    JSON_UNQUOTE(PetName -> '$.name')
FROM Pets;

Result:

+-------+-----------------------------------+
| PetId | JSON_UNQUOTE(PetName -> '$.name') |
+-------+-----------------------------------+
|     1 | Wag                               |
|     2 | Bark                              |
|     3 | Meow                              |
+-------+-----------------------------------+