Good engineers know 3 min read · Arrays 2 practice problems ↓

Array functions

array_contains, size, element_at, sort_array and the set functions, without exploding.

On this page

Show code in

Every code block on the page follows this.

You will learn

How to test, measure and pick from arrays without exploding them
How to sort, de-duplicate and join arrays
The set functions for comparing two arrays
How higher-order functions transform every element in place

Read first

explode · 3 min read
collect_list / collect_set · 3 min read

Comfortable with these? Read on.

TL;DR Most array questions do not need explode. array_contains, size, element_at, sort_array, array_distinct and the set functions work on the whole array in one row, which avoids multiplying rows and a later groupBy to put them back together.

Why not explode everything?

Exploding a column of arrays turns one row into many, and answering "does this cart contain milk?" then needs a groupBy to collapse it again: two steps, a shuffle, and empty or null arrays silently disappear along the way. Array functions answer the same question on the row itself.

Run the example

PySparkSpark SQL

from pyspark.sql import functions as F
result = carts.select(
    "user",
    F.size("items").alias("n_items"),
    F.array_contains("items", "milk").alias("has_milk"),
    F.element_at("items", 1).alias("first_item"),
    F.sort_array(F.array_distinct("items")).alias("unique_sorted"),
    F.array_join("items", ", ").alias("as_text"),
)

SELECT user,
       size(items)                          AS n_items,
       array_contains(items, 'milk')        AS has_milk,
       element_at(items, 1)                 AS first_item,
       sort_array(array_distinct(items))    AS unique_sorted,
       array_join(items, ', ')              AS as_text
FROM carts

Switch to PySpark to edit and run this example in your browser.

Look at the last two rows. Cy's empty cart has size 0 and no first item. Di's cart is null, and size returns -1 for it in classic Spark (null when ANSI mode is on, the Spark 4.0 default). Filtering with size(items) > 0 handles both.

The toolkit

Question	Function	Notes
How many elements?	`size(arr)`	-1 or null for a null array
Contains a value?	`array_contains(arr, v)`	null if the array is null
Element by position	`element_at(arr, n)`, `arr[i]`	element_at is 1-based and negative n counts from the end; brackets are 0-based
Sort	`sort_array(arr, asc=False)`, `array_sort`	sort_array puts nulls first ascending
Remove duplicates	`array_distinct(arr)`	Keeps first-seen order
Into a string	`array_join(arr, sep)`	Skips nulls unless you pass a replacement
Min / max element	`array_min`, `array_max`
Part of an array	`slice(arr, start, length)`	1-based
Position of a value	`array_position(arr, v)`	1-based, 0 if absent
Flatten nested arrays	`flatten(arr_of_arrays)`

Comparing two arrays

Function	a = [1,2,3], b = [2,3,4]
array_union(a, b)	[1, 2, 3, 4]
array_intersect(a, b)	[2, 3]
array_except(a, b)	[1]
arrays_overlap(a, b)	true

They answer questions such as "which permissions did this user lose?" (array_except(old, new)) without exploding either side. All of them return de-duplicated results.

Higher-order functions

To change every element, Spark has functions that take a lambda and run it inside the JVM, element by element:

PySparkSpark SQL · transform, filter, exists, aggregate (Spark 3.1+ in Python)

carts.select(
    F.transform("items", lambda x: F.upper(x)).alias("upper_items"),
    F.filter("items", lambda x: x != "milk").alias("no_milk"),
    F.exists("items", lambda x: x.startswith("b")).alias("has_b_item"),
    F.aggregate("prices", F.lit(0.0), lambda acc, x: acc + x).alias("cart_total"),
)

SELECT transform(items, x -> upper(x))              AS upper_items,
       filter(items, x -> x != 'milk')                AS no_milk,
       exists(items, x -> x LIKE 'b%')                AS has_b_item,
       aggregate(prices, 0D, (acc, x) -> acc + x)      AS cart_total
FROM carts

Why this matters: the Python lambda is not run by Python. PySpark calls it once to build a Spark expression (upper(x)), which the JVM then evaluates for every element. That is why it is as fast as any built-in, unlike a Python UDF. The in-browser engine does not run higher-order functions yet, so this example is read-only.

Common mistakes

Exploding just to filter or count

array_contains and size work in place, with no extra rows and no shuffle.

Mixing up 0-based and 1-based indexes

arr[0] and element_at(arr, 1) are the same element.

Treating size(null) as 0

It is -1 (or null in ANSI mode). Handle null arrays explicitly.

Writing a Python UDF to map over an array

transform does it natively and much faster.

Key takeaways

Use array functions in place instead of explode + groupBy.
element_at and slice are 1-based; brackets are 0-based.
array_union, array_intersect and array_except compare arrays directly.
transform, filter and aggregate run lambdas in the JVM, as fast as built-ins.

Check yourself

3 questions

1. What is element_at(array("a","b","c"), -1)?

Show the answer

"c". A negative index counts from the end.

2. Which returns the elements of a that are not in b?

Show the answer

array_except(a, b). array_except is set difference.

3. Why is F.transform faster than a Python UDF doing the same thing?

Show the answer

The lambda becomes a Spark expression evaluated in the JVM. No rows are sent to a Python process.

Practice it

Interview problems that use this: write the PySpark, run it, and get graded on hidden tests.

Solve: Sorted Distinct Purchase List →

Keep going

Up next · lesson 25 of 35 · 4 min read
JSON and structs
Parse JSON strings with from_json, reach into nested fields, and flatten structs.

Previous: regexp_extract

Primary sources: Collection functions · functions.transform