Functions/Lists & arrays/list_zip
Lists & arrays Scalar

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.

Arguments & returns

list_zip(…)

Scalar
Overload link … Varargs Returns
#01 ANY STRUCT[]

… accepts a variable number of arguments of the type shown.

Example

2 more ↓
list_zip example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

More examples

list_zip example 2
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.
list_zip example 3
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

Used in larger rewrites 1

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

Snowflakearray_except()

SELECT ARRAY_EXCEPT([1, 2, 3], [2])
In DuckDB
SELECT CASE WHEN [1, 2, 3] IS NULL OR [2] IS NULL THEN NULL ELSE LIST_TRANSFORM(LIST_FILTER(LIST_ZIP([1, 2, 3], GENERATE_SERIES(1, LENGTH([1, 2, 3]))), pair -> (LENGTH(LIST_FILTER([1, 2, 3][1:pair[2]], e -> e IS NOT DISTINCT FROM pair[1])) > LENGTH(LIST_FILTER([2], e -> e IS NOT DISTINCT FROM pair[1])))), pair -> pair[1]) END

SQLGlot example ↗