Utilities

Utilities are functions for working with data of various types that are difficult to categorise. Their descriptions are collected in this section.

coalesce()

Description

Returns the first value other than NULL from the list of argument values.
Usage

coalesce(argument1[, argument2, {…​])

If the only argument has the value NULL, then NULL is returned.

See example 
SELECT
    coalesce(NULL, 'Tengri', NULL) AS result_1,
    coalesce(NULL, '', NULL) AS result_2,
    coalesce('Tengri') AS result_3,
    coalesce(NULL) AS result_4;
+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| Tengri   |          | Tengri   | null     |
+----------+----------+----------+----------+

generate_series()

Description

Generates a list of values in the range between start and stop.
Usage

generate_series([start,] stop[, step])

The parameters start and stop are treated as "inclusive".
The default value for start is 0, and for step is 1.

See example 
SELECT
    generate_series(10) AS stop,
    generate_series(5, 10) AS start_stop,
    generate_series(5, 10, 2) AS start_stop_step;
+--------------------------+----------------+-----------------+
| stop                     | start_stop     | start_stop_step |
+--------------------------+----------------+-----------------+
| {0,1,2,3,4,5,6,7,8,9,10} | {5,6,7,8,9,10} | {5,7,9}         |
+--------------------------+----------------+-----------------+

hash()

Description

Returns a hash of the data from argument as a number.
Usage

hash(argument)
See example 
SELECT
    hash('Tengri') AS hash;
+----------------------+
|         hash         |
+----------------------+
| 15418814193266442000 |
+----------------------+

unnest()

Description

Expands lists or structures from argument into a set of distinct values.
Usage

unnest(argument) [, recursive := true] [, max_depth := <num>]

Applying the function to a list yields one line for each element in the list. The usual scalar expressions in the same SELECT expression are repeated for each row output.

When multiple lists are expanded in the same SELECT expression, they are expanded each into a separate column. If one list is longer than another, the shorter list is filled with NULL values.

The function changes the cardinality of the data.

Parameters

  • recursive := true
    Enables recursive mode. If this mode is enabled (value true), the function fully expands lists and then fully expands nested structures. This can be useful for fully "flattening" columns that contain lists within lists or structures within lists. Note that lists within structures are not expanded.

    For more information about the parameter, see examples

    Let’s show how this parameter works on two examples with the same data with and without the parameter enabled:

    SELECT
    unnest([[1, 2, 3], [4, 5]], recursive := true) AS result;
    +--------+
| result |
+--------+
| 1      |
+--------+
| 2      |
+--------+
| 3      |
+--------+
| 4      |
+--------+
| 5      |
+--------+
    SELECT
    unnest([[1, 2, 3], [4, 5]]) AS result;
    +---------+
|  result |
+---------+
| {1,2,3} |
+---------+
| {4,5}   |
+---------+

  • max_depth := <num>
    The max_depth parameter allows you to limit the maximum depth of recursive deployment. Recursive mode is automatically enabled if the maximum depth is specified.

    For more information about the parameter, see examples

    Let’s show how this parameter works using three examples with the same data: with default deployment depth (1), with deployment depth 2 and with deployment depth 3:

    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]])
AS result;
    +-------------------------+
|          result         |
+-------------------------+
| {['T', 'e'],['n', 'g']} |
+-------------------------+
| {['r', 'i'],[]}         |
+-------------------------+
| {['!', '!', '!']}       |
+-------------------------+
    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]],
    max_depth := 2)
AS result;
    +---------+
|  result |
+---------+
| {T,e}   |
+---------+
| {n,g}   |
+---------+
| {r,i}   |
+---------+
| {}      |
+---------+
| {!,!,!} |
+---------+
    SELECT
    unnest([[['T', 'e'], ['n', 'g']], [['r', 'i'], []], [['!', '!', '!']]],
    max_depth := 3)
AS result;
    +--------+
| result |
+--------+
| T      |
+--------+
| e      |
+--------+
| n      |
+--------+
| g      |
+--------+
| r      |
+--------+
| i      |
+--------+
| !      |
+--------+
| !      |
+--------+
| !      |
+--------+
See more examples 
SELECT
    unnest([1,2,3])       AS numbers,
    unnest(['a','b','c']) AS letters;
+---------+---------+
| numbers | letters |
+---------+---------+
| 1       | a       |
+---------+---------+
| 2       | b       |
+---------+---------+
| 3       | c       |
+---------+---------+
SELECT
    unnest([1,2,3])   AS numbers,
    unnest(['a','b']) AS letters;
+---------+---------+
| numbers | letters |
+---------+---------+
| 1       | a       |
+---------+---------+
| 2       | b       |
+---------+---------+
| 3       | null    |
+---------+---------+
SELECT
    unnest([{'column_a': 1, 'column_b': 84},
            {'column_a': 100, 'column_b': NULL, 'column_c':22}],
            recursive := true);
+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
| 1        | 84       | null     |
+----------+----------+----------+
| 100      | null     | 22       |
+----------+----------+----------+
SELECT
    unnest([{'column_a': 1, 'column_b': 84},
            {'column_a': 100, 'column_b': NULL, 'column_c':22}])
AS result;
+-----------------------------------------------------+
|                        result                       |
+-----------------------------------------------------+
| {"column_a": 1, "column_b": 84, "column_c": null}   |
+-----------------------------------------------------+
| {"column_a": 100, "column_b": null, "column_c": 22} |
+-----------------------------------------------------+