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 dictGetFloat64('all_types_dict', 'Float64_value', 1)
```

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

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

Introduced in: v1.1.0

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

**Syntax**

```
dictGetFloat64OrDefault(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 dictGetFloat64('all_types_dict', 'Float64_value', 1);

-- for key which does not exist, returns the provided default value (nan)
SELECT dictGetFloat64OrDefault('all_types_dict', 'Float64_value', 999, nan);
```

```
┌─dictGetFloat⋯_value', 1)─┐
│            987654.123456 │
└──────────────────────────┘
┌─dictGetFloat⋯, 999, nan)─┐
│                      nan │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Creates an array, containing all the parents of a key in the [hierarchical dictionary](/docs/sql-reference/statements/create/dictionary/layouts/hierarchical#hierarchical-dictionaries).

**Syntax**

```
dictGetHierarchy(dict_name, key)
```

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `key` — Key value. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

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

**Examples**

**Get hierarchy for a key**

```
SELECT dictGetHierarchy('hierarchical_dictionary', 5)
```

```
┌─dictGetHiera⋯ionary', 5)─┐
│ [5,2,1]                  │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
dictGetIPv4(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 dictGetIPv4('all_types_dict', 'IPv4_value', 1)
```

```
┌─dictGetIPv4('all_⋯ 'IPv4_value', 1)─┐
│ 192.168.0.1                         │
└─────────────────────────────────────┘
```

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

Introduced in: v23.1.0

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

**Syntax**

```
dictGetIPv4OrDefault(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 dictGetIPv4('all_types_dict', 'IPv4_value', 1);

-- for key which does not exist, returns the provided default value
SELECT dictGetIPv4OrDefault('all_types_dict', 'IPv4_value', 999, toIPv4('0.0.0.0'));
```

```
┌─dictGetIPv4('all_⋯ 'IPv4_value', 1)─┐
│ 192.168.0.1                         │
└─────────────────────────────────────┘
┌─dictGetIPv4OrDefa⋯0.0.0.0'))─┐
│ 0.0.0.0                      │
└──────────────────────────────┘
```

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

Introduced in: v23.1.0

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

**Syntax**

```
dictGetIPv6(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 dictGetIPv6('all_types_dict', 'IPv6_value', 1)
```

```
┌─dictGetIPv6('all_⋯ 'IPv6_value', 1)─┐
│ 2001:db8:85a3::8a2e:370:7334        │
└─────────────────────────────────────┘
```

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

Introduced in: v23.1.0