How to Flatten Nested JSON Arrays (and Export to CSV)

Nested JSON — arrays inside objects inside arrays — is everywhere: API responses, Stripe exports, MongoDB dumps, log files. Spreadsheets can't read it, and most "JSON to CSV" tools either choke on nesting or silently turn your data into [object Object]. This guide shows how to flatten nested JSON into flat rows from the command line, for free, without uploading your data anywhere.

The problem with one-level flattening

Take a typical export:

[
  { "id": 1, "name": "Widget", "tags": ["sale", "new"] },
  { "id": 2, "name": "Gadget", "tags": ["sale"] }
]

Each item has a tags array. A naive converter gives you a column containing the raw array as text. What you usually want is one row per tag, with the parent fields repeated:

$ transmute items.json -p '[{"op":"flatten","field":"tags"}]' -o csv
id,name,tags
1,Widget,sale
1,Widget,new
2,Gadget,sale

The flatten operation expands an array field into multiple rows. Non-array fields are copied onto every expanded row, so each row stays self-contained.

Flattening nested objects instead of arrays

If the nested value is an object rather than an array — say a meta object with several keys — you have two options:

Keep it as JSON in one cell

Transmute serializes nested objects and arrays as compact JSON inside the CSV cell (properly quoted), so no data is lost:

id,meta
1,"{""color"":""red""}"

Promote fields with map

$ transmute items.json -p '[{"op":"map","expr":"({...item, color: item.meta?.color ?? null})"}]' -o csv

The spread keeps existing columns and adds the promoted field on top.

Adding computed columns while you're at it

The add operation evaluates an expression per row — useful for counts, totals or derived flags:

$ transmute items.json -p '[{"op":"add","fields":{"tag_count":"item.tags.length"}}]' -o csv
id,name,tags,tag_count
1,Widget,"[""sale"",""new""]",2
2,Gadget,"[""sale""]",1

Deeply nested structures

For two or more levels of nesting, chain operations. Flatten level by level:

$ transmute orders.json -p '[{"op":"flatten","field":"lines"},{"op":"flatten","field":"discounts"}]' -o csv

If a field is missing on some rows, flatten passes those rows through untouched — you don't lose records, they just aren't expanded.

Getting the CLI

All processing happens locally. Nothing is uploaded — see the free web demo if you'd rather try it in the browser first.

Gotchas

• Arrays of objects: when the flattened values are objects, their keys are merged into the row. Name collisions keep the parent's value unless the child defines the key.
• null vs missing: both serialize to empty CSV cells; use map if you need to distinguish them.
• Excel and leading zeros: if IDs like "007" lose their zeros after import, format the column as text in your spreadsheet tool.
• Huge files: for multi-hundred-MB files prefer splitting first ({"op":"head","n":50000}) or use the desktop app's batch mode.