Purpose
This function returns data from the specified path in a JSON document. It returns NULL if any parameter is NULL or the path cannot be located in the document.
Syntax
JSON_EXTRACT(json_doc, path[, path] ...)
Considerations
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 matched values from the path parameter. If multiple values are returned, the matched values are automatically wrapped in an array in the order they were generated by the paths. Otherwise, a single matched 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