Explode

Expand array elements into rows while handling empty collections deliberately.

Step 1 of 6 · Learn

Expand arrays

The setup makes two quantities per order, except cancelled order 1003 has an empty array. explode creates one row per element, repeating order_id. It drops empty and null arrays; explode_outer instead preserves those source rows with a null element.

Lesson reference: PySpark and Scala

Expand arrays

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import (
    col, lit, array, when, explode, explode_outer, posexplode,
    create_map, to_json, struct, from_json, get_json_object, count, row_number
)

array_orders = orders.select(
    "order_id", when(col("status") == "Cancelled", array().cast("array<int>"))
    .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities")
)

result = array_orders.select(
    col("order_id"), explode(col("quantities")).alias("quantity")
).orderBy("order_id", "quantity")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val array_orders = orders.select(
  col("order_id"), when(col("status") === "Cancelled", array().cast("array<int>"))
  .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities")
)

val result = array_orders.select(
    col("order_id"), explode(col("quantities")).alias("quantity")
).orderBy("order_id", "quantity")
result.show(false)

Keep element positions

posexplode returns both the zero-based position and the element value. Positions restart for each source array. Its indexing differs from element_at, whose first index is 1. Empty arrays produce no rows in this example.

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import (
    col, lit, array, when, explode, explode_outer, posexplode,
    create_map, to_json, struct, from_json, get_json_object, count, row_number
)

array_orders = orders.select(
    "order_id", when(col("status") == "Cancelled", array().cast("array<int>"))
    .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities")
)

result = array_orders.select(
    "order_id", posexplode("quantities").alias("position", "quantity")
).orderBy("order_id", "position")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val array_orders = orders.select(
  col("order_id"), when(col("status") === "Cancelled", array().cast("array<int>"))
  .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities")
)

val result = array_orders.select(
  col("order_id"), posexplode(col("quantities")).as(Seq("position", "quantity"))
).orderBy("order_id", "position")
result.show(false)

Filter expanded rows

Once an array is expanded, a DataFrame filter removes individual element rows. Here quantities at least 2 remain. An order disappears from the output if none of its expanded elements pass; this differs from keeping an empty array in the source row.

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import (
    col, lit, array, when, explode, explode_outer, posexplode,
    create_map, to_json, struct, from_json, get_json_object, count, row_number
)

array_orders = orders.select(
    "order_id", when(col("status") == "Cancelled", array().cast("array<int>"))
    .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities")
)

result = array_orders.select(
    col("order_id"), explode(col("quantities")).alias("quantity")
).filter(col("quantity") >= 2).orderBy("order_id", "quantity")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val array_orders = orders.select(
  col("order_id"), when(col("status") === "Cancelled", array().cast("array<int>"))
  .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities")
)

val result = array_orders.select(
    col("order_id"), explode(col("quantities")).alias("quantity")
).filter(col("quantity") >= 2).orderBy("order_id", "quantity")
result.show(false)

example.pyPySpark

from pyspark.sql import Window from pyspark.sql.functions import ( col, lit, array, when, explode, explode_outer, posexplode, create_map, to_json, struct, from_json, get_json_object, count, row_number ) array_orders = orders.select( "order_id", when(col("status") == "Cancelled", array().cast("array<int>")) .otherwise(array(col("item_count"), col("item_count") + 1)).alias("quantities") ) result = array_orders.select( col("order_id"), explode(col("quantities")).alias("quantity") ).orderBy("order_id", "quantity") result.show(truncate=False)

Array rowOne row per elementExpanded rows

Source array_orders6 rows

order_id	quantities
1001	[2,3]
1002	[3,4]
1003	[]
1004	[4,5]
1005	[1,2]
1006	[2,3]

Expand arrays

Result10 rows

order_id	quantity
1001	2
1001	3
1002	3
1002	4
1004	4
1004	5
1005	1
1005	2
1006	2
1006	3

Each nonempty source array creates two rows. The empty array for order 1003 produces no rows with explode.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.