list[index]

list[begin[:end][:step]]

arg1 || arg2

array_extract(list, index)

array_pop_back(list)

array_pop_front(list)

array_push_front(list, element)

array_to_string(list, delimiter)

array_to_string_comma_default(array)

concat(value, ...)

contains(list, element)

flatten(nested_list)

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

length(list)

list_aggregate(list, function_name, ...)

list_any_value(list)

list_append(list, element)

list_approx_count_distinct(list)

list_avg(list)

list_bit_and(list)

list_bit_or(list)

list_bit_xor(list)

list_bool_and(list)

list_bool_or(list)

list_concat(list_1, ..., list_n)

list_contains(list, element)

list_cosine_distance(list1, list2)

list_cosine_similarity(list1, list2)

list_count(list)

list_distance(list1, list2)

list_distinct(list)

list_entropy(list)

list_extract(list, index)

list_filter(list, lambda(x))

list_first(list)

list_grade_up(list[, col1][, col2])

list_has_all(list1, list2)

list_has_any(list1, list2)

list_histogram(list)

list_inner_product(list1, list2)

list_intersect(list1, list2)

list_kurtosis(list)

list_kurtosis_pop(list)

list_last(list)

list_mad(list)

list_max(list)

list_median(list)

list_min(list)

list_mode(list)

list_negative_inner_product(list1, list2)

list_position(list, element)

list_prepend(element, list)

list_product(list)

list_reduce(list, lambda(x,y)[, initial_value])

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

list_reverse(list)

list_reverse_sort(list[, col1])

list_select(value_list, index_list)

list_sem(list)

list_skewness(list)

list_slice(list, begin, end)

list_slice(list, begin, end, step)

list_sort(list[, col1][, col2])

list_stddev_pop(list)

list_stddev_samp(list)

list_string_agg(list)

list_sum(list)

list_transform(list, lambda(x))

list_unique(list)

list_value(arg, ...)

list_var_pop(list)

list_var_samp(list)

list_where(value_list, mask_list)

list_zip(list_1, ..., list_n[, truncate])

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

repeat(list, count)

unnest(list)

unpivot_list(arg, ...)

List Operators

The following operators are supported for lists:

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

SELECT [upper(x) FOR x IN strings IF len(x) > 0] AS strings
FROM (VALUES (['Hello', '', 'World'])) t(strings);

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] AS filtered;

Under the hood, [f(x) FOR x IN l IF g(x)] is translated to:

l.list_apply(lambda x, i: {'filter': g(x, i), 'result': f(x, i)})
    .list_filter(lambda x: x.filter)
    .list_apply(lambda x: x.result)

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 dimth dimension of array arr.

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

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

generate_series for Date Ranges

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

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.

Order-sensitive aggregate functions are applied in the order of the list. The ORDER BY, DISTINCT and FILTER clauses are not supported by list_aggregate. They may instead be emulated using list_sort, list_grade_up, list_select, list_distinct and list_filter.

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 specifying 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 LAST. I.e., the values are sorted in ascending order and NULL values are placed last. 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]);

[-5, 1, 3, 5, NULL, NULL]

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

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

[1, 2, 3, NULL]

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

[-5, 1, 3, 5, NULL, NULL]

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 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 lambda parameter1, parameter2, ...: expression. For details, see the lambda functions page.

Related Functions

• The aggregate functions list and histogram produce lists and lists of structs.
• The unnest function is used to unnest a list by one level.