Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.


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, including true, false, JSON maps, and lists.


    For more information on JSON operators ::, ::$, and ::%, see Accessing Fields in a JSON Object. Also, see the Character Encodings and Collation section for information on overriding collation settings.

JSON_EXTRACT_<type>(json, keypath)


  • 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.


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  |