List Functions

`array_length()`

Description	Returns the length of the list.
Usage	`array_length(list)`

See example

SELECT
    array_length([1,2,3])             AS result_1,
    array_length(['Tengri'])          AS result_2,
    array_length(split('Tengri', '')) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 3        | 1        | 6        |
+----------+----------+----------+

`flatten()`

Description	Takes a nested list and returns a list flattened by one level of nesting.
Usage	`flatten(nested_list)`

See example

SELECT
    flatten([[1, 2], [3, 4, 5]])       AS result_1,
    flatten([[1], [2], [3], [4], [5]]) AS result_2;

+-------------+-------------+
| result_1    | result_2    |
+-------------+-------------+
| {1,2,3,4,5} | {1,2,3,4,5} |
+-------------+-------------+

`list_aggregate()`

Description	Applies a specified aggregate function to a given list.
Usage	`list_aggregate(list, function_name, ...)`
Aliases	`aggregate()`, `array_aggr()`, `array_aggregate()`, `list_aggr()`

See example

SELECT
    list_aggregate([1, 2, 3], 'max')                           AS result_1,
    list_aggregate([1, 2, 3], 'any_value')                     AS result_2,
    list_aggregate(['I', 'love', 'Tengri'], 'string_agg', ' ') AS result_3;

+----------+----------+---------------+
| result_1 | result_2 | result_3      |
+----------+----------+---------------+
| 3        | 1        | I love Tengri |
+----------+----------+---------------+

`list_concat()`

Description	Concatenates several lists into one.
Usage	`list_concat(list_1, list_2 ...)`
Aliases	`list_cat()`, `array_concat()`, `array_cat()`

NULL values are ignored.

`list_contains()`

Description	Checks whether an element is present in a list.
Usage	`list_contains(list, element)`
Aliases	`array_contains()`, `array_has()`, `list_has()`

See example

SELECT
    list_contains([1,2,3], 3)          AS result_1,
    list_contains([1,2,3, NULL], 3)    AS result_2,
    list_contains([1,2,3, NULL], NULL) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | true     | null     |
+----------+----------+----------+

`list_cosine_distance()`

Description	Calculates the cosine distance between two lists (numeric vectors).
Usage	`list_cosine_distance(list_1, list_2)`

See example

SELECT
    list_cosine_distance([1, 2, 3], [1, 2, 3]) AS result_1,
    list_cosine_distance([1, 2, 3], [2, 4, 6]) AS result_2,
    list_cosine_distance([1, 2, 3], [5, 1, 9]) AS result_3;

+----------+----------+---------------------+
| result_1 | result_2 | result_3            |
+----------+----------+---------------------+
| 0        | 0        | 0.12153793814103686 |
+----------+----------+---------------------+

`list_cosine_similarity()`

Description	Calculates the cosine similarity between two lists (numeric vectors).
Usage	`list_cosine_similarity(list_1, list_2)`

See example

SELECT
    list_cosine_similarity([1, 2, 3], [1, 2, 3]) AS result_1,
    list_cosine_similarity([1, 2, 3], [2, 4, 6]) AS result_2,
    list_cosine_similarity([1, 2, 3], [5, 1, 9]) AS result_3;

+----------+----------+--------------------+
| result_1 | result_2 | result_3           |
+----------+----------+--------------------+
| 1        | 1        | 0.8784620618589631 |
+----------+----------+--------------------+

`list_distance()`

Description	Calculates the Euclidean distance between two points, the coordinates of which are specified as lists.
Usage	`list_distance(list_1, list_2)`

Calculates the Euclidean distance between two points, the coordinates of which are specified as lists — numerical vectors of equal length.

See example

SELECT
    list_distance([1, 2, 3], [1, 2, 3]) AS result_1,
    list_distance([1, 2, 3], [1, 2, 4]) AS result_2,
    list_distance([0, 0], [3, 4])       AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 0        | 1        | 5        |
+----------+----------+----------+

`list_distinct()`

Description	Removes all duplicates and empty values from a list.
Usage	`list_distinct(list)`

Removes all duplicates and empty values from a list. Does not preserve the original order of the elements.

See example

SELECT
    list_distinct([1, 2, 3, 1, 2, 3]) AS result_1,
    list_distinct([1, 2, 3, NULL])    AS result_2,
    list_distinct([NULL])             AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| {3,2,1}  | {3,2,1}  | {}       |
+----------+----------+----------+

`list_extract()`

Description	Returns an element from a list by index.
Usage	`list_extract(list, index)`
Aliases	`list_element()`

Element numbering starts at 1.

See example

SELECT
    list_extract(['A', 'B', 'C'], 1)   AS result_1,
    list_extract(['A',  NULL, 'C'], 2) AS result_2,
    list_extract(['A', 'B', 'C'], 5)   AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| A        | null     | null     |
