Skip to main content

SingleStoreDB v8.1

JSON_EXTRACT_<type>

Removes data from a JSON document determined by what parts of the path arguments that match .

Will return NULL if there are no matching paths or if any of the arguments are NULL.

{“alpha”: 1, “beta”: 2, “gamma”: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.

    Note

    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.

Example 1

Extracts the value for beta.

SELECT JSON_EXTRACT_DOUBLE('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'beta') 
as get_beta;

****
+----------+
| get_beta |
+----------+
|        2 |
+----------+

Example 2

Extracts value for 1 from an array.

SELECT JSON_EXTRACT_DOUBLE('[3,4,5]', 1) as get_result;

****
+------------+
| get_result |
+------------+
|          4 |
+------------+

Note

Since JSON uses a zero-indexed array positions, extracting for zero will result in 3 and 2 will result in a 5 for the example above.

Example 3

Extracts the value for gamma which is a string.

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma') 
as get_gamma;

****
+-----------+
| get_gamma |
+-----------+
| [3,4,5]   |
+-----------+

Example 4

Extracts the value for gamma. Since arrays use zero based numbering, the 1 after gamma will extract the number in the first position of the string which is 4 in this example.

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma', 1) 
as get_gamma;

****
+-----------+
| get_gamma |
+-----------+
| 4         |
+-----------+

Example 5

The result in the following example is NULL as zeta has not indicated value.

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'zeta') 
as get_zeta;

****
+-----------+
| get_zeta  |
+-----------+
| NULL      |
+-----------+

Example 6

The result in the next example is rounded up to the next whole integer. If the value had been 1.25 the result would have been a 1.

SELECT JSON_EXTRACT_BIGINT('{"alpha":1, "beta":2, "gamma": [3,4,5], "delta":1.5}', 'delta') 
as get_delta;

****
+-----------+
| get_delta |
+-----------+
| 2         |
+-----------+

Example 7

The value for gamma is a string so the entire string is returned.

SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma') 
as get_gamma;

****
+-----------+
| get_gamma |
+-----------+
| [3,4,5]   |
+-----------+

Example 8

Returns the value for gamma which is a non-numeric string in this example.

SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": "A string"}', 'gamma') 
as get_gamma;

****
+-----------+
| get_gamma |
+-----------+
| A string  |
+-----------+
In this section: