Understanding Nested JSON Data
JSON is a tree; CSV is a grid. Every JSON-to-CSV problem you have ever seen comes from forcing one into the other — and there are exactly three honest ways to do it.
APIs, app exports and NoSQL databases all speak nested JSON: objects inside objects, arrays inside objects, and records that do not all share the same shape. Spreadsheets and CSV files are flat — rows and columns, nothing else. Converting between them is not a formatting problem, it is a shape problem, and the strategy you choose decides whether data survives the trip.
A concrete example
{
"id": 7,
"customer": { "name": "Ana", "address": { "city": "Oslo", "zip": "0150" } },
"tags": ["vip", "beta"],
"total": 129.50
}
Three different things in this one record resist a flat grid: nested objects (customer.address.city is three levels deep), an array (tags has two values but a cell holds one), and — in real exports — inconsistent records, where the next record has no tags at all, or an extra field this one lacks.
Strategy 1 — Flatten with dot notation
Nested objects are unfolded into columns named by their path: customer.name, customer.address.city, customer.address.zip. Nothing is lost, the result stays one row per record, and the column names tell you exactly where each value came from. This is the right default for most exports, and it is what our JSON to CSV converter and JSON to Excel converter do. Its weakness: deeply nested data produces very wide tables with long column names.
Strategy 2 — Keep arrays as JSON text
An array inside a record has no natural single cell, so the honest options are limited. Writing the array as compact JSON text (["vip","beta"]) preserves it exactly and keeps one row per record — a person or a later script can still parse it. The alternatives are worse: joining with a separator like vip;beta corrupts any value that itself contains the separator, and silently dropping the array — which some converters do — is data loss disguised as convenience. If a tool does not tell you what it did with your arrays, assume the worst and check.
Strategy 3 — Explode arrays into rows
When the array is the point of the data — order line items, events, measurements — the correct shape is one row per array element, with the parent fields repeated on each row. Record 7 above becomes two rows, one for vip and one for beta. This is a deliberate modelling decision, not a conversion default: it changes your row count and duplicates parent data, which is exactly right for analysis and exactly wrong for a customer list. Do it consciously, in your analysis tool, after a faithful flat conversion.
The inconsistent-records problem
Real arrays of objects rarely share one schema. The correct behaviour is the union of all fields: every field that appears anywhere becomes a column, and records missing a field get an empty cell. The two failure modes to watch for are converters that use only the first record's fields (dropping everything unique to later records) and converters that error out entirely. Both are common; test any converter with a two-record file whose second record has one extra field.
When not to flatten at all
If your goal is to query the data rather than read it in a spreadsheet — filtering on nested conditions, aggregating arrays — flattening destroys structure you will want back. Keep the JSON, and use a JSON-aware tool instead. Flatten for humans and spreadsheets; keep the tree for machines. To check your file's shape first, run it through the JSON Formatter and look at the nesting before you convert.