> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Json Functions

# JSON Functions

<h2 id="types-of-functions">
  Types of JSON functions
</h2>

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.

<h3 id="simplejson-visitparam-functions">
  simpleJSON (visitParam) functions
</h3>

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.

<h3 id="jsonextract-functions">
  JSONExtract functions
</h3>

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

<h3 id="case-insensitive-jsonextract-functions">
  Case-Insensitive JSONExtract Functions
</h3>

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.
</Note>

{/*AUTOGENERATED_START*/}

<h2 id="JSONAllPaths">
  JSONAllPaths
</h2>

Introduced in: v24.8.0

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

**Syntax**

```sql theme={null}
JSONAllPaths(json)
```

**Arguments**

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

**Returned value**

Returns an array of all paths in the JSON column. [`Array(String)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

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

<h2 id="JSONAllPathsWithTypes">
  JSONAllPathsWithTypes
</h2>

Introduced in: v24.8.0

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

**Syntax**

```sql theme={null}
JSONAllPathsWithTypes(json)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

```response title=Response theme={null}
┌─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'} │
└──────────────────────────────────────┴───────────────────────────────────────────┘
```

<h2 id="JSONAllValues">
  JSONAllValues
</h2>

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

```sql theme={null}
JSONAllValues(json)
```

**Arguments**

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

**Returned value**

Returns an array of all values as strings in the JSON column. [`Array(String)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

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

<h2 id="JSONArrayLength">
  JSONArrayLength
</h2>

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

```sql theme={null}
JSONArrayLength(json)
```

**Aliases**: `JSON_ARRAY_LENGTH`

**Arguments**

* `json` — String with valid JSON. [`String`](/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)`](/reference/data-types/nullable)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT
    JSONArrayLength(''),
    JSONArrayLength('[1,2,3]');
```

```response title=Response theme={null}
┌─JSONArrayLength('')─┬─JSONArrayLength('[1,2,3]')─┐
│                ᴺᵁᴸᴸ │                          3 │
└─────────────────────┴────────────────────────────┘
```

<h2 id="JSONDynamicPaths">
  JSONDynamicPaths
</h2>

Introduced in: v24.8.0

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

**Syntax**

```sql theme={null}
JSONDynamicPaths(json)
```

**Arguments**

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

**Returned value**

Returns an array of dynamic paths in the JSON column. [`Array(String)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

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

<h2 id="JSONDynamicPathsWithTypes">
  JSONDynamicPathsWithTypes
</h2>

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

```sql theme={null}
JSONDynamicPathsWithTypes(json)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

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

<h2 id="JSONExtract">
  JSONExtract
</h2>

Introduced in: v19.14.0

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

**Syntax**

```sql theme={null}
JSONExtract(json[, indices_or_keys, ...], return_type)
```

**Arguments**

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

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

```response title=Response theme={null}
┌─res──────────────────────────────┐
│ ('hello',[-100,200,300])         │
└──────────────────────────────────┘
```

<h2 id="JSONExtractArrayRaw">
  JSONExtractArrayRaw
</h2>

Introduced in: v20.1.0

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

**Syntax**

```sql theme={null}
JSONExtractArrayRaw(json[, indices_or_keys, ...])
```

**Arguments**

* `json` — JSON string to parse. [`String`](/reference/data-types/string)
* `indices_or_keys` — A list of zero or more arguments each of which can be either string or integer. [`String`](/reference/data-types/string) or [`(U)Int*`](/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)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractArrayRaw('{"a": "hello", "b": [-100, 200.0, "hello"]}', 'b') AS res;
```

```response title=Response theme={null}
┌─res──────────────────────────┐
│ ['-100','200.0','"hello"']   │
└──────────────────────────────┘
```

<h2 id="JSONExtractArrayRawCaseInsensitive">
  JSONExtractArrayRawCaseInsensitive
</h2>

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

```sql theme={null}
JSONExtractArrayRawCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

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

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractArrayRawCaseInsensitive('{"Items": [1, 2, 3]}', 'ITEMS')
```

```response title=Response theme={null}
['1','2','3']
```

<h2 id="JSONExtractBool">
  JSONExtractBool
</h2>

Introduced in: v20.1.0

Parses JSON and extracts a value of Bool type.

**Syntax**

```sql theme={null}
JSONExtractBool(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractBool('{"passed": true}', 'passed') AS res;
```

```response title=Response theme={null}
┌─res─┐
│   1 │
└─────┘
```

<h2 id="JSONExtractBoolCaseInsensitive">
  JSONExtractBoolCaseInsensitive
</h2>

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

```sql theme={null}
JSONExtractBoolCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

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

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractBoolCaseInsensitive('{"IsActive": true}', 'isactive')
```

```response title=Response theme={null}
1
```

<h2 id="JSONExtractCaseInsensitive">
  JSONExtractCaseInsensitive
</h2>

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

```sql theme={null}
JSONExtractCaseInsensitive(json [, indices_or_keys...], return_type)
```

**Arguments**

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

**Returned value**

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

**Examples**

**int\_type**

```sql title=Query theme={null}
SELECT JSONExtractCaseInsensitive('{"Number": 123}', 'number', 'Int32')
```

```response title=Response theme={null}
123
```

**array\_type**

```sql title=Query theme={null}
SELECT JSONExtractCaseInsensitive('{"List": [1, 2, 3]}', 'list', 'Array(Int32)')
```

```response title=Response theme={null}
[1,2,3]
```

<h2 id="JSONExtractFloat">
  JSONExtractFloat
</h2>

Introduced in: v20.1.0

Parses JSON and extracts a value of Float type.

**Syntax**

```sql theme={null}
JSONExtractFloat(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

```response title=Response theme={null}
┌─res─┐
│ 200 │
└─────┘
```

<h2 id="JSONExtractFloatCaseInsensitive">
  JSONExtractFloatCaseInsensitive
</h2>

Introduced in: v25.8.0

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

**Syntax**

```sql theme={null}
JSONExtractFloatCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns the extracted Float value, 0 if not found or cannot be converted. [`Float64`](/reference/data-types/float)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractFloatCaseInsensitive('{"Price": 12.34}', 'PRICE')
```

```response title=Response theme={null}
12.34
```

<h2 id="JSONExtractInt">
  JSONExtractInt
</h2>

Introduced in: v20.1.0

Parses JSON and extracts a value of Int type.

**Syntax**

```sql theme={null}
JSONExtractInt(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

Returns an Int value if it exists, otherwise returns `0`. [`Int64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1) AS res;
```

```response title=Response theme={null}
┌──res─┐
│ -100 │
└──────┘
```

<h2 id="JSONExtractIntCaseInsensitive">
  JSONExtractIntCaseInsensitive
</h2>

Introduced in: v25.8.0

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

**Syntax**

```sql theme={null}
JSONExtractIntCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns the extracted Int value, 0 if not found or cannot be converted. [`Int64`](/reference/data-types/int-uint)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractIntCaseInsensitive('{"Value": 123}', 'value')
```

```response title=Response theme={null}
123
```

**nested**

```sql title=Query theme={null}
SELECT JSONExtractIntCaseInsensitive('{"DATA": {"COUNT": 42}}', 'data', 'Count')
```

```response title=Response theme={null}
42
```

<h2 id="JSONExtractKeys">
  JSONExtractKeys
</h2>

Introduced in: v21.11.0

Parses a JSON string and extracts the keys.

**Syntax**

```sql theme={null}
JSONExtractKeys(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

Returns an array with the keys of the JSON object. [`Array(String)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}') AS res;
```

```response title=Response theme={null}
┌─res─────────┐
│ ['a','b']   │
└─────────────┘
```

<h2 id="JSONExtractKeysAndValues">
  JSONExtractKeysAndValues
</h2>

Introduced in: v20.1.0

Parses key-value pairs from a JSON where the values are of the given ClickHouse data type.

**Syntax**

```sql theme={null}
JSONExtractKeysAndValues(json[, indices_or_keys, ...], value_type)
```

**Arguments**

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

**Returned value**

Returns an array of tuples with the parsed key-value pairs. [`Array(Tuple(String, value_type))`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'Int8', 'x') AS res;
```

```response title=Response theme={null}
┌─res────────────────────┐
│ [('a',5),('b',7),('c',11)] │
└────────────────────────┘
```

<h2 id="JSONExtractKeysAndValuesCaseInsensitive">
  JSONExtractKeysAndValuesCaseInsensitive
</h2>

Introduced in: v25.8.0

Parses key-value pairs from JSON using case-insensitive key matching. This function is similar to [`JSONExtractKeysAndValues`](#JSONExtractKeysAndValues).

**Syntax**

```sql theme={null}
JSONExtractKeysAndValuesCaseInsensitive(json [, indices_or_keys...], value_type)
```

**Arguments**

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

**Returned value**

Returns an array of tuples containing key-value pairs. [`Array(Tuple(String, T))`](/reference/data-types/array)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractKeysAndValuesCaseInsensitive('{"Name": "Alice", "AGE": 30}', 'String')
```

```response title=Response theme={null}
[('Name','Alice'),('AGE','30')]
```

<h2 id="JSONExtractKeysAndValuesRaw">
  JSONExtractKeysAndValuesRaw
</h2>

Introduced in: v20.4.0

Returns an array of tuples with keys and values from a JSON object. All values are represented as unparsed strings.

**Syntax**

```sql theme={null}
JSONExtractKeysAndValuesRaw(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

Returns an array of tuples with parsed key-value pairs where values are unparsed strings. [`Array(Tuple(String, String))`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b": "hello"}') AS res;
```

```response title=Response theme={null}
┌─res──────────────────────────────────┐
│ [('a','[-100,200.0]'),('b','"hello"')] │
└──────────────────────────────────────┘
```

<h2 id="JSONExtractKeysAndValuesRawCaseInsensitive">
  JSONExtractKeysAndValuesRawCaseInsensitive
</h2>

Introduced in: v25.8.0

Extracts raw key-value pairs from JSON using case-insensitive key matching. This function is similar to [`JSONExtractKeysAndValuesRaw`](#JSONExtractKeysAndValuesRaw).

**Syntax**

```sql theme={null}
JSONExtractKeysAndValuesRawCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns an array of tuples containing key-value pairs as raw strings. [`Array(Tuple(String, String))`](/reference/data-types/array)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractKeysAndValuesRawCaseInsensitive('{"Name": "Alice", "AGE": 30}')
```

```response title=Response theme={null}
[('Name','"Alice"'),('AGE','30')]
```

<h2 id="JSONExtractKeysCaseInsensitive">
  JSONExtractKeysCaseInsensitive
</h2>

Introduced in: v25.8.0

Parses a JSON string and extracts the keys using case-insensitive key matching to navigate to nested objects. This function is similar to [`JSONExtractKeys`](#JSONExtractKeys).

**Syntax**

```sql theme={null}
JSONExtractKeysCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns an array of keys from the JSON object. [`Array(String)`](/reference/data-types/array)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractKeysCaseInsensitive('{"Name": "Alice", "AGE": 30}')
```

```response title=Response theme={null}
['Name','AGE']
```

**nested**

```sql title=Query theme={null}
SELECT JSONExtractKeysCaseInsensitive('{"User": {"name": "John", "AGE": 25}}', 'user')
```

```response title=Response theme={null}
['name','AGE']
```

<h2 id="JSONExtractRaw">
  JSONExtractRaw
</h2>

Introduced in: v20.1.0

Returns a part of JSON as unparsed string.

**Syntax**

```sql theme={null}
JSONExtractRaw(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

Returns the part of JSON as an unparsed string. If the part does not exist or has a wrong type, an empty string will be returned. [`String`](/reference/data-types/string)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') AS res;
```

```response title=Response theme={null}
┌─res──────────────┐
│ [-100,200.0,300] │
└──────────────────┘
```

<h2 id="JSONExtractRawCaseInsensitive">
  JSONExtractRawCaseInsensitive
</h2>

Introduced in: v25.8.0

Returns part of the JSON as an unparsed string using case-insensitive key matching. This function is similar to [`JSONExtractRaw`](#JSONExtractRaw).

**Syntax**

```sql theme={null}
JSONExtractRawCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns the raw JSON string of the extracted element. [`String`](/reference/data-types/string)

**Examples**

**object**

```sql title=Query theme={null}
SELECT JSONExtractRawCaseInsensitive('{"Object": {"key": "value"}}', 'OBJECT')
```

```response title=Response theme={null}
{"key":"value"}
```

<h2 id="JSONExtractString">
  JSONExtractString
</h2>

Introduced in: v20.1.0

Parses JSON and extracts a value of String type.

**Syntax**

```sql theme={null}
JSONExtractString(json[, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

Returns a String value if it exists, otherwise returns an empty string. [`String`](/reference/data-types/string)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') AS res;
```

```response title=Response theme={null}
┌─res───┐
│ hello │
└───────┘
```

<h2 id="JSONExtractStringCaseInsensitive">
  JSONExtractStringCaseInsensitive
</h2>

Introduced in: v25.8.0

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

**Syntax**

```sql theme={null}
JSONExtractStringCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns the extracted string value, empty string if not found. [`String`](/reference/data-types/string)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractStringCaseInsensitive('{"ABC": "def"}', 'abc')
```

```response title=Response theme={null}
def
```

**nested**

```sql title=Query theme={null}
SELECT JSONExtractStringCaseInsensitive('{"User": {"Name": "John"}}', 'user', 'name')
```

```response title=Response theme={null}
John
```

<h2 id="JSONExtractUInt">
  JSONExtractUInt
</h2>

Introduced in: v20.1.0

Parses JSON and extracts a value of UInt type.

**Syntax**

```sql theme={null}
JSONExtractUInt(json [, indices_or_keys, ...])
```

**Arguments**

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

**Returned value**

Returns a UInt value if it exists, otherwise returns `0`. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) AS res;
```

```response title=Response theme={null}
┌─res─┐
│ 300 │
└─────┘
```

<h2 id="JSONExtractUIntCaseInsensitive">
  JSONExtractUIntCaseInsensitive
</h2>

Introduced in: v25.8.0

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

**Syntax**

```sql theme={null}
JSONExtractUIntCaseInsensitive(json [, indices_or_keys]...)
```

**Arguments**

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

**Returned value**

Returns the extracted UInt value, 0 if not found or cannot be converted. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**basic**

```sql title=Query theme={null}
SELECT JSONExtractUIntCaseInsensitive('{"COUNT": 789}', 'count')
```

```response title=Response theme={null}
789
```

<h2 id="JSONHas">
  JSONHas
</h2>

Introduced in: v20.1.0

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

**Syntax**

```sql theme={null}
JSONHas(json[ ,indices_or_keys, ...])
```

**Arguments**

* `json` — JSON string to parse [`String`](/reference/data-types/string)
* `[ ,indices_or_keys, ...]` — A list of zero or more arguments. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint)

**Returned value**

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

**Examples**

**Usage example**

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

```response title=Response theme={null}
1
0
```

<h2 id="JSONKey">
  JSONKey
</h2>

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

```sql theme={null}
JSONKey(json[, indices_or_keys, ...])
```

**Arguments**

* `json` — JSON string to parse. [`String`](/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`](/reference/data-types/string) or [`Int*`](/reference/data-types/int-uint)

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONKey('{"a": "hello", "b": [-100, 200.0, 300]}', 1);
```

```response title=Response theme={null}
a
```

<h2 id="JSONLength">
  JSONLength
</h2>

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

```sql theme={null}
JSONLength(json [, indices_or_keys, ...])
```

**Arguments**

* `json` — JSON string to parse [`String`](/reference/data-types/string)
* `[, indices_or_keys, ...]` — Optional. A list of zero or more arguments. [`String`](/reference/data-types/string) or [`(U)Int8/16/32/64`](/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`](/reference/data-types/int-uint)

**Examples**

**Usage example**

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

```response title=Response theme={null}
1
1
```

<h2 id="JSONMergePatch">
  JSONMergePatch
</h2>

Introduced in: v23.10.0

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

**Syntax**

```sql theme={null}
JSONMergePatch(json1[, json2, ...])
```

**Aliases**: `jsonMergePatch`

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSONMergePatch('{"a":1}', '{"name": "joey"}', '{"name": "tom"}', '{"name": "zoey"}') AS res;
```

```response title=Response theme={null}
┌─res───────────────────┐
│ {"a":1,"name":"zoey"} │
└───────────────────────┘
```

<h2 id="JSONSharedDataPaths">
  JSONSharedDataPaths
</h2>

Introduced in: v24.8.0

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

**Syntax**

```sql theme={null}
JSONSharedDataPaths(json)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

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

<h2 id="JSONSharedDataPathsWithTypes">
  JSONSharedDataPathsWithTypes
</h2>

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

```sql theme={null}
JSONSharedDataPathsWithTypes(json)
```

**Arguments**

* `json` — JSON column. [`JSON`](/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)`](/reference/data-types/map)

**Examples**

**Usage example**

```sql title=Query theme={null}
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;
```

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

<h2 id="JSONType">
  JSONType
</h2>

Introduced in: v20.1.0

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

**Syntax**

```sql theme={null}
JSONType(json[, indices_or_keys, ...])
```

**Arguments**

* `json` — JSON string to parse [`String`](/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`](/reference/data-types/string) or [`(U)Int8/16/32/64`](/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`](/reference/data-types/enum)

**Examples**

**Usage example**

```sql title=Query theme={null}
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';
```

```response title=Response theme={null}
1
1
1
```

<h2 id="JSON_EXISTS">
  JSON\_EXISTS
</h2>

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

```sql theme={null}
JSON_EXISTS(json, path)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSON_EXISTS('{"hello":1}', '$.hello');
SELECT JSON_EXISTS('{"hello":{"world":1}}', '$.hello.world');
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[*]');
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[0]');
```

```response title=Response theme={null}
┌─JSON_EXISTS(⋯ '$.hello')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯llo.world')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[*]')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[0]')─┐
│                        1 │
└──────────────────────────┘
```

<h2 id="JSON_QUERY">
  JSON\_QUERY
</h2>

Introduced in: v21.8.0

Parses a JSON and extract a value as a JSON array or JSON object.
If the value does not exist, an empty string will be returned.

**Syntax**

```sql theme={null}
JSON_QUERY(json, path)
```

**Arguments**

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

**Returned value**

Returns the extracted JSON array or JSON object as a string, or an empty string if the value does not exist. [`String`](/reference/data-types/string)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSON_QUERY('{"hello":"world"}', '$.hello');
SELECT JSON_QUERY('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]');
SELECT JSON_QUERY('{"hello":2}', '$.hello');
SELECT toTypeName(JSON_QUERY('{"hello":2}', '$.hello'));
```

```response title=Response theme={null}
["world"]
[0, 1, 4, 0, -1, -4]
[2]
String
```

<h2 id="JSON_VALUE">
  JSON\_VALUE
</h2>

Introduced in: v21.11.0

Parses a JSON and extract a value as a JSON scalar. If the value does not exist, an empty string will be returned by default.

This function is controlled by the following settings:

* by SET `function_json_value_return_type_allow_nullable` = `true`, `NULL` will be returned. If the value is complex type (such as: struct, array, map), an empty string will be returned by default.
* by SET `function_json_value_return_type_allow_complex` = `true`, the complex value will be returned.

**Syntax**

```sql theme={null}
JSON_VALUE(json, path)
```

**Arguments**

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

**Returned value**

Returns the extracted JSON scalar as a string, or an empty string if the value does not exist. [`String`](/reference/data-types/string)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT JSON_VALUE('{"hello":"world"}', '$.hello');
SELECT JSON_VALUE('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]');
SELECT JSON_VALUE('{"hello":2}', '$.hello');
SELECT JSON_VALUE('{"hello":"world"}', '$.b') settings function_json_value_return_type_allow_nullable=true;
```

```response title=Response theme={null}
world
0
2
ᴺᵁᴸᴸ
```

<h2 id="dynamicElement">
  dynamicElement
</h2>

Introduced in: v24.1.0

Extracts a column with specified type from a `Dynamic` column.

This function allows you to extract values of a specific type from a Dynamic column. If a row contains a value
of the requested type, it returns that value. If the row contains a different type or NULL, it returns NULL
for scalar types or an empty array for array types.

**Syntax**

```sql theme={null}
dynamicElement(dynamic, type_name)
```

**Arguments**

* `dynamic` — Dynamic column to extract from. [`Dynamic`](/reference/data-types/dynamic)
* `type_name` — The name of the variant type to extract (e.g., 'String', 'Int64', 'Array(Int64)').

**Returned value**

Returns values of the specified type from the Dynamic column. Returns NULL for non-matching types (or empty array for array types). [`Any`](/reference/data-types/index)

**Examples**

**Extracting different types from Dynamic column**

```sql title=Query theme={null}
CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, dynamicType(d), dynamicElement(d, 'String'), dynamicElement(d, 'Int64'), dynamicElement(d, 'Array(Int64)'), dynamicElement(d, 'Date'), dynamicElement(d, 'Array(String)') FROM test
```

```response title=Response theme={null}
┌─d─────────────┬─dynamicType(d)─┬─dynamicElement(d, 'String')─┬─dynamicElement(d, 'Int64')─┬─dynamicElement(d, 'Array(Int64)')─┬─dynamicElement(d, 'Date')─┬─dynamicElement(d, 'Array(String)')─┐
│ ᴺᵁᴸᴸ          │ None           │ ᴺᵁᴸᴸ                        │                       ᴺᵁᴸᴸ │ []                                │                      ᴺᵁᴸᴸ │ []                                 │
│ 42            │ Int64          │ ᴺᵁᴸᴸ                        │                         42 │ []                                │                      ᴺᵁᴸᴸ │ []                                 │
│ Hello, World! │ String         │ Hello, World!               │                       ᴺᵁᴸᴸ │ []                                │                      ᴺᵁᴸᴸ │ []                                 │
│ [1,2,3]       │ Array(Int64)   │ ᴺᵁᴸᴸ                        │                       ᴺᵁᴸᴸ │ [1,2,3]                           │                      ᴺᵁᴸᴸ │ []                                 │
└───────────────┴────────────────┴─────────────────────────────┴────────────────────────────┴───────────────────────────────────┴───────────────────────────┴────────────────────────────────────┘
```

<h2 id="dynamicType">
  dynamicType
</h2>

Introduced in: v24.1.0

Returns the variant type name for each row of a `Dynamic` column.

For rows containing NULL, the function returns 'None'. For all other rows, it returns the actual data type
stored in that row of the Dynamic column (e.g., 'Int64', 'String', 'Array(Int64)').

**Syntax**

```sql theme={null}
dynamicType(dynamic)
```

**Arguments**

* `dynamic` — Dynamic column to inspect. [`Dynamic`](/reference/data-types/dynamic)

**Returned value**

Returns the type name of the value stored in each row, or 'None' for NULL values. [`String`](/reference/data-types/string)

**Examples**

**Inspecting types in Dynamic column**

```sql title=Query theme={null}
CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, dynamicType(d) FROM test;
```

```response title=Response theme={null}
┌─d─────────────┬─dynamicType(d)─┐
│ ᴺᵁᴸᴸ          │ None           │
│ 42            │ Int64          │
│ Hello, World! │ String         │
│ [1,2,3]       │ Array(Int64)   │
└───────────────┴────────────────┘
```

<h2 id="isDynamicElementInSharedData">
  isDynamicElementInSharedData
</h2>

Introduced in: v24.1.0

Returns true for rows in a Dynamic column that are stored in shared variant format rather than as separate subcolumns.

When a Dynamic column has a `max_types` limit, values that exceed this limit are stored in a shared binary format
instead of being separated into individual typed subcolumns. This function identifies which rows are stored in this shared format.

**Syntax**

```sql theme={null}
isDynamicElementInSharedData(dynamic)
```

**Arguments**

* `dynamic` — Dynamic column to inspect. [`Dynamic`](/reference/data-types/dynamic)

**Returned value**

Returns true if the value is stored in shared variant format, false if stored as a separate subcolumn or is NULL. [`Bool`](/reference/data-types/boolean)

**Examples**

**Checking storage format in Dynamic column with max\_types limit**

```sql title=Query theme={null}
CREATE TABLE test (d Dynamic(max_types=2)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, isDynamicElementInSharedData(d) FROM test;
```

```response title=Response theme={null}
┌─d─────────────┬─isDynamicElementInSharedData(d)─┐
│ ᴺᵁᴸᴸ          │ false                           │
│ 42            │ false                           │
│ Hello, World! │ true                            │
│ [1,2,3]       │ true                            │
└───────────────┴─────────────────────────────────┘
```

<h2 id="isValidJSON">
  isValidJSON
</h2>

Introduced in: v20.1.0

Checks that the string passed is valid JSON.

**Syntax**

```sql theme={null}
isValidJSON(json)
```

**Arguments**

* `json` — JSON string to validate [`String`](/reference/data-types/string)

**Returned value**

Returns `1` if the string is valid JSON, otherwise `0`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT isValidJSON('{"a": "hello", "b": [-100, 200.0, 300]}') = 1;
SELECT isValidJSON('not JSON') = 0;
```

```response title=Response theme={null}
1
0
```

**Using integers to access both JSON arrays and JSON objects**

```sql title=Query theme={null}
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 0);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 1);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 2);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', -1);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', -2);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 3);
```

```response title=Response theme={null}
0
1
1
1
1
1
0
```

<h2 id="prettyPrintJSON">
  prettyPrintJSON
</h2>

Introduced in: v26.4.0

Returns a pretty-printed version of a JSON string with newlines and indentation with spaces.

**Syntax**

```sql theme={null}
prettyPrintJSON(json [, indent])
```

**Arguments**

* `json` — A valid JSON string to format. [`String`](/reference/data-types/string)
* `indent` — Number of spaces per indentation level. Default: 4. Max: 32 [`UInt*`](/reference/data-types/int-uint)

**Returned value**

A pretty-printed JSON string. [`String`](/reference/data-types/string)

**Examples**

**Simple object**

```sql title=Query theme={null}
SELECT prettyPrintJSON('{"a":1,"b":"hello"}');
```

```response title=Response theme={null}
{
    "a": 1,
    "b": "hello"
}
```

**Custom indent**

```sql title=Query theme={null}
SELECT prettyPrintJSON('{"a":1}', 8);
```

```response title=Response theme={null}
{
        "a": 1
}
```

<h2 id="simpleJSONExtractBool">
  simpleJSONExtractBool
</h2>

Introduced in: v21.4.0

Parses a true/false value from the value of the field named `field_name`.
The result is `UInt8`.

**Syntax**

```sql theme={null}
simpleJSONExtractBool(json, field_name)
```

**Aliases**: `visitParamExtractBool`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns `1` if the value of the field is `true`, `0` otherwise. This means this function will return `0` including (and not only) in the following cases:

* If the field doesn't exists.
* If the field contains `true` as a string, e.g.: `{"field":"true"}`.
* If the field contains `1` as a numerical value. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":false,"bar":true}');
INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONExtractBool(json, 'bar') FROM jsons ORDER BY json;
SELECT simpleJSONExtractBool(json, 'foo') FROM jsons ORDER BY json;
```

```response title=Response theme={null}
0
1
0
0
```

<h2 id="simpleJSONExtractFloat">
  simpleJSONExtractFloat
</h2>

Introduced in: v21.4.0

Parses `Float64` from the value of the field named `field_name`.
If `field_name` is a string field, it tries to parse a number from the beginning of the string.
If the field does not exist, or it exists but does not contain a number, it returns `0`.

**Syntax**

```sql theme={null}
simpleJSONExtractFloat(json, field_name)
```

**Aliases**: `visitParamExtractFloat`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns the number parsed from the field if the field exists and contains a number, otherwise `0`. [`Float64`](/reference/data-types/float)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractFloat(json, 'foo') FROM jsons ORDER BY json;
```

```response title=Response theme={null}
0
-4000
0
-3.4
5
```

<h2 id="simpleJSONExtractInt">
  simpleJSONExtractInt
</h2>

Introduced in: v21.4.0

Parses `Int64` from the value of the field named `field_name`.
If `field_name` is a string field, it tries to parse a number from the beginning of the string.
If the field does not exist, or it exists but does not contain a number, it returns `0`.

**Syntax**

```sql theme={null}
simpleJSONExtractInt(json, field_name)
```

**Aliases**: `visitParamExtractInt`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns the number parsed from the field if the field exists and contains a number, `0` otherwise [`Int64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractInt(json, 'foo') FROM jsons ORDER BY json;
```

```response title=Response theme={null}
0
-4
0
-3
5
```

<h2 id="simpleJSONExtractRaw">
  simpleJSONExtractRaw
</h2>

Introduced in: v21.4.0

Returns the value of the field named `field_name` as a `String`, including separators.

**Syntax**

```sql theme={null}
simpleJSONExtractRaw(json, field_name)
```

**Aliases**: `visitParamExtractRaw`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns the value of the field as a string, including separators if the field exists, or an empty string otherwise [`String`](/reference/data-types/string)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":{"def":[1,2,3]}}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractRaw(json, 'foo') FROM jsons ORDER BY json;
```

```response title=Response theme={null}
"-4e3"
-3.4
5
{"def":[1,2,3]}
```

<h2 id="simpleJSONExtractString">
  simpleJSONExtractString
</h2>

Introduced in: v21.4.0

Parses `String` in double quotes from the value of the field named `field_name`.

**Implementation details**

There is currently no support for code points in the format `\uXXXX\uYYYY` that are not from the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).

**Syntax**

```sql theme={null}
simpleJSONExtractString(json, field_name)
```

**Aliases**: `visitParamExtractString`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns the unescaped value of a field as a string, including separators. An empty string is returned if the field doesn't contain a double quoted string, if unescaping fails or if the field doesn't exist [`String`](/reference/data-types/string)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"\\n\\u0000"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263a"}');
INSERT INTO jsons VALUES ('{"foo":"hello}');

SELECT simpleJSONExtractString(json, 'foo') FROM jsons ORDER BY json;
```

```response title=Response theme={null}
\n\0

☺
```

<h2 id="simpleJSONExtractUInt">
  simpleJSONExtractUInt
</h2>

Introduced in: v21.4.0

Parses `UInt64` from the value of the field named `field_name`.
If `field_name` is a string field, it tries to parse a number from the beginning of the string.
If the field does not exist, or it exists but does not contain a number, it returns `0`.

**Syntax**

```sql theme={null}
simpleJSONExtractUInt(json, field_name)
```

**Aliases**: `visitParamExtractUInt`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns the number parsed from the field if the field exists and contains a number, `0` otherwise [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"4e3"}');
INSERT INTO jsons VALUES ('{"foo":3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractUInt(json, 'foo') FROM jsons ORDER BY json;
```

```response title=Response theme={null}
0
4
0
3
5
```

<h2 id="simpleJSONHas">
  simpleJSONHas
</h2>

Introduced in: v21.4.0

Checks whether there is a field named `field_name`.

**Syntax**

```sql theme={null}
simpleJSONHas(json, field_name)
```

**Aliases**: `visitParamHas`

**Arguments**

* `json` — The JSON in which the field is searched for. [`String`](/reference/data-types/string)
* `field_name` — The name of the field to search for. [`const String`](/reference/data-types/string)

**Returned value**

Returns `1` if the field exists, `0` otherwise [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Usage example**

```sql title=Query theme={null}
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONHas(json, 'foo') FROM jsons;
SELECT simpleJSONHas(json, 'bar') FROM jsons;
```

```response title=Response theme={null}
1
0
```

<h2 id="toJSONString">
  toJSONString
</h2>

Introduced in: v21.7.0

Serializes a value to its JSON representation. Various data types and nested structures are supported.
64-bit [integers](/reference/data-types/int-uint) or bigger (like `UInt64` or `Int128`) are enclosed in quotes by default. [output\_format\_json\_quote\_64bit\_integers](/reference/settings/formats#output_format_json_quote_64bit_integers) controls this behavior.
Special values `NaN` and `inf` are replaced with `null`. Enable [output\_format\_json\_quote\_denormals](/reference/settings/formats#output_format_json_quote_denormals) setting to show them.
When serializing an [Enum](/reference/data-types/enum) value, the function outputs its name.

See also:

* [output\_format\_json\_quote\_64bit\_integers](/reference/settings/formats#output_format_json_quote_64bit_integers)
* [output\_format\_json\_quote\_denormals](/reference/settings/formats#output_format_json_quote_denormals)

**Syntax**

```sql theme={null}
toJSONString(value)
```

**Arguments**

* `value` — Value to serialize. Value may be of any data type. [`Any`](/reference/data-types/index)

**Returned value**

Returns the JSON representation of the value. [`String`](/reference/data-types/string)

**Examples**

**Map serialization**

```sql title=Query theme={null}
SELECT toJSONString(map('key1', 1, 'key2', 2));
```

```response title=Response theme={null}
┌─toJSONString(map('key1', 1, 'key2', 2))─┐
│ {"key1":1,"key2":2}                     │
└─────────────────────────────────────────┘
```

**Special values**

```sql title=Query theme={null}
SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1;
```

```response title=Response theme={null}
┌─toJSONString(tuple(1.25, NULL, NaN, plus(inf), minus(inf), []))─┐
│ [1.25,null,"nan","inf","-inf",[]]                               │
└─────────────────────────────────────────────────────────────────┘
```