Spark Array Functions & collect_list vs collect_set

Spark’s higher-order array functions, plus the collect_* family for rolling multiple rows into an array.

Common

• NVL(x, default) — default value for null
• array_contains(arr, val) — array membership check, returns Bool
• collect_list() / collect_set() — roll multiple rows into an array
• to_json() — struct → JSON
• transform(arr, x -> x*2) — apply an expression to every element
• filter(arr, x -> x > 0) — keep elements matching a predicate
• exists(arr, x -> x > 0) — true if any element matches → equivalent to Python’s any()
• forall(arr, x -> ...) — true if all elements match → equivalent to Python’s all()

collect_list() vs collect_set()

Data:

customer_id	item_bought
101	laptop
101	mouse
101	mouse
102	monitor
102	hdmi_cable

SELECT
    customer_id,
    collect_list(item_bought) AS all_items,
    collect_set(item_bought) AS unique_items
FROM store_sales
GROUP BY customer_id;

Result:

customer_id	all_items (collect_list)	unique_items (collect_set)
101	["laptop", "mouse", "mouse"]	["laptop", "mouse"]
102	["monitor", "hdmi_cable"]	["monitor", "hdmi_cable"]

The difference: collect_list keeps duplicates, collect_set de-dupes.

---

References

• Spark SQL Built-in Functions — https://spark.apache.org/docs/latest/api/sql/

Related: back to the Cross-Engine DB Query overview; see also Spark data types and the ClickHouse counterpart, ClickHouse array and conditional functions.