# Dictionary (/docs/reference/sql/functions/dictionary)



This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See [SQL interface scope](/docs/reference/clickhouse-compatibility#service-interfaces).

dictGet [#dictget]

Retrieves values from a dictionary.

**Syntax**

```sql
dictGet('dict_name', attr_names, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_names` — Name of the column of the dictionary, or tuple of column names. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. For a dictionary with a simple key, an expression returning a `UInt64` value. For a dictionary with a composite (complex) key, an expression returning a tuple of the key values. If the composite key consists of a single attribute, its value may be passed directly, without wrapping it in `tuple`. [`UInt64`](/reference/data-types/int-uint) or [`Tuple(T)`](/reference/data-types/tuple)

**Returned value**

Returns the value of the dictionary attribute that corresponds to id\_expr if the key is found.
If the key is not found, returns the content of the `<null_value>` element specified for the attribute in the dictionary configuration.

**Examples**

**Retrieve a single attribute**

```sql title=Query
CREATE TABLE ext_dict_test_source (id UInt32, c1 UInt32, c2 String) ENGINE = Memory;
INSERT INTO ext_dict_test_source VALUES (1, 1, '1'), (2, 2, '2'), (3, 3, '3');
CREATE DICTIONARY ext_dict_test (id UInt32, c1 UInt32, c2 String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'ext_dict_test_source' DB currentDatabase())) LAYOUT(FLAT()) LIFETIME(MIN 0 MAX 0);
SELECT dictGet('ext_dict_test', 'c1', toUInt64(1)) AS val
```

```text title=Response
1
```

**Multiple attributes**

```sql title=Query
CREATE TABLE dict_mult_source (id UInt32, c1 UInt32, c2 String) ENGINE = Memory;
INSERT INTO dict_mult_source VALUES (1, 1, '1'), (2, 2, '2'), (3, 3, '3');
CREATE DICTIONARY ext_dict_mult (id UInt32, c1 UInt32, c2 String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'dict_mult_source' DB currentDatabase())) LAYOUT(FLAT()) LIFETIME(MIN 0 MAX 0);
SELECT
    dictGet('ext_dict_mult', ('c1','c2'), number + 1) AS val,
    toTypeName(val) AS type
FROM system.numbers
LIMIT 3;
```

```text title=Response
┌─val─────┬─type───────────┐
│ (1,'1') │ Tuple(        ↴│
│         │↳    c1 UInt32,↴│
│         │↳    c2 String) │
│ (2,'2') │ Tuple(        ↴│
│         │↳    c1 UInt32,↴│
│         │↳    c2 String) │
│ (3,'3') │ Tuple(        ↴│
│         │↳    c1 UInt32,↴│
│         │↳    c2 String) │
└─────────┴────────────────┘
```

*Introduced in version 18.16.0.*

dictGetAll [#dictgetall]

Retrieves the attribute values of all the nodes that matched each key in a [regular expression tree dictionary](/reference/statements/create/dictionary/layouts/regexp-tree).

Besides returning values of type `Array(T)` instead of `T`, this function behaves similarly to [`dictGet`](#dictGet).

**Syntax**

```sql
dictGetAll(dict_name, attr_names, id_expr[, limit])
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_names` — Name of the column of the dictionary, or tuple of column names. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/reference/data-types/tuple)
* `limit` — Optional. Maximum length for each value array returned. When truncating, child nodes are given precedence over parent nodes, and otherwise the defined list order for the regexp tree dictionary is respected. If unspecified, the array length is unlimited. [`UInt*`](/reference/data-types/int-uint)

**Returned value**

Returns an array of the dictionary attribute values that correspond to `id_expr` for each attribute specified by `attr_names`.
If there is no key corresponding to `id_expr` in the dictionary, an empty array is returned.

:::note
ClickHouse throws an exception if it cannot parse the value of the attribute or the value does not match the attribute data type.
:::
[`Array(T)`](/reference/data-types/array)

**Examples**

**Usage example**

```sql title=Query
CREATE TABLE regexp_os (id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)) ENGINE = Memory;
INSERT INTO regexp_os VALUES
    (1, 0, 'Linux',      ['os_replacement'], ['Linux']),
    (2, 1, 'Android',    ['os_replacement'], ['Android']),
    (3, 2, 'Android 1',  ['os_replacement'], ['Android']),
    (4, 3, 'Android 12', ['os_replacement'], ['Android']);
CREATE DICTIONARY regexp_tree (regexp String, os_replacement String DEFAULT 'Other') PRIMARY KEY regexp SOURCE(CLICKHOUSE(TABLE 'regexp_os' DB currentDatabase())) LIFETIME(MIN 0 MAX 0) LAYOUT(REGEXP_TREE);
SELECT
    'Mozilla/5.0 (Linux; Android 12; SM-G998B) Mobile Safari/537.36' AS user_agent,

    -- This will match ALL applicable patterns
    dictGetAll('regexp_tree', 'os_replacement', 'Mozilla/5.0 (Linux; Android 12; SM-G998B) Mobile Safari/537.36') AS all_matches,

    -- This returns only the first match
    dictGet('regexp_tree', 'os_replacement', 'Mozilla/5.0 (Linux; Android 12; SM-G998B) Mobile Safari/537.36') AS first_match;
```

```text title=Response
┌─user_agent─────────────────────────────────────────────────────┬─all_matches─────────────────────────────┬─first_match─┐
│ Mozilla/5.0 (Linux; Android 12; SM-G998B) Mobile Safari/537.36 │ ['Android','Android','Android','Linux'] │ Android     │
└────────────────────────────────────────────────────────────────┴─────────────────────────────────────────┴─────────────┘
```

*Introduced in version 23.5.0.*

dictGetChildren [#dictgetchildren]

Returns first-level children as an array of indexes. It is the inverse transformation for [dictGetHierarchy](#dictGetHierarchy).

**Syntax**

```sql
dictGetChildren(dict_name, key)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `key` — Key to be checked. [`const String`](/reference/data-types/string)

**Returned value**

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

**Examples**

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

```sql title=Query
CREATE TABLE hierarchy_source (id UInt64, parent_id UInt64, name String) ENGINE = Memory;
INSERT INTO hierarchy_source VALUES (0, 0, 'Root'), (1, 0, 'Level 1 - Node 1'), (2, 1, 'Level 2 - Node 2'), (3, 1, 'Level 2 - Node 3'), (4, 2, 'Level 3 - Node 4'), (5, 2, 'Level 3 - Node 5'), (6, 3, 'Level 3 - Node 6');
CREATE DICTIONARY hierarchical_dictionary (id UInt64, parent_id UInt64 HIERARCHICAL, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'hierarchy_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetChildren('hierarchical_dictionary', 2);
```

```text title=Response
┌─dictGetChild⋯ionary', 2)─┐
│ [4,5]                    │
└──────────────────────────┘
```

*Introduced in version 21.4.0.*

dictGetDate [#dictgetdate]

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

**Syntax**

```sql
dictGetDate(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Date_value Date) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '2020-01-01');
CREATE DICTIONARY all_types_dict (id UInt32, Date_value Date) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetDate('all_types_dict', 'Date_value', 1)
```

```text title=Response
┌─dictGetDate(⋯_value', 1)─┐
│               2020-01-01 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetDateOrDefault [#dictgetdateordefault]

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

**Syntax**

```sql
dictGetDateOrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Date_value Date) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '2024-01-15');
CREATE DICTIONARY all_types_dict (id UInt32, Date_value Date) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetDate('all_types_dict', 'Date_value', 1);

-- for key which does not exist, returns the provided default value
SELECT dictGetDateOrDefault('all_types_dict', 'Date_value', 999, toDate('1970-01-01'));
```

```text title=Response
┌─dictGetDate(⋯_value', 1)─┐
│               2024-01-15 │
└──────────────────────────┘
┌─dictGetDateO⋯70-01-01'))─┐
│               1970-01-01 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetDateTime [#dictgetdatetime]

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

**Syntax**

```sql
dictGetDateTime(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, DateTime_value DateTime) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '2024-01-15 10:30:00');
CREATE DICTIONARY all_types_dict (id UInt32, DateTime_value DateTime) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetDateTime('all_types_dict', 'DateTime_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetDateTimeOrDefault [#dictgetdatetimeordefault]

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

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

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, DateTime_value DateTime) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '2024-01-15 10:30:00');
CREATE DICTIONARY all_types_dict (id UInt32, DateTime_value DateTime) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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'));
```

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

*Introduced in version 1.1.0.*

dictGetDescendants [#dictgetdescendants]

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

**Syntax**

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

**Arguments**

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

**Returned value**

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

**Examples**

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

```sql title=Query
CREATE TABLE hierarchy_source (id UInt64, parent_id UInt64, name String) ENGINE = Memory;
INSERT INTO hierarchy_source VALUES (0, 0, 'Root'), (1, 0, 'Level 1 - Node 1'), (2, 1, 'Level 2 - Node 2'), (3, 1, 'Level 2 - Node 3'), (4, 2, 'Level 3 - Node 4'), (5, 2, 'Level 3 - Node 5'), (6, 3, 'Level 3 - Node 6');
CREATE DICTIONARY hierarchical_dictionary (id UInt64, parent_id UInt64 HIERARCHICAL, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'hierarchy_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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)
```

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

*Introduced in version 21.4.0.*

dictGetFloat32 [#dictgetfloat32]

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

**Syntax**

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

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Float32_value Float32) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -123.123);
CREATE DICTIONARY all_types_dict (id UInt32, Float32_value Float32) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetFloat32('all_types_dict', 'Float32_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetFloat32OrDefault [#dictgetfloat32ordefault]

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

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

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Float32_value Float32) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 123.45);
CREATE DICTIONARY all_types_dict (id UInt32, Float32_value Float32) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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);
```

```text title=Response
┌─dictGetFloat32('all_types_dict', 'Float32_value', 1)─┐
│                                               123.45 │
└──────────────────────────────────────────────────────┘
┌─dictGetFloat32OrDefault('all_types_dict', 'Float32_value', 999, -1.)─┐
│                                                                   -1 │
└──────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetFloat64 [#dictgetfloat64]

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

**Syntax**

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

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Float64_value Float64) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -123.123);
CREATE DICTIONARY all_types_dict (id UInt32, Float64_value Float64) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetFloat64('all_types_dict', 'Float64_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetFloat64OrDefault [#dictgetfloat64ordefault]

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

```sql
dictGetFloat64OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Float64_value Float64) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 987654.123456);
CREATE DICTIONARY all_types_dict (id UInt32, Float64_value Float64) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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);
```

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

*Introduced in version 1.1.0.*

dictGetHierarchy [#dictgethierarchy]

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

**Syntax**

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

**Arguments**

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

**Returned value**

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

**Examples**

**Get hierarchy for a key**

```sql title=Query
CREATE TABLE hierarchy_source (id UInt64, parent_id UInt64, name String) ENGINE = Memory;
INSERT INTO hierarchy_source VALUES (0, 0, 'Root'), (1, 0, 'Level 1 - Node 1'), (2, 1, 'Level 2 - Node 2'), (3, 1, 'Level 2 - Node 3'), (4, 2, 'Level 3 - Node 4'), (5, 2, 'Level 3 - Node 5'), (6, 3, 'Level 3 - Node 6');
CREATE DICTIONARY hierarchical_dictionary (id UInt64, parent_id UInt64 HIERARCHICAL, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'hierarchy_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetHierarchy('hierarchical_dictionary', 5)
```

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

*Introduced in version 1.1.0.*

dictGetIPv4 [#dictgetipv4]

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

**Syntax**

```sql
dictGetIPv4(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, IPv4_value IPv4) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '192.168.0.1');
CREATE DICTIONARY all_types_dict (id UInt32, IPv4_value IPv4) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetIPv4('all_types_dict', 'IPv4_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetIPv4OrDefault [#dictgetipv4ordefault]

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

```sql
dictGetIPv4OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, IPv4_value IPv4) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '192.168.0.1');
CREATE DICTIONARY all_types_dict (id UInt32, IPv4_value IPv4) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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'));
```

```text title=Response
┌─dictGetIPv4('all_types_dict', 'IPv4_value', 1)─┐
│ 192.168.0.1                                    │
└────────────────────────────────────────────────┘
┌─dictGetIPv4OrDefault('all_types_dict', 'IPv4_value', 999, toIPv4('0.0.0.0'))─┐
│ 0.0.0.0                                                                      │
└──────────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 23.1.0.*

dictGetIPv6 [#dictgetipv6]

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

**Syntax**

```sql
dictGetIPv6(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, IPv6_value IPv6) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '2001:db8:85a3::8a2e:370:7334');
CREATE DICTIONARY all_types_dict (id UInt32, IPv6_value IPv6) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetIPv6('all_types_dict', 'IPv6_value', 1)
```

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

*Introduced in version 23.1.0.*

dictGetIPv6OrDefault [#dictgetipv6ordefault]

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

**Syntax**

```sql
dictGetIPv6OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, IPv6_value IPv6) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '2001:db8:85a3::8a2e:370:7334');
CREATE DICTIONARY all_types_dict (id UInt32, IPv6_value IPv6) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetIPv6('all_types_dict', 'IPv6_value', 1);

-- for key which does not exist, returns the provided default value
SELECT dictGetIPv6OrDefault('all_types_dict', 'IPv6_value', 999, '::1'::IPv6);
```

```text title=Response
┌─dictGetIPv6('all_types_dict', 'IPv6_value', 1)─┐
│ 2001:db8:85a3::8a2e:370:7334                   │
└────────────────────────────────────────────────┘
┌─dictGetIPv6OrDefault('all_types_dict', 'IPv6_value', 999, CAST('::1', 'IPv6'))─┐
│ ::1                                                                            │
└────────────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 23.1.0.*

dictGetInt16 [#dictgetint16]

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

**Syntax**

```sql
dictGetInt16(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int16_value Int16) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -5000);
CREATE DICTIONARY all_types_dict (id UInt32, Int16_value Int16) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetInt16('all_types_dict', 'Int16_value', 1)
```

```text title=Response
┌─dictGetInt16⋯_value', 1)─┐
│                    -5000 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetInt16OrDefault [#dictgetint16ordefault]

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

**Syntax**

```sql
dictGetInt16OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int16_value Int16) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -5000);
CREATE DICTIONARY all_types_dict (id UInt32, Int16_value Int16) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetInt16('all_types_dict', 'Int16_value', 1);

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

