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, it returns NULL.
Syntax
JSON_EXTRACT(json_doc, path[, path] ...)
Considerations
The json_doc parameter specifies the JSON document, and path is the path parameter. If the json_doc parameter is not a valid JSON document or any path parameter is not a valid path expression, an error occurs.
The return value consists of all matching values from the path parameter. If multiple values are returned, the matching values are automatically wrapped in an array in the order they were generated by the path. 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