Flattening Nested JSON for Spreadsheets: A Practical Guide

Spreadsheets are flat — rows and columns, one value per cell. JSON is nested — objects inside objects, arrays inside objects. Getting from one to the other means making explicit decisions about how nesting collapses into columns, and the decisions that seem obvious in theory get messy fast on real data.

The basic transformation: dot notation

{
  "id": 204,
  "customer": { "name": "Alice", "region": "West" },
  "total": 89.5
}

flattens to columns:

id, customer.name, customer.region, total
204, Alice, West, 89.5

Every nested object key gets joined to its parent with a dot, all the way down. This is the part that's genuinely mechanical and rarely surprising — the interesting cases are what happens next.

Arrays: flattened by index

{ "id": 204, "tags": ["urgent", "billing"] }

id, tags.0, tags.1
204, urgent, billing

Each array position becomes its own column. This works cleanly for short, fixed-length arrays. It works less cleanly for arrays whose length varies a lot between records — a tags array with 2 items on one row and 15 on another produces 15 mostly-empty tags.N columns across the whole sheet, which is correct but not exactly pleasant to read.

Inconsistent shapes across array items

Real-world API data is rarely perfectly uniform — some records have an optional field, others don't. Our JSON to CSV converter handles this by taking the union of every key seen across all records as the full column set, in first-seen order. A record missing a given key just gets an empty cell there — no error, no row silently dropped:

[
  { "id": 1, "name": "Alice" },
  { "id": 2, "name": "Bob", "email": "[email protected]" }
]

id, name, email
1, Alice,
2, Bob, [email protected]

A single object vs an array of objects

A lone JSON object converts to a one-row CSV — you don't need to wrap it in an array first. An array of objects becomes one row per item. Both are handled the same way under the hood; the array is just the general case where a single object is the length-1 special case.

When flattening isn't the right move

Deeply nested, highly variable structures (a tree with unpredictable depth, a document store record with wildly different shapes per type) don't flatten cleanly into a meaningful spreadsheet no matter how the tool handles it — you'll end up with dozens of sparse, hard-to-read columns. In that case, a spreadsheet genuinely isn't the right format for the data; flattening will "succeed" mechanically while producing something nobody wants to actually read. If you just need to eyeball the structure rather than get it into a sheet, our JSON Viewer is usually a better fit than forcing a CSV export.