JSON to CSV: Where Headers Come From and Why Values Change

When a JSON-to-CSV conversion loses data, it almost never loses it during the "conversion" step. It loses it in two questions nobody asked: who decided the header, and is every cell still recognizable as the value it used to be?

A field disappears in thirty seconds

Plenty of converters — including the idiomatic jq pipeline — take the header from the first record only. Reproduce the consequences in Python:

复制data = [{"id": 1, "name": "A"}, {"id": 2, "email": "b@x.com"}]
first_keys = list(data[0].keys())          # ['id', 'name']
rows = [[r.get(k, "") for k in first_keys] for r in data]
# The second record's email vanishes with no error and no warning

The email key is not in the header, so it does not even qualify to become an empty cell — the column was never created, and the value is silently dropped. The fix is a key union: scan every record and create a column for every key that appears anywhere. Run the same data through a union-based converter and the header becomes id,name,email, with a blank cell where name is missing. Nothing is lost.

The forward converter behind our /convert/csv page uses exactly this strategy. Feeding it [{"id":1,"name":"A"},{"id":2,"email":"b@x.com"}] produces:

复制id,name,email
1,A,
2,,b@x.com

Keys are ordered by first appearance and missing fields become empty cells. Both jsonoperations and jsonbeam call this "the classic bug fix" — but "use the union" is only half the story. The union has a price of its own, which is the next section.

Three ways to build a header, and who each one is for

The selection criterion is not "which one is fancier" but what the downstream consumer does with this CSV:

• A colleague glancing at the data: first-row keys are fine, record shapes are usually uniform.
• A report or a data pipeline: the key union is the baseline — a blank cell is itself information.
• An automated recurring export: the explicit column list. Shape drift (an upstream field added, a key renamed) should fail loudly, not quietly produce a table nobody notices is wrong.

The third option is the most neglected. In automation, "the conversion succeeded" and "the conversion is correct" are two different claims, and an explicit column list is the only mechanism that separates them.

When keys contain dots, flattening paths collide

Dot-notation flattening turns {"a": {"b": 2}} into a column named a.b. But what if a key itself contains a dot? Tested with pandas:

复制import pandas as pd
d = [{"a.b": 1, "a": {"b": 2}}]
pd.json_normalize(d, sep=".")
# Only one column survives: ['a.b'] — two entirely different keys collapse onto one path

The literal key a.b and the nested path a → b produce the same column name, one overwrites the other, and the data is silently corrupted. This is a constructed example, but it happens in real data: monitoring metric names (cpu.usage) and log fields (error.type) love dots.

Two remedies:

1. Change the separator. Use sep="_" or a custom separator, after confirming original keys never contain it.
2. Change the path notation. JSON Pointer uses / as its separator (/a/b); toolvore's Excel converter takes this route. It has the same class of ambiguity — a key containing / must be written ~1 — but the escaping rules are standardized in RFC 6901 rather than invented per tool.

Whichever you choose, write the rule into the export documentation. The CSV header is the only "schema" this data carries; a reader has nowhere else to recover your flattening rules.

Arrays: three disposal strategies, each losing something different

Arrays are the real obstacle in JSON → CSV: one cell cannot hold several values. Three strategies, with their losses verified in practice:

Our /convert/csv and /convert/table converters use JSON-in-cell. Input [{"orderId":"A001","items":[{"sku":"X1","qty":2},{"sku":"Y9","qty":1}]}] produces:

复制orderId,items
A001,"[{""sku"":""X1"",""qty"":2},{""sku"":""Y9"",""qty"":1}]"

When you need exploded rows, pandas record_path does it directly:

复制orders = [{"orderId": "A001", "total": 99,
           "items": [{"sku": "X1", "qty": 2}, {"sku": "Y9", "qty": 1}]}]
items = pd.json_normalize(orders, record_path=["items"],
                          meta=["orderId", "total"], sep=".")
# Two detail rows; orderId and total duplicated on each

Exploding has three traps to settle in advance: exploding several arrays at once builds a Cartesian product; empty arrays produce no rows by default (your customer list loses people); and duplicated parent fields mean downstream aggregation by orderId needs deduplication. The convertcsvonline guide lists "decide the grain first" as step one for exactly this reason — once the grain is fixed, all three questions have a single answer.

