collect_list / collect_set
Gather the values of a group into an array, with or without duplicates, and in which order.
On this page
Show code in
Every code block on the page follows this.
You will learn
- How to gather group values into an array
- Why the order of collect_list is not guaranteed, and how to get a stable order
- The difference between collect_list and collect_set
- Why collecting is one of the most memory-hungry aggregations
collect_list gathers a group's values into an array, keeping duplicates; collect_set keeps distinct values. Both skip nulls, and neither guarantees an order.What it does
It is the reverse of explode: many rows in, one array per group out.
Step by step
visits
| user | page |
|---|---|
| u1 | home |
| u1 | search |
| u1 | home |
| u2 | cart |
| u2 | null |
Output
| user | pages | distinct_pages |
|---|---|---|
| u1 | [home, search, home] | [home, search] |
| u2 | [cart] | [cart] |
The null page for u2 is skipped by both. collect_set drops the second "home".
Run the example
from pyspark.sql import functions as F result = (visits.groupBy("user").agg( F.collect_list("page").alias("pages"), F.collect_set("page").alias("distinct_pages"), F.size(F.collect_set("page")).alias("n_distinct"), ))
SELECT user, collect_list(page) AS pages, collect_set(page) AS distinct_pages, size(collect_set(page)) AS n_distinct FROM visits GROUP BY user
Switch to PySpark to edit and run this example in your browser.
Getting a reliable order
collect_list adds values in whatever order rows reach the aggregation, which depends on partitioning and shuffle timing. Sorting the DataFrame first does not fix that, because the groupBy shuffle reorders rows. Two reliable options:
- Collect structs whose first field is the sort key, then sort the array:
F.sort_array(F.collect_list(F.struct("ts", "page"))), and take.pagefrom the result. Structs sort by their fields in order. - Use a window ordered by time with a full frame, and
collect_listover it, then deduplicate to one row per group.
array_agg, the SQL-standard name for collect_list, in recent versions. It has the same ordering caveat; the struct-and-sort trick works everywhere.Under the hood: memory
Most aggregations shrink data before the shuffle: a sum is one number per group. collect_list cannot; the partial result is the whole list. Every value is shuffled, and the complete array for a group must fit in one task's memory. A single user with 50 million events produces one 50-million-element array and, often, an out-of-memory error. Collect only what you need, cap it with slice, or rethink whether you need the array at all.
Common mistakes
Relying on collect_list order
Sorting before groupBy and expecting it to stick
Collecting huge groups
Key takeaways
- collect_list keeps duplicates; collect_set removes them. Both skip nulls.
- Neither guarantees order, even after an orderBy.
- Collect structs and sort_array them for a deterministic order.
- Collecting shuffles every value and holds each group's array in memory.
Check yourself
3 questions1. How do you get a guaranteed time-ordered list of pages per user?
Show the answer
sort_array(collect_list(struct("ts", "page"))). Structs sort by their first field, so sorting the collected structs orders by ts. A prior orderBy is lost in the shuffle.
2. A group has values a, b, null, a. What does collect_set return (in some order)?
Show the answer
[a, b]. collect_set removes duplicates and both collect functions skip nulls.
3. Why is collect_list risky on skewed data?
Show the answer
The full array of a huge group must fit in one task's memory. Partial aggregation cannot shrink a list, so a hot key builds one enormous array.
Practice it
Interview problems that use collect_list / collect_set: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: functions.collect_list · functions.collect_set