Data conversion
How to convert nested JSON to CSV
CSV stores rows and columns, while JSON can contain nested objects, arrays, and mixed structures. A reliable conversion requires clear rules for selecting records, naming columns, and representing nested values.
Document summary
Choose one array of similar records, flatten object paths into stable column names, handle nested arrays deliberately, and inspect the output before importing it into a spreadsheet or database.
Key takeaways
- The cleanest CSV source is an array of records with a reasonably consistent shape.
- Flattened column names should preserve the original path and avoid ambiguous collisions.
- Arrays of objects often need a second table instead of being forced into one cell.
Understand the difference between JSON and CSV
JSON represents hierarchical data. CSV represents a flat table where every row shares a set of columns.
The conversion process therefore needs a policy for nested objects, arrays, missing values, and data types.
| JSON feature | CSV challenge | Common approach |
|---|---|---|
| Nested object | No nested cell structure | Flatten the path into columns. |
| Array of primitives | One cell cannot hold multiple typed values | Join values with a chosen separator. |
| Array of objects | Represents a related table | Create repeated rows or a separate CSV. |
| Missing property | Rows have different shapes | Leave the cell empty or use a defined placeholder. |
Choose the record array
The best source is an array of similar objects. When the JSON root is an object, identify the property that contains the records you want to export.
{
"page": 1,
"results": [
{"id": 1, "name": "Ada"},
{"id": 2, "name": "Lin"}
]
}[
{"id": 1, "name": "Ada"},
{"id": 2, "name": "Lin"}
]Flatten nested objects with stable paths
Dotted paths make the original hierarchy visible without creating nested CSV cells. For example, profile.city becomes a column name while preserving where the value came from.
{
"id": 1,
"profile": {
"name": "Ada",
"city": "London"
}
}id,profile.name,profile.city
1,Ada,LondonHandle arrays deliberately
Primitive arrays can often be joined into one cell. Arrays of objects usually represent one-to-many relationships and need a more careful choice.
- Join small primitive arrays with a delimiter such as a vertical bar.
- Repeat the parent row for each child record when that supports the destination.
- Create a separate child table and keep a parent identifier.
- Store compact JSON in one cell only when the destination can parse it later.
Preserve values and data types
CSV has no universal type system. Spreadsheet software may reinterpret identifiers, dates, leading zeros, and long numbers.
| Value | Risk | Safer export decision |
|---|---|---|
| 00123 | Leading zeros may disappear | Treat as text when it is an identifier. |
| 2026-07-19 | May be reformatted by locale | Use an explicit ISO date and document the column. |
| 1234567890123456789 | Spreadsheet precision loss | Export as text when exact digits matter. |
| true or false | May become inconsistent labels | Use one documented representation. |
Build a complete and predictable header set
Records may contain optional fields. Scan the relevant records to collect the full column set, then output the same columns in the same order for every row.
Escape commas, quotes, and line breaks correctly
A CSV field containing a delimiter, quotation mark, or line break must be quoted. Quotation marks inside the field are represented by doubling them.
1,Ada,Hello, world1,Ada,"Hello, world"Protect spreadsheet exports from formula injection
Spreadsheet applications may treat cells beginning with characters such as equals, plus, minus, or at sign as formulas. Untrusted values should be escaped or imported with safe text handling.
Review the generated CSV before import
Preview the header row, several records, missing values, and quoted fields before downloading. Test the file in the actual destination when data types or nested relationships matter.
- 1
Confirm the selected record array.
- 2
Review flattened column names.
- 3
Inspect array handling.
- 4
Check identifiers, dates, and large numbers.
- 5
Open a sample in the destination application.
