Purpose
This function returns data from a specified path in a JSON document. If any parameter is NULL or the path cannot be located in the document, NULL is returned.
Syntax
JSON_EXTRACT(json_doc, path[, path] ...)
Description
The json_doc parameter specifies the JSON document, and path is a path parameter. An error occurs if the json_doc parameter is not a valid JSON document or any path parameter is not a valid path expression.
The return value consists of all matching values for the path parameter. If these parameters return multiple values, the matching values are automatically wrapped into an array in the order corresponding to the paths that generated them. Otherwise, a single matching value is returned.
Examples
obclient> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
| 10 |
+--------------------------------------------+
1 row in set
obclient> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][1]') |
+-----------------------------------------------+
| 40 |
+-----------------------------------------------+
1 row in set
obclient> SELECT JSON_EXTRACT('{"person.name": "John", "age": 25}', '$."person.name"');
+-----------------------------------------------------------------------+
| JSON_EXTRACT('{"person.name": "John", "age": 25}', '$."person.name"') |
+-----------------------------------------------------------------------+
| "John" |
+-----------------------------------------------------------------------+
1 row in set