Scalar Functions — Array / Set

Array and set functions operate on KQL dynamic arrays. This category has the narrowest cross-dialect support — most functions are limited to ClickHouse and DuckDB, with a handful also available in PostgreSQL. SQLite and MySQL have no array type and cannot support these functions.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Function	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
`array_length()`	❌	❌	✅	✅	✅	Postgres: `array_length(arr, 1)`. DuckDB: `json_array_length` for JSON-literal args, else `array_length`
`array_concat()`	❌	❌	✅	✅	✅	Postgres folds binary `array_cat`; DuckDB: `list_concat`
`array_index_of()`	❌	❌	✅	❌	✅	ClickHouse: `indexOf - 1` (0-based); DuckDB: `array_indexof`
`array_slice()`	❌	❌	✅	✅	✅	DuckDB: `array_slice` (0-based, like KQL)
`array_split()`	❌	❌	⚠️	❌	⚠️	Literal indices → full N-way split; ClickHouse falls back to a single split index with warning, DuckDB requires a literal indices array (column-ref unsupported)
`array_reverse()`	❌	❌	✅	❌	✅	DuckDB: `array_reverse`
`array_rotate_left()`	❌	❌	✅	❌	✅	DuckDB: `list_slice` + `list_concat` (no native rotate)
`array_rotate_right()`	❌	❌	✅	❌	✅	DuckDB: `list_slice` + `list_concat` (no native rotate)
`array_sort_asc()` / `array_sort_desc()`	❌	❌	✅	❌	✅	ClickHouse: `arraySort`/`arrayReverseSort`; DuckDB: `list_sort`/`list_reverse_sort`. Single-array form only (multi-array/nulls_last rejected)
`array_sum()`	❌	❌	✅	❌	✅	ClickHouse: `arraySum()`; DuckDB: `array_aggregate(arr, 'sum')`
`array_iif()` / `array_iff()`	❌	❌	✅	❌	✅	ClickHouse: `arrayMap` with `if()` lambda; DuckDB: `list_transform` over `list_zip`
`set_has_element()`	❌	❌	✅	✅	✅	Postgres: `= ANY`; DuckDB: `array_contains`
`set_intersect()`	❌	❌	✅	❌	✅	ClickHouse: `arrayIntersect()`; DuckDB: `array_intersect`
`set_union()`	❌	❌	✅	❌	✅	ClickHouse: `arrayConcat` + `arrayDistinct`; DuckDB: `list_distinct(array_cat(...))`
`set_difference()`	❌	❌	✅	❌	✅	ClickHouse: `arrayFilter` lambda; DuckDB: `list_filter` lambda
`zip()`	❌	❌	✅	❌	✅	ClickHouse: `arrayZip()`; DuckDB: `list_zip`
`series_sum()`	❌	❌	✅	❌	✅	ClickHouse: `arraySum()`; DuckDB: `array_aggregate(arr, 'sum')`
`series_add()`	❌	❌	✅	❌	✅	ClickHouse: `arrayMap (a,b) -> a+b`; DuckDB: `list_transform` over `list_zip`
`series_subtract()`	❌	❌	✅	❌	✅	ClickHouse: `arrayMap (a,b) -> a-b`; DuckDB: `list_transform` over `list_zip`
`series_multiply()`	❌	❌	✅	❌	✅	ClickHouse: `arrayMap (a,b) -> a*b`; DuckDB: `list_transform` over `list_zip`
`series_divide()`	❌	❌	✅	❌	✅	ClickHouse: `arrayMap (a,b) -> a/b`; DuckDB: `list_transform` over `list_zip`
`series_fill_const()`	❌	❌	✅	❌	✅	Element-wise null-coalesce (default fill `0`). ClickHouse: `arrayMap` + `if(isNull(...))`; DuckDB: `list_transform` + `COALESCE`
`series_stats()`	❌	❌	⚠️	❌	⚠️	Returns separate values, not a dynamic bag. ClickHouse falls back to `arrayAvg()`, DuckDB to `list_avg()`, both with warning — use per-stat aggregates directly
`jaccard_index()`	❌	❌	✅	❌	✅	`\|intersect\|` / `\|union\|` via intersect + distinct primitives (ClickHouse `arrayIntersect`/`arrayDistinct`, DuckDB `array_intersect`/`list_distinct`)

note

If you need array operations and your pipeline targets SQLite or MySQL, consider restructuring your query to use scalar string operations or JSON extraction instead. For ClickHouse targets, the full array and series function set is available natively.