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:

Do not leave it nested

CSV has no way to hold an object. If a row still contains one when it is serialized, the cell comes out as [object Object], and an array comes out as its elements joined with commas. So promote the fields you need before the CSV step:

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.

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: everything is held in memory, so for multi-hundred-MB files split the input first or take a slice with {"op":"head","n":50000}.