In MySQL, the JSON_EXTRACT() function returns data from a JSON document. The actual data returned is determined by the path you provide as an argument.

You provide the JSON document as the first argument, followed by the path of the data to return. You can also provide multiple paths to return data from multiple places in the document.

Syntax

The syntax goes like this:

JSON_EXTRACT(json_doc, path[, path] ...)

Where json_doc is the JSON document and path is the path to the value to return.

If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": 3}', '$.b') AS 'Result';

Result:

+--------+
| Result |
+--------+
| 2      |
+--------+

In this case, we returned the value of the key b.

Here’s another example:

SELECT 
  JSON_EXTRACT('{"Name": "Bart", "Age": 10}', '$.Name') AS 'Result';

Result:

+--------+
| Result |
+--------+
| "Bart" |
+--------+

Example 2 – Non-Existent Path

If you specify a path that doesn’t exist a NULL value is returned.

SELECT 
  JSON_EXTRACT('{"Name": "Homer", "Age": 39}', '$.Gender') AS 'Result';

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

You’ll also get a NULL value if any of the arguments are NULL.

Example 3 – Arrays

Here’s an example using an array.

SELECT JSON_EXTRACT('[1, 2, 3]', '$[2]') AS 'Result';

Result:

+--------+
| Result |
+--------+
| 3      |
+--------+

Arrays use zero-based numbering, so in this case, the third element is replaced.

Here’s another array example. This time we return a value from a nested array.

SELECT JSON_EXTRACT('[1, 2, [3, 4, 5]]', '$[2][1]') AS 'Result';

Result:

+--------+
| Result |
+--------+
| 4      |
+--------+

Example 4 – Multiple Paths

You can specify more than one path in order to return data from multiple places within the JSON document.

Basic example:

SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1]', '$[3]') AS 'Result';

Result:

+--------+
| Result |
+--------+
| [2, 4] |
+--------+

Example 5 – A Larger JSON Document

Here’s an example with a slightly larger JSON document.

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_EXTRACT(@data, '$.Person.Name', '$.Person.Age', '$.Person.Hobbies') AS 'Result';

Result:

+---------------------------------------+
| Result                                |
+---------------------------------------+
| ["Homer", 39, ["Eating", "Sleeping"]] |
+---------------------------------------+

We can also choose a specific element from the array if needed:

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_EXTRACT(@data, '$.Person.Name', '$.Person.Age', '$.Person.Hobbies[1]') AS 'Result';

Result:

+---------------------------+
| Result                    |
+---------------------------+
| ["Homer", 39, "Sleeping"] |
+---------------------------+