JSON Functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/json-functions.md)

## Types of JSON functions[​](#types-of-functions "Direct link to Types of JSON functions")

There are two sets of functions to parse JSON:

- [`simpleJSON*` (`visitParam*`)](#simplejson-visitparam-functions) which is made for parsing a limited subset of JSON extremely fast.
- [`JSONExtract*`](#jsonextract-functions) which is made for parsing ordinary JSON.

### simpleJSON (visitParam) functions[​](#simplejson-visitparam-functions "Direct link to simpleJSON (visitParam) functions")

ClickHouse has special functions for working with simplified JSON. All these JSON functions are based on strong assumptions about what the JSON can be. They try to do as little as possible to get the job done as quickly as possible.

The following assumptions are made:

1. The field name (function argument) must be a constant.
2. The field name is somehow canonically encoded in JSON. For example: `simpleJSONHas('{"abc":"def"}', 'abc') = 1`, but `simpleJSONHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0`
3. Fields are searched for on any nesting level, indiscriminately. If there are multiple matching fields, the first occurrence is used.
4. The JSON does not have space characters outside of string literals.

### JSONExtract functions[​](#jsonextract-functions "Direct link to JSONExtract functions")

These functions are based on [simdjson](https://github.com/lemire/simdjson), and designed for more complex JSON parsing requirements.

### Case-Insensitive JSONExtract Functions[​](#case-insensitive-jsonextract-functions "Direct link to Case-Insensitive JSONExtract Functions")

These functions perform ASCII case-insensitive key matching when extracting values from JSON objects.
They work identically to their case-sensitive counterparts, except that object keys are matched without regard to case.
When multiple keys match with different cases, the first match is returned.

Note

These functions may be less performant than their case-sensitive counterparts, so use the regular JSONExtract functions if possible.

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

Introduced in: v24.8.0

Returns the list of all paths stored in each row in JSON column.

**Syntax**

```
JSONAllPaths(json)
```

**Arguments**

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

**Returned value**

Returns an array of all paths 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, JSONAllPaths(json) FROM test;
```

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

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

Introduced in: v24.8.0

Returns the list of all paths and their data types stored in each row in JSON column.

**Syntax**

```
JSONAllPathsWithTypes(json)
```

**Arguments**

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

**Returned value**

Returns a map of all 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, JSONAllPathsWithTypes(json) FROM test;
```

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

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

Introduced in: v26.4.0

Returns all values from each row in a JSON column as an array of strings.
Values are serialized in their text representation and ordered by their path names.

**Syntax**

```
JSONAllValues(json)
```

**Arguments**

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

**Returned value**

Returns an array of all values as strings 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, JSONAllValues(json) FROM test;
```

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

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

Introduced in: v23.2.0

Returns the number of elements in the outermost JSON array.
The function returns `NULL` if input JSON string is invalid.

**Syntax**

```
JSONArrayLength(json)
```

**Aliases**: `JSON_ARRAY_LENGTH`

**Arguments**

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

**Returned value**

Returns the number of array elements if `json` is a valid JSON array string, otherwise returns `NULL`. [`Nullable(UInt64)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Usage example**

```
SELECT
    JSONArrayLength(''),
    JSONArrayLength('[1,2,3]');
```

```
┌─JSONArrayLength('')─┬─JSONArrayLength('[1,2,3]')─┐
│                ᴺᵁᴸᴸ │                          3 │
└─────────────────────┴────────────────────────────┘
```

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

Introduced in: v24.8.0

Returns the list of dynamic paths that are stored as separate subcolumns in JSON column.

**Syntax**

```
JSONDynamicPaths(json)
```

**Arguments**

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

**Returned value**

Returns an array of dynamic paths 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, JSONDynamicPaths(json) FROM test;
```

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

## 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**