extract_json_value
NAME
extract_json_value(): Extract value from JSON notation via XPathTYPE
FunctionDESCRIPTION
extract_json_value() accepts text in JSON format, and an XPath expression, and extracts data from JSON matching path.
While XPath was originally developed for XML, its usage in other fields became quickly widespread, including searching through object oriented structures. XPath easily applies to JSON.
This function internally relies on json_to_xml(): it first converts the JSON data to XML, then uses ExtractValue to apply XPath.
NOTE: this function is CPU intensive. This solution should ideally be implemented through built-in functions, not stored routines.
SYNOPSIS
extract_json_value(
json_text TEXT CHARSET utf8
xpath TEXT CHARSET utf8
) RETURNS TEXT CHARSET utf8Input:
- json_text: a valid JSON formatted text.
- xpath: a valid XPath notation.
EXAMPLES
Extract JSON data:
mysql> SET @json := '
{
"menu": {
"id": "file",
"value": "File",
"popup": {
"menuitem": [
{"value": "New", "onclick": "CreateNewDoc()"},
{"value": "Open", "onclick": "OpenDoc()"},
{"value": "Close", "onclick": "CloseDoc()"}
]
}
}
}
';
mysql> SELECT extract_json_value(@json, '//id') AS result;
+--------+
| result |
+--------+
| file |
+--------+
mysql> SELECT extract_json_value(@json, 'count(/menu/popup/menuitem)') AS count_items;
+-------------+
| count_items |
+-------------+
| 3 |
+-------------+