Search Shortcut cmd + k | ctrl + k
Search cmd+k ctrl+k
Dark Mode
1.1 (stable)
List Functions
Name Description
`list[index]` Bracket notation serves as an alias for `list_extract`.
`list[begin:end]` Bracket notation with colon is an alias for `list_slice`.
`list[begin:end:step]` `list_slice` in bracket notation with an added `step` feature.
`array_pop_back(list)` Returns the list without the last element.
`array_pop_front(list)` Returns the list without the first element.
`flatten(list_of_lists)` Concatenate a list of lists into a single list. This only flattens one level of the list (see examples).
`len(list)` Return the length of the list.
`list_aggregate(list, name)` Executes the aggregate function `name` on the elements of `list`. See the List Aggregates section for more details.
`list_any_value(list)` Returns the first non-null value in the list.
`list_append(list, element)` Appends `element` to `list`.
`list_concat(list1, list2)` Concatenates two lists.
`list_contains(list, element)` Returns true if the list contains the element.
`list_cosine_similarity(list1, list2)` Compute the cosine similarity between two lists.
`list_distance(list1, list2)` Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.
`list_distinct(list)` Removes all duplicates and `NULL` values from a list. Does not preserve the original order.
`list_dot_product(list1, list2)` Computes the dot product of two same-sized lists of numbers.
`list_extract(list, index)` Extract the `index`th (1-based) value from the list.
`list_filter(list, lambda)` Constructs a list from those elements of the input list for which the lambda function returns true. See the Lambda Functions page for more details.
`list_grade_up(list)` Works like sort, but the results are the indexes that correspond to the position in the original `list` instead of the actual values.
`list_has_all(list, sub-list)` Returns true if all elements of sub-list exist in list.
`list_has_any(list1, list2)` Returns true if any elements exist is both lists.
`list_intersect(list1, list2)` Returns a list of all the elements that exist in both `l1` and `l2`, without duplicates.
`list_position(list, element)` Returns the index of the element if the list contains the element. If the element is not found, it returns `NULL`.
`list_prepend(element, list)` Prepends `element` to `list`.
`list_reduce(list, lambda)` Returns a single value that is the result of applying the lambda function to each element of the input list. See the Lambda Functions page for more details.
`list_resize(list, size[, value])` Resizes the list to contain `size` elements. Initializes new elements with `value` or `NULL` if `value` is not set.
`list_reverse_sort(list)` Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about the `NULL` sorting order.
`list_reverse(list)` Reverses the list.
`list_select(value_list, index_list)` Returns a list based on the elements selected by the `index_list`.
`list_slice(list, begin, end, step)` `list_slice` with added `step` feature.
`list_slice(list, begin, end)` Extract a sublist using slice conventions. Negative values are accepted. See slicing.
`list_sort(list)` Sorts the elements of the list. See the Sorting Lists section for more details about the sorting order and the `NULL` sorting order.
`list_transform(list, lambda)` Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions page for more details.
`list_unique(list)` Counts the unique elements of a list.
`list_value(any, ...)` Create a `LIST` containing the argument values.
`list_where(value_list, mask_list)` Returns a list with the `BOOLEAN`s in `mask_list` applied as a mask to the `value_list`.
`list_zip(list_1, list_2, ...[, truncate])` Zips k `LIST`s to a new `LIST` whose length will be that of the longest list. Its elements are structs of k elements from each list `list_1`, …, `list_k`, missing elements are replaced with `NULL`. If `truncate` is set, all lists are truncated to the smallest list length.
`unnest(list)` Unnests a list by one level. Note that this is a special function that alters the cardinality of the result. See the `unnest` page for more details.

#### `list[index]`

 Description Bracket notation serves as an alias for `list_extract`. Example `[4, 5, 6][3]` Result `6` Alias `list_extract`

#### `list[begin:end]`

 Description Bracket notation with colon is an alias for `list_slice`. Example `[4, 5, 6][2:3]` Result `[5, 6]` Alias `list_slice`

#### `list[begin:end:step]`

 Description `list_slice` in bracket notation with an added `step` feature. Example `[4, 5, 6][:-:2]` Result `[4, 6]` Alias `list_slice`

#### `array_pop_back(list)`

 Description Returns the list without the last element. Example `array_pop_back([4, 5, 6])` Result `[4, 5]`

#### `array_pop_front(list)`

 Description Returns the list without the first element. Example `array_pop_front([4, 5, 6])` Result `[5, 6]`

#### `flatten(list_of_lists)`

 Description Concatenate a list of lists into a single list. This only flattens one level of the list (see examples). Example `flatten([[1, 2], [3, 4]])` Result `[1, 2, 3, 4]`

#### `len(list)`

 Description Return the length of the list. Example `len([1, 2, 3])` Result `3` Alias `array_length`

#### `list_aggregate(list, name)`

 Description Executes the aggregate function `name` on the elements of `list`. See the List Aggregates section for more details. Example `list_aggregate([1, 2, NULL], 'min')` Result `1` Aliases `list_aggr`, `aggregate`, `array_aggregate`, `array_aggr`

#### `list_any_value(list)`

 Description Returns the first non-null value in the list. Example `list_any_value([NULL, -3])` Result `-3`

#### `list_append(list, element)`

 Description Appends `element` to `list`. Example `list_append([2, 3], 4)` Result `[2, 3, 4]` Aliases `array_append`, `array_push_back`

#### `list_concat(list1, list2)`

 Description Concatenates two lists. Example `list_concat([2, 3], [4, 5, 6])` Result `[2, 3, 4, 5, 6]` Aliases `list_cat`, `array_concat`, `array_cat`

#### `list_contains(list, element)`

 Description Returns true if the list contains the element. Example `list_contains([1, 2, NULL], 1)` Result `true` Aliases `list_has`, `array_contains`, `array_has`

#### `list_cosine_similarity(list1, list2)`

 Description Compute the cosine similarity between two lists. Example `list_cosine_similarity([1, 2, 3], [1, 2, 5])` Result `0.9759000729485332`

#### `list_distance(list1, list2)`

 Description Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length. Example `list_distance([1, 2, 3], [1, 2, 5])` Result `2.0`

#### `list_distinct(list)`

 Description Removes all duplicates and `NULL` values from a list. Does not preserve the original order. Example `list_distinct([1, 1, NULL, -3, 1, 5])` Result `[1, 5, -3]` Alias `array_distinct`

#### `list_dot_product(list1, list2)`

 Description Computes the dot product of two same-sized lists of numbers. Example `list_dot_product([1, 2, 3], [1, 2, 5])` Result `20.0` Alias `list_inner_product`

#### `list_extract(list, index)`

 Description Extract the `index`th (1-based) value from the list. Example `list_extract([4, 5, 6], 3)` Result `6` Aliases `list_element`, `array_extract`

#### `list_filter(list, lambda)`

 Description Constructs a list from those elements of the input list for which the lambda function returns true. See the Lambda Functions page for more details. Example `list_filter([4, 5, 6], x -> x > 4)` Result `[5, 6]` Aliases `array_filter`, `filter`

#### `list_grade_up(list)`

 Description Works like sort, but the results are the indexes that correspond to the position in the original `list` instead of the actual values. Example `list_grade_up([30, 10, 40, 20])` Result `[2, 4, 1, 3]` Alias `array_grade_up`

#### `list_has_all(list, sub-list)`

 Description Returns true if all elements of sub-list exist in list. Example `list_has_all([4, 5, 6], [4, 6])` Result `true` Alias `array_has_all`

#### `list_has_any(list1, list2)`

 Description Returns true if any elements exist is both lists. Example `list_has_any([1, 2, 3], [2, 3, 4])` Result `true` Alias `array_has_any`

#### `list_intersect(list1, list2)`

 Description Returns a list of all the elements that exist in both `l1` and `l2`, without duplicates. Example `list_intersect([1, 2, 3], [2, 3, 4])` Result `[2, 3]` Alias `array_intersect`

