Why Nested JSON Resists CSV
CSV is a flat table. Nested JSON has objects inside objects and arrays of variable length. Flattening means choosing which nested paths become columns and which arrays become extra rows—or JSON strings in a cell.
Flattening Strategies
Pick deliberately; each loses different information.
| Strategy | Result | Best for |
|---|---|---|
| Dot-notation columns | customer.email as a column | Shallow nesting |
| Row explosion | One CSV row per array item | Line items, events |
| JSON-in-cell | Nested blob kept as text | Rarely; last resort |
| Multiple CSVs | orders.csv + items.csv | Relational analytics |
Step-by-Step: Online Conversion
For many API arrays of similar objects.
- Ensure the JSON is an array of records (or wrap a single object).
- Open the JSON to CSV Converter.
- Convert and preview headers—look for dotted nested names.
- Clean in the Online CSV Editor if needed.
- Download CSV or continue to CSV to Excel.
Step-by-Step: Python json_normalize
Great when arrays need controlled expansion.
import json
import pandas as pd
with open("orders.json") as f:
data = json.load(f)
# Flatten dict fields
orders = pd.json_normalize(data, sep=".")
# Or expand a nested list into rows
items = pd.json_normalize(
data,
record_path=["items"],
meta=["order_id", ["customer", "email"]],
sep=".",
)
items.to_csv("items.csv", index=False)Arrays of Different Lengths
If one order has 1 item and another has 12, exploding items to rows is usually clearer than creating item1_sku…item12_sku columns. Wide sparse CSVs become unusable quickly.
Real-World Examples
API payloads people flatten weekly.
E-commerce orders
Orders JSON → line-item CSV for fulfillment ops; order-level CSV for finance.
CRM export API
Nested company properties flattened with dotted headers for a spreadsheet hand-off.
Event payloads
Array of events normalized to one row per event for BI.
Common Mistakes
Flattening bugs look like “missing data.”
- Assuming every record has the same nested keys.
- Exploding multiple arrays at once and creating a Cartesian mess.
- Turning IDs into numbers during conversion.
- Losing array order when it mattered.
- Pretty-printing multi-megabyte JSON in Excel instead of converting properly.
Best Practices
Make the flat model explicit.
- Document the grain of each output CSV.
- Prefer multiple tables over monster wide sheets.
- Keep raw JSON immutable.
- Validate row counts vs array lengths.
- Use Convert CSV Online for quick passes; script complex explosions.
Why Use Convert CSV Online?
Convert CSV Online is free, browser-based, and requires no account for everyday conversions. The JSON to CSV Converter flattens many nested payloads instantly for Excel review. Client-side workflows work on Windows, macOS, and Linux browsers.
See headers first
If dotted columns look wrong, adjust the JSON shape before you build a pipeline.
Conclusion
Flattening JSON to CSV is a modeling choice: dotted columns, row explosions, or multiple files. Choose the grain, verify counts, and keep the original JSON as source of truth.
FAQ
How do I flatten nested JSON to CSV?
Use a converter for shallow objects, or json_normalize / custom scripts when arrays must become multiple rows. Decide the row grain first.
What does dot notation mean in CSV headers?
Nested object paths become column names like customer.email after flattening.
How do I flatten a JSON array field?
Usually emit one CSV row per array element, copying parent fields onto each row—or write a separate related CSV.
Can every JSON document become one CSV?
Not cleanly. Deep or irregular structures may need multiple tables or a different format.
Will flattening lose data?
It can if you drop paths or truncate arrays. Keep raw JSON and verify counts.
What tool flattens JSON to CSV online?
Convert CSV Online’s JSON to CSV Converter handles many common nested record arrays in the browser.
References
Convert your CSV in the browser
Preview, clean, and convert CSV files free with Convert CSV Online—no installation and no account required for everyday conversions.