Nested data and JSON: a practical Spark & Data Eng guide

Introduction

Real-world data rarely arrives in flat, tabular form. APIs return nested JSON payloads, logs contain embedded objects, and event streams carry arrays of transactions within a single record. Working effectively with nested data and JSON is a core skill that separates production-grade data engineers from those still wrestling with malformed extracts. In this guide, we'll walk through the practical patterns you'll encounter in Spark workloads: how to explode arrays, navigate struct fields, parse JSON at scale, and flatten complex schemas for downstream analytics.

This isn't theoretical. We'll work through real scenarios you've likely hit in e-commerce pipelines, API ingestion layers, and event processing systems.

Understanding the Problem: Why Nested Data Matters

When you load JSON or event data into Spark, the schema often preserves hierarchy. A user event might look like this:

{
  "user_id": 12345,
  "event_type": "purchase",
  "timestamp": "2024-01-15T10:30:00Z",
  "purchase": {
    "order_id": "ORD-789",
    "items": [
      {"sku": "SHOE-001", "qty": 2, "price": 49.99},
      {"sku": "SOCK-012", "qty": 5, "price": 3.99}
    ],
    "total": 109.97
  }
}

The challenge: your analytics warehouse expects one row per item, not nested arrays buried inside struct columns. Your BI tool chokes on anything deeper than one level. Your SQL analysts expect flat tables. This is where the techniques in nested data and JSON processing become essential.

Explode for Arrays: Unwinding Nested Collections

The explode function is your first tool. It takes an array column and generates one output row for each element. In the purchase example above, the items array has two elements; explode will give you two rows.

from pyspark.sql import SparkSession
from pyspark.sql.functions import explode, col

spark = SparkSession.builder.appName("nested_demo").getOrCreate()

# Load sample JSON
df = spark.read.json("s3://my-bucket/events.json")

# Schema will infer: user_id, event_type, timestamp, purchase (struct)
# purchase contains: order_id, items (array of structs), total

# Explode the items array
exploded_df = df.select(
    col("user_id"),
    col("event_type"),
    col("purchase.order_id").alias("order_id"),
    col("purchase.total").alias("order_total"),
    explode(col("purchase.items")).alias("item")
)

# Now access nested fields within the exploded item
items_df = exploded_df.select(
    col("user_id"),
    col("order_id"),
    col("order_total"),
    col("item.sku").alias("sku"),
    col("item.qty").alias("quantity"),
    col("item.price").alias("unit_price")
)

items_df.show()

Result: two rows, one per item, with all nested fields at the top level. This is the foundation of flattening.

Real-world tip: if you have multiple nested arrays (like items and discounts), use explode_outer or carefully join the exploded datasets so you don't lose null rows or accidentally cartesian-product your data. We've seen pipelines mysteriously inflate row counts by 10x because someone exploded two arrays without thinking about cardinality.

Struct Fields and Dot Notation: Navigating Deep Hierarchies

For non-array nested data, you don't need explode. Just use dot notation to access struct fields. Spark understands the schema hierarchy and will push down access efficiently.

# Accessing nested struct fields
user_purchase_df = df.select(
    col("user_id"),
    col("purchase.order_id"),
    col("purchase.total"),
    col("purchase.order_id").alias("order_id")
)

# You can also use bracket notation
alt_df = df.select(
    col("user_id"),
    col("purchase")["order_id"].alias("order_id"),
    col("purchase")["total"].alias("total")
)

# Or raw SQL
df.createOrReplaceTempView("events")
sql_df = spark.sql("""
SELECT
  user_id,
  purchase.order_id,
  purchase.total
FROM events
""")

The dot notation approach is cleaner and works seamlessly with catalyst optimizations. Most teams standardize on this pattern in their data pipelines.

If your struct is deeply nested (three, four levels deep), you can chain them: col("level1.level2.level3.field"). The schema must match exactly, or you'll get a AnalysisException. Always validate schema before production ingestion runs.

Parsing JSON Strings with from_json and Schemas

Sometimes your data arrives as a JSON string inside a regular column, not as an already-parsed struct. This happens frequently when you're reading from Kafka, CloudPub/Sub, or legacy APIs that serialize everything as text. You need from_json to parse the string into a struct.

from pyspark.sql.functions import from_json, schema_of_json
from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DoubleType, ArrayType

