Nested JSON to CSV

Nested objects become dotted columns

The conversion walks down to each leaf value and names the column after the path it took. A city inside addr inside user becomes one column called user.addr.city. There is no depth limit, so the column count grows with the shape of your data rather than with the number of keys at the top level.

This part is well behaved and reversible in principle: the dots record the structure you started with.

[
  { "id": 1, "user": { "name": "Ada", "addr": { "city": "London" } } },
  { "id": 2, "user": { "name": "Alan", "addr": { "city": "Wilmslow" } } }
]

id,user.name,user.addr.city
1,Ada,London
2,Alan,Wilmslow

Arrays are the hard part

An array has no name for its members, only positions, so there is no honest column name to derive. The converter handles the two cases differently, and the split is worth knowing before you trust the output.

An array of plain values collapses into one cell, joined with a semicolon and a space:

[
  { "id": 1, "tags": ["a", "b"] },
  { "id": 2, "tags": ["c"] }
]

id,tags
1,a; b
2,c

Arrays of objects get indexed columns

When the array holds objects, each position gets its own set of columns, numbered by index. This is faithful — nothing is merged — but the column count is set by the longest row in your whole dataset.

Look at row 2: it has one item, so four of its cells are empty. With one order of 50 line items in a file of 10,000 orders, every other row carries 49 sets of blank columns.

[
  { "id": 1, "items": [{ "sku": "x", "qty": 2 }, { "sku": "y", "qty": 1 }] },
  { "id": 2, "items": [{ "sku": "z", "qty": 5 }] }
]

id,items[0].sku,items[0].qty,items[1].sku,items[1].qty
1,x,2,y,1
2,z,5,,

Missing keys align, they do not shift

Records in real data rarely all carry the same keys. The header is the union of every key seen, in first-seen order, and a record missing one gets an empty cell rather than a shifted row.

This is the behaviour you want, and it is worth checking in any converter you use: the naive implementation takes the first record's keys as the header and silently drops every field that only appears later.

[
  { "id": 1, "name": "Ada" },
  { "id": 2, "email": "a@b.c" }
]

id,name,email
1,Ada,
2,,a@b.c

Wrapped payloads, and when unwrapping stops

API responses usually put the rows inside an envelope. When exactly one property of the top-level object is an array, that array is treated as the rows — so you can paste a response in without reshaping it first.

{ "items": [{ "a": 1 }, { "a": 2 }] }

a
1
2

Two arrays means no guess

With two arrays there is no way to tell which one holds the rows, so the converter stops guessing and flattens the whole object as a single record instead. The result is one wide row, which is almost certainly not what you wanted — and that is the point: it is visibly wrong rather than quietly half right.

If you see a single row of indexed columns, pick the array you meant and convert that.

{ "items": [{ "a": 1 }], "other": [{ "b": 2 }] }

items[0].a,other[0].b
1,2

Quoting, and the spreadsheet formula problem

A cell gets quoted when it contains the delimiter, a quote character, or a newline — the standard CSV rules, with embedded quotes doubled.

One addition worth knowing about: a cell starting with =, +, -, or @ is also quoted. Those characters make Excel and Google Sheets treat the cell as a formula, which is how a CSV export turns into code execution on someone else's machine. Quoting is the cheap half of the fix.

[{ "formula": "=1+1", "note": "a,b", "q": "say \"hi\"" }]

formula,note,q
"=1+1","a,b","say ""hi"""

What you cannot get back

Converting back produces flat keys, not the nesting you started with. The dots survive as literal characters in the key name — nothing reassembles them into objects, because a plain CSV reader has no way to know whether user.name was a nested field or a column that genuinely had a dot in its name.

• —Types are re-guessed on the way back. 01234 in a CSV cell parses as the number 1234, the same way it does in unquoted YAML. Postcodes and account IDs are the usual casualties.
• —`null` and empty string become the same thing. Both are an empty cell, and an empty cell reads back as an empty string.
• —Arrays do not come back. A a; b cell is a string containing a semicolon, not a list.

id,user.name
1,Ada

[
  {
    "id": 1,
    "user.name": "Ada"
  }
]

Practical advice

Treat CSV as an export format, not a storage format. It is the right answer when the destination is a spreadsheet, a BI tool, or a person; it is the wrong answer when you plan to read the data back as JSON later.

• —Convert the array you actually want rows from, not the envelope around it.
• —If arrays inside your records vary in length, flip the shape: one row per array item, parent fields repeated.
• —Check the header row before you trust a file — it tells you exactly which leaves the flattener found.
• —Keep the original JSON. It is the only copy that still knows the types and the structure.