JSON_ EXTRACT_ <type>
On this page
Extracts data from JSON, optionally based on a path specification.
When used with JSON object or array and a path specification, extracts data from the JSON object or array that matches the path specification.
When used with a JSON argument and no path specification, extracts data from the JSON to a value of <type>.
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 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
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_
is used with a JSON object or array literal and a path specification, the return values are as listed below.
-
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>
isSQL-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_
if the value is JSON-null (such asEXTRACT_ JSON {"a": null}
). -
SQL-NULL for
JSON_
if the value is JSON-null (such asEXTRACT_ STRING {"a": null}
). -
0 for
JSON_
andEXTRACT_ BIGINT JSON_
if the value is JSON-null (such asEXTRACT_ DOUBLE {"a": null}
).
Refer to JSON_JSON_
when used with a JSON argument only.
JSON_ EXTRACT_ <type> with Path Specification
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-NULL |
Extracts the JSON value found at the path ( If the extracted object is a JSON string, quotes are not removed. Returns SQL-NULL if keypath is not found. |
|
string or SQL-NULL |
Extracts the JSON value found at the path ( If the extracted object is a JSON string, quotes are removed. Returns SQL-NULL if keypath is not found. |
|
|
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_ EXTRACT_ <type> - JSON Argument Only
The following table summarizes the functionality and return values of JSON_
over a JSON argument only.
For all functions, the return value matches the type of the function.JSON_
returns a JSON value, JSON_
returns a string value, and so on.
<json> |
Function |
Return |
Notes |
---|---|---|---|
SQL-NULL |
|
SQL-NULL |
|
Empty string |
|
SQL-NULL |
|
JSON-null |
|
JSON-null |
Returns JSON-null |
|
SQL-NULL |
||
Empty JSON Object |
|
|
Returns JSON value. |
|
|
Returns string value. |
|
|
|
Return value matches type of function. |
|
JSON boolean value |
|
|
Returns JSON value. |
|
|
Returns string value. |
|
|
|
Returns 1 for true, 0 for false. Return value matches type of function. |
|
JSON string |
|
"<string>" |
Does not remove quotes. |
|
<string > |
Removes quotes. |
|
|
numeric prefix or
|
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 |
|
numeric value |
Returns JSON value. |
|
numeric value |
Returns string value. |
|
|
rounded numeric value |
Returns BIGINT. |
|
|
numeric value |
Returns DOUBLE. |
|
JSON object |
|
<json object> |
Extracts full object. |
|
0 |
Return value matches type of function. |
|
JSON array |
|
<json array> |
Extracts full array. |
|
|
Return value matches type of function. |
|
Invalid JSON |
|
SQL-NULL |
|
|
|
Multi-Argument Examples
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 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
.
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.
.[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
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
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
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
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 |
+-----------+
Single Argument Examples
Example 1 - JSON_ EXTRACT_ JSON
Extract from a JSON string with " "
quotes.JSON_
function does not remove the " "
quotes from the string.
SELECT JSON_EXTRACT_JSON('"a string"');
+---------------------------------------------+
| JSON_EXTRACT_JSON('"a string"') |
+---------------------------------------------+
| "a string" |
+---------------------------------------------+
Extract from a string without " "
quotes.
SELECT JSON_EXTRACT_JSON('a string');
+----------------------------------------+
| JSON_EXTRACT_JSON('a string') |
+----------------------------------------+
| NULL |
+----------------------------------------+
The 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.
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.
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.JSON_
function removes the " "
quotes from the string.
SELECT JSON_EXTRACT_STRING('"a string"');
+----------------------------------+
| JSON_EXTRACT_STRING('"a string"')|
+----------------------------------+
| a string |
+----------------------------------+
The JSON_
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.
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.
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.
SELECT JSON_EXTRACT_BIGINT('3.14');
+-------------------------------+
| JSON_EXTRACT_BIGINT('3.14') |
+-------------------------------+
| 3 |
+-------------------------------+
Extract from a JSON string with a numeric prefix.
SELECT JSON_EXTRACT_BIGINT('"12.3a"');
+-------------------------------+
| JSON_EXTRACT_BIGINT('"12.3a"')|
+-------------------------------+
| 12 |
+-------------------------------+
Example 4 - JSON_ EXTRACT_ DOUBLE
Extract from a JSON double value.
SELECT JSON_EXTRACT_DOUBLE('3.14');
+-------------------------------+
| JSON_EXTRACT_DOUBLE('3.14') |
+-------------------------------+
| 3.14 |
+-------------------------------+
Extract from a JSON string containing a double value.
SELECT JSON_EXTRACT_DOUBLE('"12.3a"');
+-------------------------------+
| JSON_EXTRACT_DOUBLE('"12.3a"')|
+-------------------------------+
| 12.3 |
+-------------------------------+
Last modified: September 30, 2024