# Option 1: Infer schema from a sample JSON string
sample_json = """
{
  "order_id": "ORD-789",
  "items": [
    {"sku": "SHOE-001", "qty": 2, "price": 49.99}
  ],
  "total": 109.97
}
"""

inferred_schema = schema_of_json(sample_json)

# Option 2: Define schema explicitly (recommended for production)
purchase_schema = StructType([
    StructField("order_id", StringType(), True),
    StructField("items", ArrayType(StructType([
        StructField("sku", StringType(), True),
        StructField("qty", IntegerType(), True),
        StructField("price", DoubleType(), True)
    ])), True),
    StructField("total", DoubleType(), True)
])

# Parse the JSON string column
parsed_df = df.withColumn(
    "purchase_parsed",
    from_json(col("purchase_json_str"), purchase_schema)
)

# Now you can access nested fields as before
parsed_df = parsed_df.select(
    col("user_id"),
    col("purchase_parsed.order_id"),
    explode(col("purchase_parsed.items")).alias("item")
)

Always define your schema explicitly in production code. Inferring schemas can fail silently or behave inconsistently across data batches. We've seen incidents where schema inference succeeded on day one but crashed when a new field appeared in the JSON payload on day 15. Define schemas as part of your codebase, version them, and test schema evolution.

Flattening for Analytics Tables: The End Goal

The ultimate goal is usually a flattened, analytics-ready table. This means: one row per "grain" (e.g., per line item), all fields at the top level, no nested structures, and optimized for query performance.

from pyspark.sql.functions import col, explode, when, coalesce

# Starting from the raw nested data
raw_df = spark.read.json("s3://my-bucket/events.json")

# Step 1: Parse any JSON strings
if "purchase_json_str" in raw_df.columns:
    raw_df = raw_df.withColumn(
        "purchase_parsed",
        from_json(col("purchase_json_str"), purchase_schema)
    )
else:
    raw_df = raw_df.withColumn("purchase_parsed", col("purchase"))

# Step 2: Explode arrays
exploded_df = raw_df.select(
    col("user_id"),
    col("event_type"),
    col("timestamp"),
    col("purchase_parsed.order_id").alias("order_id"),
    col("purchase_parsed.total").alias("order_total"),
    explode(col("purchase_parsed.items")).alias("item")
)

# Step 3: Flatten struct fields
flattened_df = exploded_df.select(
    col("user_id"),
    col("event_type"),
    col("timestamp"),
    col("order_id"),
    col("order_total"),
    col("item.sku").alias("sku"),
    col("item.qty").alias("quantity"),
    col("item.price").alias("unit_price"),
    (col("item.qty") * col("item.price")).alias("line_total")
)

# Step 4: Write to analytics warehouse
flattened_df.write \
    .mode("overwrite") \
    .format("parquet") \
    .option("compression", "snappy") \
    .save("s3://my-bucket/analytics/line_items")

This pattern scales well. We've used it to process billions of events daily. Key points:

• Each step is chainable and debuggable.
• Intermediate DataFrames let you inspect schema and data at each stage.
• Parquet is efficient for downstream queries and partitioning.
• Derived columns (like line_total) are calculated once, not on every query.

Common Mistakes

Mistake 1: Exploding multiple arrays without understanding cardinality. If your data has items (5 elements) and discounts (3 elements), exploding both creates 15 rows per input row. Validate expected output counts and join carefully.

Mistake 2: Ignoring nulls in nested data. A user might have a purchase field that's null. Accessing col("purchase.order_id") will return null, but explode(col("purchase.items")) will drop the entire row. Use explode_outer if you need to preserve nulls.

Mistake 3: Hardcoding schema assumptions. Production JSON often evolves. New fields appear, types change. Always version your schemas, use schema validation frameworks, and test with real data samples before deploying to ETL pipelines.

Mistake 4: Over-nesting flattened data. If you flatten once, you're done. Don't re-nest fields for aesthetic reasons. Flat tables are faster to query and easier to reason about.

Key Takeaways

• Use explode to convert array columns into multiple rows; understand cardinality to avoid row inflation.
• Access struct fields with dot notation: col("parent.child.field") is cleaner and more efficient than alternatives.
• Parse JSON strings with from_json and always define explicit schemas in production code.
• Flatten nested data in stages: parse → explode → extract fields → derive columns → write. Each step is testable.
• Nulls in nested structures require careful handling; use explode_outer and conditional logic to preserve rows when needed.
• Target a fully flattened analytics schema: one row per grain, all fields at top level, ready for SQL and BI tools.