--- layout: docu redirect_from: - /docs/sql/functions/list title: List Functions --- | Function | Description | |:--|:-------| | [`list[index]`](#listindex) | Extracts a single list element using a (1-based) `index`. | | [`list[begin[:end][:step]]`](#listbeginendstep) | Extracts a sublist using [slice conventions]({% link docs/stable/sql/functions/list.md %}#slicing). Negative values are accepted. | | [`list1 && list2`](#list_has_anylist1-list2) | Alias for `list_has_any`. | | [`list1 <-> list2`](#list_distancelist1-list2) | Alias for `list_distance`. | | [`list1 <=> list2`](#list_cosine_distancelist1-list2) | Alias for `list_cosine_distance`. | | [`list1 <@ list2`](#list_has_alllist1-list2) | Alias for `list_has_all`. | | [`list1 @> list2`](#list_has_alllist1-list2) | Alias for `list_has_all`. | | [`arg1 || arg2`](#arg1--arg2) | Concatenates two strings, lists, or blobs. Any `NULL` input results in `NULL`. See also [`concat(arg1, arg2, ...)`]({% link docs/stable/sql/functions/text.md %}#concatvalue-) and [`list_concat(list1, list2, ...)`]({% link docs/stable/sql/functions/list.md %}#list_concatlist_1--list_n). | | [`aggregate(list, function_name, ...)`](#list_aggregatelist-function_name-) | Alias for `list_aggregate`. | | [`apply(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. | | [`array_aggr(list, function_name, ...)`](#list_aggregatelist-function_name-) | Alias for `list_aggregate`. | | [`array_aggregate(list, function_name, ...)`](#list_aggregatelist-function_name-) | Alias for `list_aggregate`. | | [`array_append(list, element)`](#list_appendlist-element) | Alias for `list_append`. | | [`array_apply(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. | | [`array_cat(list_1, ..., list_n)`](#list_concatlist_1--list_n) | Alias for `list_concat`. | | [`array_concat(list_1, ..., list_n)`](#list_concatlist_1--list_n) | Alias for `list_concat`. | | [`array_contains(list, element)`](#list_containslist-element) | Alias for `list_contains`. | | [`array_distinct(list)`](#list_distinctlist) | Alias for `list_distinct`. | | [`array_extract(list, index)`](#array_extractlist-index) | Extracts the `index`th (1-based) value from the `list`. | | [`array_filter(list, lambda(x))`](#list_filterlist-lambdax) | Alias for `list_filter`. | | [`array_grade_up(list[, col1][, col2])`](#list_grade_uplist-col1-col2) | Alias for `list_grade_up`. | | [`array_has(list, element)`](#list_containslist-element) | Alias for `list_contains`. | | [`array_has_all(list1, list2)`](#list_has_alllist1-list2) | Alias for `list_has_all`. | | [`array_has_any(list1, list2)`](#list_has_anylist1-list2) | Alias for `list_has_any`. | | [`array_indexof(list, element)`](#list_positionlist-element) | Alias for `list_position`. | | [`array_intersect(list1, list2)`](#list_intersectlist1-list2) | Alias for `list_intersect`. | | [`array_length(list)`](#lengthlist) | Alias for `length`. | | [`array_pop_back(list)`](#array_pop_backlist) | Returns the `list` without the last element. | | [`array_pop_front(list)`](#array_pop_frontlist) | Returns the `list` without the first element. | | [`array_position(list, element)`](#list_positionlist-element) | Alias for `list_position`. | | [`array_prepend(element, list)`](#list_prependelement-list) | Alias for `list_prepend`. | | [`array_push_back(list, element)`](#list_appendlist-element) | Alias for `list_append`. | | [`array_push_front(list, element)`](#array_push_frontlist-element) | Prepends `element` to `list`. | | [`array_reduce(list, lambda(x,y)[, initial_value])`](#list_reducelist-lambdaxy-initial_value) | Alias for `list_reduce`. | | [`array_resize(list, size[[, value]])`](#list_resizelist-size-value) | Alias for `list_resize`. | | [`array_reverse(list)`](#list_reverselist) | Alias for `list_reverse`. | | [`array_reverse_sort(list[, col1])`](#list_reverse_sortlist-col1) | Alias for `list_reverse_sort`. | | [`array_select(value_list, index_list)`](#list_selectvalue_list-index_list) | Alias for `list_select`. | | [`array_slice(list, begin, end)`](#list_slicelist-begin-end) | Alias for `list_slice`. | | [`array_slice(list, begin, end, step)`](#list_slicelist-begin-end-step) | Alias for `list_slice`. | | [`array_sort(list[, col1][, col2])`](#list_sortlist-col1-col2) | Alias for `list_sort`. | | [`array_to_string(list, delimiter)`](#array_to_stringlist-delimiter) | Concatenates list/array elements using an optional `delimiter`. | | [`array_to_string_comma_default(array)`](#array_to_string_comma_defaultarray) | Concatenates list/array elements with a comma delimiter. | | [`array_transform(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. | | [`array_unique(list)`](#list_uniquelist) | Alias for `list_unique`. | | [`array_where(value_list, mask_list)`](#list_wherevalue_list-mask_list) | Alias for `list_where`. | | [`array_zip(list_1, ..., list_n[, truncate])`](#list_ziplist_1--list_n-truncate) | Alias for `list_zip`. | | [`char_length(list)`](#lengthlist) | Alias for `length`. | | [`character_length(list)`](#lengthlist) | Alias for `length`. | | [`concat(value, ...)`](#concatvalue-) | Concatenates multiple strings or lists. `NULL` inputs are skipped. See also [operator `||`](#arg1--arg2). | | [`contains(list, element)`](#containslist-element) | Returns `true` if the `list` contains the `element`. | | [`filter(list, lambda(x))`](#list_filterlist-lambdax) | Alias for `list_filter`. | | [`flatten(nested_list)`](#flattennested_list) | [Flattens](#flattening) a nested list by one level. | | [`generate_series(start[, stop][, step])`](#generate_seriesstart-stop-step) | Creates a list of values between `start` and `stop` - the stop parameter is inclusive. | | [`grade_up(list[, col1][, col2])`](#list_grade_uplist-col1-col2) | Alias for `list_grade_up`. | | [`len(list)`](#lengthlist) | Alias for `length`. | | [`length(list)`](#lengthlist) | Returns the length of the `list`. | | [`list_aggr(list, function_name, ...)`](#list_aggregatelist-function_name-) | Alias for `list_aggregate`. | | [`list_aggregate(list, function_name, ...)`](#list_aggregatelist-function_name-) | Executes the aggregate function `function_name` on the elements of `list`. See the [List Aggregates](#list-aggregates) section for more details. | | [`list_any_value(list)`](#list_any_valuelist) | Applies aggregate function [`any_value`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_append(list, element)`](#list_appendlist-element) | Appends `element` to `list`. | | [`list_apply(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. | | [`list_approx_count_distinct(list)`](#list_approx_count_distinctlist) | Applies aggregate function [`approx_count_distinct`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_avg(list)`](#list_avglist) | Applies aggregate function [`avg`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_bit_and(list)`](#list_bit_andlist) | Applies aggregate function [`bit_and`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_bit_or(list)`](#list_bit_orlist) | Applies aggregate function [`bit_or`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_bit_xor(list)`](#list_bit_xorlist) | Applies aggregate function [`bit_xor`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_bool_and(list)`](#list_bool_andlist) | Applies aggregate function [`bool_and`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_bool_or(list)`](#list_bool_orlist) | Applies aggregate function [`bool_or`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_cat(list_1, ..., list_n)`](#list_concatlist_1--list_n) | Alias for `list_concat`. | | [`list_concat(list_1, ..., list_n)`](#list_concatlist_1--list_n) | Concatenates lists. `NULL` inputs are skipped. See also [operator `||`](#arg1--arg2). | | [`list_contains(list, element)`](#list_containslist-element) | Returns true if the list contains the element. | | [`list_cosine_distance(list1, list2)`](#list_cosine_distancelist1-list2) | Computes the cosine distance between two same-sized lists. | | [`list_cosine_similarity(list1, list2)`](#list_cosine_similaritylist1-list2) | Computes the cosine similarity between two same-sized lists. | | [`list_count(list)`](#list_countlist) | Applies aggregate function [`count`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_distance(list1, list2)`](#list_distancelist1-list2) | Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length. | | [`list_distinct(list)`](#list_distinctlist) | Removes all duplicates and `NULL` values from a list. Does not preserve the original order. | | [`list_dot_product(list1, list2)`](#list_inner_productlist1-list2) | Alias for `list_inner_product`. | | [`list_element(list, index)`](#list_extractlist-index) | Alias for `list_extract`. | | [`list_entropy(list)`](#list_entropylist) | Applies aggregate function [`entropy`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_extract(list, index)`](#list_extractlist-index) | Extract the `index`th (1-based) value from the list. | | [`list_filter(list, lambda(x))`](#list_filterlist-lambdax) | Constructs a list from those elements of the input `list` for which the `lambda` function returns `true`. DuckDB must be able to cast the `lambda` function's return type to `BOOL`. The return type of `list_filter` is the same as the input list's. See [`list_filter` examples]({% link docs/stable/sql/functions/lambda.md %}#list_filter-examples). | | [`list_first(list)`](#list_firstlist) | Applies aggregate function [`first`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_grade_up(list[, col1][, col2])`](#list_grade_uplist-col1-col2) | Works like [`list_sort`](#list_sortlist-col1-col2), but the results are the indexes that correspond to the position in the original list instead of the actual values. | | [`list_has(list, element)`](#list_containslist-element) | Alias for `list_contains`. | | [`list_has_all(list1, list2)`](#list_has_alllist1-list2) | Returns true if all elements of list2 are in list1. NULLs are ignored. | | [`list_has_any(list1, list2)`](#list_has_anylist1-list2) | Returns true if the lists have any element in common. NULLs are ignored. | | [`list_histogram(list)`](#list_histogramlist) | Applies aggregate function [`histogram`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_indexof(list, element)`](#list_positionlist-element) | Alias for `list_position`. | | [`list_inner_product(list1, list2)`](#list_inner_productlist1-list2) | Computes the inner product between two same-sized lists. | | [`list_intersect(list1, list2)`](#list_intersectlist1-list2) | Returns a list of all the elements that exist in both `list1` and `list2`, without duplicates. | | [`list_kurtosis(list)`](#list_kurtosislist) | Applies aggregate function [`kurtosis`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_kurtosis_pop(list)`](#list_kurtosis_poplist) | Applies aggregate function [`kurtosis_pop`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_last(list)`](#list_lastlist) | Applies aggregate function [`last`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_mad(list)`](#list_madlist) | Applies aggregate function [`mad`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_max(list)`](#list_maxlist) | Applies aggregate function [`max`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_median(list)`](#list_medianlist) | Applies aggregate function [`median`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_min(list)`](#list_minlist) | Applies aggregate function [`min`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_mode(list)`](#list_modelist) | Applies aggregate function [`mode`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_negative_dot_product(list1, list2)`](#list_negative_inner_productlist1-list2) | Alias for `list_negative_inner_product`. | | [`list_negative_inner_product(list1, list2)`](#list_negative_inner_productlist1-list2) | Computes the negative inner product between two same-sized lists. | | [`list_pack(arg, ...)`](#list_valuearg-) | Alias for `list_value`. | | [`list_position(list, element)`](#list_positionlist-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)`](#list_prependelement-list) | Prepends `element` to `list`. | | [`list_product(list)`](#list_productlist) | Applies aggregate function [`product`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_reduce(list, lambda(x,y)[, initial_value])`](#list_reducelist-lambdaxy-initial_value) | Reduces all elements of the input `list` into a single scalar value by executing the `lambda` function on a running result and the next list element. The `lambda` function has an optional `initial_value` argument. See [`list_reduce` examples]({% link docs/stable/sql/functions/lambda.md %}#list_reduce-examples). | | [`list_resize(list, size[[, value]])`](#list_resizelist-size-value) | Resizes the `list` to contain `size` elements. Initializes new elements with `value` or `NULL` if `value` is not set. | | [`list_reverse(list)`](#list_reverselist) | Reverses the `list`. | | [`list_reverse_sort(list[, col1])`](#list_reverse_sortlist-col1) | Sorts the elements of the list in reverse order. See the [Sorting Lists](#sorting-lists) section for more details about sorting order and `NULL` values. | | [`list_select(value_list, index_list)`](#list_selectvalue_list-index_list) | Returns a list based on the elements selected by the `index_list`. | | [`list_sem(list)`](#list_semlist) | Applies aggregate function [`sem`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_skewness(list)`](#list_skewnesslist) | Applies aggregate function [`skewness`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_slice(list, begin, end)`](#list_slicelist-begin-end) | Extracts a sublist or substring using [slice conventions]({% link docs/stable/sql/functions/list.md %}#slicing). Negative values are accepted. | | [`list_slice(list, begin, end, step)`](#list_slicelist-begin-end-step) | list_slice with added step feature. | | [`list_sort(list[, col1][, col2])`](#list_sortlist-col1-col2) | Sorts the elements of the list. See the [Sorting Lists](#sorting-lists) section for more details about sorting order and `NULL` values. | | [`list_stddev_pop(list)`](#list_stddev_poplist) | Applies aggregate function [`stddev_pop`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_stddev_samp(list)`](#list_stddev_samplist) | Applies aggregate function [`stddev_samp`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_string_agg(list)`](#list_string_agglist) | Applies aggregate function [`string_agg`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_sum(list)`](#list_sumlist) | Applies aggregate function [`sum`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_transform(list, lambda(x))`](#list_transformlist-lambdax) | Returns a list that is the result of applying the `lambda` function to each element of the input `list`. The return type is defined by the return type of the `lambda` function. See [`list_transform` examples]({% link docs/stable/sql/functions/lambda.md %}#list_transform-examples). | | [`list_unique(list)`](#list_uniquelist) | Counts the unique elements of a `list`. | | [`list_value(arg, ...)`](#list_valuearg-) | Creates a LIST containing the argument values. | | [`list_var_pop(list)`](#list_var_poplist) | Applies aggregate function [`var_pop`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_var_samp(list)`](#list_var_samplist) | Applies aggregate function [`var_samp`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | [`list_where(value_list, mask_list)`](#list_wherevalue_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_n[, truncate])`](#list_ziplist_1--list_n-truncate) | Zips n `LIST`s to a new `LIST` whose length will be that of the longest list. Its elements are structs of n elements from each list `list_1`, …, `list_n`, missing elements are replaced with `NULL`. If `truncate` is set, all lists are truncated to the smallest list length. | | [`range(start[, stop][, step])`](#rangestart-stop-step) | Creates a list of values between `start` and `stop` - the stop parameter is exclusive. | | [`reduce(list, lambda(x,y)[, initial_value])`](#list_reducelist-lambdaxy-initial_value) | Alias for `list_reduce`. | | [`repeat(list, count)`](#repeatlist-count) | Repeats the `list` `count` number of times. | | [`unnest(list)`](#unnestlist) | Unnests a list by one level. Note that this is a special function that alters the cardinality of the result. See the [unnest page]({% link docs/stable/sql/query_syntax/unnest.md %}) for more details. | | [`unpivot_list(arg, ...)`](#unpivot_listarg-) | Identical to list_value, but generated as part of unpivot for better error messages. | #### `list[index]`
| **Description** | Extracts a single list element using a (1-based) `index`. | | **Example** | `[4, 5, 6][3]` | | **Result** | `6` | | **Alias** | `list_extract` | #### `list[begin[:end][:step]]`
| **Description** | Extracts a sublist using [slice conventions]({% link docs/stable/sql/functions/list.md %}#slicing). Negative values are accepted. | | **Example** | `[4, 5, 6][3]` | | **Result** | `6` | | **Alias** | `list_slice` | #### `arg1 || arg2`
| **Description** | Concatenates two strings, lists, or blobs. Any `NULL` input results in `NULL`. See also [`concat(arg1, arg2, ...)`]({% link docs/stable/sql/functions/text.md %}#concatvalue-) and [`list_concat(list1, list2, ...)`]({% link docs/stable/sql/functions/list.md %}#list_concatlist_1--list_n). | | **Example 1** | `'Duck' || 'DB'` | | **Result** | `DuckDB` | | **Example 2** | `[1, 2, 3] || [4, 5, 6]` | | **Result** | `[1, 2, 3, 4, 5, 6]` | | **Example 3** | `'\xAA'::BLOB || '\xBB'::BLOB` | | **Result** | `\xAA\xBB` | #### `array_extract(list, index)`
| **Description** | Extracts the `index`th (1-based) value from the `list`. | | **Example** | `array_extract([4, 5, 6], 3)` | | **Result** | `6` | #### `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]` | #### `array_push_front(list, element)`
| **Description** | Prepends `element` to `list`. | | **Example** | `array_push_front([4, 5, 6], 3)` | | **Result** | `[3, 4, 5, 6]` | #### `array_to_string(list, delimiter)`
| **Description** | Concatenates list/array elements using an optional `delimiter`. | | **Example 1** | `array_to_string([1, 2, 3], '-')` | | **Result** | `1-2-3` | | **Example 2** | `array_to_string(['aa', 'bb', 'cc'], '')` | | **Result** | `aabbcc` | #### `array_to_string_comma_default(array)`
| **Description** | Concatenates list/array elements with a comma delimiter. | | **Example** | `array_to_string_comma_default(['Banana', 'Apple', 'Melon'])` | | **Result** | `Banana,Apple,Melon` | #### `concat(value, ...)`
| **Description** | Concatenates multiple strings or lists. `NULL` inputs are skipped. See also [operator `||`](#arg1--arg2). | | **Example 1** | `concat('Hello', ' ', 'World')` | | **Result** | `Hello World` | | **Example 2** | `concat([1, 2, 3], NULL, [4, 5, 6])` | | **Result** | `[1, 2, 3, 4, 5, 6]` | #### `contains(list, element)`
| **Description** | Returns `true` if the `list` contains the `element`. | | **Example** | `contains([1, 2, NULL], 1)` | | **Result** | `true` | #### `flatten(nested_list)`
| **Description** | [Flattens](#flattening) a nested list by one level. | | **Example** | `flatten([[1, 2, 3], [4, 5]])` | | **Result** | `[1, 2, 3, 4, 5]` | #### `generate_series(start[, stop][, step])`
| **Description** | Creates a list of values between `start` and `stop` - the stop parameter is inclusive. | | **Example** | `generate_series(2, 5, 3)` | | **Result** | `[2, 5]` | #### `length(list)`
| **Description** | Returns the length of the `list`. | | **Example** | `length([1,2,3])` | | **Result** | `3` | | **Aliases** | `char_length`, `character_length`, `len` | #### `list_aggregate(list, function_name, ...)`
| **Description** | Executes the aggregate function `function_name` on the elements of `list`. See the [List Aggregates](#list-aggregates) section for more details. | | **Example** | `list_aggregate([1, 2, NULL], 'min')` | | **Result** | `1` | | **Aliases** | `aggregate`, `array_aggr`, `array_aggregate`, `list_aggr` | #### `list_any_value(list)`
| **Description** | Applies aggregate function [`any_value`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_any_value([3,3,9])` | | **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_approx_count_distinct(list)`
| **Description** | Applies aggregate function [`approx_count_distinct`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_approx_count_distinct([3,3,9])` | | **Result** | `2` | #### `list_avg(list)`
| **Description** | Applies aggregate function [`avg`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_avg([3,3,9])` | | **Result** | `5.0` | #### `list_bit_and(list)`
| **Description** | Applies aggregate function [`bit_and`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_bit_and([3,3,9])` | | **Result** | `1` | #### `list_bit_or(list)`
| **Description** | Applies aggregate function [`bit_or`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_bit_or([3,3,9])` | | **Result** | `11` | #### `list_bit_xor(list)`
| **Description** | Applies aggregate function [`bit_xor`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_bit_xor([3,3,9])` | | **Result** | `9` | #### `list_bool_and(list)`
| **Description** | Applies aggregate function [`bool_and`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_bool_and([true, false])` | | **Result** | `false` | #### `list_bool_or(list)`
| **Description** | Applies aggregate function [`bool_or`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_bool_or([true, false])` | | **Result** | `true` | #### `list_concat(list_1, ..., list_n)`
| **Description** | Concatenates lists. `NULL` inputs are skipped. See also [operator `||`](#arg1--arg2). | | **Example** | `list_concat([2, 3], [4, 5, 6], [7])` | | **Result** | `[2, 3, 4, 5, 6, 7]` | | **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** | `array_contains`, `array_has`, `list_has` | #### `list_cosine_distance(list1, list2)`
| **Description** | Computes the cosine distance between two same-sized lists. | | **Example** | `list_cosine_distance([1, 2, 3], [1, 2, 3])` | | **Result** | `0.0` | | **Alias** | `<=>` | #### `list_cosine_similarity(list1, list2)`
| **Description** | Computes the cosine similarity between two same-sized lists. | | **Example** | `list_cosine_similarity([1, 2, 3], [1, 2, 3])` | | **Result** | `1.0` | #### `list_count(list)`
| **Description** | Applies aggregate function [`count`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_count([3,3,9])` | | **Result** | `3` | #### `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` | | **Alias** | `<->` | #### `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** | `[5, -3, 1]` | | **Alias** | `array_distinct` | #### `list_entropy(list)`
| **Description** | Applies aggregate function [`entropy`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_entropy([3,3,9])` | | **Result** | `0.9182958340544893` | #### `list_extract(list, index)`
| **Description** | Extract the `index`th (1-based) value from the list. | | **Example** | `list_extract([4, 5, 6], 3)` | | **Result** | `6` | | **Alias** | `list_element` | #### `list_filter(list, lambda(x))`
| **Description** | Constructs a list from those elements of the input `list` for which the `lambda` function returns `true`. DuckDB must be able to cast the `lambda` function's return type to `BOOL`. The return type of `list_filter` is the same as the input list's. See [`list_filter` examples]({% link docs/stable/sql/functions/lambda.md %}#list_filter-examples). | | **Example** | `list_filter([3, 4, 5], lambda x : x > 4)` | | **Result** | `[5]` | | **Aliases** | `array_filter`, `filter` | #### `list_first(list)`
| **Description** | Applies aggregate function [`first`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_first([3,3,9])` | | **Result** | `3` | #### `list_grade_up(list[, col1][, col2])`
| **Description** | Works like [`list_sort`](#list_sortlist-col1-col2), but the results are the indexes that correspond to the position in the original list instead of the actual values. | | **Example** | `list_grade_up([3, 6, 1, 2])` | | **Result** | `[3, 4, 1, 2]` | | **Aliases** | `array_grade_up`, `grade_up` | #### `list_has_all(list1, list2)`
| **Description** | Returns true if all elements of list2 are in list1. NULLs are ignored. | | **Example** | `list_has_all([1, 2, 3], [2, 3])` | | **Result** | `true` | | **Aliases** | `<@`, `@>`, `array_has_all` | #### `list_has_any(list1, list2)`
| **Description** | Returns true if the lists have any element in common. NULLs are ignored. | | **Example** | `list_has_any([1, 2, 3], [2, 3, 4])` | | **Result** | `true` | | **Aliases** | `&&`, `array_has_any` | #### `list_histogram(list)`
| **Description** | Applies aggregate function [`histogram`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_histogram([3,3,9])` | | **Result** | `{3=2, 9=1}` | #### `list_inner_product(list1, list2)`
| **Description** | Computes the inner product between two same-sized lists. | | **Example** | `list_inner_product([1, 2, 3], [1, 2, 3])` | | **Result** | `14.0` | | **Alias** | `list_dot_product` | #### `list_intersect(list1, list2)`
| **Description** | Returns a list of all the elements that exist in both `list1` and `list2`, without duplicates. | | **Example** | `list_intersect([1, 2, 3], [2, 3, 4])` | | **Result** | `[3, 2]` | | **Alias** | `array_intersect` | #### `list_kurtosis(list)`
| **Description** | Applies aggregate function [`kurtosis`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_kurtosis([3,3,9])` | | **Result** | `NULL` | #### `list_kurtosis_pop(list)`
| **Description** | Applies aggregate function [`kurtosis_pop`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_kurtosis_pop([3,3,9])` | | **Result** | `-1.4999999999999978` | #### `list_last(list)`
| **Description** | Applies aggregate function [`last`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_last([3,3,9])` | | **Result** | `9` | #### `list_mad(list)`
| **Description** | Applies aggregate function [`mad`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_mad([3,3,9])` | | **Result** | `0.0` | #### `list_max(list)`
| **Description** | Applies aggregate function [`max`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_max([3,3,9])` | | **Result** | `9` | #### `list_median(list)`
| **Description** | Applies aggregate function [`median`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_median([3,3,9])` | | **Result** | `3.0` | #### `list_min(list)`
| **Description** | Applies aggregate function [`min`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_min([3,3,9])` | | **Result** | `3` | #### `list_mode(list)`
| **Description** | Applies aggregate function [`mode`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_mode([3,3,9])` | | **Result** | `3` | #### `list_negative_inner_product(list1, list2)`
| **Description** | Computes the negative inner product between two same-sized lists. | | **Example** | `list_negative_inner_product([1, 2, 3], [1, 2, 3])` | | **Result** | `-14.0` | | **Alias** | `list_negative_dot_product` | #### `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** | `array_indexof`, `array_position`, `list_indexof` | #### `list_prepend(element, list)`
| **Description** | Prepends `element` to `list`. | | **Example** | `list_prepend(3, [4, 5, 6])` | | **Result** | `[3, 4, 5, 6]` | | **Alias** | `array_prepend` | #### `list_product(list)`
| **Description** | Applies aggregate function [`product`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_product([3,3,9])` | | **Result** | `81.0` | #### `list_reduce(list, lambda(x,y)[, initial_value])`
| **Description** | Reduces all elements of the input `list` into a single scalar value by executing the `lambda` function on a running result and the next list element. The `lambda` function has an optional `initial_value` argument. See [`list_reduce` examples]({% link docs/stable/sql/functions/lambda.md %}#list_reduce-examples). | | **Example** | `list_reduce([1, 2, 3], lambda x, y : x + y)` | | **Result** | `6` | | **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(list)`
| **Description** | Reverses the `list`. | | **Example** | `list_reverse([3, 6, 1, 2])` | | **Result** | `[2, 1, 6, 3]` | | **Alias** | `array_reverse` | #### `list_reverse_sort(list[, col1])`
| **Description** | Sorts the elements of the list in reverse order. See the [Sorting Lists](#sorting-lists) section for more details about sorting order and `NULL` values. | | **Example** | `list_reverse_sort([3, 6, 1, 2])` | | **Result** | `[6, 3, 2, 1]` | | **Alias** | `array_reverse_sort` | #### `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_sem(list)`
| **Description** | Applies aggregate function [`sem`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_sem([3,3,9])` | | **Result** | `1.6329931618554523` | #### `list_skewness(list)`
| **Description** | Applies aggregate function [`skewness`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_skewness([3,3,9])` | | **Result** | `1.7320508075688796` | #### `list_slice(list, begin, end)`
| **Description** | Extracts a sublist or substring using [slice conventions]({% link docs/stable/sql/functions/list.md %}#slicing). Negative values are accepted. | | **Example** | `list_slice([4, 5, 6], 2, 3)` | | **Result** | `[5, 6]` | | **Alias** | `array_slice` | #### `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_sort(list[, col1][, col2])`
| **Description** | Sorts the elements of the list. See the [Sorting Lists](#sorting-lists) section for more details about sorting order and `NULL` values. | | **Example** | `list_sort([3, 6, 1, 2])` | | **Result** | `[1, 2, 3, 6]` | | **Alias** | `array_sort` | #### `list_stddev_pop(list)`
| **Description** | Applies aggregate function [`stddev_pop`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_stddev_pop([3,3,9])` | | **Result** | `2.8284271247461903` | #### `list_stddev_samp(list)`
| **Description** | Applies aggregate function [`stddev_samp`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_stddev_samp([3,3,9])` | | **Result** | `3.4641016151377544` | #### `list_string_agg(list)`
| **Description** | Applies aggregate function [`string_agg`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_string_agg([3,3,9])` | | **Result** | `3,3,9` | #### `list_sum(list)`
| **Description** | Applies aggregate function [`sum`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_sum([3,3,9])` | | **Result** | `15` | #### `list_transform(list, lambda(x))`
| **Description** | Returns a list that is the result of applying the `lambda` function to each element of the input `list`. The return type is defined by the return type of the `lambda` function. See [`list_transform` examples]({% link docs/stable/sql/functions/lambda.md %}#list_transform-examples). | | **Example** | `list_transform([1, 2, 3], lambda x : x + 1)` | | **Result** | `[2, 3, 4]` | | **Aliases** | `apply`, `array_apply`, `array_transform`, `list_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(arg, ...)`
| **Description** | Creates a LIST containing the argument values. | | **Example** | `list_value(4, 5, 6)` | | **Result** | `[4, 5, 6]` | | **Alias** | `list_pack` | #### `list_var_pop(list)`
| **Description** | Applies aggregate function [`var_pop`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_var_pop([3,3,9])` | | **Result** | `8.0` | #### `list_var_samp(list)`
| **Description** | Applies aggregate function [`var_samp`]({% link docs/stable/sql/functions/aggregates.md %}#general-aggregate-functions) to the `list`. | | **Example** | `list_var_samp([3,3,9])` | | **Result** | `12.0` | #### `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(list_1, ..., list_n[, truncate])`
| **Description** | Zips n `LIST`s to a new `LIST` whose length will be that of the longest list. Its elements are structs of n elements from each list `list_1`, …, `list_n`, missing elements are replaced with `NULL`. If `truncate` is set, all lists are truncated to the smallest list length. | | **Example 1** | `list_zip([1, 2], [3, 4], [5, 6])` | | **Result** | `[(1, 3, 5), (2, 4, 6)]` | | **Example 2** | `list_zip([1, 2], [3, 4], [5, 6, 7])` | | **Result** | `[(1, 3, 5), (2, 4, 6), (NULL, NULL, 7)]` | | **Example 3** | `list_zip([1, 2], [3, 4], [5, 6, 7], true)` | | **Result** | `[(1, 3, 5), (2, 4, 6)]` | | **Alias** | `array_zip` | #### `range(start[, stop][, step])`
| **Description** | Creates a list of values between `start` and `stop` - the stop parameter is exclusive. | | **Example** | `range(2, 5, 3)` | | **Result** | `[2]` | #### `repeat(list, count)`
| **Description** | Repeats the `list` `count` number of times. | | **Example** | `repeat([1, 2, 3], 5)` | | **Result** | `[1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3]` | #### `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]({% link docs/stable/sql/query_syntax/unnest.md %}) for more details. | | **Example** | `unnest([1, 2, 3])` | | **Result** | Multiple rows: `'1'`, `'2'`, `'3'` | #### `unpivot_list(arg, ...)`
| **Description** | Identical to list_value, but generated as part of unpivot for better error messages. | | **Example** | `unpivot_list(4, 5, 6)` | | **Result** | `[4, 5, 6]` | ## List Operators The following operators are supported for lists: | Operator | Description | Example | Result | |-|--|---|-| | `&&` | Alias for [`list_has_any`](#list_has_anylist1-list2). | `[1, 2, 3, 4, 5] && [2, 5, 5, 6]` | `true` | | `@>` | Alias for [`list_has_all`](#list_has_alllist1-list2), 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`](#list_has_alllist1-list2), where the list on the **left** of the operator is the sublist. | `[1, 4] <@ [1, 2, 3, 4]` | `true` | | `||` | Similar to [`list_concat`](#list_concatlist_1--list_n), except any `NULL` input results in `NULL`. | `[1, 2, 3] || [4, 5, 6]` | `[1, 2, 3, 4, 5, 6]` | | `<=>` | Alias for [`list_cosine_distance`](#list_cosine_distancelist1-list2). | `[1, 2, 3] <=> [1, 2, 5]` | `0.007416606` | | `<->` | Alias for [`list_distance`](#list_distancelist1-list2). | `[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: ```sql SELECT [lower(x) FOR x IN strings] AS strings FROM (VALUES (['Hello', '', 'World'])) t(strings); ```
| strings | |------------------| | [hello, , world] | ```sql 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: ```sql SELECT [4, 5, 6] AS l, [x FOR x, i IN l IF i != 2] AS filtered; ```
| l | filtered | |-----------|----------| | [4, 5, 6] | [4, 6] | Under the hood, `[f(x) FOR x IN l IF g(x)]` is translated to: ```sql 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)`](#range) and [`generate_series(start, stop, step)`](#generate_series), 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)` ```sql SELECT range(5); ``` ```text [0, 1, 2, 3, 4] ``` #### `range(start, stop)` ```sql SELECT range(2, 5); ``` ```text [2, 3, 4] ``` #### `range(start, stop, step)` ```sql SELECT range(2, 5, 3); ``` ```text [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)` ```sql SELECT generate_series(5); ``` ```text [0, 1, 2, 3, 4, 5] ``` #### `generate_series(start, stop)` ```sql SELECT generate_series(2, 5); ``` ```text [2, 3, 4, 5] ``` #### `generate_series(start, stop, step)` ```sql SELECT generate_series(2, 5, 3); ``` ```text [2, 5] ``` #### `generate_subscripts(arr, dim)` The `generate_subscripts(arr, dim)` function generates indexes along the `dim`th dimension of array `arr`. ```sql 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 ```sql 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 ```sql 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`](#list_slicelist-begin-end) 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: ```sql SELECT list_slice([1, 2, 3, 4, 5], 2, 4); ``` ```text [2, 3, 4] ``` ```sql SELECT ([1, 2, 3, 4, 5])[2:4:2]; ``` ```text [2, 4] ``` ```sql SELECT([1, 2, 3, 4, 5])[4:2:-2]; ``` ```text [4, 2] ``` ```sql SELECT ([1, 2, 3, 4, 5])[:]; ``` ```text [1, 2, 3, 4, 5] ``` ```sql SELECT ([1, 2, 3, 4, 5])[:-:2]; ``` ```text [1, 3, 5] ``` ```sql SELECT ([1, 2, 3, 4, 5])[:-:-2]; ``` ```text [5, 3, 1] ``` ## List Aggregates The function [`list_aggregate`](#list_aggregatelist-function_name-) 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`. ```sql SELECT list_aggregate([1, 2, -4, NULL], 'min'); ``` ```text -4 ``` ```sql SELECT list_aggregate([2, 4, 8, 42], 'sum'); ``` ```text 56 ``` ```sql SELECT list_aggregate([[1, 2], [NULL], [2, 10, 3]], 'last'); ``` ```text [2, 10, 3] ``` ```sql SELECT list_aggregate([2, 4, 8, 42], 'string_agg', '|'); ``` ```text 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`. ```sql SELECT list_min([1, 2, -4, NULL]); ``` ```text -4 ``` ```sql SELECT list_sum([2, 4, 8, 42]); ``` ```text 56 ``` ```sql SELECT list_last([[1, 2], [NULL], [2, 10, 3]]); ``` ```text [2, 10, 3] ``` #### `array_to_string` Concatenates list/array elements using an optional delimiter. ```sql SELECT array_to_string([1, 2, 3], '-') AS str; ``` ```text 1-2-3 ``` This is equivalent to the following SQL: ```sql SELECT list_aggr([1, 2, 3], 'string_agg', '-') AS str; ``` ```text 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 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.](../query_syntax/orderby). `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. ```sql SELECT list_sort([1, 3, NULL, 5, NULL, -5]); ``` ```sql [NULL, NULL, -5, 1, 3, 5] ``` This query provides the sort order. The `NULL` order uses the configurable default value. ```sql SELECT list_sort([1, 3, NULL, 2], 'ASC'); ``` ```sql [NULL, 1, 2, 3] ``` This query provides both the sort order and the `NULL` order. ```sql SELECT list_sort([1, 3, NULL, 2], 'DESC', 'NULLS FIRST'); ``` ```sql [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. ```sql SELECT list_sort([1, 3, NULL, 5, NULL, -5]); ``` ```sql [NULL, NULL, -5, 1, 3, 5] ``` This query provides the `NULL` sort order. ```sql SELECT list_reverse_sort([1, 3, NULL, 2], 'NULLS LAST'); ``` ```sql [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: ```sql SELECT flatten([ [1, 2], [3, 4] ]); ``` ```text [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: ```sql SELECT flatten([ [ [1, 2], [3, 4], ], [ [5, 6], [7, 8], ] ]); ``` ```text [[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: ```sql SELECT flatten([]); ``` ```text [] ``` If the entire input to flatten is `NULL`, return `NULL`: ```sql SELECT flatten(NULL); ``` ```text NULL ``` If a list whose only entry is `NULL` is flattened, return an empty list: ```sql SELECT flatten([NULL]); ``` ```text [] ``` If the sub-list in a list of lists only contains `NULL`, do not modify the sub-list: ```sql -- (Note the extra set of parentheses vs. the prior example) SELECT flatten([[NULL]]); ``` ```text [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: ```sql SELECT flatten([[NULL], [NULL]]); ``` ```text [NULL, NULL] ``` ## Lambda Functions DuckDB supports lambda functions in the form `lambda parameter1, parameter2, ...: expression`. For details, see the [lambda functions page]({% link docs/stable/sql/functions/lambda.md %}). ## Related Functions * The [aggregate functions]({% link docs/stable/sql/functions/aggregates.md %}) `list` and `histogram` produce lists and lists of structs. * The [`unnest` function]({% link docs/stable/sql/query_syntax/unnest.md %}) is used to unnest a list by one level.