Flattening nested JSON to CSV without losing data

Run this through almost any converter:

[
  { "id": 1, "name": "Ada", "tags": ["admin"] },
  { "id": 2, "name": "Grace", "tags": ["admin", "ops"], "team": { "name": "core" } }
]

Many of them give you three columns: id, name, tags. The team object is gone. Not truncated, not flagged, just absent, because the converter read the keys of the first object and treated that as the schema.

CSV is a rectangle: a fixed column set, one scalar per cell. JSON is a tree with optional keys, arbitrary depth and arrays anywhere. There is no correct mapping between them, only a set of policies, and the converters that feel simple are the ones that picked the policies for you without saying so.

Column discovery: union, not the first row

Two ways to decide what the columns are. Walk every row and collect the union of leaf paths, or read one object and take its keys.

The second is not a performance optimisation, it is data loss with a plausible excuse. Papa Parse takes its fields from the keys of the first object unless you pass an explicit columns option:

Papa.unparse([{ a: 1 }, { a: 2, b: 3 }]);
// "a\r\n1\r\n2"   the b column never existed

Papa.unparse(rows, { columns: ['a', 'b'] });
// you supply the union yourself

pandas json_normalize takes the union, which is one reason people reach for it. The cost is that a sparse document produces a wide, mostly empty table, which is the honest representation of a sparse document. If you want fewer columns, drop them on purpose.

Streaming makes this genuinely hard: with NDJSON you do not know the column set until the last line, so you either buffer the file or make two passes.

Nested objects and the separator you have to expose

Nested objects flatten to dotted paths, so {"team": {"name": "core"}} becomes team.name. pandas uses . by default and lets you change it:

pd.json_normalize({"user": {"name": {"first": "Ada"}}})
# column: user.name.first

pd.json_normalize(data, sep="__")
# column: user__name__first

The separator has to be configurable, because a dot is a legal character in a JSON key. These two documents flatten to the same column:

{ "a": { "b": 1 } }
{ "a.b": 1 }

Once they collide the reverse trip is guesswork, and a converter that lets one silently overwrite the other has produced a file that reconstructs to the wrong shape. Either pick a separator absent from your keys or escape it where it appears inside one. Do not assume dots never appear in keys: in event and analytics payloads they appear constantly.

Arrays: four policies, one sane default

This is where converters diverge most.

Policy	Output for tags: ["admin","ops"]	What it costs
Index columns	tags.0 = admin, tags.1 = ops	Column count is set by the longest array in the file. One row with 400 tags gives every row 400 columns
Join into a cell	tags = admin,ops	Breaks as soon as a value contains the join character, and [] and [""] render identically
JSON in a cell	tags = ["admin","ops"]	Ugly, needs correct quoting, survives the round trip exactly
Explode into rows	Two rows, other fields repeated	Row count no longer matches record count, so aggregates over the other columns double count

Index columns are fine at fixed small arity: a lat/long pair, an RGB triple. For anything unbounded the column count is decided by your worst row rather than your typical one.

Joining is the most common default and the worst one, lossy in three directions at once: the delimiter can occur in the data, empty array and array-of-empty-string collapse together, and nested objects get stringified anyway.

JSON in a cell is the right default for arrays you are not exploding, because it is the only policy that is exactly reversible. The cell is quoted per RFC 4180 with inner quotes doubled, and any reader that knows the column holds JSON parses it straight back. It looks worse in Excel and it is correct.

Exploding is right when the array is the point: line items on an order, events in a session. That is what record_path does:

import pandas as pd

data = [
    {"id": 1, "name": "Ada",   "orders": [{"sku": "A1", "qty": 2}]},
    {"id": 2, "name": "Grace", "orders": [{"sku": "B7", "qty": 1},
                                          {"sku": "C3", "qty": 5}]},
]

pd.json_normalize(data, record_path="orders", meta=["id", "name"])
#   sku  qty  id   name
# 0  A1    2   1    Ada
# 1  B7    1   2  Grace
# 2  C3    5   2  Grace

record_path names the array to turn into rows and meta names the parent fields copied onto each. Watch what happens to a record whose orders array is empty: it produces no rows and vanishes entirely. You also get one array per pass, since two sibling arrays would need a cross product, so run a pass per array and join on the id.

Heterogeneous arrays

An array whose objects have different keys is the same union problem one level down. Under index columns, [{"a":1},{"b":2}] gives items.0.a and items.1.b, two columns never both populated, with the column set now depending on element position. Under explode it gives two rows with columns a and b, which is better because position stops being part of the identity. Arrays mixing scalars and objects have no rectangular form at all; serialise them as JSON in a cell.

