How to Flatten Nested JSON

Nested objects: dotted paths

A nested object becomes one column per leaf value, named with a dotted path:

{ "address": { "city": "NYC", "zip": "10001" } }

becomes two columns, address.city and address.zip. This works to any depth, and the separator (here, a dot) is usually configurable.

Arrays: the actual design decision

A list of plain values (["red", "blue"]) can be joined into one text column — "red, blue" — without losing anything meaningful. A list of objects ([{"sku":"A","qty":2}]) is a different problem: flattening it into more columns only works if every element has the same shape, and even then you'd need a column per element index, which breaks the moment one record has more elements than another.

Never explode into extra rows

One common approach turns an array into one output row per element, multiplying the row count. A flattening tool should not do this by default: it silently breaks the 1:1 relationship between a source record and an output row, which is a far more surprising failure mode than “this cell contains readable JSON text.” The safer default is to keep a list of objects, or anything with mixed types, as its own compact JSON text in one cell — visible and inspectable, never guessed at or silently dropped.

A real bug this exposes: don't trust a join function blindly

Joining an array like ["a", null, "c"] into one string sounds trivial, but the obvious approach — a string-aggregate function — can silently drop a NULL element entirely, producing "a, c" with no trace that a third element ever existed. The fix is to convert a NULL element to the literal text null before joining, so the count of elements is never silently lost. This is a real, verified behavior of the underlying engine, not a hypothetical edge case — it was caught by testing the join function against a real NULL-containing array before shipping, not assumed to be safe.

Missing key vs. explicit null: a real, unavoidable limitation

Once a JSON schema is inferred across many records, a field that was entirely absent from one record and a field that was explicitly set to null in another both become an identical NULL in the flattened output. JSON can tell these two cases apart; a flat table cannot. A flattening tool should say this plainly rather than leave it as a silent surprise.

When to flatten, and when not to

Flatten when the destination genuinely needs flat columns — a spreadsheet, a CSV-only pipeline, a tool with no concept of nested types. If the destination can represent nested data natively, converting straight to Parquet instead preserves the original structure exactly, with no flattening decision to make at all.