JSON_ EXTRACT_ <type>
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 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> [, …]);
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-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}
).
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. |
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 |
+-----------+
Last modified: September 30, 2024