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

Updated 27 September 20264 min readCopy link

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]. Transmute never loses a value: a nested object or array that is still nested at the CSV step lands in the cell as compact JSON, so you can always get it back. This guide shows how to go one step further and flatten it into real columns, 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, so Transmute writes one as compact JSON in the cell instead of throwing it away. That is faithful — nothing is lost and the cell parses back into the same object — but it is not a column you can sort or sum:

$ transmute items.json -o csv
id,name,meta
1,Widget,"{""color"":""red""}"
2,Gadget,"{""color"":""blue""}"

So promote the fields you actually need before the CSV step:

Promote fields with map

$ transmute items.json -p '[{"op":"map","expr":"({...item, color: item.meta?.color ?? null})"}]' -o csv
id,name,meta,color
1,Widget,"{""color"":""red""}",red
2,Gadget,"{""color"":""blue""}",blue

The spread keeps existing columns and adds the promoted field on top. omit drops the leftover meta column when you no longer need it.

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}.