Flattening JSON for CSV Export: The Round-Trip Gotchas

Flattening nested JSON — turning {"a":{"b":1}} into {"a.b":1} — looks like the most boring transformation in data tooling. Walk the tree, join the path with dots, done; it’s a coding-interview warm-up. But a flat key is a serialization format for a path, and like every serialization format it has ambiguities: several different nested structures can flatten to the same flat object, which means the reverse trip has to guess. Most of the time the guess is invisible because your data never hits the edge cases. When it does — a key with a literal dot in it, an object whose keys are "0" and "1", an empty array you needed to keep — the loss is silent, and you only find out after the round trip.

This post walks the edges in order of how often they bite, using the exact behavior of our JSON flattener as the reference implementation — every example below is the tool’s real output.

Why flatten at all

Three jobs keep producing flat JSON:

• CSV and spreadsheet export. CSV is a grid; it has columns, not trees. Before nested API responses can become rows in a JSON to CSV conversion, every leaf needs a column name, and user.address.city is that name. This is the big one, and it’s why the round-trip question matters: people flatten, edit in a spreadsheet, and want their JSON back.
• Analytics and event pipelines. Most event-property systems accept a flat bag of key–value pairs, so nested context gets flattened into page.section, cart.items.0.sku, and friends.
• Diffing and auditing config. Two flat objects diff line-by-line; two trees don’t. Flattening both sides turns “what changed in this config?” into a set comparison on paths.

All three are one-way trips until someone wants to go back.

Two ways to spell a path

Before the ambiguities, the notation choice — because it decides which ambiguities you get. There are two conventions for writing array positions into a path, and our tool offers both, alongside a choice of delimiter (dot, slash, or underscore):

Bracket notation (a[0].b) marks indices with square brackets:

{ "tags": ["free", "client-side"] }

{ "tags[0]": "free", "tags[1]": "client-side" }

Delimiter notation (a.0.b) spells the index as just another path segment:

{ "tags.0": "free", "tags.1": "client-side" }

Bracket notation keeps the array/object distinction in the syntax: [0] can only ever mean an array index, so the unflattener never has to guess. What it trades away is compatibility — brackets annoy downstream tools that treat them as special characters, and plenty of analytics and column-naming conventions expect pure dot-paths.

Delimiter notation is maximally portable — every key is plain word.word.word — but it erases the distinction between “index 0 of an array” and “object key that happens to be the string \"0\"”. That erasure is the core ambiguity.

The core ambiguity: is "0" an index or a key?

These two structures are different JSON:

{ "a": ["x"] }

{ "a": { "0": "x" } }

In delimiter mode, both flatten to the identical flat object:

{ "a.0": "x" }

The information distinguishing them is gone — not mangled, gone. So an unflattener handed {"a.0": "x"} must pick a rule, and our tool’s rule is documented and deliberate: in delimiter mode, a purely-numeric path segment is always read back as an array index. Run it and you get:

{ "a": ["x"] }

— every time, never {"a": {"0": "x"}}. If your original was the object-with-numeric-string-keys shape, the round trip quietly rewrote it into an array. There is no warning for this one, because from the flat side the two inputs are literally indistinguishable; no amount of cleverness recovers a bit that was never written down. The fix is upstream: use bracket mode, where {"a": {"0": "x"}} flattens to {"a.0": "x"} but {"a": ["x"]} flattens to {"a[0]": "x"} — different spellings, so both survive the trip. That’s the trade in one sentence: bracket mode spends syntax to buy back a bit of information.

The always-an-index rule has a second visible consequence: indices are positions, not labels. Unflatten {"a[2]": "x"} (or {"a.2": "x"} in delimiter mode) and the tool has to materialize positions 0 and 1 to put something at position 2:

{ "a": [null, null, "x"] }

Delete a “row” from the middle of a flattened array in a spreadsheet and this is what comes back — a hole filled with null, not a shorter array. (There’s also a sanity cap: indices above 100,000 are rejected with an error naming the offending key, rather than allocating a hundred-million-slot array because someone typed a[99999999].)

When two different keys collapse into one

JSON keys are arbitrary strings — including strings that contain your delimiter. Which means this input:

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

contains two different structures — a top-level key literally named a.b, and a nested path a → b — that flatten, with a dot delimiter, to the same flat key. Our tool’s output:

{ "a.b": 2 }

One value survived. Flattening never throws on this; instead it behaves like a normal object write — last value wins — and reports what happened in a warnings array shown above the output. For this input you get both applicable warnings:

