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> [, ]);

JSON_EXTRACT_<type>(<json>);

Arguments

json: valid JSON, or the name of a JSON column.
keypath: (optional) 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}).

Refer to JSON_EXTRACT_<type> - JSON Argument Only the return values of JSON_EXTRACT_<type> when used with a JSON argument only.

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.

JSON_EXTRACT_<type> - JSON Argument Only

The following table summarizes the functionality and return values of JSON_EXTRACT_<type> over a JSON argument only.

For all functions, the return value matches the type of the function. JSON_EXTRACT_JSON returns a JSON value, JSON_EXTRACT_STRING returns a string value, and so on.

<json>	Function	Return	Notes
SQL-NULL	`JSON_EXTRACT_<type>(NULL)`	SQL-NULL
Empty string	`JSON_EXTRACT_<type>('')`	SQL-NULL
JSON-null	`JSON_EXTRACT_JSON('null')`	JSON-null	Returns JSON-null
	`JSON_EXTRACT_STRING('null')` `JSON_EXTRACT_BIGINT('null')` `JSON_EXTRACT_DOUBLE('null')`	SQL-NULL
Empty JSON Object	`JSON_EXTRACT_JSON('{}')`	`{}`	Returns JSON value.
	`JSON_EXTRACT_STRING('{}')`	`{}`	Returns string value.
	`JSON_EXTRACT_BIGINT('{}')` `JSON_EXTRACT_DOUBLE('{}')`	`0`	Return value matches type of function.
JSON boolean value	`JSON_EXTRACT_JSON('true')` `JSON_EXTRACT_JSON('false')`	`true` `false`	Returns JSON value.
	`JSON_EXTRACT_STRING('true')` `JSON_EXTRACT_STRING('false')`	`true` `false`	Returns string value.
	`JSON_EXTRACT_BIGINT('true')` `JSON_EXTRACT_DOUBLE('true')` `JSON_EXTRACT_BIGINT('false')` `JSON_EXTRACT_DOUBLE('false')`	`1` `0`	Returns 1 for true, 0 for false. Return value matches type of function.
JSON string	`JSON_EXTRACT_JSON('"<string>"')`	"<string>"	Does not remove quotes.
	`JSON_EXTRACT_STRING('"<string>"')`	<string >	Removes quotes.
	`JSON_EXTRACT_BIGINT('"<string>"')` `JSON_EXTRACT_DOUBLE('"<string>"')`	numeric prefix or `0`	If string has a valid numeric prefix, returns the numeric prefix, rounded appropriately. Returns 0 if the string does not have a valid numeric prefix.
JSON number	`JSON_EXTRACT_JSON('<numeric>')`	numeric value	Returns JSON value.
	`JSON_EXTRACT_STRING('<numeric>')`	numeric value	Returns string value.
	`JSON_EXTRACT_BIGINT('<numeric>')`	rounded numeric value	Returns BIGINT.
	`JSON_EXTRACT_DOUBLE('<numeric>')`	numeric value	Returns DOUBLE.
JSON object	`JSON_EXTRACT_JSON(<json object>)` `JSON_EXTRACT_STRING(<json object>)`	<json object>	Extracts full object.
	`JSON_EXTRACT_BIGINT(<json object>)` `JSON_EXTRACT_DOUBLE(<json object>)`	0	Return value matches type of function.
JSON array	`JSON_EXTRACT_JSON(<json array>)` `JSON_EXTRACT_STRING(<json array˘>)`	<json array>	Extracts full array.
	`JSON_EXTRACT_BIGINT(<json array>)` `JSON_EXTRACT_DOUBLE(<json array>)`	`0`	Return value matches type of function.
