JSON_EXTRACT_<type>

The JSON_EXTRACT_<type>() function extracts data from a JSON object or array using a path specification. The function returns NULL if the path does not match or if either argument is NULL.

SingleStore supports the following JSON_EXTRACT_<type> functions:

  • JSON_EXTRACT_DOUBLE extracts numeric values and is equivalent to the ::% JSON operator.

  • JSON_EXTRACT_STRING extracts text and binary values and is equivalent to the ::$ JSON operator. The result collation is controlled by the json_extract_string_collation engine variable. Refer to List of Engine Variables for more information.

  • JSON_EXTRACT_JSON extracts any valid JSON value, including true, false, JSON maps, and JSON lists.

  • JSON_EXTRACT_BIGINT extracts BIGINT values. For other data types booleans (true and false) are converted to 1 and 0, respectively, and floating-point values are rounded to the nearest integer (for example, 1.4 becomes 1, and 1.5 becomes 2).

The :: operator can be applied to columns of any scalar type (JSON, BLOB, INT, and so on). It does not work with ROW expression or RECORD type, such as ROW('value1', 'value2')::field_name. Note that ROW is not a type; it is an expression that returns a RECORD type. Applying the :: operator to a RECORD or ARRAY value returns an error. The :: operator also cannot be used on user-defined function (UDF) outputs. To access values from a ROW expression, define the individual named columns in the query.

Note

Refer to Accessing Fields in a JSON Object for more information on JSON operators ::, ::$, and ::%. Refer to 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.

JSON_EXTRACT_<type> with Path Specification Examples

Example 1 - JSON_EXTRACT_DOUBLE

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

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

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 - JSON_EXTRACT_JSON - Array value

Extracts the value for the path gamma.1. Since arrays use zero-based indexing, the function will extract the number in the second 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 - JSON_EXTRACT_JSON - NULL value

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

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

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

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:

Was this article helpful?