Topic 8 of 28 · Advanced
Functions Reference: Complex Types, Aggregations & Windows
Arrays, maps, structs and JSON, plus aggregate and window functions for real pipelines.
Concept
Real data arrives nested: arrays of items, maps of attributes, JSON strings. Spark has built-in functions to explode, inspect and rebuild these types without UDFs.
Aggregates run inside groupBy().agg(...). The same functions become window functions when you add .over(Window...) — rows stay, and each gets the group result.
A Window spec is Window.partitionBy(...).orderBy(...), optionally with .rowsBetween(...). Ranking and offset functions require orderBy.
Cheat sheet
| Function | What it does | Example |
|---|---|---|
| F.explode(arr) | One row per item; drops null/empty arrays | F.explode('items') |
| F.explode_outer(arr) | Like explode but keeps null/empty as null | F.explode_outer('tags') |
| F.posexplode(arr) | Adds the position (pos, col) | F.posexplode('steps') |
| F.array_contains(arr, v) | True if the array has v | F.array_contains('skills', 'sql') |
| F.size(arr_or_map) | Number of elements | F.size('items') |
| F.flatten(arr_of_arr) | Array of arrays → one array | [[1,2],[3]] → [1,2,3] |
| F.array_distinct(arr) | Remove duplicate elements | [1,1,2] → [1,2] |
| F.map_keys / F.map_values | Keys / values of a map | F.map_keys('attrs') |
| F.from_json(c, schema) | JSON string → struct/array | F.from_json('raw', schema) |
| F.to_json(c) | Struct/array → JSON string | F.to_json(F.struct('*')) |
| F.struct(*cols) | Group columns into one nested column | F.struct('city', 'pin') |
| F.sum / F.avg | Total / mean | F.sum('amount') |
| F.count(c) / F.count('*') | Non-null count / all rows | F.count('email') |
| F.countDistinct(c) | Exact distinct count | F.countDistinct('user_id') |
| F.min / F.max | Smallest / largest | F.max('order_date') |
| F.first / F.last (ignorenulls) | First / last value in group order | F.last('status', True) |
| F.collect_list / F.collect_set | Values into array (with / without duplicates) | F.collect_set('city') |
| F.row_number().over(w) | 1, 2, 3 per partition | Latest record per key |
| F.rank() / F.dense_rank() | Rank with / without gaps | Top-N per group |
| F.lag(c, n) / F.lead(c, n) | Previous / next row value | F.lag('amount').over(w) |
Code (PySpark 3.5)
from pyspark.sql import functions as F, Window
from pyspark.sql.types import StructType, StructField, StringType, ArrayType
schema = StructType([
StructField("user", StringType()),
StructField("items", ArrayType(StringType())),
])
orders = (raw
.withColumn("data", F.from_json("payload", schema))
.select("order_id", "order_date", "amount", "data.user", "data.items"))
# Complex types
items = (orders
.select("order_id", "user", F.posexplode_outer("items").alias("pos", "item"))
.withColumn("is_sql_book", F.col("item").contains("sql")))
per_user = orders.groupBy("user").agg(
F.count("*").alias("orders"),
F.round(F.avg("amount"), 2).alias("avg_amount"),
F.array_distinct(F.flatten(F.collect_list("items"))).alias("all_items"),
F.countDistinct("order_date").alias("active_days"))
# Window functions: latest order + change vs previous
w = Window.partitionBy("user").orderBy(F.col("order_date").desc())
latest = (orders
.withColumn("rn", F.row_number().over(w))
.withColumn("rank", F.dense_rank().over(w))
.withColumn("prev_amount", F.lead("amount").over(w))
.filter("rn = 1"))
out = per_user.select("user", F.to_json(F.struct("orders", "avg_amount")).alias("summary_json"))Remember
- explode drops rows with null/empty arrays — use explode_outer to keep them.
- collect_set has no guaranteed order; sort with F.array_sort if order matters.
- Use row_number for dedup; dense_rank when ties should share a rank.
- countDistinct is exact but expensive; approx_count_distinct is faster on huge data.
Interview questions
How do you keep only the latest record per key?
row_number() over Window.partitionBy(key).orderBy(updated_at desc) and filter rn = 1.
How do you parse a JSON string column?
Define a schema and use F.from_json(col, schema), then select nested fields with dot notation.
groupBy aggregation vs window aggregation?
groupBy returns one row per group; a window keeps every row and attaches the group value to each.
How do you flatten an array of arrays?
F.flatten turns array<array<T>> into array<T>; add F.array_distinct to drop duplicates.
Lesson test
Take an 8-question test on “Functions Reference: Complex Types, Aggregations & Windows” — concepts, output prediction, bug spotting and interview questions.