Scalar Functions — Array / Set

Array and set functions operate on KQL dynamic arrays. This category has the narrowest cross-dialect support — most functions are ClickHouse-only, 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	Notes
array_length()	❌	❌	✅	✅	Postgres: array_length(arr, 1)
array_concat()	❌	❌	✅	✅
array_index_of()	❌	❌	✅	❌
array_slice()	❌	❌	✅	✅
array_split()	❌	❌	⚠️	❌	ClickHouse: two arraySlice calls for single index, with warning
array_reverse()	❌	❌	✅	❌
array_rotate_left()	❌	❌	✅	❌
array_rotate_right()	❌	❌	✅	❌
array_sum()	❌	❌	✅	❌	arraySum()
array_iif() / array_iff()	❌	❌	✅	❌	ClickHouse: arrayMap with if() lambda
set_has_element()	❌	❌	✅	✅
set_intersect()	❌	❌	✅	❌	arrayIntersect()
set_union()	❌	❌	✅	❌	arrayConcat + arrayDistinct
set_difference()	❌	❌	✅	❌	arrayFilter with lambda
zip()	❌	❌	✅	❌	arrayZip()
series_sum()	❌	❌	✅	❌	arraySum()
series_add()	❌	❌	✅	❌	arrayMap (a,b) -> a+b
series_subtract()	❌	❌	✅	❌	arrayMap (a,b) -> a-b
series_multiply()	❌	❌	✅	❌	arrayMap (a,b) -> a*b
series_stats()	❌	❌	⚠️	❌	ClickHouse: falls back to arrayAvg() with warning — use arrayMin/arrayMax/arrayStddevPop directly
jaccard_index()	❌	❌	❌	❌	No SQL equivalent

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.