SQL list and array functions
Transform, filter, and index lists in Haybarn SQL. Learn when to keep a collection in one value and when to expand it into rows.
Choose a function
Use a lambda when the same expression applies to every item. Filtering changes which items remain; transformation changes their values. Use unnest() when later steps need joins or row-based aggregation.
list_transform()Apply an expression to every item.list_filter()Keep items that satisfy a predicate.list_extract()Read one item by position.range()Build a sequence with an exclusive stop.generate_series()Build a sequence with an inclusive stop.unnest()Expand items into rows.
SQL list positions start at 1. JSON array paths start at 0. An empty list, a NULL list, and a list containing a NULL item are three different inputs.
Transform each item in a list
Apply a lambda expression to every item of a SQL list while retaining the list structure and item order.
A NULL item is passed to the lambda. Arithmetic on that item yields NULL; the item is not automatically removed.
Guide example · Run to view results.
| doubled |
|---|
[2, NULL, 6] |
Watch for: Haybarn 2.0 examples use lambda x: expression. Use list_filter() to remove items, or unnest() to turn the list into rows.
All lists & arrays functions
128 functions in Haybarn WASM 2.0.0-rc12. Browse the filtered catalog →
aggregate()Executes the aggregate function
function_nameon the elements oflist.apply()Returns a list that is the result of applying the
lambdafunction to each element of the inputlist. The return type is defined by the return type of thelambdafunction.array_agg()Returns a LIST containing all the values of a column.
array_aggr()Executes the aggregate function
function_nameon the elements oflist.array_aggregate()Executes the aggregate function
function_nameon the elements oflist.array_append()array_apply()Returns a list that is the result of applying the
lambdafunction to each element of the inputlist. The return type is defined by the return type of thelambdafunction.array_cat()Concatenates lists.
NULLinputs are skipped. See also operator||.array_concat()Concatenates lists.
NULLinputs are skipped. See also operator||.array_contains()Returns true if the list contains the element.
array_cosine_distance()Computes the cosine distance between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_cosine_similarity()Computes the cosine similarity between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_cross_product()Computes the cross product of two arrays of size 3. The array elements can not be
NULL.array_distance()Computes the distance between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_distinct()Removes all duplicates and
NULLvalues from a list. Does not preserve the original order.array_dot_product()Computes the inner product between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_extract()Extracts a single character from a
stringusing a (1-based)index.array_filter()Constructs a list from those elements of the input
listfor which thelambdafunction returnstrue. DuckDB must be able to cast thelambdafunction's return type toBOOL. The return type oflist_filteris the same as the input list's.array_grade_up()Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
array_has()Returns true if the list contains the element.
array_has_all()Returns true if all elements of list2 are in list1. NULLs are ignored.
array_has_any()Returns true if the lists have any element in common. NULLs are ignored.
array_indexof()Returns the index of the
elementif thelistcontains theelement. If theelementis not found, it returnsNULL.array_inner_product()Computes the inner product between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_intersect()Returns a list containing the distinct elements that are present in both
list1andlist2.array_length()array_lengthfor lists with dimensions other than 1 not implementedarray_negative_dot_product()Computes the negative inner product between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_negative_inner_product()Computes the negative inner product between two arrays of the same size. The array elements can not be
NULL. The arrays can have any size as long as the size is the same for both arguments.array_pop_back()array_pop_front()array_position()Returns the index of the
elementif thelistcontains theelement. If theelementis not found, it returnsNULL.array_prepend()array_push_back()array_push_front()array_reduce()Reduces all elements of the input
listinto a single scalar value by executing thelambdafunction on a running result and the next list element. Thelambdafunction has an optionalinitial_valueargument.array_resize()Resizes the
listto containsizeelements. Initializes new elements withvalueorNULLifvalueis not set.array_reverse()array_reverse_sort()Sorts the elements of the list in reverse order.
array_select()Returns a list based on the elements selected by the
index_list.array_slice()list_slice with added step feature.
array_sort()Sorts the elements of the list.
array_to_string()array_to_string_comma_default()array_transform()Returns a list that is the result of applying the
lambdafunction to each element of the inputlist. The return type is defined by the return type of thelambdafunction.array_unique()Counts the unique elements of a
list.array_value()Creates an
ARRAYcontaining the argument values.array_where()Returns a list with the
BOOLEANs inmask_listapplied as a mask to thevalue_list.array_zip()Zips n
LISTs to a newLISTwhose length will be that of the longest list. Its elements are structs of n elements from each listlist_1, …,list_n, missing elements are replaced withNULL. Iftruncateis set, all lists are truncated to the smallest list length.char_length()Returns the bit-length of the
bitargument.character_length()Returns the bit-length of the
bitargument.concat()Concatenates multiple strings or lists.
NULLinputs are skipped. See also operator||.filter()Constructs a list from those elements of the input
listfor which thelambdafunction returnstrue. DuckDB must be able to cast thelambdafunction's return type toBOOL. The return type oflist_filteris the same as the input list's.flatten()Flattens a nested list by one level.
generate_series()Generate a sequence that includes its stop value when a step lands on it. Use the table form to produce one row per value.
grade_up()Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
len()Returns the bit-length of the
bitargument.length()Returns the bit-length of the
bitargument.list_aggr()Executes the aggregate function
function_nameon the elements oflist.list_aggregate()Executes the aggregate function
function_nameon the elements oflist.list_any_value()list_append()list_apply()Returns a list that is the result of applying the
lambdafunction to each element of the inputlist. The return type is defined by the return type of thelambdafunction.list_approx_count_distinct()list_avg()list_bit_and()list_bit_or()list_bit_xor()list_bool_and()list_bool_or()list_cat()Concatenates lists.
NULLinputs are skipped. See also operator||.list_concat()Concatenates lists.
NULLinputs are skipped. See also operator||.list_contains()Returns true if the list contains the element.
list_cosine_distance()Computes the cosine distance between two same-sized lists.
list_cosine_similarity()Computes the cosine similarity between two same-sized lists.
list_count()list_distance()Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.
list_distinct()Removes all duplicates and
NULLvalues from a list. Does not preserve the original order.list_dot_product()Computes the inner product between two same-sized lists.
list_element()Extract the
indexth (1-based) value from the list.list_entropy()list_extract()Read one item from a SQL list using a one-based position. Negative positions count backward from the end.
list_filter()Filter a SQL list with a lambda predicate, keeping the items for which the predicate evaluates to true.
list_first()list_grade_up()Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
list_has()Returns true if the list contains the element.
list_has_all()Returns true if all elements of list2 are in list1. NULLs are ignored.
list_has_any()Returns true if the lists have any element in common. NULLs are ignored.
list_histogram()list_indexof()Returns the index of the
elementif thelistcontains theelement. If theelementis not found, it returnsNULL.list_inner_product()Computes the inner product between two same-sized lists.
list_intersect()Returns a list containing the distinct elements that are present in both
list1andlist2.list_kurtosis()list_kurtosis_pop()list_last()list_mad()list_max()list_median()list_min()list_mode()list_negative_dot_product()Computes the negative inner product between two same-sized lists.
list_negative_inner_product()Computes the negative inner product between two same-sized lists.
list_pack()Creates a LIST containing the argument values.
list_position()Returns the index of the
elementif thelistcontains theelement. If theelementis not found, it returnsNULL.list_prepend()list_product()list_reduce()Reduces all elements of the input
listinto a single scalar value by executing thelambdafunction on a running result and the next list element. Thelambdafunction has an optionalinitial_valueargument.list_resize()Resizes the
listto containsizeelements. Initializes new elements withvalueorNULLifvalueis not set.list_reverse()list_reverse_sort()Sorts the elements of the list in reverse order.
list_select()Returns a list based on the elements selected by the
index_list.list_sem()list_skewness()list_slice()list_slice with added step feature.
list_sort()Sorts the elements of the list.
list_stddev_pop()list_stddev_samp()list_string_agg()list_sum()list_transform()Apply a lambda expression to every item of a SQL list while retaining the list structure and item order.
list_unique()Counts the unique elements of a
list.list_value()Creates a LIST containing the argument values.
list_var_pop()list_var_samp()list_where()Returns a list with the
BOOLEANs inmask_listapplied as a mask to thevalue_list.list_zip()Zips n
LISTs to a newLISTwhose length will be that of the longest list. Its elements are structs of n elements from each listlist_1, …,list_n, missing elements are replaced withNULL. Iftruncateis set, all lists are truncated to the smallest list length.range()Generate numeric sequences as a list or as rows. The stop value is excluded, which makes range useful for bounded iteration.
reduce()Reduces all elements of the input
listinto a single scalar value by executing thelambdafunction on a running result and the next list element. Thelambdafunction has an optionalinitial_valueargument.unpivot_list()Identical to list_value, but generated as part of unpivot for better error messages.