# Work with list values

Shape / columns

A variant of [Parse nested values](https://meridian.online/operations/parse-nested-values).

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

Page: <https://meridian.online/operations/parse-nested-values/work-with-list-values>

## Status

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.

## What other tools call it

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

- Azure Data Factory: [`Array functions`](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-array-functions) (read 2026-09-27)
- Azure Data Factory: [`Collection functions`](https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions) (read 2026-09-27)
- Apache DataFusion: [`Array Functions`](https://datafusion.apache.org/user-guide/sql/scalar_functions.html) (read 2026-09-27)
- dbt-utils: [`Array functions`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros) (read 2026-09-27)
- DuckDB: [`Array Functions (family)`](https://duckdb.org/docs/current/sql/functions/array) (read 2026-09-27)
- DuckDB: [`List Functions (family)`](https://duckdb.org/docs/current/sql/functions/list) (read 2026-09-27)
- Duckle: [`Array Distinct`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`Array Length`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`Contains`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`Element At`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Ibis: [`ArrayAll`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayAny`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayConcat`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayContains`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayDistinct`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayFilter`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayFlatten`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayIndex`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayIntersect`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayLength`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayMap`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayMax`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayMean`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayMin`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayMode`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayPosition`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayRemove`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayRepeat`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArraySlice`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArraySort`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayStringJoin`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27)
- Ibis: [`ArraySum`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayUnion`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- Ibis: [`ArrayZip`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/arrays.py) (read 2026-09-27)
- OpenRefine: [`Array functions`](https://openrefine.org/docs/manual/grelfunctions#array-functions) (read 2026-09-27)
- Polars: [`Expr.arr (group)`](https://docs.pola.rs/api/python/stable/reference/expressions/array.html) (read 2026-09-27)
- Polars: [`Expr.list (group)`](https://docs.pola.rs/api/python/stable/reference/expressions/list.html) (read 2026-09-27)
- Polars: [`SQL functions: Array (group)`](https://docs.pola.rs/api/python/stable/reference/sql/functions/array.html) (read 2026-09-27)
- Power Query M: [`List functions`](https://learn.microsoft.com/en-us/powerquery-m/list-functions) (read 2026-09-27)