#### `list_position(list, element)`

 Description Returns the index of the element if the list contains the element. If the element is not found, it returns `NULL`. Example `list_position([1, 2, NULL], 2)` Result `2` Aliases `list_indexof`, `array_position`, `array_indexof`

#### `list_prepend(element, list)`

 Description Prepends `element` to `list`. Example `list_prepend(3, [4, 5, 6])` Result `[3, 4, 5, 6]` Aliases `array_prepend`, `array_push_front`

#### `list_reduce(list, lambda)`

 Description Returns a single value that is the result of applying the lambda function to each element of the input list. See the Lambda Functions page for more details. Example `list_reduce([4, 5, 6], (x, y) -> x + y)` Result `15` Aliases `array_reduce`, `reduce`

#### `list_resize(list, size[, value])`

 Description Resizes the list to contain `size` elements. Initializes new elements with `value` or `NULL` if `value` is not set. Example `list_resize([1, 2, 3], 5, 0)` Result `[1, 2, 3, 0, 0]` Alias `array_resize`

#### `list_reverse_sort(list)`

 Description Sorts the elements of the list in reverse order. See the Sorting Lists section for more details about the `NULL` sorting order. Example `list_reverse_sort([3, 6, 1, 2])` Result `[6, 3, 2, 1]` Alias `array_reverse_sort`

#### `list_reverse(list)`

 Description Reverses the list. Example `list_reverse([3, 6, 1, 2])` Result `[2, 1, 6, 3]` Alias `array_reverse`

#### `list_select(value_list, index_list)`

 Description Returns a list based on the elements selected by the `index_list`. Example `list_select([10, 20, 30, 40], [1, 4])` Result `[10, 40]` Alias `array_select`

#### `list_slice(list, begin, end, step)`

 Description `list_slice` with added `step` feature. Example `list_slice([4, 5, 6], 1, 3, 2)` Result `[4, 6]` Alias `array_slice`

#### `list_slice(list, begin, end)`

 Description Extract a sublist using slice conventions. Negative values are accepted. See slicing. Example `list_slice([4, 5, 6], 2, 3)` Result `[5, 6]` Alias `array_slice`

#### `list_sort(list)`

 Description Sorts the elements of the list. See the Sorting Lists section for more details about the sorting order and the `NULL` sorting order. Example `list_sort([3, 6, 1, 2])` Result `[1, 2, 3, 6]` Alias `array_sort`

#### `list_transform(list, lambda)`

 Description Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions page for more details. Example `list_transform([4, 5, 6], x -> x + 1)` Result `[5, 6, 7]` Aliases `array_transform`, `apply`, `list_apply`, `array_apply`

#### `list_unique(list)`

 Description Counts the unique elements of a list. Example `list_unique([1, 1, NULL, -3, 1, 5])` Result `3` Alias `array_unique`

#### `list_value(any, ...)`

 Description Create a `LIST` containing the argument values. Example `list_value(4, 5, 6)` Result `[4, 5, 6]` Alias `list_pack`

#### `list_where(value_list, mask_list)`

 Description Returns a list with the `BOOLEAN`s in `mask_list` applied as a mask to the `value_list`. Example `list_where([10, 20, 30, 40], [true, false, false, true])` Result `[10, 40]` Alias `array_where`

#### `list_zip(list1, list2, ...)`

 Description Zips k `LIST`s to a new `LIST` whose length will be that of the longest list. Its elements are structs of k elements from each list `list_1`, …, `list_k`, missing elements are replaced with `NULL`. If `truncate` is set, all lists are truncated to the smallest list length. Example `list_zip([1, 2], [3, 4], [5, 6])` Result `[(1, 3, 5), (2, 4, 6)]` Alias `array_zip`

#### `unnest(list)`

 Description Unnests a list by one level. Note that this is a special function that alters the cardinality of the result. See the `unnest` page for more details. Example `unnest([1, 2, 3])` Result `1`, `2`, `3`

## List Operators

The following operators are supported for lists:

Operator Description Example Result
`&&` Alias for `list_has_any`. `[1, 2, 3, 4, 5] && [2, 5, 5, 6]` `true`
`@>` Alias for `list_has_all`, where the list on the right of the operator is the sublist. `[1, 2, 3, 4] @> [3, 4, 3]` `true`
`<@` Alias for `list_has_all`, where the list on the left of the operator is the sublist. `[1, 4] <@ [1, 2, 3, 4]` `true`
`||` Alias for `list_concat`. `[1, 2, 3] || [4, 5, 6]` `[1, 2, 3, 4, 5, 6]`
`<=>` Alias for `list_cosine_similarity`. `[1, 2, 3] <=> [1, 2, 5]` `0.9759000729485332`
`<->` Alias for `list_distance`. `[1, 2, 3] <-> [1, 2, 5]` `2.0`

## List Comprehension

Python-style list comprehension can be used to compute expressions over elements in a list. For example:

``````SELECT [lower(x) FOR x IN strings] AS strings
FROM (VALUES (['Hello', '', 'World'])) t(strings);
``````
strings
[hello, , world]
``````SELECT [upper(x) FOR x IN strings IF len(x) > 0] AS strings
FROM (VALUES (['Hello', '', 'World'])) t(strings);
``````
strings
[HELLO, WORLD]

List comprehensions can also use the position of the list elements by adding a second variable. In the following example, we use `x, i`, where `x` is the value and `i` is the position:

``````SELECT [4, 5, 6] as l, [x FOR x, i IN l IF i != 2] filtered;
``````
l filtered
[4, 5, 6] [4, 6]

## Range Functions

DuckDB offers two range functions, `range(start, stop, step)` and `generate_series(start, stop, step)`, and their variants with default arguments for `stop` and `step`. The two functions' behavior is different regarding their `stop` argument. This is documented below.

### `range`

The `range` function creates a list of values in the range between `start` and `stop`. The `start` parameter is inclusive, while the `stop` parameter is exclusive. The default value of `start` is 0 and the default value of `step` is 1.

Based on the number of arguments, the following variants of `range` exist.

#### `range(stop)`

``````SELECT range(5);
``````
``````[0, 1, 2, 3, 4]
``````

#### `range(start, stop)`

``````SELECT range(2, 5);
``````
``````[2, 3, 4]
``````

#### `range(start, stop, step)`

``````SELECT range(2, 5, 3);
``````
``````[2]
``````

### `generate_series`

The `generate_series` function creates a list of values in the range between `start` and `stop`. Both the `start` and the `stop` parameters are inclusive. The default value of `start` is 0 and the default value of `step` is 1. Based on the number of arguments, the following variants of `generate_series` exist.

#### `generate_series(stop)`

``````SELECT generate_series(5);
``````
``````[0, 1, 2, 3, 4, 5]
``````

#### `generate_series(start, stop)`

``````SELECT generate_series(2, 5);
``````
``````[2, 3, 4, 5]
``````

#### `generate_series(start, stop, step)`

``````SELECT generate_series(2, 5, 3);
``````
``````[2, 5]
``````

#### `generate_subscripts(arr, dim)`

The `generate_subscripts(arr, dim)` function generates indexes along the `dim`th dimension of array `arr`.

``````SELECT generate_subscripts([4, 5, 6], 1) AS i;
``````
i
1
2
3

### Date Ranges

Date ranges are also supported for `TIMESTAMP` and `TIMESTAMP WITH TIME ZONE` values. Note that for these types, the `stop` and `step` arguments have to be specified explicitly (a default value is not provided).

#### `range` for Date Ranges

``````SELECT *
FROM range(DATE '1992-01-01', DATE '1992-03-01', INTERVAL '1' MONTH);
``````
range
1992-01-01 00:00:00
1992-02-01 00:00:00

#### `generate_series` for Date Ranges

``````SELECT *
FROM generate_series(DATE '1992-01-01', DATE '1992-03-01', INTERVAL '1' MONTH);
``````
generate_series
1992-01-01 00:00:00
1992-02-01 00:00:00
1992-03-01 00:00:00

## Slicing