```text title=Response
┌─dictGetInt16⋯_value', 1)─┐
│                    -5000 │
└──────────────────────────┘
┌─dictGetInt16⋯', 999, -1)─┐
│                       -1 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetInt32 [#dictgetint32]

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

**Syntax**

```sql
dictGetInt32(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int32_value Int32) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -1000000);
CREATE DICTIONARY all_types_dict (id UInt32, Int32_value Int32) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetInt32('all_types_dict', 'Int32_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetInt32OrDefault [#dictgetint32ordefault]

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

```sql
dictGetInt32OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int32_value Int32) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -1000000);
CREATE DICTIONARY all_types_dict (id UInt32, Int32_value Int32) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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);
```

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

*Introduced in version 1.1.0.*

dictGetInt64 [#dictgetint64]

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

**Syntax**

```sql
dictGetInt64(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int64_value Int64) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -9223372036854775807);
CREATE DICTIONARY all_types_dict (id UInt32, Int64_value Int64) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetInt64('all_types_dict', 'Int64_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetInt64OrDefault [#dictgetint64ordefault]

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

```sql
dictGetInt64OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int64_value Int64) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -9223372036854775808);
CREATE DICTIONARY all_types_dict (id UInt32, Int64_value Int64) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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);
```

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

*Introduced in version 1.1.0.*

dictGetInt8 [#dictgetint8]

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

**Syntax**

```sql
dictGetInt8(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int8_value Int8) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -100);
CREATE DICTIONARY all_types_dict (id UInt32, Int8_value Int8) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetInt8('all_types_dict', 'Int8_value', 1)
```

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

*Introduced in version 1.1.0.*

dictGetInt8OrDefault [#dictgetint8ordefault]

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

```sql
dictGetInt8OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, Int8_value Int8) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, -100);
CREATE DICTIONARY all_types_dict (id UInt32, Int8_value Int8) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetInt8('all_types_dict', 'Int8_value', 1);

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

