# JSON\_EXTRACT\_\<type>

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](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists.md) 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](https://docs.singlestore.com/db/v9.1/create-a-database/using-json.md). Also, see [Character Encodings and Collation](https://docs.singlestore.com/db/v9.1/create-a-database/using-json.md) for information on overriding collation settings.

## Syntax

```sql
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](https://docs.singlestore.com/#section-idm234533607462939.md) 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](https://docs.singlestore.com/#section-idm234533609752868.md) 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 ObjectorArray Literal | `JSON_EXTRACT_JSON(<json>, <keypath> [,...])`   | jsonorSQL`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> [,...])` | stringorSQL`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> [,...])` | integeror0        | 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> [,...])` | doubleor0         | 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 prefixor`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.

```sql
SELECT JSON_EXTRACT_DOUBLE('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'beta') 
AS get_beta;


```

```output

+----------+
| get_beta |
+----------+
|        2 |
+----------+
```

## Example 2 - JSON\_EXTRACT\_DOUBLE

Extracts value for 1 from an array.

```sql
SELECT JSON_EXTRACT_DOUBLE('[3,4,5]', 1) AS get_result;


```

```output

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

```sql
SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma') 
AS get_gamma;


```

```output

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

```sql
SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma', 1) 
AS get_gamma;


```

```output

+-----------+
| get_gamma |
+-----------+
| 4         |
+-----------+
```

## Example 5 - JSON\_EXTRACT\_JSON - NULL value

The result in the following example is `NULL` as zeta has no value.

```sql
SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'zeta') 
AS get_zeta;


```

```output

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

```sql
SELECT JSON_EXTRACT_BIGINT('{"alpha":1, "beta":2, "gamma": [3,4,5], "delta":1.5}', 'delta') 
AS get_delta;


```

```output

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

```sql
SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma') 
AS get_gamma;


```

```output

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

```sql
SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": "A string"}', 'gamma') 
AS get_gamma;


```

```output

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

```sql
SELECT JSON_EXTRACT_JSON('"a string"');

```

```output

+---------------------------------------------+
| JSON_EXTRACT_JSON('"a string"')             |
+---------------------------------------------+
|                         "a string"          |
+---------------------------------------------+

```

Extract from a string without `" "` quotes.

```sql
SELECT JSON_EXTRACT_JSON('a string');

```

```output

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

```sql
SELECT JSON_EXTRACT_JSON('["array element"]');

```

```output

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

```sql
SELECT JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}');

```

```output

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

```sql
SELECT JSON_EXTRACT_STRING('"a string"');

```

```output

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

```sql
SELECT JSON_EXTRACT_STRING('["array element"]');

```

```output

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

```sql
SELECT JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}');

```

```output

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

```sql
SELECT JSON_EXTRACT_BIGINT('3.14');

```

```output

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

```sql
SELECT JSON_EXTRACT_BIGINT('"12.3a"');

```

```output

+-------------------------------+
| JSON_EXTRACT_BIGINT('"12.3a"')|
+-------------------------------+
|                       12      |
+-------------------------------+
```

## Example 4 - JSON\_EXTRACT\_DOUBLE

Extract from a JSON double value.

```sql
SELECT JSON_EXTRACT_DOUBLE('3.14');

```

```output

+-------------------------------+
| JSON_EXTRACT_DOUBLE('3.14')   |
+-------------------------------+
|                          3.14 |
+-------------------------------+
```

Extract from a JSON string containing a double value. Extracts a valid numeric prefix from the string.

```sql
SELECT JSON_EXTRACT_DOUBLE('"12.3a"');

```

```output

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

***

Modified at: April 8, 2026

Source: [/db/v9.1/reference/sql-reference/json-functions/json-extract-type/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-extract-type/)

(An index of the documentation is available at /llms.txt)