+----------+----------+----------+

`list_grade_up()`

Description	Returns a sorted list of the original indices of the list elements.
Usage	`list_grade_up(list)`
Aliases	`array_grade_up()`, `grade_up()`

Element numbering starts at 1.

See example

SELECT
    list_grade_up([3, 2, 1, 0])    AS result_1,
    list_grade_up(['C', 'A', 'B']) AS result_1,
    list_grade_up([1, NULL, 2])    AS result_3;

+-----------+----------+----------+
| result_1  | result_1 | result_3 |
+-----------+----------+----------+
| {4,3,2,1} | {2,3,1}  | {1,3,2}  |
+-----------+----------+----------+

`list_has_all()`

Description	Checks that all elements of the second list are contained in the first list.
Usage	`list_has_all(list_1, list_2)`
Aliases	`array_has_all()`

See example

SELECT
    list_has_all([1, 2, 3], [1, 2])       AS result_1,
    list_has_all([1, 2, 3], [1, 2, NULL]) AS result_2,
    list_has_all([1, 2, 3], [1, 2, 3, 4]) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | true     | false    |
+----------+----------+----------+

`list_has_any()`

Description	Checks whether the two lists have at least one element in common.
Usage	`list_has_any(list_1, list_2)`
Aliases	`array_has_any()`

See example

SELECT
    list_has_any([1, 2, 3], [1, 4])       AS result_1,
    list_has_any([1, 2, 3], [1, 2, NULL]) AS result_2,
    list_has_any([1, 2, 3], [4])          AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | true     | false    |
+----------+----------+----------+

`list_inner_product()`

Description	Calculates the scalar product of two numerical vectors of equal length.
Usage	`list_inner_product(list_1, list_2)`
Aliases	`list_dot_product()`

See example

SELECT
    list_inner_product([1, 2], [1, 2])       AS result_1,
    list_inner_product([1, -2], [-1, 2])     AS result_2,
    list_inner_product([1, 2, 3], [0, 0, 0]) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 5        | -5       | 0        |
+----------+----------+----------+

`list_negative_inner_product()`

Description	Calculates the scalar product of two numerical vectors of equal length, multiplied by `-1`.
Usage	`list_negative_inner_product(list_1, list_2)`
Aliases	`list_negative_dot_product()`

See example

SELECT
    list_negative_inner_product([1, 2], [1, 2])       AS result_1,
    list_negative_inner_product([1, -2], [-1, 2])     AS result_2,
    list_negative_inner_product([1, 2, 3], [0, 0, 0]) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| -5       | 5        | 0        |
+----------+----------+----------+

`list_position()`

Description	Returns the index of an element in a list.
Usage	`list_position(list, element)`
Aliases	`array_indexof()`, `array_position()`, `list_indexof()`

Returns the index of an element in a list. If the element is not found, returns NULL. If there is more than one occurrence, returns the index of the first occurrence.

See example

SELECT
    list_position(['A', 'B', 'C'], 'B')      AS result_1,
    list_position(['A', 'B', 'C'], 'X')      AS result_2,
    list_position(['A', 'B', 'A', 'C'], 'A') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2        | null     | 1        |
+----------+----------+----------+

`list_resize()`

Description	Resizes the list to the specified size.
Usage	`list_resize(list, size[, value])`
Aliases	`array_resize()`

Resizes the list to the specified size. If the specified size is greater than the current size, appends values from value to the list, or NULL values if value is not specified. If the specified size is less than the current size, removes elements from the right end.

See example

SELECT
    list_resize(['A', 'B', 'C'], 2)      AS result_1,
    list_resize(['A', 'B', 'C'], 4)      AS result_2,
    list_resize(['A', 'B', 'C'], 5, 'X') AS result_3;

+----------+--------------+-------------+
| result_1 | result_2     | result_3    |
+----------+--------------+-------------+
| {A,B}    | {A,B,C,null} | {A,B,C,X,X} |
+----------+--------------+-------------+

`list_reverse_sort()`

Description	Sorts the list in descending order.
Usage	`list_reverse_sort(list[, null_order])`
Aliases	`array_reverse_sort()`

The null_order parameter allows you to specify the placement of NULL values after sorting:

NULLS FIRST — NULL values at the beginning
NULLS LAST — NULL values at the end (default)

See example

SELECT
    list_reverse_sort([1, 3, 2])                      AS result_1,
    list_reverse_sort([1, 3, NULL, 2])                AS result_2,
    list_reverse_sort([1, 3, NULL, 2], 'NULLS FIRST') AS result_3;

+----------+--------------+--------------+
| result_1 | result_2     | result_3     |
+----------+--------------+--------------+
| {3,2,1}  | {3,2,1,None} | {None,3,2,1} |
+----------+--------------+--------------+

