Turn a nested JSON API response into a flat, queryable table

The analytics team wants one row per item, but your API response nests objects inside arrays inside more objects, five levels deep.

BeginnerRead 5 minDoing: an hour6 steps

What you'll have at the end

A flat table with one row per item, and zero orders dropped by explode silently swallowing an empty or null array.

Before you start

You need

A Spark DataFrame already holding the raw API response, with nested structs and arrays showing up in printSchema() exactly as the API returned them.

Not covered

Parsing a raw JSON string into that structured DataFrame in the first place, and quarantining API records that fail to parse at all.

Leans on

6 steps

Save an API's raw reply before parsing anything from it

for when the API reply is still a raw string that needs saving and parsing before it ever becomes a DataFrame with real nested structs.

No gate stops bad data from reaching your Spark load

for catching a malformed or missing field inside the payload itself, which explode's row-loss bug does not protect against.

Checked 25 Aug 2026