Shape / columns · All operations

Work with list values

A variant of Parse nested values

Test, index, sort, filter, combine or summarise the elements inside list and array values.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Test, index, sort, filter, combine and summarise list values

SQL

CREATE TEMP TABLE bags AS SELECT * FROM (VALUES
  (1, [3, 1, 2]), (2, [5]), (3, []::INTEGER[]), (4, NULL::INTEGER[])
) t(id, l);
CREATE TEMP TABLE result AS
SELECT id,
       list_contains(l, 2) AS has_two,
       l[1] AS first_item,
       list_sort(l) AS sorted,
       list_filter(l, lambda x: x > 1) AS over_one,
       list_concat(l, [9]) AS with_nine,
       list_sum(l) AS total,
       len(l) AS n
FROM bags;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT string_agg(to_json(r)::VARCHAR, ';' ORDER BY id) FROM result r) = '{"id":1,"has_two":true,"first_item":3,"sorted":[1,2,3],"over_one":[3,2],"with_nine":[3,1,2,9],"total":6,"n":3};{"id":2,"has_two":false,"first_item":5,"sorted":[5],"over_one":[5],"with_nine":[5,9],"total":5,"n":1};{"id":3,"has_two":false,"first_item":null,"sorted":[],"over_one":[],"with_nine":[9],"total":null,"n":0};{"id":4,"has_two":null,"first_item":null,"sorted":null,"over_one":null,"with_nine":[9],"total":null,"n":null}') THEN 'as expected' ELSE error('the lists were not tested, indexed, sorted, filtered, combined and summed as expected, or an empty and a missing list were not told apart') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the lists were not tested, indexed, sorted, filtered, combined and summed as expected, or an empty and a missing list were not told apart”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)

Array Functionsread 2026-09-27
123 functions, 67 of them aliases: array_append, array_sort, flatten, array_distance, string_to_array, …

Azure Data Factory (2)

Array functionsread 2026-09-27
20 functions: array, at, contains, distinct, except, …
Collection functionsread 2026-09-27
10 functions: contains, empty, first, intersection, join, …

dbt-utils (1)

Array functionsread 2026-09-27
3 macros: array_append, array_concat, array_construct

DuckDB (2)

Array Functions (family)read 2026-09-27
The functions documented on the Array Functions page
List Functions (family)read 2026-09-27
The functions documented on the List Functions page

Duckle (4)

Array Distinctread 2026-09-25
distinct elements of a list
Array Lengthread 2026-09-25
length of a list
Containsread 2026-09-25
boolean column: does the list contain a value
Element Atread 2026-09-25
element of a list by index

Ibis (24)

ArrayAllread 2026-09-27
Test whether every element of a boolean array is true
ArrayAnyread 2026-09-27
Test whether any element of a boolean array is true
ArrayConcatread 2026-09-27
Join two or more arrays end to end
ArrayContainsread 2026-09-27
Test whether an array holds a given value
ArrayDistinctread 2026-09-27
Reduce an array to its unique elements
ArrayFilterread 2026-09-27
Keep the array elements for which a lambda is true
ArrayFlattenread 2026-09-27
Remove one level of nesting from an array of arrays
ArrayIndexread 2026-09-27
Get the array element at a given index
ArrayIntersectread 2026-09-27
Keep the elements two arrays have in common
ArrayLengthread 2026-09-27
Count the elements of an array
ArrayMapread 2026-09-27
Apply a lambda to every element of an array
ArrayMaxread 2026-09-27
Largest element of each array
ArrayMeanread 2026-09-27
Mean of the elements of each array
ArrayMinread 2026-09-27
Smallest element of each array
ArrayModeread 2026-09-27
Most frequent element of each array
ArrayPositionread 2026-09-27
Find the index of a value within an array
ArrayRemoveread 2026-09-27
Drop every occurrence of a value from an array
ArrayRepeatread 2026-09-27
Repeat the elements of an array a given number of times
ArraySliceread 2026-09-27
Take a start-to-stop slice of an array
ArraySortread 2026-09-27
Sort the elements of an array
ArrayStringJoinread 2026-09-27
Join the strings of an array into one string with a separator
ArraySumread 2026-09-27
Sum of the elements of each array
ArrayUnionread 2026-09-27
Combine two arrays, keeping the distinct elements
ArrayZipread 2026-09-27
Zip arrays element by element into an array of structs

OpenRefine (1)

Array functionsread 2026-09-27
10 functions: join, sort, reverse, uniques, zip

Polars (3)

Expr.arr (group)read 2026-09-27
29 expressions: get, contains, sort, unique, sum and others; work on the elements of fixed-size array values
Expr.list (group)read 2026-09-27
44 expressions: get, contains, eval, set_union, explode and others; work on the elements of list values
SQL functions: Array (group)read 2026-09-27
12 functions: ARRAY_AGG, ARRAY_CONTAINS, ARRAY_GET, ARRAY_LENGTH, ARRAY_LOWER and others; SQL array functions

Power Query M (1)

List functionsread 2026-09-27
71 functions: List.Transform, List.Select, List.Sum, List.Distinct, List.Generate

Background

A list is a finite, ordered collection of items, and its implementations may offer operations such as getting an item by index, map, filter and reduce.^[1] A DuckDB LIST column may hold lists of different lengths in different rows, but all their values must have the same underlying type.^[2] DuckDB's list aggregates, such as list_sum and list_avg, apply an aggregate function to the elements of one list.^[3]

When to use it

In DuckDB, list[index] extracts an element by a 1-based index, and ['a', 'b', 'c'][-1] returns 'c'.^[3]^[2] DuckDB's list_distinct removes all duplicates and NULL values from a list, and does not preserve the original order.^[3] Sort after removing duplicates when the order matters, and count NULLs separately when they mean something. (Meridian's view) DuckDB's list_sort sorts in ascending order with NULLs last by default, and sorts the same way as DuckDB's ORDER BY clause.^[3]

In DuckDB, unnesting an empty list or a NULL value generates no output rows for its input row.^[4] Work on the list in place when the answer is one value per row, such as a count or a total, and unnest it only when each element needs a row of its own. (Meridian's view) DuckDB's flatten concatenates a list of lists into a single list, but only one level at a time.^[3]

The method

DuckDB supports lambda functions of the form lambda parameter: expression, which list_transform applies to each element and list_filter uses to choose elements.^[3] DuckDB also accepts Python-style list comprehensions, which it translates into list_apply and list_filter calls.^[3] DuckDB's list_aggregate runs a named aggregate function on the elements of a list.^[3]

DuckDB's LIST is similar to PostgreSQL's ARRAY type, some array_ functions are provided for PostgreSQL compatibility, and fixed-length lists use DuckDB's ARRAY type.^[2] DuckDB compares lists position by position, and when every shared position is equal the longer list sorts last.^[2]

Sources

[1]List (abstract data type) - Wikipediasecondary source
[2]List Type – DuckDBdocumentation
[3]List Functions – DuckDBdocumentation
[4]Unnesting – DuckDBdocumentation