`list_select()`

Description	Returns a list of elements from the source list based on their indices.
Usage	`list_select(list, index_list)`
Aliases	`array_select()`

Element numbering starts at 1.

See example

SELECT
    list_select([4, 5, 6], [1, 3])          AS result_1,
    list_select([4, 5, 6], [1, 3, 4])       AS result_2,
    list_select([4, 5, 6, NULL], [1, 3, 4]) AS result_3;

+----------+------------+------------+
| result_1 | result_2   | result_3   |
+----------+------------+------------+
| {4,6}    | {4,6,None} | {4,6,None} |
+----------+------------+------------+

`list_sort()`

Description	Sorts the list.
Usage	`list_sort(list[, sort_order, null_order])`
Aliases	`array_sort()`

The sort_order parameter allows you to specify the sort order:

ASC — ascending (default)
DESC — descending

The null_order parameter allows you to specify the placement of NULL values after sorting:

NULLS FIRST — NULL values at the beginning
NULLS LAST — NULL values at the end (default)

The second parameter can only be set if the first one is set.

See example

SELECT
    list_sort([1, 3, 2])                              AS result_1,
    list_sort([1, 3, NULL, 2], 'DESC')                AS result_2,
    list_sort([1, 3, NULL, 2], 'DESC', 'NULLS FIRST') AS result_3;

+----------+--------------+--------------+
| result_1 | result_2     | result_3     |
+----------+--------------+--------------+
| {1,2,3}  | {3,2,1,None} | {None,3,2,1} |
+----------+--------------+--------------+

`list_unique()`

Description	Calculates the number of unique elements in a list.
Usage	`list_unique(list)`
Aliases	`array_unique()`

See example

SELECT
    list_unique(['A', 'B', 'A'])       AS result_1,
    list_unique(['A', 'B', 'A', NULL]) AS result_2,
    list_unique(['A', NULL, NULL])     AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2        | 2        | 1        |
+----------+----------+----------+

`list_value()`

Description	Creates a list from the arguments passed.
Usage	`list_value(value_1, value_2, ...)`
Aliases	`list_pack()`

See example

SELECT
    list_value(1, 2, 3)       AS result_1,
    list_value(1)             AS result_2,
    list_value(1, 2, 3, NULL) AS result_3;

+----------+----------+--------------+
| result_1 | result_2 | result_3     |
+----------+----------+--------------+
| {1,2,3}  | {1}      | {1,2,3,None} |
+----------+----------+--------------+

`list_where()`

Description	Filters a list using a list of logical flags.
Usage	`list_where(list, mask_list)`
Aliases	`array_where()`

See example

SELECT
    list_where([1, 2, 3], [true, false, true])        AS result_1,
    list_where([1, 2, 3, 4], [true, false, true])     AS result_2,
    list_where([1, 2, 3], [true, false, true, false]) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| {1,3}    | {1,3}    | {1,3}    |
+----------+----------+----------+

`list_zip()`

Description	Combines lists into a new list whose length is equal to the length of the longest list.
Usage	`list_zip(list_1, ..., list_n[, truncate])`
Aliases	`array_zip()`

Combines n lists into a new list whose length is equal to the length of the longest list. Its elements are tuples containing n elements from each list. Missing elements are replaced with NULL.

If the truncate parameter is specified (set to TRUE), all lists are truncated to the length of the shortest list.

See example

SELECT
    list_zip(['A', 'B', 'C'], ['1', '2', '3'])  AS result_1,
    list_zip(['A', 'B'], ['1', '2', '3'])       AS result_2,
    list_zip(['A', 'B'], ['1', '2', '3'], true) AS result_3;

+------------------------------------+-------------------------------------+-------------------------+
| result_1                           | result_2                            | result_3                |
+------------------------------------+-------------------------------------+-------------------------+
| {['A', '1'],['B', '2'],['C', '3']} | {['A', '1'],['B', '2'],[None, '3']} | {['A', '1'],['B', '2']} |
+------------------------------------+-------------------------------------+-------------------------+

`range()`

Description	Generates a list of consecutive numbers within a specified range.
Usage	`range([start,] stop[, step])`

The end limit (stop) is not included in the result.

If the start parameter is not specified, the sequence starts at 0.

The step parameter can be used to specify the step size of the sequence.

See example

SELECT
    range(10)       AS result_1,
    range(5, 10)    AS result_2,
    range(5, 10, 2) AS result_3;

+-----------------------+-------------+----------+
| result_1              | result_2    | result_3 |
+-----------------------+-------------+----------+
| {0,1,2,3,4,5,6,7,8,9} | {5,6,7,8,9} | {5,7,9}  |
+-----------------------+-------------+----------+