Spark JSON and Nested Data

JSON files describe records with fields inside fields and lists inside lists. Web services, mobile apps, and event trackers produce this format all day long. Spark reads JSON into nested columns and gives you simple tools to open them up into flat tables.

The Layman View

Think of a parcel that contains smaller boxes, and each small box holds items. Nested data follows that pattern. A flat table is a shelf where every item sits in the open. Spark helps you unpack the boxes onto the shelf.

Sample JSON File

{"id": 1, "name": "Asha", "address": {"city": "Pune", "zip": "411001"}, "skills": ["spark", "sql"]}
{"id": 2, "name": "Ben",  "address": {"city": "Delhi", "zip": "110001"}, "skills": ["python"]}

Each line holds one complete record. Spark calls this the JSON Lines layout and reads it by default.

Reading JSON

df = spark.read.json("people.json")
df.printSchema()

root
 |-- address: struct
 |    |-- city: string
 |    |-- zip: string
 |-- id: long
 |-- name: string
 |-- skills: array
 |    |-- element: string

A file that stores one large JSON array or pretty-printed records across many lines needs the multiLine option.

df = spark.read.option("multiLine", True).json("big_array.json")

Reaching Inside a Struct

Use dot notation to read a field inside a struct column.

from pyspark.sql import functions as F

df.select("name", "address.city", F.col("address.zip").alias("zip")).show()

name  city   zip
Asha  Pune   411001
Ben   Delhi  110001

Flattening Arrays with explode

The explode function turns each item of an array into its own row. Other columns repeat beside each item.

df.select("name", F.explode("skills").alias("skill")).show()

Before explode                After explode

name  skills                  name  skill
Asha  [spark, sql]            Asha  spark
Ben   [python]         --->   Asha  sql
                              Ben   python

Rows with an empty or null array vanish from the result. Use explode_outer to keep those rows with a null value.

Counting Items Without Exploding

df.select("name", F.size("skills").alias("skill_count"),
          F.array_contains("skills", "spark").alias("knows_spark")).show()

Parsing JSON Stored in a Text Column

Many tables keep raw JSON inside a string column. The from_json function converts the text into a struct when you supply a schema.

from pyspark.sql.types import StructType, StructField, StringType, IntegerType

raw = spark.createDataFrame([('{"device":"phone","clicks":7}',)], ["payload"])
payload_schema = StructType([
    StructField("device", StringType()),
    StructField("clicks", IntegerType())
])

parsed = raw.withColumn("data", F.from_json("payload", payload_schema))
parsed.select("data.device", "data.clicks").show()

Building Nested Output

The struct and to_json functions reverse the process.

out = df.select("id", F.to_json(F.struct("name", "address")).alias("json_text"))
out.show(truncate=False)

Writing Flat Results

flat = (df.select("id", "name", "address.city", F.explode("skills").alias("skill")))
flat.write.mode("overwrite").parquet("/data/people_flat")

Good Habits

  • Supply a schema for large JSON inputs. Inference scans the whole dataset.
  • Flatten data once, store it as Parquet, and run later queries on the flat copy.
  • Check for null structs before you read their fields.
  • Keep an eye on explode. Each exploded array multiplies the row count.

Nested Arrays of Structs

Orders often hold a list of item records. Each item carries its own fields. Explode the array first and read the struct fields after.

orders = spark.read.json("orders.json")
# {"order_id": 10, "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}]}

lines = (orders
    .select("order_id", F.explode("items").alias("item"))
    .select("order_id", "item.sku", "item.qty"))
lines.show()

order_id  items                               order_id  sku  qty
10        [{A1,2}, {B7,1}]          --->      10        A1   2
                                              10        B7   1

Each order now spreads across one row per item. A groupBy on sku gives total quantities per product.

Dealing with Changing Fields

Event producers add new fields over time. Spark fills missing fields with null when records disagree. Old files that lack a new field still load without errors, and the new column simply stays empty for those rows. Review the printed schema after each new data delivery so you notice additions early.

Previous lesson
Back to course
Next lesson