Dictionary

dictGet

Retrieves values from a dictionary.

Syntax

dictGet('dict_name', attr_names, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_names — Name of the column of the dictionary, or tuple of column names. String or Tuple(String)
id_expr — Key value. An expression returning UInt64/Tuple(T). UInt64 or Tuple(T)

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

SELECT dictGet('ext_dict_test', 'c1', toUInt64(1)) AS val

Multiple attributes

SELECT
    dictGet('ext_dict_mult', ('c1','c2'), number + 1) AS val,
    toTypeName(val) AS type
FROM system.numbers
LIMIT 3;

┌─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.

dictGetAll

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

Syntax

dictGetAll(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

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;

┌─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.

dictGetChildren

Returns first-level children as an array of indexes. It is the inverse transformation for dictGetHierarchy.

Syntax

dictGetChildren(dict_name, key)

Arguments

dict_name — Name of the dictionary. String
key — Key to be checked. const String

Returned value

Returns the first-level descendants for the key. Array(UInt64)

Examples

Get the first-level children of a dictionary

SELECT dictGetChildren('hierarchical_dictionary', 2);

┌─dictGetChild⋯ionary', 2)─┐
│ [4,5]                    │
└──────────────────────────┘

Introduced in version 21.4.

dictGetDate

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

Syntax

dictGetDate(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetDate('all_types_dict', 'Date_value', 1)

┌─dictGetDate(⋯_value', 1)─┐
│               2020-01-01 │
└──────────────────────────┘

Introduced in version 1.1.

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

dictGetDateOrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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'));

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

Introduced in version 1.1.

dictGetDateTime

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

Syntax

dictGetDateTime(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

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

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

Introduced in version 1.1.

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

dictGetDateTimeOrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

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

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

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

Introduced in version 1.1.

dictGetDescendants

Returns all descendants as if the dictGetChildren function were applied level times recursively.

Syntax

dictGetDescendants(dict_name, key, level)

Arguments

dict_name — Name of the dictionary. String
key — Key to be checked. const String
level — Key to be checked. Hierarchy level. If level = 0 returns all descendants to the end. UInt8

Returned value

Returns the descendants for the key. Array(UInt64)

Examples

Get the first-level children of a dictionary

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

SELECT dictGetDescendants('hierarchical_dictionary', 0, 2)

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

Introduced in version 21.4.

dictGetFloat32

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

Syntax

dictGetFloat32(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

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

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

Introduced in version 1.1.

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

dictGetFloat32OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

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

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

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

Introduced in version 1.1.

dictGetFloat64

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

Syntax

dictGetFloat64(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetFloat64('all_types_dict', 'Float64_value', 1)

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

Introduced in version 1.1.

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

dictGetFloat64OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- for key which exists
SELECT dictGetFloat64('all_types_dict', 'Float64_value', 1);

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

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

Introduced in version 1.1.

dictGetHierarchy

Creates an array, containing all the parents of a key in the hierarchical dictionary.

Syntax

dictGetHierarchy(dict_name, key)

Arguments

dict_name — Name of the dictionary. String
key — Key value. const String

Returned value

Returns parents for the key. Array(UInt64)

Examples

Get hierarchy for a key

SELECT dictGetHierarchy('hierarchical_dictionary', 5)

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

Introduced in version 1.1.

dictGetIPv4

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

Syntax

dictGetIPv4(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetIPv4('all_types_dict', 'IPv4_value', 1)

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

Introduced in version 1.1.

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

dictGetIPv4OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- for key which exists
SELECT dictGetIPv4('all_types_dict', 'IPv4_value', 1);

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

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

Introduced in version 23.1.

dictGetIPv6

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

Syntax

dictGetIPv6(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetIPv6('all_types_dict', 'IPv6_value', 1)

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

Introduced in version 23.1.

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

dictGetIPv6OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

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

Introduced in version 23.1.

dictGetInt16

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

Syntax

dictGetInt16(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetInt16('all_types_dict', 'Int16_value', 1)

┌─dictGetInt16⋯_value', 1)─┐
│                    -5000 │
└──────────────────────────┘

Introduced in version 1.1.

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

dictGetInt16OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

┌─dictGetInt16⋯_value', 1)─┐
│                    -5000 │
└──────────────────────────┘
┌─dictGetInt16⋯', 999, -1)─┐
│                       -1 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetInt32

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

Syntax

dictGetInt32(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetInt32('all_types_dict', 'Int32_value', 1)

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

Introduced in version 1.1.

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

dictGetInt32OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetInt64

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
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetInt64('all_types_dict', 'Int64_value', 1)

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

Introduced in version 1.1.

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

dictGetInt64OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetInt8

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
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetInt8('all_types_dict', 'Int8_value', 1)

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

Introduced in version 1.1.

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

dictGetInt8OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

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

Introduced in version 1.1.

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

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

Arguments

dict_name — Name of the dictionary. String
attr_name — Attribute to match. String
value_expr — Value to match against the attribute. 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

SELECT dictGetKeys('task_id_to_priority_dictionary', 'priority_level', 'high') AS ids;

┌─ids───┐
│ [4,2] │
└───────┘

Introduced in version 25.12.

dictGetOrDefault

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

Syntax

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

Arguments

dict_name — Name of the dictionary. String
attr_names — Name of the column of the dictionary, or tuple of column names. String or Tuple(String)
id_expr — Key value. An expression returning UInt64/Tuple(T). UInt64 or Tuple(T)
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

SELECT dictGetOrDefault('ext_dict_mult', 'c1', toUInt64(999), 0) AS val

Introduced in version 18.16.

dictGetOrNull

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

Syntax

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

SELECT
    (number, toDate('2019-05-20')),
    dictGetOrNull('range_key_dictionary', 'value', number, toDate('2019-05-20')),
FROM system.numbers LIMIT 5 FORMAT TabSeparated;

(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.

dictGetString

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

Syntax

dictGetString(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetString('all_types_dict', 'String_value', 1)

┌─dictGetString(⋯_value', 1)─┐
│ test string                │
└────────────────────────────┘

Introduced in version 1.1.

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

dictGetStringOrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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');

┌─dictGetString(⋯_value', 1)─┐
│ test string                │
└────────────────────────────┘
┌─dictGetStringO⋯ 999, 'default')─┐
│ default                         │
└─────────────────────────────────┘

Introduced in version 1.1.

dictGetUInt16

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

Syntax

dictGetUInt16(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetUInt16('all_types_dict', 'UInt16_value', 1)

┌─dictGetUInt1⋯_value', 1)─┐
│                     5000 │
└──────────────────────────┘

Introduced in version 1.1.

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

dictGetUInt16OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

┌─dictGetUInt1⋯_value', 1)─┐
│                     5000 │
└──────────────────────────┘
┌─dictGetUInt1⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetUInt32

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

Syntax

dictGetUInt32(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetUInt32('all_types_dict', 'UInt32_value', 1)

┌─dictGetUInt3⋯_value', 1)─┐
│                  1000000 │
└──────────────────────────┘

Introduced in version 1.1.

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

dictGetUInt32OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

┌─dictGetUInt3⋯_value', 1)─┐
│                  1000000 │
└──────────────────────────┘
┌─dictGetUInt3⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetUInt64

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

Syntax

dictGetUInt64(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetUInt64('all_types_dict', 'UInt64_value', 1)

┌─dictGetUInt6⋯_value', 1)─┐
│      9223372036854775807 │
└──────────────────────────┘

Introduced in version 1.1.

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

dictGetUInt64OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

┌─dictGetUInt6⋯_value', 1)─┐
│      9223372036854775807 │
└──────────────────────────┘
┌─dictGetUInt6⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetUInt8

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

Syntax

dictGetUInt8(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetUInt8('all_types_dict', 'UInt8_value', 1)

┌─dictGetUInt8⋯_value', 1)─┐
│                      100 │
└──────────────────────────┘

Introduced in version 1.1.

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

dictGetUInt8OrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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);

┌─dictGetUInt8⋯_value', 1)─┐
│                      100 │
└──────────────────────────┘
┌─dictGetUInt8⋯e', 999, 0)─┐
│                        0 │
└──────────────────────────┘

Introduced in version 1.1.

dictGetUUID

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

Syntax

dictGetUUID(dict_name, attr_name, id_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. An expression returning a dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)

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

SELECT dictGetUUID('all_types_dict', 'UUID_value', 1)

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

Introduced in version 1.1.

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

dictGetUUIDOrDefault(dict_name, attr_name, id_expr, default_value_expr)

Arguments

dict_name — Name of the dictionary. String
attr_name — Name of the column of the dictionary. String or Tuple(String)
id_expr — Key value. Expression returning dictionary key-type value or tuple value (dictionary configuration dependent). Expression or Tuple(T)
default_value_expr — Value(s) returned if the dictionary does not contain a row with the id_expr key. Expression or Tuple(T)

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

-- 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   │
└────────────────────────────────────────┘

Introduced in version 1.1.

dictHas

Checks whether a key is present in a dictionary.

Syntax

dictHas('dict_name', id_expr)

Arguments

dict_name — Name of the dictionary. String
id_expr — Key value const String

Returned value

Returns 1 if the key exists, otherwise 0. UInt8

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 │
└──────────────────────────┘

Introduced in version 1.1.

dictIsIn

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
child_id_expr — Key to be checked. String
ancestor_id_expr — Alleged ancestor of the child_id_expr key. const 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

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 │
└──────────────────────────┘

Introduced in version 1.1.

On this page