JSON_EXTRACT_<type>
This is a set of 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.JSON_EXTRACT_BIGINT
is for allBIGINT
types. Other data types are handled as follows: Booleans (true and false) become 1 and 0, respectively. Floats are rounded as follows: 1.4 becomes 1, while 1.5 becomes 2. For all other data types, 0 is returned.Notice
For more information on JSON operators
::
,::$
, and::%
, see Accessing Fields in a JSON Object. Also, see Character Encodings and Collation for information on overriding collation settings.
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, if value is JSON NULL (such as
{"a": NULL}
), or if keyname does not exist.
Examples
SELECT JSON_EXTRACT_DOUBLE('{"a":1, "b":2, "c": [3,4,5]}', 'b') as get_b; +-------+ | get_b | +-------+ | 2 | +-------+ SELECT JSON_EXTRACT_JSON('{"a":1, "b":2, "c": [3,4,5]}', 'c') as get_c; +---------+ | get_c | +---------+ | [3,4,5] | +---------+ SELECT JSON_EXTRACT_JSON('{"a":1, "b":2, "c": [3,4,5]}', 'c', 1) as get_c; +-------+ | get_c | +-------+ | 4 | +-------+ SELECT JSON_EXTRACT_JSON('{"a":1, "b":2, "c": [3,4,5]}', 'z') as get_z; +-------+ | get_z | +-------+ | NULL | +-------+ SELECT JSON_EXTRACT_BIGINT('{"a":1, "b":2, "c": [3,4,5], "d":1.5}', 'd') as get_d; +-------+ | get_d | +-------+ | 2 | +-------+ SELECT JSON_EXTRACT_STRING('{"a":1, "b":2, "c": [3,4,5]}', 'c') as get_c; +---------+ | get_c | +---------+ | [3,4,5] | +---------+ SELECT JSON_EXTRACT_STRING('{"a":1, "b":2, "c": "A string"}', 'c') as get_c; +----------+ | get_c | +----------+ | A string | +----------+