Flattening Nested JSON for CSV

The most common flattening strategy joins nested object keys with dots: { "user": { "name": "Sam", "role": "admin" } } becomes columns user.name and user.role with values Sam and admin. Some tools use underscores (user_name) or brackets (user[name]); pick one convention and keep it consistent across exports so Excel formulas and pivot tables remain stable.

Deep nesting—customer.address.geo.lat—can produce hundreds of columns when API schemas evolve. Preview column names before importing large files; wide CSVs with sparse data are hard to navigate in Excel and may exceed column limits in older tools. Consider projecting only the fields your report needs rather than flattening entire documents blindly. Document the projection list alongside the export job so the next analyst reproduces the same columns.

Arrays of primitives often stringify into a single cell—["red","blue"] becomes the text ["red","blue"] or red;blue depending on converter settings. Arrays of objects may expand to indexed columns (items.0.name, items.1.name), generate multiple CSV rows (one row per array element, duplicating parent fields), or require normalization into a separate related table.

Highly variable arrays—where each element has different keys—produce sparse CSV with many empty cells. An orders array where some line items include discount fields and others do not yields columns that are mostly blank. For analytics, normalizing into two CSV files (orders.csv and line_items.csv) linked by order_id is often cleaner than one impossibly wide sheet. Decide row grain before export: one row per order, per line item, or per shipment event.

Reporting dashboards usually want one row per entity (one row per order, one row per user). Choose row-expansion when child arrays represent repeatable sub-records—line items, event logs, survey responses. Choose single-row flattening with indexed columns when array length is bounded and small (three phone numbers max).

Data warehouse loads often prefer JSON columns preserved in a lake and flattened in SQL with LATERAL FLATTEN or equivalent. For ad hoc Excel exports, browser-based flattening tools provide immediate preview without standing up ETL infrastructure. Match the strategy to how the consumer will filter and aggregate. When in doubt, ask whether the spreadsheet user expects one row per parent or one row per child event.

Null and missing keys behave differently: null may become an empty cell while a missing key omits the column entirely for that row. Mixed types in the same logical field—sometimes a string, sometimes a number—break pivot tables and SUM formulas after export.

Unicode, emoji, and newline characters inside string values require UTF-8 encoding and proper CSV quoting. Embedded commas and quotes in flattened values must be escaped per RFC 4180 or Excel misaligns columns. Always spot-check rows with special characters before sharing exports company-wide.

Flattening is inherently lossy regarding structure unless you store enough metadata to rebuild nesting. CSV to JSON tools can infer object hierarchy from dot-notation column names, but array reconstruction requires consistent indexing (items.0, items.1) or separate files.

Do not expect CSV→JSON→CSV round trips to preserve original nesting without explicit structure rules documented alongside the export. For editable workflows, define a canonical flat schema and treat nested JSON as the system-of-record format, with CSV as a derived view.

Browser-based nested JSON converters excel at ad hoc exports with live column preview—ideal for product managers validating API samples. Scriptable CLI flatteners (jq, custom Node scripts) fit CI pipelines that emit nightly CSV snapshots. Pick tools that expose array expansion mode explicitly rather than guessing defaults.

Before sharing flattened CSV company-wide, strip internal-only fields (internal_ids, cost_basis, PII) at flatten time. Wide exports make accidental oversharing easy because obscure nested keys become visible columns. Column allow-lists beat deny-lists when exporting customer data. Review a 50-row sample with legal or compliance before scheduling automated nightly flatten jobs.