JSON to CSV Can Quietly Lose Your Data — 7 Traps to Avoid

Converting JSON to CSV looks like one of the easiest jobs in programming: take the objects, spread them into columns, done.

The catch is that CSV is a flat table and JSON is a tree.

Every step of that translation forces decisions about nesting, arrays, missing values, numbers, and schema changes. If those decisions are implicit, data can silently change meaning — or disappear entirely.

Here are seven traps worth checking before you trust a JSON → CSV pipeline.

1. “Just flatten it” can hide schema changes

Consider a typical API response:

{
  "order": {
    "id": 1001,
    "customer": {
      "name": "Ada",
      "address": {
        "city": "London",
        "zip": "SW1A 1AA"
      }
    }
  },
  "note": "rush"
}

A reasonable flattened CSV might be:

order.id,order.customer.name,order.customer.address.city,order.customer.address.zip,note
1001,Ada,London,SW1A 1AA,rush

So far, so good.

The problem is that “flatten” is not one algorithm.

One converter may join paths with ., another with _, and another may flatten only one level before stringifying the rest.

All three can produce valid CSV while giving you very different schemas.

Before converting, you should be able to see the exact output columns — including nested paths — and understand how they were derived.

2. Arrays are where row counts change

Now add an array:

[
  {
    "orderId": 1,
    "customer": "Ada",
    "items": [
      { "sku": "A1", "qty": 2 },
      { "sku": "B2", "qty": 1 }
    ]
  }
]

There is no single correct CSV representation for items.

You could keep the whole array as JSON in one cell.

You could join a primitive array into a string.

Or you could explode the array into repeated rows:

orderId,customer,items.sku,items.qty
1,Ada,A1,2
1,Ada,B2,1

Each choice changes the meaning of the output.

• Keep preserves one input record as one CSV row.
• Join turns multiple values into one scalar cell.
• Explode increases the number of rows.

That is why array behavior should ideally be configurable per path rather than controlled by one global “arrays” switch.

Multiple arrays make this even more important. Exploding two sibling arrays can accidentally create a Cartesian product and multiply rows dramatically.

A converter should never make that decision silently.

3. NDJSON schema drift can make your header lie

JSON Lines / NDJSON looks naturally streamable because each line is a separate object:

{"id":1,"payload":{"kind":"pageview"}}
{"id":2,"payload":{"kind":"purchase","amount":19.95},"trace":"t-9"}

If a converter derives the header only from the first record, it might produce:

id,payload.kind
1,pageview
2,purchase

Two real fields disappeared:

• payload.amount
• trace

A complete schema instead needs the union of fields across the input:

id,payload.kind,payload.amount,trace
1,pageview,,
2,purchase,19.95,t-9

For very large files, scanning the entire input before showing a preview may be expensive.

That is fine — but the preview should say when it is based on a sample.

A schema inferred from the first 1,000 rows of a 1 GB file is provisional. Calling it provisional is not a weakness; it is an important correctness signal.

4. null and missing are not the same thing

These records are semantically different:

[
  {"a": null, "b": 1},
  {"b": 2},
  {"a": 3, "b": null}
]

In the first record, a exists and is explicitly null.

In the second record, a does not exist at all.

A naive CSV conversion makes them look identical:

a,b
,1
,2
3,

CSV has no built-in distinction between “explicitly null” and “missing”.

So you need a policy.

Possible choices include:

• collapse both to an empty cell;
• write a literal such as null for explicit nulls;
• add presence columns when the distinction matters.

Any of those can be valid.

The dangerous option is collapsing the distinction without realizing it happened.

5. Numbers can change before they ever reach CSV

JSON number syntax is not limited to what JavaScript Number, IEEE-754 doubles, or spreadsheet numeric cells can represent exactly.

For example:

[
  {
    "id": 900719925474099312345,
    "tiny": 1e-400,
    "negativeZero": -0
  }
]

If a converter parses those values into a floating-point type and later serializes them again:

• the large integer can be rounded;
• an extremely small exponent can underflow;
• -0 can lose its original representation.

If preserving numeric text matters, the converter needs to preserve the original JSON number lexeme instead of round-tripping through an imprecise numeric type.

For identifiers, financial exports, scientific data, and audit pipelines, that difference matters.

“Looks like the same number” is not always good enough.

6. Spreadsheet formula injection is easy to overlook

CSV is often opened in Excel, Google Sheets, or another spreadsheet application.

That means some cell values can be interpreted as formulas rather than plain data.

For example:

[
  {"value":"=1+1"},
  {"value":"+cmd"},
  {"value":"-2"},
  {"value":"@channel"},
  {"value":"=HYPERLINK(\"http://example.com\")"}
]

This is the class of problem commonly called CSV or formula injection.

There are two broad strategies:

1. Preserve the original value and warn or flag it.
2. Neutralize formula-like prefixes during export.

Neutralization changes the data, so it should be an explicit policy.

And importantly, ordinary CSV quoting is not a security boundary here. A correctly quoted CSV cell can still be interpreted as a formula by spreadsheet software after import.

The converter should at least identify formula-like values so the user knows what will happen downstream.

7. Large files turn memory usage into a correctness issue

A 2 GB export is not just a bigger version of a 2 KB export.

It is a different operating regime.

Loading the entire document into a DOM, object graph, or DataFrame may exhaust memory long before conversion finishes.

NDJSON can naturally be processed record by record.

A giant top-level JSON array requires an incremental parser if you want bounded memory usage.

The exact implementation varies, but the principle is simple:

Input size should not force the converter to hold the entire dataset in memory.

If a tool has a size limit, that is completely reasonable.

What matters is that the limit is explicit instead of being discovered when the process consumes nearly all available RAM.

A practical checklist

Before trusting a JSON → CSV pipeline, ask:

1. Can I see the exact output schema before converting?
   Including flattened nested paths.

2. What happens to arrays?
   Can I choose keep, join, or explode behavior per path where the array shape allows it?

3. How is the header discovered?
   Is it based on the complete input, or is a sampled preview clearly marked as provisional?

4. What happens to null versus missing fields?
   Is that policy explicit?

5. Can values change during conversion?
   Are number representations preserved when necessary, and are spreadsheet formula-like cells identified?

A converter that answers those questions explicitly is not doing anything magical.

It is simply refusing to hide important decisions from you.

That is the difference between “convert JSON to CSV” and “convert JSON to CSV without surprises.”

---

Full disclosure: this is exactly the problem I built jsonnorm to solve.

It previews the schema, marks sampled previews as Provisional sample, supports per-path keep/join/explode rules where applicable, distinguishes null from missing and lets you choose how each is written to CSV, preserves JSON number text, and flags formula-like cells before download.

It is free, requires no sign-up, keeps no conversion history, and supports files up to 100 MiB.

You can try two of the examples from this article directly:

• Array policies: https://jsonnorm.com/examples?s=array-explode-rows&src=devto
• Null vs. missing: https://jsonnorm.com/examples?s=null-missing-both-empty&src=devto

There is also an anonymous API — no account or API key required. POST JSON or NDJSON and get CSV back:

https://jsonnorm.com/api/?src=devto