Purpose
JSON_EXTRACT() returns data from the specified paths in a JSON document. If any argument is NULL, or no paths are located in the document, the return value is NULL.
Syntax
JSON_EXTRACT(json_doc, path[, path] ...)
Description
json_doc specifies the name of the JSON document, and path specifies the path. If the JSON document specified for json_doc is invalid, or any path expression specified for path is invalid, an error occurs.
The return value consists of all matching values of the path arguments. If these arguments return multiple values, the matching values are automatically wrapped into an array. The order of matching values is the same as that of the paths. 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