JSON_EXTRACT

Description

This function returns data from the specified path in a JSON document. It returns NULL if any parameter is NULL or if the path cannot be located in the document.

Syntax

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

Remarks

The json_doc parameter specifies the JSON document, and the path parameter specifies the path. An error occurs if json_doc is not a valid JSON document or if any path parameter is not a valid path expression.

The return value consists of all matching values from the path parameter. If these parameters return multiple values, the matching values are automatically wrapped in an array in the order they are generated by the path. Otherwise, a single matching value is returned.

Examples

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
| 10                                         |
+--------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]') |
+-----------------------------------------------+
| 40                                            |
+-----------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_EXTRACT('{"person.name": "John", "age": 25}', '$."person.name"');
+-----------------------------------------------------------------------+
| JSON_EXTRACT('{"person.name": "John", "age": 25}', '$."person.name"') |
+-----------------------------------------------------------------------+
| "John"                                                                |
+-----------------------------------------------------------------------+
1 row in set (0.001 sec)