Sign inTry Free

Important

The SingleStore 9.1 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 9.1.

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. Will return NULL if the path is not matched or if either of the arguments are NULL.

When used with a JSON argument and no path specification, extracts data from the JSON to a value of <type>. With this usage, it is possible to extract from JSON string, numeric, boolean, and NULL values in addition to extracting from JSON objects and arrays.

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


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

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

JSON_EXTRACT_<type> - JSON Argument Only 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.

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

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.

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.

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.

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.

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.

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.

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. Extracts a valid numeric prefix from the string.

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

Encoded Operations for JSON_EXTRACT_BIGINT

When you use JSON_EXTRACT_BIGINT(<json>, <keypath> [...]) on a JSON columnstore column stored as SeekableJSON, SingleStore evaluates predicates and GROUP BY operations directly on the encoded representation of extracted integer or boolean values. The engine does not decode all JSON values before evaluation. This optimization can significantly improve the performance of filters, GROUP BY, and aggregate functions over JSON integer and boolean key paths.

Encoded operations for JSON_EXTRACT_BIGINT(...) apply only when the following conditions are met:

  • The JSON column uses SeekableJSON encoding.

  • The key path contains non-repeating values (at most one value or NULL per row). JSON arrays or nested collections at that path are not supported.

  • The extracted values use one of the following internal encodings: Integer, IntegerRLE, or SeekableIntegerRLE.

  • The query accesses the key path using JSON_EXTRACT_BIGINT(...).

  • The query does not also select the full JSON document from the same column. For example, selecting the JSON column together with JSON_EXTRACT_BIGINT(...) disables this optimization.

Note

Other JSON extraction functions, such as JSON_EXTRACT_STRING and JSON_EXTRACT_DOUBLE, decode JSON values before evaluation. They do not use encoded operations.

Example

The following example uses encoded operations for both filtering and aggregation:

-- Uses encoded operations for filter and aggregation
SELECT
    JSON_EXTRACT_BIGINT(data, 'is_premium') AS is_premium,
    JSON_EXTRACT_BIGINT(data, 'level') DIV 10 AS level_bucket,
    COUNT(*) AS count,
    AVG(JSON_EXTRACT_BIGINT(data, 'points')) AS avg_points
FROM json_mixed_test
WHERE JSON_EXTRACT_BIGINT(data, 'level') > 20
GROUP BY is_premium, level_bucket;

In this example:

  • The WHERE JSON_EXTRACT_BIGINT(data, 'level') > 20 predicate runs on encoded integer values.

  • The computed expression level DIV 10 operates on encoded data.

  • GROUP BY and AVG(points) also execute on encoded values.

  • The engine decodes only rows that satisfy the filter, rather than all rows in the table.

Last modified: March 2, 2026

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In