MySQL JSON_EXTRACT() Function

In MySQL, the JSON_EXTRACT() function extracts the data specified by the path expression in the JSON document and returns it.

`JSON_EXTRACT()` Syntax

Here is the syntax of the MySQL JSON_EXTRACT() function:

JSON_EXTRACT(json, path, ...)

Parameters

json: Required. A JSON document.
path: Required. You should specify at least one path expression.

Return value

The JSON_EXTRACT() function returns all values in the JSON document matched by the path expression. If the path expression matches a value, return the value， and if the path expression matches more than one value, return an array containing all the values.

The JSON_EXTRACT() function will return NULL in the following cases:

If the specified path does not exist in the JSON document.
If any parameter is NULL.

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression, MySQL will give an error.

`JSON_EXTRACT()` Examples

Here are some examples of JSON_EXTRACT().

Example: Array

The following statement shows how to extract an element from an array:

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]');

+------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]') |
+------------------------------------------+
| {"x": 3}                                 |
+------------------------------------------+

Let’s see another example of JSON_EXTRACT() with multiple path parameters:

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]');

+------------------------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]') |
+------------------------------------------------------------+
| [3, 2, 1]                                                  |
+------------------------------------------------------------+

Here, JSON_EXTRACT() returned an array containing all matched data.

Example: Object

The following statement shows how to extract data from an object:

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y');

+----------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y') |
+----------------------------------------------+
| [1, 2]                                       |
+----------------------------------------------+

Let’s see another example of JSON_EXTRACT() with multiple path parameters. The following example extracts the data of the member x and y in an object and returns the matched values as an array.

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y');

+-----------------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y') |
+-----------------------------------------------------+
| [1, [1, 2]]                                         |
+-----------------------------------------------------+

JSON_EXTRACT() Syntax