How to Convert JSON to CSV (and Keep Nested Data Sane)

Going from JSON to CSV sounds like a formatting change, but it is really a shape change. JSON is a tree: objects nested inside arrays inside objects, to any depth. CSV is a flat grid of rows and columns with no concept of nesting. Most of the pain people hit converting json to csv comes from forcing the first shape into the second. Paste your data into the JSON to CSV converter to see the mapping immediately, then read on for the rules that decide whether the result is clean or a mess.

The core mismatch: a tree versus a grid

A CSV file has exactly two dimensions: rows down the page and columns across it. Every cell holds a single scalar value, a string or a number, with no structure inside it. JSON has no such limit. A value can itself be an object or an array, and those can contain more objects and arrays. So before any conversion runs, you have to answer one question: which part of the JSON becomes the rows? Once you fix that, the columns and the flattening fall out of it.

The happy path: an array of flat objects

The case that maps perfectly is a top-level array where every element is a flat object with the same scalar keys. Each object becomes one row, each key becomes one column, and the header row is the union of the keys. Nothing is lost because nothing was nested in the first place.

[
  { "id": 1, "name": "Ada",   "active": true },
  { "id": 2, "name": "Grace", "active": false }
]

That converts to a CSV every spreadsheet will read without complaint:

id,name,active
1,Ada,true
2,Grace,false

Flattening nested objects with dot notation

Real JSON is rarely flat. When a value is itself an object, the standard trick is to flatten it into the parent row by joining the key path with a separator, usually a dot. A nested address object turns into several dotted columns rather than one column holding a blob.

[
  {
    "id": 1,
    "name": "Ada",
    "address": { "city": "London", "zip": "EC1" }
  }
]

becomes one row with the nested keys promoted to top-level columns:

id,name,address.city,address.zip
1,Ada,London,EC1

Dot notation is a convention, not a standard, so the receiving side has to know the dots mean nesting if it ever needs to rebuild the tree. It works to any depth: address.geo.lat, address.geo.lng, and so on. The deeper the JSON, the wider the CSV gets.

Exploding arrays of values

Arrays are the hard part, because a single cell cannot hold a list. You have three honest options, and which one is right depends on what the data means.

1. Join into one cell. Turn ["a","b"] into a single field like "a; b". Simple and lossy: you can no longer tell a real semicolon in a value from a separator.
2. Index into columns. Expand tags[0], tags[1], tags[2] as separate columns. Keeps every value addressable, but the column count is now fixed to the longest array.
3. Explode into rows. Emit one CSV row per array element, repeating the parent fields. This is the right move when the array is the list of records you actually care about, for example line items inside an order.

Delimiters, quoting, and the rules that bite

CSV looks trivial until a value contains a comma. The format is loosely described by RFC 4180, and the rules that matter most are about quoting. Any field that contains the delimiter, a double quote, or a line break must be wrapped in double quotes, and any literal double quote inside that field is escaped by doubling it.

The "comma" in CSV is not sacred. Many tools, and most non-US locales, use a semicolon or a tab instead, partly because a comma is also the decimal separator in much of Europe. Pick a delimiter your destination expects, and if you control both ends, a tab is the least likely to collide with the data.

Excel and the UTF-8 BOM

The classic gotcha: you export clean UTF-8, open it in Excel, and accented names or emoji turn into garbage like é. Excel does not assume UTF-8 for plain CSV. The fix is to prepend a UTF-8 byte-order mark, the three bytes EF BB BF, to the front of the file. Excel then reads the encoding correctly. The cost is that stricter parsers may treat that BOM as part of the first column name, so only add it when the target is Excel.

Round-tripping CSV back to JSON

Going the other way, CSV back to JSON, is where the lossy nature of the grid shows up. Two things are gone by the time data lands in a CSV, and no parser can invent them back without help.

• Types. Every CSV cell is text. The string 42 could be a number or a string, true could be a boolean or the word, and 007 might be a ZIP code you must not turn into 7. Without a schema, a CSV-to-JSON pass has to guess, and guessing wrong is how leading zeros and phone numbers get mangled.
• Structure. Dotted headers like address.city only rebuild into nested objects if the parser is told to interpret the dots. A naive parser gives you a flat key literally named "address.city" instead of an address object.
• Null versus empty. An empty cell could mean null, an empty string, or a missing key. JSON distinguishes all three; CSV collapses them into one blank.

The practical rule: treat CSV as a presentation and exchange format for tabular records, not as a faithful container for arbitrary JSON. If you need a perfect round trip, keep the original JSON as the source of truth and regenerate the CSV when you need it, rather than reconstructing JSON from a CSV that has already thrown type information away.

A short checklist

1. Decide which array becomes your rows before anything else.
2. Flatten nested objects with dotted column names.
3. Choose join, index, or explode for each array, based on meaning.
4. Quote any field containing the delimiter, a quote, or a newline.
5. Add a UTF-8 BOM only when the file is headed for Excel.
6. Remember the trip is lossy: keep the JSON if you need it back exactly.

If your JSON will not convert at all, the problem is usually upstream: it is not valid in the first place. Validate it with the JSON formatter first, fix any errors, then convert with confidence.