Outdated Version
You are viewing an older version of this section. View current production version.
JSON_EXTRACT_<type>
This is a set of three JSON functions, which returns the value of a given key in a JSON map or an element in a JSON array. Maps are in the form:
{"a": 1, "b": 2, "c": 3}
-
JSON_EXTRACT_DOUBLE
is for all numeric data (equivalent to the::%
JSON operator). JavaScript has only one numeric type. -
JSON_EXTRACT_STRING
is for all text and binary types (equivalent to the::$
JSON operator). -
JSON_EXTRACT_JSON
is for all valid JavaScript types, includingtrue
,false
, JSON maps, and lists.InfoFor more info on JSON operators
::
,::$
, and::%
, see Accessing Fields in a JSON Object.
JSON_EXTRACT_<type>(json, keypath)
Arguments
- json: a valid JSON map, or the name of a JSON column.
- keypath: The path to the key to extract (comma-separated list of dictionary keys or zero-indexed array positions).
Return Value
- The extracted element.
- SQL NULL if json is not a valid JSON array, or if keyname does not exist.
Examples
mysql> select json_extract_double('{"a":1, "b":2, "c": [3,4,5]}', 'b') as get_b;
+-------+
| get_b |
+-------+
| 2 |
+-------+
mysql> select json_extract_json('{"a":1, "b":2, "c": [3,4,5]}', 'c') as get_c;
+---------+
| get_c |
+---------+
| [3,4,5] |
+---------+
mysql> select json_extract_json('{"a":1, "b":2, "c": [3,4,5]}', 'c', 1) as get_c;
+-------+
| get_c |
+-------+
| 4 |
+-------+
mysql> select json_extract_json('{"a":1, "b":2, "c": [3,4,5]}', 'z') as get_z;
+-------+
| get_z |
+-------+
| NULL |
+-------+