Functions/Lists & arrays/filter
Lists & arrays Scalar

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.

Alias of list_filter()

Arguments & returns

filter(list, lambda(x))

Scalar
Overload link listlambda(x) Returns
#01 ANY[]LAMBDA ANY[]

Example

filter example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

Used in larger rewrites 5

These examples use filter as one part of a larger SQL translation.

PostgreSQL

SELECT CORR(a, b) FILTER(WHERE c > 0)
In Haybarn
SELECT CORR(a, b) FILTER(WHERE c > 0)

SQLGlot example ↗

Apache Hivecollect_list()

COLLECT_LIST(x)
In Haybarn
ARRAY_AGG(x) FILTER(WHERE x IS NOT NULL)

SQLGlot example ↗

Apache Sparkcollect_set()

SELECT COLLECT_SET(sample_col) FROM sample_table
In Haybarn
SELECT LIST(DISTINCT sample_col) FILTER(WHERE NOT sample_col IS NULL) FROM sample_table

SQLGlot example ↗

BigQueryarray_agg()

SELECT ARRAY_AGG(x IGNORE NULLS) AS x
In Haybarn
SELECT ARRAY_AGG(x) FILTER(WHERE x IS NOT NULL) AS x

SQLGlot example ↗

Snowflakearray_agg()

SELECT ARRAY_AGG(DISTINCT a)
In Haybarn
SELECT ARRAY_AGG(DISTINCT a) FILTER(WHERE a IS NOT NULL)

SQLGlot example ↗