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:
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:
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                              |
+-------+-----------------------------------+