DB Software v7.8

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

  • JSON_EXTRACT_BIGINT is for all BIGINT 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 |
+----------+
In this section: