JSON_EXTRACT_<type>

Extracts data from a JSON object or array based on a path specification. Will return NULL if the path is not matched or if any of the arguments are NULL.

The following JSON_EXTRACT_<type> functions are supported:

  • 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

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.

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

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. If the value had been 1.25 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

Was this article helpful?