JSON_EXTRACT_<type>

JSON_EXTRACT_DOUBLE is for all numeric data (equivalent to the ::% JSON operator).
JSON_EXTRACT_STRING is for all text and binary types (equivalent to the ::$ JSON operator). The result collation is controlled by the json_extract_string_collation engine variable. Refer to Sync Variables Lists for more information.
JSON_EXTRACT_JSON is for all valid JSON types, including true, false, JSON maps, and JSON 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.

Syntax

SQL

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_EXTRACT_<type> is used with a JSON object or array literal and a path specification, the return values are as listed below. Refer to JSON_EXTRACT_<type> with Path Specification for more details.

SQL NULL if 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 NULL if <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 NULL if <json> is SQL NULL, an empty string, a JSON string, a JSON number, a JSON boolean value, or an empty JSON object or array.
JSON null for JSON_EXTRACT_JSON if the value is JSON null (such as {"a": null}).
SQL NULL for JSON_EXTRACT_STRING if the value is JSON null (such as {"a": null}).
0 for JSON_EXTRACT_BIGINT and JSON_EXTRACT_DOUBLE if the value is JSON null (such as {"a": null}).

JSON_EXTRACT_<type> with Path Specification

The following table summarizes the functionality and return types of JSON_EXTRACT_<type> when both a JSON object or array literal and a path specification are provided as arguments.

`<json>`	Function	Return	Comments
JSON Object or Array Literal	`JSON_EXTRACT_JSON(<json>, <keypath> [,...])`	json or SQL `NULL`	Extracts the JSON value found at the path (`<keypath> [,...]`). Value returned as JSON. If the extracted object is a JSON string, quotes are not removed. Returns SQL `NULL` if keypath is not found.
	`JSON_EXTRACT_STRING(<json>, <keypath> [,...])`	string or SQL `NULL`	Extracts the JSON value found at the path (`<keypath> [,...]`). Value returned as a string. If the extracted object is a JSON string, quotes are removed. Returns SQL `NULL` if keypath is not found.
	`JSON_EXTRACT_BIGINT(<json>, <keypath> [,...])`	integer or 0	If the extracted value is a JSON string, extracts a valid numeric prefix and rounds to an Integer. Value returned as a BIGINT. Otherwise, returns 0.
	`JSON_EXTRACT_DOUBLE(<json>, <keypath> [,...])`	double or 0	If the extracted value is a JSON string, extracts a valid numeric prefix. Value returned as DOUBLE. Otherwise, returns 0.

Multi-Argument Examples

Example 1

Extracts the value for beta.

SQL

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.

SQL

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

Extracts the value for the path gamma.

SQL

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 the path gamma.1. Since arrays use zero-based indexing, the function will extract the number in the first position of the array [3,4,5] which is 4 in this example.

SQL

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 no value.

SQL

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

SQL

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 the path gamma is an array so the entire array is returned as a string value.

SQL

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 the path gamma which is a non-numeric string in this example.

SQL

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

+-----------+
| get_gamma |
+-----------+
| A string  |
+-----------+

JSON_EXTRACT_<type>

On this page

Syntax

Arguments

Return Value

JSON_EXTRACT_<type> with Path Specification

Multi-Argument Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Was this article helpful?

On this page

Was this article helpful?