Good engineers know 4 min read · Nested data 2 practice problems ↓

JSON and structs

Parse JSON strings with from_json, reach into nested fields, and flatten structs.

On this page

Show code in

Every code block on the page follows this.

You will learn

What a struct column is and how to reach into it
How to parse JSON strings with from_json and a schema
How to flatten nested data into flat columns
When get_json_object, schema_of_json or VARIANT fit better

Read first

select · 4 min read
explode · 3 min read

Comfortable with these? Read on.

TL;DR A struct is a column that holds named fields, like a row inside a row. Read a field with F.col("address.city"). A JSON string must be parsed first with F.from_json(col, schema); then select("parsed.*") flattens it. Explode arrays of structs to get one row per element.

Structs: a row inside a row

When Spark reads nested JSON or Parquet, objects become struct columns. printSchema() shows them as a tree:

printSchema()

root
 |-- order_id: long
 |-- customer: struct
 |    |-- name: string
 |    |-- address: struct
 |    |    |-- city: string
 |    |    |-- pin: string
 |-- items: array
 |    |-- element: struct
 |    |    |-- sku: string
 |    |    |-- qty: long

PySparkSpark SQL · Reaching into structs

orders.select(
    "order_id",
    F.col("customer.name").alias("customer_name"),
    F.col("customer.address.city").alias("city"),
    "customer.address.*",          # every field of address as its own column
)

SELECT order_id,
       customer.name          AS customer_name,
       customer.address.city  AS city,
       customer.address.*
FROM orders

Build structs with F.struct("a", "b"). A common trick: max(struct(ts, value)) compares structs field by field, so it returns the value of the latest timestamp.

Parsing JSON strings

Kafka messages, API payloads and log lines often arrive as a JSON string column. Spark cannot look inside it until it is parsed:

PySparkSpark SQL · from_json with a schema

schema = "event STRING, user_id BIGINT, props STRUCT<page: STRING, ms: INT>"

events = (raw
    .withColumn("e", F.from_json("value", schema))
    .select("e.event", "e.user_id", "e.props.page", "e.props.ms"))

SELECT e.event, e.user_id, e.props.page, e.props.ms
FROM (SELECT from_json(value, 'event STRING, user_id BIGINT, props STRUCT<page: STRING, ms: INT>') AS e
      FROM raw)

A string that is not valid JSON, or does not fit the schema, becomes a null struct (or nulls in some fields) in the default PERMISSIVE mode. Count them.
Fields in the JSON but not in the schema are ignored. That makes the schema a contract: new upstream fields appear only when you add them.
F.schema_of_json(F.lit(sample)) infers a schema from one example string, useful to draft the schema once.
F.to_json(struct) goes the other way, for example to write a Kafka message.

Flattening an order

Step 1 · parse

Parse the string column into a struct with from_json. One row per order.

The command

o = raw.select(F.from_json("value", order_schema).alias("o"))

Step 2 · explode items

items is an array of structs. Explode it to get one row per line item; keep the order fields alongside.

The command

lines = o.select("o.order_id", "o.customer", F.explode("o.items").alias("item"))

Step 3 · flatten

Pull the struct fields up into plain columns with dotted paths or .*.

The command

flat = lines.select("order_id", F.col("customer.name").alias("customer_name"), "item.sku", "item.qty")

Step 4 · result

A flat table of order lines, ready for joins and aggregation. Use explode_outer in step 2 if orders without items must be kept.

Other options

Tool	Use when
`get_json_object(col, "$.props.page")`	You need one or two fields and do not want to write a schema. Parses the string each call, so slower for many fields.
`json_tuple`	Several top-level fields at once, without a schema
`from_json`	Most cases: typed, parsed once, fast
`VARIANT` / `parse_json` (Spark 4.0)	The shape changes often or is unknown: store semi-structured data efficiently and query paths later

Note: the in-browser engine does not support structs or JSON functions yet, so the examples in this lesson are read-only. They are standard PySpark and run on any Spark 3.x or 4.x cluster.

Common mistakes

Using get_json_object for many fields

Each call re-parses the string. Parse once with from_json.

Not counting rows that failed to parse

Invalid JSON becomes null quietly.

Explode instead of explode_outer for optional arrays

Orders with no items disappear.

Inferring the JSON schema on every run

Slow and unstable. Write the schema down.

Key takeaways

Structs are nested rows; reach fields with dotted paths or .*.
Parse JSON strings once with from_json and an explicit schema.
Explode arrays of structs, then select fields to flatten.
Invalid JSON becomes null: count and quarantine it.

Check yourself

3 questions

1. How do you select every field of the struct column address as separate columns?

Show the answer

select("address.*"). The .* expands a struct into its fields.

2. What does from_json return for a string that is not valid JSON (default mode)?

Show the answer

null. Invalid input becomes null in PERMISSIVE mode.

3. Why prefer from_json over many get_json_object calls?

Show the answer

It parses the string once instead of once per call. Each get_json_object call parses the JSON again.

Practice it

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

Solve: Parse Application Log Lines →

Keep going

Up next · lesson 26 of 35 · 4 min read
UDFs and pandas UDFs
When a Python function is the only way, how to write it, and why built-ins are much faster.

Previous: Array functions

Primary sources: functions.from_json · JSON files