Values with no CSV equivalent

CSV has one type: text. Everything else is convention.

null versus empty string. JSON distinguishes them, CSV does not: ,, and ,"", are the same value to most readers, so the round trip collapses one into the other. If that matters, write a sentinel such as \N (the Postgres COPY convention), or accept that nulls come back as empty strings and say so.

Booleans. Lowercase true and false is the JSON spelling and survives. Excel renders TRUE/FALSE and some tools emit 1/0, either of which needs an explicit mapping coming back.

Numbers. A JSON string containing 007 is read by Excel as 7, and 1E5 becomes 100000. CSV quoting does not stop that, since Excel guesses the type after stripping the quotes. Large integers hit the precision boundary if anything in the chain routes them through a float, so emit number source text verbatim.

Dates. JSON has no date type; ISO 8601 or RFC 3339 strings are the convention. Excel converts a date-like string such as 2026-03-04 into a date value and redisplays it in the machine’s locale format, and ambiguous formats like 03/04/2026 can come back as a different day entirely, so never let a spreadsheet be an intermediate hop.

CSV mechanics that bite

RFC 4180 is short and worth following. Fields containing a comma, a double quote or a line break must be quoted; a literal double quote inside a quoted field is written twice; line endings are CRLF. Embedded newlines in a quoted field are legal and plenty of CSV readers still get them wrong, so if your strings contain newlines, test the consumer first.

The delimiter is not always a comma. Excel in a locale where the decimal separator is a comma expects semicolon separated files, which is why a valid CSV opens as one column on a colleague’s machine. Offer a delimiter setting, or ship the sep=; first line Excel understands.

Then the byte order mark: Excel reads a UTF-8 CSV as UTF-8 only if the file starts with one, and without it accented characters are decoded as the system codepage and mangled. Those three bytes are noise to every other tool, so make the BOM a toggle and turn it on for the Excel path.

CSV injection

If a cell’s first character is =, +, - or @, Excel, Google Sheets and LibreOffice treat the cell as a formula and evaluate it on open. OWASP calls this CSV injection. Some guidance adds tab and carriage return to the trigger list.

You cannot quote your way out of it: RFC 4180 quotes are stripped by the reader before the formula is evaluated. So if any string in your JSON came from a user and reaches a CSV that someone opens, you have handed an attacker a formula running in a trusted context. Formulas can fetch remote URLs, which means neighbouring cells can leave the building.

The mitigation is to neutralise the leading character as you write the cell:

const RISKY = /^[=+\-@\t\r]/;

function safeCell(value) {
  const s = String(value);
  return RISKY.test(s) ? "'" + s : s;
}

The apostrophe forces Excel to treat the content as text. It is not free: to a non-spreadsheet reader it is now part of the data, so the round trip breaks for those values. Prefixing is right for files a human opens in a spreadsheet and wrong for files a machine reads back, which makes it a per-export switch rather than a hidden default.

Going back the other way

CSV to JSON has one large trap: type inference. Every value in the file is text, so the converter guesses which ones are numbers and fails predictably. 007 becomes 7, 1E5 becomes 100000, 1.0 becomes 1. Postcodes, part numbers, phone numbers and version strings all die to the same rule. The safe default is to emit every value as a string and let the caller cast what it knows, with inference opt in per column rather than a file-wide heuristic. The CSV to JSON tool makes that switch explicit for exactly this reason.

A default policy worth stating

Decision	Default	Why
Columns	Union of all leaf paths, sorted	First-object sampling drops fields with no warning
Nested objects	Dotted path, separator configurable	Keys may legally contain the separator
Arrays	JSON in a cell	The only reversible policy. Explode when the array is the record
Empty containers	[] and {} literally	Distinguishable from null and from empty string
Nulls	Empty cell, documented	Or a sentinel where the distinction is load bearing
Numbers	Source text verbatim	Never round trip through a float on the way out
Line endings	CRLF	RFC 4180, and LF breaks more readers than CRLF does
BOM	Off, with an Excel toggle	Right for pipelines, wrong for Excel, so let the user say which
Formula characters	Prefixed on the Excel path only	Prefixing changes the data, so it should not happen silently

These are not the only defensible answers. The point is that every converter makes all nine calls whether it tells you or not, and only the ones that tell you can be trusted with a payload too large to eyeball. The flattener shows the path set before you commit, and the table view shows the rectangle you are about to get.