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.

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.

Transform each item in a list
SQL
Haybarn WASM 2.0.0-rc12 · In your browser

Guide example · Run to view results.

Expected result
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.

Full list_transform() reference →

All lists & arrays functions

128 functions in Haybarn WASM 2.0.0-rc12. Browse the filtered catalog →

  • aggregate()

    Executes the aggregate function function_name on the elements of list.

  • apply()

    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.

  • array_agg()

    Returns a LIST containing all the values of a column.

  • array_aggr()

    Executes the aggregate function function_name on the elements of list.

  • array_aggregate()

    Executes the aggregate function function_name on the elements of list.

  • array_append()
  • array_apply()

    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.

  • array_cat()

    Concatenates lists. NULL inputs are skipped. See also operator ||.

  • array_concat()

    Concatenates lists. NULL inputs 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 NULL values 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 string using a (1-based) index.

  • array_filter()

    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.

  • 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 element if the list contains the element. If the element is not found, it returns NULL.

  • 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 list1 and list2.

  • array_length()

    array_length for lists with dimensions other than 1 not implemented

  • array_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 element if the list contains the element. If the element is not found, it returns NULL.

  • array_prepend()
  • array_push_back()
  • array_push_front()
  • array_reduce()

    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.

  • array_resize()

    Resizes the list to contain size elements. Initializes new elements with value or NULL if value is 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 lambda function to each element of the input list. The return type is defined by the return type of the lambda function.

  • array_unique()

    Counts the unique elements of a list.

  • array_value()

    Creates an ARRAY containing the argument values.

  • array_where()

    Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list.

  • array_zip()

    Zips n LISTs 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.

  • char_length()

    Returns the bit-length of the bit argument.

  • character_length()

    Returns the bit-length of the bit argument.

  • concat()

    Concatenates multiple strings or lists. NULL inputs are skipped. See also operator ||.

  • filter()

    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.

  • 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 bit argument.

  • length()

    Returns the bit-length of the bit argument.

  • list_aggr()

    Executes the aggregate function function_name on the elements of list.

  • list_aggregate()

    Executes the aggregate function function_name on the elements of list.

  • list_any_value()
  • list_append()
  • list_apply()

    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.

  • 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. NULL inputs are skipped. See also operator ||.

  • list_concat()

    Concatenates lists. NULL inputs 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 NULL values 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 element if the list contains the element. If the element is not found, it returns NULL.

  • 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 list1 and list2.

  • 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 element if the list contains the element. If the element is not found, it returns NULL.

  • list_prepend()
  • list_product()
  • list_reduce()

    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.

  • list_resize()

    Resizes the list to contain size elements. Initializes new elements with value or NULL if value is 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 in mask_list applied as a mask to the value_list.

  • list_zip()

    Zips n LISTs 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()

    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 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.

  • unpivot_list()

    Identical to list_value, but generated as part of unpivot for better error messages.