Spark Scala Collect List and Set
collect_list and collect_set are aggregate functions that gather the values in each group into an array. collect_list keeps every value, including duplicates; collect_set keeps only the distinct ones. array_agg is a SQL alias for collect_list. They're the go-to tools when you want to roll many rows up into a single row that holds all their values.
Gathering values with collect_list
collect_list takes a column — either by name or as a Column — and returns an array containing every value it sees in the group, duplicates and all:
def collect_list(columnName: String): Columndef collect_list(e: Column): ColumnA typical use is rolling each customer's individual order rows up into one array of products:
val df = Seq(
("Alice", "Keyboard"),
("Alice", "Mouse"),
("Alice", "Keyboard"),
("Bob", "Monitor"),
("Bob", "Cable"),
("Carol", "Laptop"),
).toDF("customer", "product")
val df2 = df
.groupBy("customer")
.agg(collect_list("product").as("products"))
.orderBy("customer")
df2.show(false)
// +--------+---------------------------+
// |customer|products |
// +--------+---------------------------+
// |Alice |[Keyboard, Mouse, Keyboard]|
// |Bob |[Monitor, Cable] |
// |Carol |[Laptop] |
// +--------+---------------------------+
Notice that Alice's array contains Keyboard twice — collect_list preserves every occurrence. The order of elements within each array is not guaranteed; it reflects the order Spark happened to process the rows. If you need a specific order, apply array_sort or sort_array to the result, or use collect_list as a window function over an ordered window.
Keeping only distinct values with collect_set
collect_set works exactly like collect_list but removes duplicates, returning the set of distinct values in each group:
def collect_set(columnName: String): Columndef collect_set(e: Column): Columnval df = Seq(
("Alice", "Keyboard"),
("Alice", "Mouse"),
("Alice", "Keyboard"),
("Bob", "Monitor"),
("Bob", "Cable"),
("Carol", "Laptop"),
).toDF("customer", "product")
val df2 = df
.groupBy("customer")
.agg(collect_set("product").as("products"))
.orderBy("customer")
df2.show(false)
// +--------+-----------------+
// |customer|products |
// +--------+-----------------+
// |Alice |[Mouse, Keyboard]|
// |Bob |[Cable, Monitor] |
// |Carol |[Laptop] |
// +--------+-----------------+
Alice's duplicate Keyboard is collapsed to a single entry. As with collect_list, the ordering of the result is not guaranteed — a set has no inherent order, so don't rely on the positions you see here.
array_agg via expr()
array_agg is a SQL-standard alias for collect_list — it collects values into an array, keeping duplicates. It isn't exposed in org.apache.spark.sql.functions, so you call it through expr():
The array_agg function first appeared in version 3.3.0.
array_agg(expr) — via expr()Reach for it when you're porting SQL that already uses array_agg, or when the name reads more naturally in your pipeline. The result is identical to collect_list:
val df = Seq(
("Alice", "Keyboard"),
("Alice", "Mouse"),
("Alice", "Keyboard"),
("Bob", "Monitor"),
("Bob", "Cable"),
("Carol", "Laptop"),
).toDF("customer", "product")
val df2 = df
.groupBy("customer")
.agg(expr("array_agg(product)").as("products"))
.orderBy("customer")
df2.show(false)
// +--------+---------------------------+
// |customer|products |
// +--------+---------------------------+
// |Alice |[Keyboard, Mouse, Keyboard]|
// |Bob |[Monitor, Cable] |
// |Carol |[Laptop] |
// +--------+---------------------------+
How nulls are handled
Both functions skip null values entirely — nulls never appear in the resulting array. If every value in a group is null, you get an empty array rather than an array of nulls:
val df = Seq(
("Alice", Some("Keyboard")),
("Alice", None),
("Alice", Some("Mouse")),
("Bob", None),
("Bob", None),
("Carol", Some("Laptop")),
).toDF("customer", "product")
val df2 = df
.groupBy("customer")
.agg(
collect_list("product").as("list_products"),
collect_set("product").as("set_products"),
)
.orderBy("customer")
df2.show(false)
// +--------+-----------------+-----------------+
// |customer|list_products |set_products |
// +--------+-----------------+-----------------+
// |Alice |[Keyboard, Mouse]|[Mouse, Keyboard]|
// |Bob |[] |[] |
// |Carol |[Laptop] |[Laptop] |
// +--------+-----------------+-----------------+
Alice's None is dropped from both arrays, and Bob — whose only values were null — ends up with empty arrays. This is worth remembering when the array length matters: size on the collected column counts non-null values, not the total number of rows in the group.
Related Functions
To count rows or distinct values in a group instead of collecting them, see count and countDistinct. Once you have an array, array_distinct removes duplicates from an existing collect_list result, and array_sort puts the elements in a predictable order. To pick a single representative value from each group rather than all of them, see first and last.