The function `list_slice` can be used to extract a sublist from a list. The following variants exist:

• `list_slice(list, begin, end)`
• `list_slice(list, begin, end, step)`
• `array_slice(list, begin, end)`
• `array_slice(list, begin, end, step)`
• `list[begin:end]`
• `list[begin:end:step]`

The arguments are as follows:

• `list`
• Is the list to be sliced
• `begin`
• Is the index of the first element to be included in the slice
• When `begin < 0` the index is counted from the end of the list
• When `begin < 0` and `-begin > length`, `begin` is clamped to the beginning of the list
• When `begin > length`, the result is an empty list
• Bracket Notation: When `begin` is omitted, it defaults to the beginning of the list
• `end`
• Is the index of the last element to be included in the slice
• When `end < 0` the index is counted from the end of the list
• When `end > length`, end is clamped to `length`
• When `end < begin`, the result is an empty list
• Bracket Notation: When `end` is omitted, it defaults to the end of the list. When `end` is omitted and a `step` is provided, `end` must be replaced with a `-`
• `step` (optional)
• Is the step size between elements in the slice
• When `step < 0` the slice is reversed, and `begin` and `end` are swapped
• Must be non-zero

Examples:

``````SELECT list_slice([1, 2, 3, 4, 5], 2, 4);
``````
``````[2, 3, 4]
``````
``````SELECT ([1, 2, 3, 4, 5])[2:4:2];
``````
``````[2, 4]
``````
``````SELECT([1, 2, 3, 4, 5])[4:2:-2];
``````
``````[4, 2]
``````
``````SELECT ([1, 2, 3, 4, 5])[:];
``````
``````[1, 2, 3, 4, 5]
``````
``````SELECT ([1, 2, 3, 4, 5])[:-:2];
``````
``````[1, 3, 5]
``````
``````SELECT ([1, 2, 3, 4, 5])[:-:-2];
``````
``````[5, 3, 1]
``````

## List Aggregates

The function `list_aggregate` allows the execution of arbitrary existing aggregate functions on the elements of a list. Its first argument is the list (column), its second argument is the aggregate function name, e.g., `min`, `histogram` or `sum`.

`list_aggregate` accepts additional arguments after the aggregate function name. These extra arguments are passed directly to the aggregate function, which serves as the second argument of `list_aggregate`.

``````SELECT list_aggregate([1, 2, -4, NULL], 'min');
``````
``````-4
``````
``````SELECT list_aggregate([2, 4, 8, 42], 'sum');
``````
``````56
``````
``````SELECT list_aggregate([[1, 2], [NULL], [2, 10, 3]], 'last');
``````
``````[2, 10, 3]
``````
``````SELECT list_aggregate([2, 4, 8, 42], 'string_agg', '|');
``````
``````2|4|8|42
``````

### `list_*` Rewrite Functions

The following is a list of existing rewrites. Rewrites simplify the use of the list aggregate function by only taking the list (column) as their argument. `list_avg`, `list_var_samp`, `list_var_pop`, `list_stddev_pop`, `list_stddev_samp`, `list_sem`, `list_approx_count_distinct`, `list_bit_xor`, `list_bit_or`, `list_bit_and`, `list_bool_and`, `list_bool_or`, `list_count`, `list_entropy`, `list_last`, `list_first`, `list_kurtosis`, `list_kurtosis_pop`, `list_min`, `list_max`, `list_product`, `list_skewness`, `list_sum`, `list_string_agg`, `list_mode`, `list_median`, `list_mad` and `list_histogram`.

``````SELECT list_min([1, 2, -4, NULL]);
``````
``````-4
``````
``````SELECT list_sum([2, 4, 8, 42]);
``````
``````56
``````
``````SELECT list_last([[1, 2], [NULL], [2, 10, 3]]);
``````
``````[2, 10, 3]
``````

#### `array_to_string`

Concatenates list/array elements using an optional delimiter.

``````SELECT array_to_string([1, 2, 3], '-') AS str;
``````
``````1-2-3
``````

This is equivalent to the following SQL:

``````SELECT list_aggr([1, 2, 3], 'string_agg', '-') AS str;
``````
``````1-2-3
``````

## Sorting Lists

The function `list_sort` sorts the elements of a list either in ascending or descending order. In addition, it allows to provide whether `NULL` values should be moved to the beginning or to the end of the list. It has the same sorting behavior as DuckDB's `ORDER BY` clause. Therefore, (nested) values compare the same in `list_sort` as in `ORDER BY`.

By default, if no modifiers are provided, DuckDB sorts `ASC NULLS FIRST`. I.e., the values are sorted in ascending order and `NULL` values are placed first. This is identical to the default sort order of SQLite. The default sort order can be changed using `PRAGMA` statements..

`list_sort` leaves it open to the user whether they want to use the default sort order or a custom order. `list_sort` takes up to two additional optional parameters. The second parameter provides the sort order and can be either `ASC` or `DESC`. The third parameter provides the `NULL` order and can be either `NULLS FIRST` or `NULLS LAST`.

This query uses the default sort order and the default `NULL` order.

``````SELECT list_sort([1, 3, NULL, 5, NULL, -5]);
``````
``````[NULL, NULL, -5, 1, 3, 5]
``````

This query provides the sort order. The `NULL` order uses the configurable default value.

``````SELECT list_sort([1, 3, NULL, 2], 'ASC');
``````
``````[NULL, 1, 2, 3]
``````

This query provides both the sort order and the `NULL` order.

``````SELECT list_sort([1, 3, NULL, 2], 'DESC', 'NULLS FIRST');
``````
``````[NULL, 3, 2, 1]
``````

`list_reverse_sort` has an optional second parameter providing the `NULL` sort order. It can be either `NULLS FIRST` or `NULLS LAST`.

This query uses the default `NULL` sort order.

``````SELECT list_sort([1, 3, NULL, 5, NULL, -5]);
``````
``````[NULL, NULL, -5, 1, 3, 5]
``````

This query provides the `NULL` sort order.

``````SELECT list_reverse_sort([1, 3, NULL, 2], 'NULLS LAST');
``````
``````[3, 2, 1, NULL]
``````

## Flattening

The flatten function is a scalar function that converts a list of lists into a single list by concatenating each sub-list together. Note that this only flattens one level at a time, not all levels of sub-lists.

Convert a list of lists into a single list:

``````SELECT
flatten([
[1, 2],
[3, 4]
]);
``````
``````[1, 2, 3, 4]
``````

If the list has multiple levels of lists, only the first level of sub-lists is concatenated into a single list:

``````SELECT
flatten([
[
[1, 2],
[3, 4],
],
[
[5, 6],
[7, 8],
]
]);
``````
``````[[1, 2], [3, 4], [5, 6], [7, 8]]
``````

In general, the input to the flatten function should be a list of lists (not a single level list). However, the behavior of the flatten function has specific behavior when handling empty lists and `NULL` values.

If the input list is empty, return an empty list:

``````SELECT flatten([]);
``````
``````[]
``````

If the entire input to flatten is `NULL`, return `NULL`:

``````SELECT flatten(NULL);
``````
``````NULL
``````

If a list whose only entry is `NULL` is flattened, return an empty list:

``````SELECT flatten([NULL]);
``````
``````[]
``````

If the sub-list in a list of lists only contains `NULL`, do not modify the sub-list:

``````-- (Note the extra set of parentheses vs. the prior example)
SELECT flatten([[NULL]]);
``````
``````[NULL]
``````

Even if the only contents of each sub-list is `NULL`, still concatenate them together. Note that no de-duplication occurs when flattening. See `list_distinct` function for de-duplication:

``````SELECT flatten([[NULL],[NULL]]);
``````
``````[NULL, NULL]
``````

## Lambda Functions

DuckDB supports lambda functions in the form `(parameter1, parameter2, ...) -> expression`. For details, see the lambda functions page.

There are also aggregate functions `list` and `histogram` that produces lists and lists of structs. The `unnest` function is used to unnest a list by one level.