v.21.4New Features

Added dictGetChildren and dictGetDescendants Functions in ClickHouse

Official CH changelog text
Added functions dictGetChildren(dictionary, key), dictGetDescendants(dictionary, key, level). Function dictGetChildren return all children as an array if indexes. It is a inverse transformation for dictGetHierarchy. Function dictGetDescendants return all descendants as if dictGetChildren was applied level times recursively. Zero level value is equivalent to infinity. Closes #14656. #22096 (Maksim Kita).
Added new dictionary functions dictGetChildren(dictionary, key) and dictGetDescendants(dictionary, key, level) to retrieve child and descendant keys from hierarchical dictionaries.

Why it matters

These functions solve the problem of navigating and querying hierarchical dictionary structures by providing a way to retrieve all immediate children or all descendants of a given key. This expands ClickHouse's dictionary capabilities by allowing inverse lookups of hierarchy, enabling more flexible and complex data analysis.

How to use it

Use dictGetChildren(dictionary, key) to get an array of all children indexes of a specified key. Use dictGetDescendants(dictionary, key, level) to get all descendants up to a specified level; setting level to zero returns descendants at all levels (infinite recursion). Both functions take the dictionary name and key as arguments in SQL queries.

Related resources

PreviousNext