Key "a.b" contains the delimiter "." and may not round-trip correctly when unflattened.
Duplicate flat key "a.b" — more than one path in the input collapses to this
flattened key; the last value wins.

Per the source, the warnings cover exactly three situations: a source key that contains the active delimiter (or, in bracket mode, a literal [ or ]), two different paths collapsing onto the same flat key, and a key that is literally __proto__, constructor, or prototype. That last one is a round-trip honesty warning: the unflattener unconditionally refuses those three names as path segments — walking attacker-supplied paths like __proto__.isAdmin and assigning through them is the textbook prototype-pollution setup — so if your data legitimately contains such a key, the flat output flattens fine but can never be unflattened, and the tool tells you so up front.

The practical escape hatch for delimiter-in-key collisions is the delimiter picker: if your keys contain dots (config keys like log.level are everywhere), flatten with / or _ instead, and the collision — and its warning — disappears.

Note the asymmetry: flattening is warnings-and-continue, but unflattening conflicting keys is a hard error. {"a": 1, "a.b": 2} can’t be merged — a can’t be both a scalar and an object — so the tool refuses with an error naming the key, since any silent resolution would be inventing data.

Empty objects and arrays: do they survive?

Here’s a subtler information leak. The obvious way to write a flattener is “recurse until you hit a leaf scalar, emit path → value.” Follow that rule literally and an empty object or empty array produces zero entries — there’s no scalar inside it to visit — so this input:

{ "server": { "host": "127.0.0.1", "meta": {} }, "logs": [] }

would flatten to just {"server.host": "127.0.0.1"}, and the round trip would return a structure with meta and logs simply missing. Whether “an empty list of logs” and “no logs field at all” are the same thing is a schema question, but a flattener has no business answering it for you.

Our implementation treats empty containers as leaves in their own right:

{ "server.host": "127.0.0.1", "server.meta": {}, "logs": [] }

— which unflattens back to exactly the original. The same “preserve, don’t judge” rule applies to falsy scalars: null, false, 0, and "" are all legitimate leaf values and all survive verbatim. And a top-level scalar or empty container — which has no path to hang a key on — is stored under the empty-string key "", so even [] as your entire input round-trips instead of vanishing.

What this means for the spreadsheet round trip

Put it together for the workflow that motivates most flattening: flatten → export CSV → edit in a spreadsheet → import → unflatten. Two distinct layers can eat your data, and it pays to know which one bit you.

The flattening layer loses exactly the bits above: array-vs-numeric-key in delimiter mode, colliding keys, and (in naive implementations) empty containers. Everything it loses in our tool is either preserved by a mode switch or reported in a warning — so the actionable habit is simply: read the warnings box before you export, not after you re-import.

The CSV layer has its own, larger appetite. CSV cells are untyped text, so 0, "0", false, null, and an empty cell all flatten (so to speak) into strings — distinguishing “the JSON null” from “the empty string” is a convention your exporter and importer must agree on, not something the format encodes. That’s the same family of loss we cataloged in CSV to Markdown table pitfalls: tabular formats hold less information than trees, and every trip through one is a negotiation about what to drop. It’s also why “just regenerate the models from the edited data” workflows drift — the same shape-inference guesswork shows up when generating Pydantic models from JSON.

A sane pipeline, in order: format and validate the source JSON, flatten it in bracket mode (keep the array bit!), check the warnings, then hand the flat object to JSON to CSV. Coming back, unflatten with the same delimiter and notation settings you flattened with — the settings are part of the encoding, and decoding with a different one is its own quiet way to scramble a structure.

The short version

Looks trivial	Actually
”Just join the path with dots”	Flat keys are a serialization format with real ambiguities
a.0 means array index 0	In delimiter mode it always unflattens as an index — {"a.0":"x"} becomes {"a":["x"]}, never {"a":{"0":"x"}}; use bracket mode to keep numeric object keys
Every key becomes a unique column	A key containing the delimiter collides with the nested path that spells the same — last value wins, with a warning, not an exception
Empty {} / [] don’t matter	A leaf-scalars-only flattener drops them; preserving them as leaf values is what makes the round trip exact
Flatten and unflatten are inverses	Only within one notation + delimiter setting, only for warning-free inputs, and only until CSV’s untyped cells get involved
Deleting a flat key deletes the element	Deleting a[1] from the middle leaves a null-filled hole, not a shorter array

Flattening is fine — it’s load-bearing in half the data tooling you use. It just isn’t free. Flatten with the notation that preserves the bits you care about, treat the warnings as part of the output, and do the trip in a tool that tells you what it dropped: JSON flattener.