Functions for Working with Dictionaries

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 dictGetInt32('all_types_dict', 'Int32_value', 1)
```

```
┌─dictGetInt32⋯_value', 1)─┐
│                -1000000  │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
dictGetInt32OrDefault(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 dictGetInt32('all_types_dict', 'Int32_value', 1);

-- for key which does not exist, returns the provided default value (-1)
SELECT dictGetInt32OrDefault('all_types_dict', 'Int32_value', 999, -1);
```

```
┌─dictGetInt32⋯_value', 1)─┐
│                -1000000  │
└──────────────────────────┘
┌─dictGetInt32⋯', 999, -1)─┐
│                       -1 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
dictGetInt64(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 dictGetInt64('all_types_dict', 'Int64_value', 1)
```

```
┌─dictGetInt64⋯_value', 1)───┐
│       -9223372036854775807 │
└────────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
dictGetInt64OrDefault(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 dictGetInt64('all_types_dict', 'Int64_value', 1);

-- for key which does not exist, returns the provided default value (-1)
SELECT dictGetInt64OrDefault('all_types_dict', 'Int64_value', 999, -1);
```

```
┌─dictGetInt64⋯_value', 1)─┐
│     -9223372036854775808 │
└──────────────────────────┘
┌─dictGetInt64⋯', 999, -1)─┐
│                       -1 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
dictGetInt8(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 dictGetInt8('all_types_dict', 'Int8_value', 1)
```

```
┌─dictGetInt8(⋯_value', 1)─┐
│                     -100 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

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