JSON Functions

**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 UInt value, 0 if not found or cannot be converted. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**basic**

```
SELECT JSONExtractUIntCaseInsensitive('{"COUNT": 789}', 'count')
```

```
789
```

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

Introduced in: v20.1.0

Checks for the existence of the provided value(s) in the JSON document.

**Syntax**

```
JSONHas(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. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns `1` if the value exists in `json`, otherwise `0` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 1;
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4) = 0;
```

```
1
0
```

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

Introduced in: v20.1.0

Returns the key of a JSON object field by its index (1-based). If the JSON is passed as a string, it is parsed first. The second argument is a JSON path to navigate into nested objects. The function returns the key name at the specified position.

**Syntax**

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

**Arguments**

- `json` — JSON string to parse. [`String`](/docs/sql-reference/data-types/string)
- `indices_or_keys` — Optional list of indices or keys specifying a path to a nested element. Each argument can be either a string (access by key) or an integer (access by index starting from 1). [`String`](/docs/sql-reference/data-types/string) or [`Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the key name at the specified position in the JSON object. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT JSONKey('{"a": "hello", "b": [-100, 200.0, 300]}', 1);
```

```
a
```

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

Introduced in: v20.1.0

Return the length of a JSON array or a JSON object.
If the value does not exist or has the wrong type, `0` will be returned.

**Syntax**

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

**Arguments**

- `json` — JSON string to parse [`String`](/docs/sql-reference/data-types/string)
- `[, indices_or_keys, ...]` — Optional. A list of zero or more arguments. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the length of the JSON array or JSON object, otherwise returns `0` if the value does not exist or has the wrong type. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 3;
SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}') = 2;
```

```
1
1
```

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

Introduced in: v23.10.0

Returns the merged JSON object string which is formed by merging multiple JSON objects.

**Syntax**

```
JSONMergePatch(json1[, json2, ...])
```

**Aliases**: `jsonMergePatch`

**Arguments**

- `json1[, json2, ...]` — One or more strings with valid JSON. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the merged JSON object string, if the JSON object strings are valid. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT JSONMergePatch('{"a":1}', '{"name": "joey"}', '{"name": "tom"}', '{"name": "zoey"}') AS res;
```

```
┌─res───────────────────┐
│ {"a":1,"name":"zoey"} │
└───────────────────────┘
```

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

Introduced in: v24.8.0

Returns the list of paths that are stored in shared data structure in JSON column.

**Syntax**

```
JSONSharedDataPaths(json)
```

**Arguments**

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

**Returned value**

Returns an array of paths stored in shared data structure in the JSON column. [`Array(String)`](/docs/sql-reference/data-types/array)

**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, JSONSharedDataPaths(json) FROM test;
```

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

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

Introduced in: v24.8.0

Returns the list of paths that are stored in shared data structure and their types in each row in JSON column.

**Syntax**

```
JSONSharedDataPathsWithTypes(json)
```

**Arguments**

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

**Returned value**

Returns a map of paths stored in shared data structure 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, JSONSharedDataPathsWithTypes(json) FROM test;
```

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

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

Introduced in: v20.1.0

Return the type of a JSON value. If the value does not exist, `Null=0` will be returned.

**Syntax**

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

**Arguments**

- `json` — JSON string to parse [`String`](/docs/sql-reference/data-types/string)
- `json[, 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)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the type of a JSON value as a string, otherwise if the value doesn't exist it returns `Null=0` [`Enum`](/docs/sql-reference/data-types/enum)

**Examples**

**Usage example**

```
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}') = 'Object';
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'String';
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 'Array';
```

```
1
1
1
```

## JSON\_EXISTS[​](#JSON_EXISTS "Direct link to JSON_EXISTS")

Introduced in: v21.8.0

If the value exists in the JSON document, `1` will be returned.
If the value does not exist, `0` will be returned.

**Syntax**

```
JSON_EXISTS(json, path)
```

**Arguments**

- `json` — A string with valid JSON. [`String`](/docs/sql-reference/data-types/string)
- `path` — A string representing the path. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `1` if the value exists in the JSON document, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**