Functions/Lists & arrays/list_count
Lists & arrays Macro

list_count()

Arguments & returns

list_count(l)

Macro
Overload link l Returns
#01 unknown unknown

Definition

list_aggr(l, 'count')

In other engines

Used in larger rewrites 2

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

BigQueryunnest()

SELECT * FROM a WHERE b IN UNNEST([1, 2, 3])
In DuckDB
SELECT * FROM a WHERE CASE WHEN [1, 2, 3] IS NULL OR ARRAY_LENGTH([1, 2, 3]) = 0 THEN FALSE WHEN ARRAY_CONTAINS([1, 2, 3], b) THEN TRUE WHEN b IS NULL OR ARRAY_LENGTH([1, 2, 3]) <> LIST_COUNT([1, 2, 3]) THEN NULL ELSE FALSE END

SQLGlot example ↗

Snowflakearrays_overlap()

SELECT ARRAYS_OVERLAP(col1, col2)
In DuckDB
SELECT (col1 && col2) OR (ARRAY_LENGTH(col1) <> LIST_COUNT(col1) AND ARRAY_LENGTH(col2) <> LIST_COUNT(col2))

SQLGlot example ↗