How to Flatten Nested JSON to CSV (and Convert It Back Without Losing Data)

Ask anyone who has piped an API response into a spreadsheet for a stakeholder how it went, and you'll usually hear the same complaint: it worked fine until the JSON had a nested object, an array, or a comma inside a text field, and then the CSV came out wrong — misaligned columns, mangled quotes, or every value turned into text. This guide covers what actually needs to happen underneath a JSON-to-CSV conversion (and the reverse), so you can either build it correctly yourself or know exactly what to look for in a tool that claims to handle it.

Why nested JSON breaks naive CSV export

CSV is fundamentally a flat, two-dimensional format — rows and columns, nothing more. JSON has no such restriction: values can be objects, arrays, or arrays of objects, nested arbitrarily deep. The moment you try to represent {"user": {"name": "Ada", "address": {"city": "London"}}} as a spreadsheet row, you have to make a decision JSON never forced you to make: how does a deeply nested value become a single flat cell, or a set of flat cells?

Most quick scripts skip this decision entirely and either crash on nested data, silently drop it, or JSON.stringify() the whole nested value into one unreadable cell without telling you that's what happened. None of those are wrong, exactly — but they're all silent, and silent conventions are exactly what breaks the moment someone else tries to read the CSV back, or tries to convert it back to JSON six months later.

Flattening nested objects: pick a separator, then be consistent

The standard approach for a nested object is dot-notation flattening: each level of nesting joins onto the column name with a separator.

{
  "name": "Ada Lovelace",
  "address": {
    "city": "London",
    "postcode": "SW1A 1AA"
  }
}

becomes three flat columns:

name,address.city,address.postcode
Ada Lovelace,London,SW1A 1AA

The separator itself (., _, or /) doesn't matter much on its own — what matters is that you use the same separator every time, and that whatever reads the CSV back into JSON knows which separator was used. A converter that lets you flatten with . today and expects _ on the way back will quietly produce the wrong nested structure, which is a much harder bug to spot than a crash.

Nested arrays: the decision most tools get wrong

Objects flatten cleanly because they have named keys. Arrays don't — and this is where "flatten JSON to CSV" tools genuinely diverge, because there are two legitimate approaches and no tool should silently pick one for you without saying so.

Index-expand. Each array item becomes its own numbered column set:

name,tags.0,tags.1,tags.2
Ada Lovelace,mathematician,writer,

This is great for short, roughly fixed-length arrays — tags, categories, a small list of phone numbers — because every item gets its own real spreadsheet column that's easy to filter and sort on.

Keep-as-JSON-text. The whole array stays as one JSON-encoded string in a single cell:

name,tags
Ada Lovelace,"[""mathematician"",""writer""]"

This preserves the array exactly — including its length — but you lose the ability to work with individual items as separate spreadsheet columns.

If your arrays vary a lot in length between records, index-expand will pad the shorter ones with empty (or null) trailing columns rather than telling you "this record only had two tags, not three." That's a structural limit of flattening arrays into columns at all, not a bug in any particular tool — if the exact length of a variable array matters to your downstream process, keep-as-JSON-text is the honest choice.

Neither option is objectively better. What matters is that the tool you use makes the choice visible and consistent, rather than burying it in a settings drawer or hard-coding one convention with no way to change it.

Custom delimiters: comma isn't universal

A plain comma is the default CSV delimiter, but it's not the only one you'll meet in practice:

• Semicolon — common in locales where a comma is the decimal separator (much of continental Europe), so Excel and other spreadsheet tools there export semicolon-delimited files by default.
• Tab (TSV) — a frequent choice for exports where field values themselves often contain commas.
• Pipe (|) — common in older systems and log-style exports.

Whichever delimiter you use, the quoting rules (RFC 4180) still apply: any field containing the delimiter, a double quote, or a line break needs to be wrapped in quotes, with internal quotes doubled. A converter that lets you switch delimiters but forgets to re-check which characters now need quoting will produce CSV that looks fine until you open it in the one spreadsheet program that parses it strictly.

Going the other way: CSV to nested JSON, with real types

Converting CSV back to JSON has its own pitfalls, mostly because every CSV cell is, technically, just a string.

Type inference turns those strings back into real JSON types where it's safe to do so:

• 42, -3.5 → JSON numbers
• true / false (any letter case) → JSON booleans
• an empty cell → null (or an empty string, depending on your setting)

The classic trap is leading zeros. A postcode like "007" or a reference ID stored as text will silently become the number 7 if a converter blindly coerces every numeric-looking string — which is precisely the kind of quiet corruption that surfaces as a support ticket weeks later. A converter with real type inference protects leading-zero strings and gives you a "force all strings" override for cases where you don't want any coercion at all.

Nested-key reconstruction is simply flattening in reverse: an address.city column becomes a nested address object with a city key, and — if the array convention was index-expand — tags.0/tags.1 columns rebuild back into a tags array.

Round-trip fidelity: why the settings have to match on both sides

Put the two directions together and you get the real test of a JSON ↔ CSV tool: does JSON → CSV → JSON actually reproduce your original structure? This only works if the flatten and unflatten logic are literal inverses of each other — same separator, same array-handling choice, on both sides. If a tool's export and import conventions were built independently (or if you export from one tool and import with another that guesses differently), you'll get valid-looking JSON back that quietly doesn't match what you started with.

If you want to sanity-check the shape of your JSON before you flatten it — spotting an unexpectedly nested field, or validating that an API response is well-formed in the first place — a plain JSON formatter and validator is worth running first.

Try it: a converter built around round-trip fidelity

We built Skojio's JSON ↔ CSV Converter specifically to make the flatten/unflatten convention visible and symmetric instead of hidden. It supports:

• Comma, semicolon, tab, pipe, or a custom delimiter, with correct RFC 4180 quoting on both read and write
• A persistent, always-visible "Nested data" control for the separator and array strategy — the one setting that determines whether your round trip actually holds
• Type inference on the CSV → JSON path, with leading-zero protection and a "force all strings" override
• A one-click "Verify round-trip" action that sends your output straight back through the other direction, so you can see your original structure reappear

Everything runs in your browser — nothing you paste or upload is sent to a server. It's a genuinely useful complement to JSON Formatter for validating structure first, and to Base64 Encoder/Decoder if your JSON payload also needs encoding for a data URI or an API header along the way.

• CSV is flat; JSON isn't — every JSON-to-CSV conversion has to make an explicit decision about how nesting becomes columns.
• Nested objects flatten cleanly with a consistent separator; nested arrays need an explicit index-expand vs keep-as-JSON-text choice.
• Custom delimiters (semicolon, tab, pipe) need the same RFC 4180 quoting rules as a plain comma.
• Type inference on CSV → JSON must protect leading-zero strings from being coerced into numbers.
• Round-trip fidelity only holds if the same separator and array-strategy settings are used on both sides of the conversion.