How Nested JSON Becomes CSV Columns

To convert nested JSON to CSV, you flatten each record into a single level of key-value pairs, turning the path to every nested value into a column name. {"address": {"city": "Pune"}} becomes a column address.city with the value Pune. Nested objects flatten cleanly because each path appears at most once per record. Arrays are the hard part, because a record can have any number of elements, and CSV has a fixed number of columns. There are four ways to handle them (keep the array as a JSON string, spread it into numbered columns, join it into one delimited cell, or give each element its own row), and which is right depends on what the CSV is for. Whatever you choose, some information is lost, so plan how to rebuild the nesting if the data has to come back.

#The flattening model

CSV is a table: every row has the same columns, every cell holds one scalar. JSON is a tree: objects nest, arrays vary in length, and values carry types. Flattening maps the tree to the table using paths:

{                                  column          value
  "id": 1,                         id              1
  "name": "Asha",                  name            Asha
  "address": {
    "city": "Pune",                address.city    Pune
    "zip": "411001"                address.zip     411001
  },
  "tags": ["admin"]                tags            ?  (depends on the array strategy)
}

Each record produces one row. The header is the union of all paths seen across all records, in the order they first appear, and a record that lacks a path gets an empty cell in that column.

#Converting a real example

Paste an array of records into JSON to CSV:

[{"id":1,"name":"Asha","address":{"city":"Pune","zip":"411001"},"tags":["admin"]},{"id":2,"name":"Ben","address":{"city":"Austin","zip":"73301"},"tags":["editor","beta"]}]

With default settings, nested objects become dotted columns and arrays are kept as JSON text:

id,name,address.city,address.zip,tags
1,Asha,Pune,411001,"[""admin""]"
2,Ben,Austin,73301,"[""editor"",""beta""]"

The doubled quotes are CSV escaping, as specified by RFC 4180: a field containing a comma, a double quote or a line break is wrapped in double quotes, and any quote inside it is written twice. A spreadsheet shows the tags cells as ["admin"] and ["editor","beta"].

If the input is a single object, it becomes one row. If it is an object that wraps the array you care about, such as {"orders": [...]}, the converter finds the array and converts that, and says which key it used.

#Four strategies for arrays

The same two records, with each strategy. The first three are options of the Arrays setting in the converter; the fourth is a different shape of output that you produce in code or a query.

The index-column strategy applied to both records gives:

id,name,address.city,address.zip,tags.0,tags.1
1,Asha,Pune,411001,admin,
2,Ben,Austin,73301,editor,beta

Asha has one tag, so tags.1 is empty for her. Arrays of objects are indexed the same way, one level at a time: items.0.sku, items.0.qty, items.1.sku. That works for two or three items and becomes unreadable for twenty.

The one-row-per-element shape (often called exploding or unnesting) is the right choice when the array is the thing you want to analyse, such as order lines. It is what UNNEST does in SQL, what record_path does in pandas' json_normalize, and what .items[] does in a jq filter. When records contain two independent arrays, explode only one of them; exploding both produces a cross product, with every tag paired with every order line.

#What flattening loses

A CSV cell is text. Everything else about the value is lost unless you encode it.

Three kinds of loss show up in that table:

• Types. CSV has none. A reader either guesses (so the ZIP code 411001 becomes a number) or treats everything as text (so real numbers become strings). Neither recovers the original exactly.
• Null versus empty versus absent. All three become an empty cell. If the distinction matters, encode it, for example with a literal null string, and agree on it with the consumer.
• Key collisions. A record with both {"a": {"b": 1}} and a literal key "a.b" produces two columns with the same name. Keys containing the separator are rare, but when they occur (dotted configuration keys, domain names as keys) choose a different separator or rename them first. A related collision appears on the way back: a CSV with both a user column and user.name cannot rebuild both, so CSV to JSON keeps the nested value and says so in a notice.

#Rebuilding the nesting: CSV back to JSON

CSV to JSON can reverse dotted headers with the Rebuild nested objects option: address.city becomes {"address": {"city": ...}}, and numeric segments such as tags.0 become array positions. Round-tripping the index-column CSV above shows what survives:

{
  "id": 1,
  "name": "Asha",
  "address": {
    "city": "Pune",
    "zip": 411001
  },
  "tags": [
    "admin",
    ""
  ]
}

The structure is back, but Asha's tags now contain an empty string that was only padding, and her ZIP code has become a number because Convert numbers & booleans is on by default. Turn that option off and every value, including id, comes back as a string. That is the general rule: a JSON to CSV to JSON round trip is lossless only if you keep arrays as JSON text, keep type detection off, and restore types from a schema that you know independently.

#Spreadsheet gotchas

Most CSVs end up in a spreadsheet, which applies its own interpretation on open:

• Leading zeros disappear. Excel reads 07001 as the number 7001. Import via the data import dialog and set the column to text, or deliver the file as XLSX.
• Long numbers lose digits. Spreadsheets store numbers as doubles and show 15 significant digits, so a 16- or 19-digit ID is rounded and displayed in scientific notation. Keep IDs as text.
• Dates are reformatted. 2026-09-26 may be displayed, and re-saved, in the local date format.
• Accents break without a BOM. Excel on Windows assumes a legacy code page for CSV unless the file starts with a UTF-8 byte-order mark. The converter's Excel-friendly (BOM) option adds one. Do not use it for files that a program will read, because some parsers treat the BOM as part of the first column name.
• Formula injection. A cell beginning with =, +, - or @ is evaluated as a formula when opened in a spreadsheet. If the JSON contains user-supplied text and the CSV will be opened by someone else, turn on the converter's Neutralize formulas option, which prefixes such cells with a single quote and leaves negative numbers such as -5 alone, or neutralise them in your own export code.
• Line endings. RFC 4180 specifies CRLF between records; virtually every modern reader also accepts LF. If a strict consumer insists on CRLF, run the file through the Line Ending Converter.

#Choosing a strategy

1. If the CSV is a transport format between programs, keep arrays as JSON, keep all values as text, and document the columns.
2. If a person will read it in a spreadsheet, flatten objects, join short arrays with ;, and explode the one array that the analysis is about.
3. If you must round-trip, keep a JSON Schema or a list of column types next to the CSV so types can be restored.
4. Preview the result as a table before you send it; the CSV Viewer shows exactly how columns and quoting came out.

Library defaults differ too. pandas' json_normalize, for example, flattens nested objects with the same dot separator but leaves arrays as Python lists, which to_csv writes as Python syntax (['admin']), not JSON. Check what your tool writes for arrays before another program depends on it.