JSON Functions

## JSONDynamicPathsWithTypes[​](#JSONDynamicPathsWithTypes "Direct link to JSONDynamicPathsWithTypes")

Introduced in: v24.8.0

Returns the list of dynamic paths that are stored as separate subcolumns and their types in each row in JSON column.

**Syntax**

```
JSONDynamicPathsWithTypes(json)
```

**Arguments**

- `json` — JSON column. [`JSON`](/docs/sql-reference/data-types/newjson)

**Returned value**

Returns a map of dynamic paths and their data types in the JSON column. [`Map(String, String)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

```
CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONDynamicPathsWithTypes(json) FROM test;
```

```
┌─json─────────────────────────────────┬─JSONDynamicPathsWithTypes(json)─┐
│ {"a":"42"}                           │ {'a':'Int64'}                   │
│ {"b":"Hello"}                        │ {}                              │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'a':'Array(Nullable(Int64))'}  │
└──────────────────────────────────────┴─────────────────────────────────┘
```

## JSONExtract[​](#JSONExtract "Direct link to JSONExtract")

Introduced in: v19.14.0

Parses JSON and extracts a value with given ClickHouse data type.

**Syntax**

```
JSONExtract(json[, indices_or_keys, ...], return_type)
```

**Arguments**

- `json` — JSON string to parse. [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — A list of zero or more arguments each of which can be either string or integer. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `return_type` — ClickHouse data type to return. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a value of specified ClickHouse data type if possible, otherwise returns the default value for that type.

**Examples**

**Usage example**

```
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))') AS res;
```

```
┌─res──────────────────────────────┐
│ ('hello',[-100,200,300])         │
└──────────────────────────────────┘
```

## JSONExtractArrayRaw[​](#JSONExtractArrayRaw "Direct link to JSONExtractArrayRaw")

Introduced in: v20.1.0

Returns an array with elements of JSON array, each represented as unparsed string.

**Syntax**

```
JSONExtractArrayRaw(json[, indices_or_keys, ...])
```

**Arguments**

- `json` — JSON string to parse. [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — A list of zero or more arguments each of which can be either string or integer. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of strings with JSON array elements. If the part is not an array or does not exist, an empty array will be returned. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT JSONExtractArrayRaw('{"a": "hello", "b": [-100, 200.0, "hello"]}', 'b') AS res;
```

```
┌─res──────────────────────────┐
│ ['-100','200.0','"hello"']   │
└──────────────────────────────┘
```

## JSONExtractArrayRawCaseInsensitive[​](#JSONExtractArrayRawCaseInsensitive "Direct link to JSONExtractArrayRawCaseInsensitive")

Introduced in: v25.8.0

Returns an array with elements of JSON array, each represented as unparsed string, using case-insensitive key matching. This function is similar to [`JSONExtractArrayRaw`](#JSONExtractArrayRaw).

**Syntax**

```
JSONExtractArrayRawCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

- `json` — JSON string to parse [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — Optional. Indices or keys to navigate to the array. Keys use case-insensitive matching [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of raw JSON strings. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**basic**

```
SELECT JSONExtractArrayRawCaseInsensitive('{"Items": [1, 2, 3]}', 'ITEMS')
```

```
['1','2','3']
```

## JSONExtractBool[​](#JSONExtractBool "Direct link to JSONExtractBool")

Introduced in: v20.1.0

Parses JSON and extracts a value of Bool type.

**Syntax**

```
JSONExtractBool(json[, indices_or_keys, ...])
```

**Arguments**

- `json` — JSON string to parse. [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — A list of zero or more arguments each of which can be either string or integer. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a Bool value if it exists, otherwise returns `0`. [`Bool`](/docs/sql-reference/data-types/boolean)

**Examples**

**Usage example**

```
SELECT JSONExtractBool('{"passed": true}', 'passed') AS res;
```

```
┌─res─┐
│   1 │
└─────┘
```

## JSONExtractBoolCaseInsensitive[​](#JSONExtractBoolCaseInsensitive "Direct link to JSONExtractBoolCaseInsensitive")

Introduced in: v25.8.0

Parses JSON and extracts a boolean value using case-insensitive key matching. This function is similar to [`JSONExtractBool`](#JSONExtractBool).

**Syntax**

```
JSONExtractBoolCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

- `json` — JSON string to parse [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the extracted boolean value (1 for true, 0 for false), 0 if not found. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**basic**

```
SELECT JSONExtractBoolCaseInsensitive('{"IsActive": true}', 'isactive')
```

```
1
```

## JSONExtractCaseInsensitive[​](#JSONExtractCaseInsensitive "Direct link to JSONExtractCaseInsensitive")

Introduced in: v25.8.0

Parses JSON and extracts a value of the given ClickHouse data type using case-insensitive key matching. This function is similar to [`JSONExtract`](#JSONExtract).

**Syntax**

```
JSONExtractCaseInsensitive(json [, indices_or_keys...], return_type)
```

**Arguments**

- `json` — JSON string to parse [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `return_type` — The ClickHouse data type to extract [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the extracted value in the specified data type. [`Any`](/docs/sql-reference/data-types)

**Examples**

**int\_type**

```
SELECT JSONExtractCaseInsensitive('{"Number": 123}', 'number', 'Int32')
```

```
123
```

**array\_type**

```
SELECT JSONExtractCaseInsensitive('{"List": [1, 2, 3]}', 'list', 'Array(Int32)')
```

```
[1,2,3]
```

## JSONExtractFloat[​](#JSONExtractFloat "Direct link to JSONExtractFloat")

Introduced in: v20.1.0

Parses JSON and extracts a value of Float type.

**Syntax**

```
JSONExtractFloat(json[, indices_or_keys, ...])
```

**Arguments**

- `json` — JSON string to parse. [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — A list of zero or more arguments each of which can be either string or integer. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a Float value if it exists, otherwise returns `0`. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) AS res;
```

```
┌─res─┐
│ 200 │
└─────┘
```

## JSONExtractFloatCaseInsensitive[​](#JSONExtractFloatCaseInsensitive "Direct link to JSONExtractFloatCaseInsensitive")

Introduced in: v25.8.0