JSON Functions

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

```
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]');
```

```
┌─JSON_EXISTS(⋯ '$.hello')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯llo.world')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[*]')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[0]')─┐
│                        1 │
└──────────────────────────┘
```

## JSON\_QUERY[​](#JSON_QUERY "Direct link to JSON_QUERY")

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

```
JSON_QUERY(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 the extracted JSON array or JSON object as a string, or an empty string if the value does not exist. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
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'));
```

```
["world"]
[0, 1, 4, 0, -1, -4]
[2]
String
```

## JSON\_VALUE[​](#JSON_VALUE "Direct link to JSON_VALUE")

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

```
JSON_VALUE(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 the extracted JSON scalar as a string, or an empty string if the value does not exist. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
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;
```

```
world
0
2
ᴺᵁᴸᴸ
```

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

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

```
dynamicElement(dynamic, type_name)
```

**Arguments**

- `dynamic` — Dynamic column to extract from. [`Dynamic`](/docs/sql-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`](/docs/sql-reference/data-types)

**Examples**

**Extracting different types from Dynamic column**

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

```
┌─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]                           │                      ᴺᵁᴸᴸ │ []                                 │
└───────────────┴────────────────┴─────────────────────────────┴────────────────────────────┴───────────────────────────────────┴───────────────────────────┴────────────────────────────────────┘
```

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

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

```
dynamicType(dynamic)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Inspecting types in Dynamic column**

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

```
┌─d─────────────┬─dynamicType(d)─┐
│ ᴺᵁᴸᴸ          │ None           │
│ 42            │ Int64          │
│ Hello, World! │ String         │
│ [1,2,3]       │ Array(Int64)   │
└───────────────┴────────────────┘
```

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

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

```
isDynamicElementInSharedData(dynamic)
```

**Arguments**

- `dynamic` — Dynamic column to inspect. [`Dynamic`](/docs/sql-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`](/docs/sql-reference/data-types/boolean)

**Examples**

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