```text title=Response
┌─dictGetInt8(⋯_value', 1)─┐
│                     -100 │
└──────────────────────────┘
┌─dictGetInt8O⋯', 999, -1)─┐
│                       -1 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetKeys [#dictgetkeys]

Returns the dictionary key(s) whose attribute equals the specified value. This is the inverse of the function `dictGet` on a single attribute.

Use setting `max_reverse_dictionary_lookup_cache_size_bytes` to cap the size of the per-query reverse-lookup cache used by `dictGetKeys`.
The cache stores serialized key tuples for each attribute value to avoid re-scanning the dictionary within the same query.
The cache is not persistent across queries. When the limit is reached, entries are evicted with LRU.
This is most effective with large dictionaries when the input has low cardinality and the working set fits in the cache. Set to `0` to disable caching.

**Syntax**

```sql
dictGetKeys('dict_name', 'attr_name', value_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Attribute to match. [`String`](/reference/data-types/string)
* `value_expr` — Value to match against the attribute. [`Expression`](/reference/data-types/special-data-types/expression)

**Returned value**

For single key dictionaries: an array of keys whose attribute equals `value_expr`. For multi key dictionaries: an array of tuples of keys whose attribute equals `value_expr`. If there is no attribute corresponding to `value_expr` in the dictionary, then an empty array is returned. RawTree throws an exception if it cannot parse the value of the attribute or the value cannot be converted to the attribute data type.

**Examples**

**Sample usage**

```sql title=Query
CREATE TABLE task_priority_source (task_id UInt64, priority_level String) ENGINE = Memory;
INSERT INTO task_priority_source VALUES (1, 'low'), (2, 'high'), (3, 'medium'), (4, 'high');
CREATE DICTIONARY task_id_to_priority_dictionary (task_id UInt64, priority_level String) PRIMARY KEY task_id SOURCE(CLICKHOUSE(TABLE 'task_priority_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 0 MAX 0);
SELECT arraySort(dictGetKeys('task_id_to_priority_dictionary', 'priority_level', 'high')) AS ids;
```

```text title=Response
┌─ids───┐
│ [2,4] │
└───────┘
```

*Introduced in version 25.12.0.*

dictGetOrDefault [#dictgetordefault]

Retrieves values from a dictionary, with a default value if the key is not found.

**Syntax**

```sql
dictGetOrDefault('dict_name', attr_names, id_expr, default_value)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_names` — Name of the column of the dictionary, or tuple of column names. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. For a dictionary with a simple key, an expression returning a `UInt64` value. For a dictionary with a composite (complex) key, an expression returning a tuple of the key values. If the composite key consists of a single attribute, its value may be passed directly, without wrapping it in `tuple`. [`UInt64`](/reference/data-types/int-uint) or [`Tuple(T)`](/reference/data-types/tuple)
* `default_value` — Default value to return if the key is not found. Type must match the attribute's data type.

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr` if the key is found.
If the key is not found, returns the `default_value` provided.

**Examples**

**Get value with default**

```sql title=Query
CREATE TABLE dict_mult_source (id UInt32, c1 UInt32, c2 String) ENGINE = Memory;
INSERT INTO dict_mult_source VALUES (1, 1, '1'), (2, 2, '2'), (3, 3, '3');
CREATE DICTIONARY ext_dict_mult (id UInt32, c1 UInt32, c2 String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'dict_mult_source' DB currentDatabase())) LAYOUT(FLAT()) LIFETIME(MIN 0 MAX 0);
SELECT dictGetOrDefault('ext_dict_mult', 'c1', toUInt64(999), 0) AS val
```

```text title=Response
0
```

*Introduced in version 18.16.0.*

dictGetOrNull [#dictgetornull]

Retrieves values from a dictionary, returning NULL if the key is not found.

**Syntax**

```sql
dictGetOrNull('dict_name', 'attr_name', id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. String literal. - `attr_name` — Name of the column to retrieve. String literal. - `id_expr` — Key value. Expression returning dictionary key-type value.

**Returned value**

Returns the value of the dictionary attribute that corresponds to `id_expr` if the key is found.
If the key is not found, returns `NULL`.

**Examples**

**Example using the range key dictionary**

```sql title=Query
CREATE TABLE range_key_dictionary_source_table (key UInt64, start_date Date, end_date Date, value String, value_nullable Nullable(String)) ENGINE = TinyLog();
INSERT INTO range_key_dictionary_source_table VALUES(1, toDate('2019-05-20'), toDate('2019-05-20'), 'First', 'First');
INSERT INTO range_key_dictionary_source_table VALUES(2, toDate('2019-05-20'), toDate('2019-05-20'), 'Second', NULL);
INSERT INTO range_key_dictionary_source_table VALUES(3, toDate('2019-05-20'), toDate('2019-05-20'), 'Third', 'Third');
CREATE DICTIONARY range_key_dictionary (key UInt64, start_date Date, end_date Date, value String, value_nullable Nullable(String)) PRIMARY KEY key SOURCE(CLICKHOUSE(TABLE 'range_key_dictionary_source_table' DB currentDatabase())) LIFETIME(MIN 1 MAX 1000) LAYOUT(RANGE_HASHED()) RANGE(MIN start_date MAX end_date);
SELECT
    (number, toDate('2019-05-20')),
    dictGetOrNull('range_key_dictionary', 'value', number, toDate('2019-05-20')),
FROM system.numbers LIMIT 5 FORMAT TabSeparated;
```

```text title=Response
(0,'2019-05-20')  \N
(1,'2019-05-20')  First
(2,'2019-05-20')  Second
(3,'2019-05-20')  Third
(4,'2019-05-20')  \N
```

*Introduced in version 21.4.0.*

dictGetRoot [#dictgetroot]

Returns the topmost ancestor (the root) of a key in a [hierarchical dictionary](/reference/statements/create/dictionary/layouts/hierarchical#hierarchical-dictionaries).

This is a convenient equivalent of taking the last element of the array returned by [dictGetHierarchy](#dictGetHierarchy), i.e. `dictGetHierarchy(dict_name, key)[-1]`.

If the key is itself a top-level node of the hierarchy, the key is returned. If the key is absent from the dictionary, `0` is returned.

**Syntax**

```sql
dictGetRoot(dict_name, key)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `key` — Key value. [`UInt64`](/reference/data-types/int-uint)

**Returned value**

Returns the topmost ancestor (the root) for the key. [`UInt64`](/reference/data-types/int-uint)

**Examples**

**Get the root for a key**

```sql title=Query
CREATE TABLE hierarchy_source (id UInt64, parent_id UInt64, name String) ENGINE = Memory;
INSERT INTO hierarchy_source VALUES (0, 0, 'Root'), (1, 0, 'Level 1 - Node 1'), (2, 1, 'Level 2 - Node 2'), (3, 1, 'Level 2 - Node 3'), (4, 2, 'Level 3 - Node 4'), (5, 2, 'Level 3 - Node 5'), (6, 3, 'Level 3 - Node 6');
CREATE DICTIONARY hierarchical_dictionary (id UInt64, parent_id UInt64 HIERARCHICAL, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'hierarchy_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetRoot('hierarchical_dictionary', 5)
```

```text title=Response
┌─dictGetRoot(⋯ionary', 5)─┐
│                        1 │
└──────────────────────────┘
```

*Introduced in version 26.7.0.*

dictGetString [#dictgetstring]

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

**Syntax**

```sql
dictGetString(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, String_value String) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 'test string');
CREATE DICTIONARY all_types_dict (id UInt32, String_value String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetString('all_types_dict', 'String_value', 1)
```

```text title=Response
┌─dictGetString(⋯_value', 1)─┐
│ test string                │
└────────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetStringOrDefault [#dictgetstringordefault]

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

**Syntax**

```sql
dictGetStringOrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, String_value String) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 'test string');
CREATE DICTIONARY all_types_dict (id UInt32, String_value String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetString('all_types_dict', 'String_value', 1);

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

```text title=Response
┌─dictGetString('all_types_dict', 'String_value', 1)─┐
│ test string                                        │
└────────────────────────────────────────────────────┘
┌─dictGetStringOrDefault('all_types_dict', 'String_value', 999, 'default')─┐
│ default                                                                  │
└──────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt16 [#dictgetuint16]

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

**Syntax**

```sql
dictGetUInt16(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt16_value UInt16) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 5000);
CREATE DICTIONARY all_types_dict (id UInt32, UInt16_value UInt16) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetUInt16('all_types_dict', 'UInt16_value', 1)
```

```text title=Response
┌─dictGetUInt1⋯_value', 1)─┐
│                     5000 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt16OrDefault [#dictgetuint16ordefault]

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

**Syntax**

```sql
dictGetUInt16OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt16_value UInt16) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 5000);
CREATE DICTIONARY all_types_dict (id UInt32, UInt16_value UInt16) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetUInt16('all_types_dict', 'UInt16_value', 1);

-- for key which does not exist, returns the provided default value (0)
SELECT dictGetUInt16OrDefault('all_types_dict', 'UInt16_value', 999, 0);
```

```text title=Response
┌─dictGetUInt1⋯_value', 1)─┐
│                     5000 │
└──────────────────────────┘
┌─dictGetUInt1⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt32 [#dictgetuint32]

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

**Syntax**

```sql
dictGetUInt32(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt32_value UInt32) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 1000000);
CREATE DICTIONARY all_types_dict (id UInt32, UInt32_value UInt32) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetUInt32('all_types_dict', 'UInt32_value', 1)
```

```text title=Response
┌─dictGetUInt3⋯_value', 1)─┐
│                  1000000 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt32OrDefault [#dictgetuint32ordefault]

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

**Syntax**

```sql
dictGetUInt32OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt32_value UInt32) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 1000000);
CREATE DICTIONARY all_types_dict (id UInt32, UInt32_value UInt32) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetUInt32('all_types_dict', 'UInt32_value', 1);

-- for key which does not exist, returns the provided default value (0)
SELECT dictGetUInt32OrDefault('all_types_dict', 'UInt32_value', 999, 0);
```

```text title=Response
┌─dictGetUInt3⋯_value', 1)─┐
│                  1000000 │
└──────────────────────────┘
┌─dictGetUInt3⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt64 [#dictgetuint64]

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

**Syntax**

```sql
dictGetUInt64(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt64_value UInt64) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 9223372036854775807);
CREATE DICTIONARY all_types_dict (id UInt32, UInt64_value UInt64) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetUInt64('all_types_dict', 'UInt64_value', 1)
```

```text title=Response
┌─dictGetUInt6⋯_value', 1)─┐
│      9223372036854775807 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt64OrDefault [#dictgetuint64ordefault]

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

**Syntax**

```sql
dictGetUInt64OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt64_value UInt64) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 9223372036854775807);
CREATE DICTIONARY all_types_dict (id UInt32, UInt64_value UInt64) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetUInt64('all_types_dict', 'UInt64_value', 1);

-- for key which does not exist, returns the provideddefault value (0)
SELECT dictGetUInt64OrDefault('all_types_dict', 'UInt64_value', 999, 0);
```

```text title=Response
┌─dictGetUInt6⋯_value', 1)─┐
│      9223372036854775807 │
└──────────────────────────┘
┌─dictGetUInt6⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt8 [#dictgetuint8]

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

**Syntax**

```sql
dictGetUInt8(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt8_value UInt8) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 100);
CREATE DICTIONARY all_types_dict (id UInt32, UInt8_value UInt8) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetUInt8('all_types_dict', 'UInt8_value', 1)
```

```text title=Response
┌─dictGetUInt8⋯_value', 1)─┐
│                      100 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUInt8OrDefault [#dictgetuint8ordefault]

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

**Syntax**

```sql
dictGetUInt8OrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UInt8_value UInt8) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, 100);
CREATE DICTIONARY all_types_dict (id UInt32, UInt8_value UInt8) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetUInt8('all_types_dict', 'UInt8_value', 1);

-- for key which does not exist, returns the provided default value (0)
SELECT dictGetUInt8OrDefault('all_types_dict', 'UInt8_value', 999, 0);
```

```text title=Response
┌─dictGetUInt8⋯_value', 1)─┐
│                      100 │
└──────────────────────────┘
┌─dictGetUInt8⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUUID [#dictgetuuid]

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

**Syntax**

```sql
dictGetUUID(dict_name, attr_name, id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `attr_name` — Name of the column of the dictionary. [`String`](/reference/data-types/string) or [`Tuple(String)`](/reference/data-types/tuple)
* `id_expr` — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). [`Expression`](/reference/data-types/special-data-types/expression) or [`Tuple(T)`](/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
RawTree 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**

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UUID_value UUID) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '123e4567-e89b-12d3-a456-426614174000');
CREATE DICTIONARY all_types_dict (id UInt32, UUID_value UUID) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGetUUID('all_types_dict', 'UUID_value', 1)
```

```text title=Response
┌─dictGetUUID(⋯_value', 1)─────────────┐
│ 123e4567-e89b-12d3-a456-426614174000 │
└──────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

dictGetUUIDOrDefault [#dictgetuuidordefault]

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

**Syntax**

```sql
dictGetUUIDOrDefault(dict_name, attr_name, id_expr, default_value_expr)
```

**Arguments**

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

```sql title=Query
CREATE TABLE all_types_test (id UInt32, UUID_value UUID) ENGINE = MergeTree() ORDER BY id;
INSERT INTO all_types_test VALUES (1, '550e8400-e29b-41d4-a716-446655440000');
CREATE DICTIONARY all_types_dict (id UInt32, UUID_value UUID) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'all_types_test' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- for key which exists
SELECT dictGetUUID('all_types_dict', 'UUID_value', 1);

-- for key which does not exist, returns the provided default value
SELECT dictGetUUIDOrDefault('all_types_dict', 'UUID_value', 999, '00000000-0000-0000-0000-000000000000'::UUID);
```

```text title=Response
┌─dictGetUUID('all_types_dict', 'UUID_value', 1)─┐
│ 550e8400-e29b-41d4-a716-446655440000           │
└────────────────────────────────────────────────┘
┌─dictGetUUIDOrDefault('all_types_dict', 'UUID_value', 999, CAST('00000000-0000-0000-0000-000000000000', 'UUID'))─┐
│ 00000000-0000-0000-0000-000000000000                                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 1.1.0.*

dictHas [#dicthas]

Checks whether a key is present in a dictionary.

**Syntax**

```sql
dictHas('dict_name', id_expr)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Check for the existence of a key in a dictionary**

```sql title=Query
CREATE TABLE hierarchy_source (id UInt64, parent_id UInt64, name String) ENGINE = Memory;
INSERT INTO hierarchy_source VALUES (0, 0, 'Root'), (1, 0, 'Level 1 - Node 1'), (2, 1, 'Level 2 - Node 2'), (3, 1, 'Level 2 - Node 3'), (4, 2, 'Level 3 - Node 4'), (5, 2, 'Level 3 - Node 5'), (6, 3, 'Level 3 - Node 6');
CREATE DICTIONARY hierarchical_dictionary (id UInt64, parent_id UInt64 HIERARCHICAL, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'hierarchy_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- 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 dictHas('hierarchical_dictionary', 2);
SELECT dictHas('hierarchical_dictionary', 7);
```

```text title=Response
┌─dictHas('hie⋯ionary', 2)─┐
│                        1 │
└──────────────────────────┘
┌─dictHas('hie⋯ionary', 7)─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*

dictIsIn [#dictisin]

Checks the ancestor of a key through the whole hierarchical chain in the dictionary.

**Syntax**

```sql
dictIsIn(dict_name, child_id_expr, ancestor_id_expr)
```

**Arguments**

* `dict_name` — Name of the dictionary. [`String`](/reference/data-types/string)
* `child_id_expr` — Key to be checked. [`String`](/reference/data-types/string)
* `ancestor_id_expr` — Alleged ancestor of the `child_id_expr` key. [`const String`](/reference/data-types/string)

**Returned value**

Returns `0` if `child_id_expr` is not a child of `ancestor_id_expr`, `1` if `child_id_expr` is a child of `ancestor_id_expr` or if `child_id_expr` is an `ancestor_id_expr`. [`UInt8`](/reference/data-types/int-uint)

**Examples**

**Check hierarchical relationship**

```sql title=Query
CREATE TABLE hierarchy_source (id UInt64, parent_id UInt64, name String) ENGINE = Memory;
INSERT INTO hierarchy_source VALUES (0, 0, 'Root'), (1, 0, 'Level 1 - Node 1'), (2, 1, 'Level 2 - Node 2'), (3, 1, 'Level 2 - Node 3'), (4, 2, 'Level 3 - Node 4'), (5, 2, 'Level 3 - Node 5'), (6, 3, 'Level 3 - Node 6');
CREATE DICTIONARY hierarchical_dictionary (id UInt64, parent_id UInt64 HIERARCHICAL, name String) PRIMARY KEY id SOURCE(CLICKHOUSE(TABLE 'hierarchy_source' DB currentDatabase())) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600);
-- valid hierarchy
SELECT dictIsIn('hierarchical_dictionary', 6, 3);

-- invalid hierarchy
SELECT dictIsIn('hierarchical_dictionary', 3, 5);
```

```text title=Response
┌─dictIsIn('hi⋯ary', 6, 3)─┐
│                        1 │
└──────────────────────────┘
┌─dictIsIn('hi⋯ary', 3, 5)─┐
│                        0 │
└──────────────────────────┘
```

*Introduced in version 1.1.0.*
