Flattening nested JSON into flat columns for CSV or Excel
A spreadsheet is a grid: rows and columns, every cell a single scalar value. JSON is a tree: objects inside objects, arrays of arrays. Converting one to the other means forcing a tree into a grid, and that only works cleanly for some shapes. Knowing where the conversion is lossless and where it quietly loses information is the whole skill.
The easy case: an array of flat objects
JSON that is already close to a table converts perfectly. An array where every element is an object with scalar values maps straight to rows and columns:
[
{ "id": 1, "name": "Ada", "active": true },
{ "id": 2, "name": "Alan", "active": false }
]
The array becomes the rows, the keys become the column headers, done. The only thing to decide is how to handle objects that are missing a key, which should produce an empty cell rather than an error.
Nested objects: dot-path keys
When a value is itself an object, the usual technique is to flatten the path into the column name with a separator, typically a dot:
{ "id": 1, "address": { "city": "Oslo", "zip": "0150" } }
becomes two columns, address.city and address.zip:
| id | address.city | address.zip |
|---|---|---|
| 1 | Oslo | 0150 |
Nesting can go as deep as you like: user.profile.contact.email is a valid flattened header. This step is lossless as long as no original key already contains a dot, which is the edge case to watch. Pick a separator that does not appear in your keys, or escape it.
Arrays are where it gets lossy
A nested array has no natural column name, because it has length rather than named fields. There are three common strategies, and each loses something:
| Strategy | Example output | What you lose |
|---|---|---|
| Index into columns | tags.0, tags.1, tags.2 | Fixed width; a row with more items overflows the headers |
| Join into one cell | "red, green, blue" | The boundary between items if a value contains the separator |
| Explode into rows | one row per array item, parent fields repeated | A single logical record now spans many rows |
Indexing suits short, bounded arrays such as a pair of coordinates. Joining suits tags and labels where you only need them readable. Exploding is what analytics pipelines do, because it keeps every value queryable, at the cost of denormalizing the parent fields across repeated rows. None of these is wrong. They answer different questions, so choose by what the spreadsheet is for.
The tradeoffs you are accepting
Flattening is a one way trip for most data. Several things do not survive the round back to JSON:
- Type information. CSV has no types.
true,42, and"42"all become bare text, and a leading zero like0150may be eaten by Excel as a number unless the column is forced to text. - Null versus empty. A JSON
nulland a missing key both tend to become an empty cell, erasing the distinction. - Deep structure. Once an array is exploded or joined, reconstructing the original nesting requires rules that the flat file does not carry.
So treat the flat export as a view for humans and spreadsheet tools, not as a faithful serialization you can convert back without thought. If you need the structure preserved, keep the JSON as the source of truth and generate the flat file on demand.
A sensible default
For most reporting tasks: flatten nested objects with dot paths, join short scalar arrays into a single cell, and explode into rows only when each array item is itself a record you want to filter and pivot. Decide the array strategy before you start, because mixing them across columns produces a file nobody can parse reliably later.
When you have the JSON in hand and want to see how a given structure lands as columns, the JSON to CSV converter does the flattening in your browser so none of the data leaves your machine.