Functions for Working with Dictionaries

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr`,
otherwise returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.

Note

ClickHouse throws an exception if it cannot parse the value of the attribute or the value does not match the attribute data type.

**Examples**

**Usage example**

```
SELECT dictGetDateTime('all_types_dict', 'DateTime_value', 1)
```

```
┌─dictGetDateT⋯_value', 1)─┐
│      2024-01-15 10:30:00 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a dictionary attribute value to `DateTime` data type regardless of the dictionary configuration, or returns the provided default value if the key is not found.

**Syntax**

```
dictGetDateTimeOrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `attr_name` — Name of the column of the dictionary. [`String`](/docs/sql-reference/data-types/string) or [`Tuple(String)`](/docs/sql-reference/data-types/tuple)
- `id_expr` — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/docs/sql-reference/data-types/special-data-types/expression) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)
- `default_value_expr` — Value(s) returned if the dictionary does not contain a row with the `id_expr` key. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr`,
otherwise returns the value passed as the `default_value_expr` parameter.

Note

ClickHouse throws an exception if it cannot parse the value of the attribute or the value does not match the attribute data type.

**Examples**

**Usage example**

```
-- for key which exists
SELECT dictGetDateTime('all_types_dict', 'DateTime_value', 1);

-- for key which does not exist, returns the provided default value
SELECT dictGetDateTimeOrDefault('all_types_dict', 'DateTime_value', 999, toDateTime('1970-01-01 00:00:00'));
```

```
┌─dictGetDateT⋯_value', 1)─┐
│      2024-01-15 10:30:00 │
└──────────────────────────┘
┌─dictGetDateT⋯0:00:00'))──┐
│      1970-01-01 00:00:00 │
└──────────────────────────┘
```

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

Introduced in: v21.4.0

Returns all descendants as if the [`dictGetChildren`](#dictGetChildren) function were applied `level` times recursively.

**Syntax**

```
dictGetDescendants(dict_name, key, level)
```

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `key` — Key to be checked. [`const String`](/docs/sql-reference/data-types/string)
- `level` — Key to be checked. Hierarchy level. If `level = 0` returns all descendants to the end. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the descendants for the key. [`Array(UInt64)`](/docs/sql-reference/data-types/array)

**Examples**

**Get the first-level children of a dictionary**

```
-- consider the following hierarchical dictionary:
-- 0 (Root)
-- └── 1 (Level 1 - Node 1)
--     ├── 2 (Level 2 - Node 2)
--     │   ├── 4 (Level 3 - Node 4)
--     │   └── 5 (Level 3 - Node 5)
--     └── 3 (Level 2 - Node 3)
--         └── 6 (Level 3 - Node 6)

SELECT dictGetDescendants('hierarchical_dictionary', 0, 2)
```

```
┌─dictGetDesce⋯ary', 0, 2)─┐
│ [3,2]                    │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a dictionary attribute value to `Float32` data type regardless of the dictionary configuration.

**Syntax**

```
dictGetFloat32(dict_name, attr_name, id_expr)
```

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `attr_name` — Name of the column of the dictionary. [`String`](/docs/sql-reference/data-types/string) or [`Tuple(String)`](/docs/sql-reference/data-types/tuple)
- `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/docs/sql-reference/data-types/special-data-types/expression) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr`,
otherwise returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.

Note

ClickHouse throws an exception if it cannot parse the value of the attribute or the value does not match the attribute data type.

**Examples**

**Usage example**

```
SELECT dictGetFloat32('all_types_dict', 'Float32_value', 1)
```

```
┌─dictGetFloat⋯_value', 1)─┐
│               -123.123   │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a dictionary attribute value to `Float32` data type regardless of the dictionary configuration, or returns the provided default value if the key is not found.

**Syntax**

```
dictGetFloat32OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `attr_name` — Name of the column of the dictionary. [`String`](/docs/sql-reference/data-types/string) or [`Tuple(String)`](/docs/sql-reference/data-types/tuple)
- `id_expr` — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/docs/sql-reference/data-types/special-data-types/expression) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)
- `default_value_expr` — Value(s) returned if the dictionary does not contain a row with the `id_expr` key. [`Expression`](/docs/sql-reference/data-types/special-data-types/expression) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr`,
otherwise returns the value passed as the `default_value_expr` parameter.

Note

ClickHouse throws an exception if it cannot parse the value of the attribute or the value does not match the attribute data type.

**Examples**

**Usage example**

```
-- for key which exists
SELECT dictGetFloat32('all_types_dict', 'Float32_value', 1);

-- for key which does not exist, returns the provided default value (-1.0)
SELECT dictGetFloat32OrDefault('all_types_dict', 'Float32_value', 999, -1.0);
```

```
┌─dictGetFloat⋯_value', 1)─┐
│                   123.45 │
└──────────────────────────┘
┌─dictGetFloat⋯e', 999, -1)─┐
│                       -1  │
└───────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a dictionary attribute value to `Float64` data type regardless of the dictionary configuration.

**Syntax**

```
dictGetFloat64(dict_name, attr_name, id_expr)
```

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `attr_name` — Name of the column of the dictionary. [`String`](/docs/sql-reference/data-types/string) or [`Tuple(String)`](/docs/sql-reference/data-types/tuple)
- `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/docs/sql-reference/data-types/special-data-types/expression) or [`Tuple(T)`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr`,
otherwise returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.

Note

ClickHouse throws an exception if it cannot parse the value of the attribute or the value does not match the attribute data type.