Tested: values that come out of the conversion changed

A fidelity test run against our real converter (Node v22.13.1 + papaparse, the same implementation behind /convert/csv):

1. null, missing, and empty string all become blank cells. Input [{"a":null},{"b":""},{"c":1}] — all three cells come out empty. CSV has no three states, only "characters present" and "characters absent". After a round trip all three read back as ""; the original semantics are unrecoverable.

2. Nested objects and arrays become JSON strings. Quotes are doubled per RFC 4180 ({""name"":""张三""}), and pasting the cell back into any JSON parser restores the value — this is why JSON-in-cell preserves fidelity.

3. A 20-digit integer loses precision at parse time, and the fault is not CSV's. Input "id": 9007199254740993; the CSV already contains 9007199254740992. JavaScript's JSON.parse uses IEEE 754 doubles, so the loss happens during parsing, before any CSV converter runs. Python's json.load preserves it. Identifier-like fields (snowflake IDs, order numbers) should arrive as strings from the producer. More on numeric precision in why big numbers lose precision.

4. Leading zeros in strings are safe at the CSV text layer. "phone": "00123" stays 00123 in the CSV, because it is a string end to end and nothing strips it. What strips leading zeros is the later step where Excel treats the cell as a number — see four ways to convert JSON to Excel.

5. Fields containing commas, quotes, or newlines. papaparse follows RFC 4180: quote the field, double the inner quotes. "He said \"hi\", ok" comes out as "He said ""hi"", ok" and round-trips intact. Hand-rolled serializers that bypass a library almost always fail exactly here.

Round trip: what the inference switch changes

CSV → JSON must guess a type for every cell. Our converter defaults to no type inference and returns everything as strings:

复制[{ "id": "9007199254740992", "phone": "00123" }]

With typeInference enabled, id becomes a number (the precision is already gone), while phone stays a string — the inference rules exclude leading-zero forms like 010, which are not legal numeric literals, so the string survives by luck.

Remember this asymmetry: inference off costs you at most types; inference on can cost you data. Columns that need numbers should be converted by explicit column definitions, not trusted to global inference. The rowsToRecords implementation in our earlier article JSON, CSV and table data conversion is exactly that principle in code.

Picking a strategy by downstream use: one decision table

The shared pattern: the closer to humans, the looser you can afford; the closer to machines, the more must be explicit.

A checklist you can copy

• [ ] Is the header a full key union or an explicit column list? If not, can you prove no record lost a field?
• [ ] Can keys contain dots or slashes? Has the separator collision been checked?
• [ ] Is the array strategy documented? Is empty-array behavior decided?
• [ ] For null, missing, and empty string: what does the reader get for each? Pick one rule and enforce it.
• [ ] Are identifier columns (IDs, order numbers, phone numbers) carried as strings end to end?
• [ ] After exploding, does the row count equal the sum of array lengths? Spot-check it.
• [ ] Do sample fields with commas, quotes, and newlines survive a round trip unchanged?
• [ ] Is global type inference off on re-import, with explicit definitions for numeric columns?

Only when all eight have a definite answer is the CSV "correct", not merely "successfully converted".

FAQ

Why doesn't the converter flatten nested objects into columns by default?

Flattening makes column count uncontrollable: deep nesting produces names like user.address.geo.lat, and combined with the key-union strategy, a single differently-shaped record can add a batch of columns. JSON strings in cells keep fidelity and a stable column count; flatten explicitly when you need it.

What if the header contains duplicate column names?

Two different keys colliding after flattening (such as a.b versus nested a→b) must be fixed upstream: change the separator or switch to JSON Pointer. For duplicates inside one object, converters usually keep the last value; duplicate header names depend on the implementation — papaparse's unparse does not deduplicate, and Excel silently merges or discards duplicates when the file opens. That is why it is on the checklist.

Are duplicated parent fields after exploding a bug?

No — it is the design. An exploded row is a line item, and a line item must be joinable back to its parent record. Aggregate downstream by the parent key; if size worries you, export the exploded detail file and a separate master table.

How do I verify my conversion lost no fields?

Run a key audit: compute the union of all keys in the source JSON, then the set of CSV header columns, and diff the two. A non-empty difference means fields were dropped. In batch pipelines, encode this as an assertion in your tests so any converter change that starts dropping fields fails automatically.