Data Formats & Debugging · Field note
Nested JSON to CSV: when flattening helps and when it destroys meaning
A practical guide to flattening nested JSON into CSV without pretending every tree-shaped structure belongs in rows and columns.
Before you start
Lint or format before comparing data, then check that cleanup did not change the fields, order, or values that matter.
Flattening JSON into CSV is useful when you need a report, spreadsheet import, or a quick export for people who think in tables. It becomes destructive when the source data is genuinely hierarchical and the flattening step hides or duplicates meaning.
That tradeoff is worth stating directly because “convert to CSV” sounds harmless until the arrays start multiplying rows.
In brief
What it is: Flattening JSON to CSV turns nested objects and arrays into row-and-column structures by choosing a path-based column strategy or row-expansion strategy.
Why it matters: A good flatten can make analysis and spreadsheet work easier, but only when the target use case really is tabular.
Watch for: Arrays, repeated structures, and optional nested objects can cause row explosion, ambiguous columns, or silent loss of context.
Ask whether the destination is a report or a data model
If the output is a one-way report or spreadsheet, flattening is often fine. If the output must round-trip back into a faithful JSON model, flattening can be the wrong representation entirely. That is the first decision to make before you tune column names or separators.
CSV is not wrong; it is just narrower than JSON. The narrower shape needs a purpose.
Arrays are where most flattening pain comes from
An array can be serialized into one cell, exploded into multiple rows, or expanded into numbered columns. None of those choices is universally correct. Each one answers a different business question. The problem is not the tool. The problem is pretending there is a neutral choice when there usually is not.
That is why a flatten tool should surface the strategy rather than hide it.
- One cell: easier export, weaker structure.
- Multiple rows: better analysis, possible row explosion.
- Numbered columns: readable for small fixed arrays, brittle for larger ones.
Make the loss visible
The most trustworthy JSON-to-CSV tools show users what was flattened and where ambiguity was introduced. That is a stronger UX than pretending a tree simply “became” a table without tradeoffs.
See it in a small example
Notice: You need to decide whether items becomes one cell, two rows, or numbered columns. There is no neutral flattening choice here.
{
"orderId": 101,
"items": [
{ "sku": "A1", "qty": 2 },
{ "sku": "B2", "qty": 1 }
]
}
What to verify
- Decide whether the output is for reporting or for faithful data transfer.
- Choose an array strategy explicitly.
- Show users where flattening duplicated or collapsed information.
Common questions
Can every JSON document be converted cleanly to CSV?
Converted, yes. Represented without tradeoffs, no.
Why did my row count explode?
Because nested arrays were expanded into multiple rows.