# JSON\_EXTRACT\_\<type>

The `JSON_EXTRACT_<type>` function extracts data from JSON, optionally using a path specification.

When used with a JSON object or array and a path specification, it extracts data from the JSON object or array that matches the path specification. The function returns `NULL` if the path does not match or if either argument is `NULL`.

When used with a JSON argument and no path specification, it extracts data from the JSON value as the specified `<type>`. This usage supports extraction from JSON string, numeric, boolean, and `NULL` values, in addition to JSON objects and arrays.

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 [Sync Variables Lists](https://docs.singlestore.com/cloud/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists.md) 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](https://docs.singlestore.com/cloud/create-a-database/using-json/#UUID-ebdae7a0-7988-45d3-c146-8dc01f6cce67.md) for more information on JSON operators `::`, `::$`, and `::%`. Refer to [Character Encodings and Collation](https://docs.singlestore.com/cloud/create-a-database/using-json/#UUID-00e2ca08-8dc7-bc24-f268-b6bfde552e6a.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    |
+-------------------------------+

```

***

Modified at: June 26, 2026

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

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