JSON_
On this page
Extracts data from a JSON object or array based on a path specification.
The following JSON_ functions are supported:
-
JSON_is for all numeric data (equivalent to theEXTRACT_ DOUBLE ::%JSON operator). -
JSON_is for all text and binary types (equivalent to theEXTRACT_ STRING ::$JSON operator).The result collation is controlled by the json_engine variable.extract_ string_ collation Refer to Sync Variables Lists for more information. -
JSON_is for all valid JSON types, includingEXTRACT_ JSON true,false, JSON maps, and JSON lists. -
JSON_is for allEXTRACT_ BIGINT BIGINTtypes.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.
Syntax
JSON_EXTRACT_<type>(<json>, <keypath> [, …]);
Arguments
-
json: a valid JSON object or the name of a JSON column. -
keypath: The path specification; a comma-separated list of object keys or zero-indexed array positions.
Return Value
When JSON_ is used with a JSON object or array literal and a path specification, the return values are as listed below.
-
SQL
NULLif value is JSON NULL (such as{"a": null}), or if keyname does not exist. -
The extracted element if
<json>is a valid JSON object or array and the path specified by the list of<keypath>s exists in<json>. -
SQL
NULLif<json>is not a valid JSON object or array or if the path specified by the list of<keypaths>does not exist in<json>. -
SQL
NULLif<json>is SQLNULL, an empty string, a JSON string, a JSON number, a JSON boolean value, or an empty JSON object or array. -
JSON
nullforJSON_if the value is JSONEXTRACT_ JSON null(such as{"a": null}). -
SQL
NULLforJSON_if the value is JSONEXTRACT_ STRING null(such as{"a": null}). -
0 for
JSON_andEXTRACT_ BIGINT JSON_if the value is JSONEXTRACT_ DOUBLE null(such as{"a": null}).
JSON_
The following table summarizes the functionality and return types of JSON_ when both a JSON object or array literal and a path specification are provided as arguments.
|
|
Function |
Return |
Comments |
|---|---|---|---|
|
JSON Object or Array Literal |
|
json or SQL |
Extracts the JSON value found at the path ( If the extracted object is a JSON string, quotes are not removed. Returns SQL |
|
|
string or SQL |
Extracts the JSON value found at the path ( If the extracted object is a JSON string, quotes are removed. Returns SQL |
|
|
|
integer or 0 |
If the extracted value is a JSON string, extracts a valid numeric prefix and rounds to an Integer. Otherwise, returns 0. |
|
|
|
double or 0 |
If the extracted value is a JSON string, extracts a valid numeric prefix. Otherwise, returns 0. |
JSON_
Example 1 - JSON_
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 - JSON_
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 zero-indexed array positions, extracting with the path 0 will extract the value 3 and the path 1 will extract the value 4 as in the example above.
Example 3 - JSON_
Extracts the value for the path gamma.
SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma')AS get_gamma;
+-----------+
| get_gamma |
+-----------+
| [3,4,5] |
+-----------+Example 4 - JSON_
Extracts the value for the path gamma..[3,4,5] 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 - JSON_
The result in the following example is NULL as zeta has no value.
SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'zeta')AS get_zeta;
+-----------+
| get_zeta |
+-----------+
| NULL |
+-----------+Example 6 - JSON_
The result in the next example is rounded up to the next whole integer.1. the result would have been 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 - JSON_
The value for the path gamma is an array so the entire array is returned as a string value.
SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma')AS get_gamma;
+-----------+
| get_gamma |
+-----------+
| [3,4,5] |
+-----------+Example 8 - JSON_
Returns the value for the path 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 |
+-----------+Last modified: April 16, 2025