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 dictGetUUID('all_types_dict', 'UUID_value', 1)
```

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

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

Introduced in: v1.1.0

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

```
dictGetUUIDOrDefault(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 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);
```

```
┌─dictGetUUID('all_t⋯ 'UUID_value', 1)─┐
│ 550e8400-e29b-41d4-a716-446655440000 │
└──────────────────────────────────────┘
┌─dictGetUUIDOrDefa⋯000000000000'::UUID)─┐
│ 00000000-0000-0000-0000-000000000000   │
└────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Checks whether a key is present in a dictionary.

**Syntax**

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

**Arguments**

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

**Returned value**

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

**Examples**

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

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

SELECT dictHas('hierarchical_dictionary', 2);
SELECT dictHas('hierarchical_dictionary', 7);
```

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

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

Introduced in: v1.1.0

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

**Syntax**

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

**Arguments**

- `dict_name` — Name of the dictionary. [`String`](/docs/sql-reference/data-types/string)
- `child_id_expr` — Key to be checked. [`String`](/docs/sql-reference/data-types/string)
- `ancestor_id_expr` — Alleged ancestor of the `child_id_expr` key. [`const String`](/docs/sql-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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Check hierarchical relationship**

```
-- valid hierarchy
SELECT dictIsIn('hierarchical_dictionary', 6, 3)

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

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

- [Example dictionaries](#example-dictionary)- [dictGet](#dictGet)- [dictGetAll](#dictGetAll)- [dictGetChildren](#dictGetChildren)- [dictGetDate](#dictGetDate)- [dictGetDateOrDefault](#dictGetDateOrDefault)- [dictGetDateTime](#dictGetDateTime)- [dictGetDateTimeOrDefault](#dictGetDateTimeOrDefault)- [dictGetDescendants](#dictGetDescendants)- [dictGetFloat32](#dictGetFloat32)- [dictGetFloat32OrDefault](#dictGetFloat32OrDefault)- [dictGetFloat64](#dictGetFloat64)- [dictGetFloat64OrDefault](#dictGetFloat64OrDefault)- [dictGetHierarchy](#dictGetHierarchy)- [dictGetIPv4](#dictGetIPv4)- [dictGetIPv4OrDefault](#dictGetIPv4OrDefault)- [dictGetIPv6](#dictGetIPv6)- [dictGetIPv6OrDefault](#dictGetIPv6OrDefault)- [dictGetInt16](#dictGetInt16)- [dictGetInt16OrDefault](#dictGetInt16OrDefault)- [dictGetInt32](#dictGetInt32)- [dictGetInt32OrDefault](#dictGetInt32OrDefault)- [dictGetInt64](#dictGetInt64)- [dictGetInt64OrDefault](#dictGetInt64OrDefault)- [dictGetInt8](#dictGetInt8)- [dictGetInt8OrDefault](#dictGetInt8OrDefault)- [dictGetKeys](#dictGetKeys)- [dictGetOrDefault](#dictGetOrDefault)- [dictGetOrNull](#dictGetOrNull)- [dictGetString](#dictGetString)- [dictGetStringOrDefault](#dictGetStringOrDefault)- [dictGetUInt16](#dictGetUInt16)- [dictGetUInt16OrDefault](#dictGetUInt16OrDefault)- [dictGetUInt32](#dictGetUInt32)- [dictGetUInt32OrDefault](#dictGetUInt32OrDefault)- [dictGetUInt64](#dictGetUInt64)- [dictGetUInt64OrDefault](#dictGetUInt64OrDefault)- [dictGetUInt8](#dictGetUInt8)- [dictGetUInt8OrDefault](#dictGetUInt8OrDefault)- [dictGetUUID](#dictGetUUID)- [dictGetUUIDOrDefault](#dictGetUUIDOrDefault)- [dictHas](#dictHas)- [dictIsIn](#dictIsIn)

Was this page helpful?