Invalid JSON	`JSON_EXTRACT_JSON(<invalid json>)` `JSON_EXTRACT_STRING(<invalid json>)`	SQL-NULL
	`JSON_EXTRACT_BIGINT(<invalid json>)` `JSON_EXTRACT_DOUBLE(<invalid json>)`	`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  |
+-----------+

Single Argument Examples

Example 1 - JSON_EXTRACT_JSON

Extract from a JSON string with " " quotes. The JSON_EXTRACT_JSON function does not remove the " " quotes from the string.

SQL

SELECT JSON_EXTRACT_JSON('"a string"');

+---------------------------------------------+
| JSON_EXTRACT_JSON('"a string"')             |
+---------------------------------------------+
|                         "a string"          |
+---------------------------------------------+

Extract from a string without " " quotes.

SQL

SELECT JSON_EXTRACT_JSON('a string');

+----------------------------------------+
| JSON_EXTRACT_JSON('a string')          |
+----------------------------------------+
|                         NULL           |
+----------------------------------------+

The JSON_EXTRACT_JSON function with one argument will return the JSON input if the input is valid JSON or SQL-NULL if the input is invalid JSON.

Extract from a JSON array. Will extract the entire array.

SQL

SELECT JSON_EXTRACT_JSON('["array element"]');

+----------------------------------------+
| JSON_EXTRACT_JSON('["array element"]') |
+----------------------------------------+
|                   ["array element"]    |
+----------------------------------------+

Extract from a JSON object with two key-value pairs, for which the values are arrays with a single element. The entire JSON object will be extracted.

SQL

SELECT JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}');

+----------------------------------------------+
| JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}') |
+----------------------------------------------+
|                     {"key2":[2],"key3":[3]}  |
+----------------------------------------------+

Example 2 - JSON_EXTRACT_STRING

Extract from a string. The JSON_EXTRACT_STRING function removes the " " quotes from the string.

SQL

SELECT JSON_EXTRACT_STRING('"a string"');

+----------------------------------+
| JSON_EXTRACT_STRING('"a string"')|
+----------------------------------+
|                    a string      |
+----------------------------------+

The JSON_EXTRACT_STRING function will return the JSON input as a string if the input is valid JSON or SQL-NULL if the input is invalid JSON.

Extract from a JSON array with one element which is a string. Will extract the entire array.

SQL

SELECT JSON_EXTRACT_STRING('["array element"]');

+------------------------------------------+
| JSON_EXTRACT_STRING('["array element"]') |
+------------------------------------------+
|                     ["array element"]    |
+------------------------------------------+

Extract from a JSON object with two key-value pairs, for which the values are arrays with a single element. The entire JSON object will be extracted.

SQL

SELECT JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}');

+------------------------------------------------+
| JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}') |
+------------------------------------------------+
|                    {"key2":[2],"key3":[3]}     |
+------------------------------------------------+

Example 3 - JSON_EXTRACT_BIGINT

Extract from a JSON double value. Rounds to the nearest integer.

SQL

SELECT JSON_EXTRACT_BIGINT('3.14');

+-------------------------------+
| JSON_EXTRACT_BIGINT('3.14')   |
+-------------------------------+
|                          3    |
+-------------------------------+

Extract from a JSON string with a numeric prefix. Extracts a valid numeric prefix from the string and rounds to the nearest integer.

SQL

SELECT JSON_EXTRACT_BIGINT('"12.3a"');

+-------------------------------+
| JSON_EXTRACT_BIGINT('"12.3a"')|
+-------------------------------+
|                       12      |
+-------------------------------+

Example 4 - JSON_EXTRACT_DOUBLE

Extract from a JSON double value.

SQL

SELECT JSON_EXTRACT_DOUBLE('3.14');

+-------------------------------+
| JSON_EXTRACT_DOUBLE('3.14')   |
+-------------------------------+
|                          3.14 |
+-------------------------------+

Extract from a JSON string containing a double value. Extracts a valid numeric prefix from the string.

SQL

SELECT JSON_EXTRACT_DOUBLE('"12.3a"');

+-------------------------------+
| JSON_EXTRACT_DOUBLE('"12.3a"')|
+-------------------------------+
|                       12.3    |
+-------------------------------+

JSON_EXTRACT_<type>

On this page

Syntax

Arguments

Return Value

JSON_EXTRACT_<type> with Path Specification

JSON_EXTRACT_<type> - JSON Argument Only

Multi-Argument Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Single Argument Examples

Example 1 - JSON_EXTRACT_JSON

Example 2 - JSON_EXTRACT_STRING

Example 3 - JSON_EXTRACT_BIGINT

Example 4 - JSON_EXTRACT_DOUBLE

Was this article helpful?

On this page

Was this article helpful?