JSON to CSV nested flattening guide

A JSON object can contain objects inside objects, arrays inside arrays, and optional fields at any level. CSV has one header row and one value per cell. There is no native CSV way to store a parent object with multiple child arrays without choosing a convention.

Flattening is therefore a modeling decision, not just a file conversion. The right CSV for invoice line items is different from the right CSV for one row per invoice. Decide what one row represents before writing code.

The record path is the array that becomes rows. In an invoice export, items might be the record path, while invoice number, customer, and date become metadata repeated on every item row.

With pandas, that looks like pd.json_normalize(data, record_path="items", meta=["invoice_id", "customer", "date"]). If fields are nested, meta paths can be lists such as ["customer", "name"]. Test with records that have missing arrays and missing fields.

Nested object keys need column names. Common separators are dots, underscores, or double underscores: customer.name, customer_name, or customer__name. Dots are readable but can conflict with systems that interpret dots as paths.

Pick a separator that the destination accepts and document it. Also handle collisions. A JSON object could contain both { "a.b": 1 } and { "a": { "b": 2 } }, which flatten to the same dotted name unless escaped.

Arrays that are not the record path need a rule. You can join values into one cell, keep JSON text inside the cell, create numbered columns, or explode into additional rows. Each choice loses something.

For analytics, child arrays usually belong in a separate CSV table with a key back to the parent. For spreadsheet review, joining short arrays may be acceptable. For later re-import, preserving JSON text in the cell may be less lossy than inventing columns.

Inspect several records before flattening: the smallest, the largest, one with missing fields, one with multiple children, and one with unusual characters. Schema drift is common in JSON exports, especially from APIs.

After writing CSV, count rows and compare against the chosen record path length. If the input has 100 invoices and 425 line items, an item-level CSV should have 425 rows, not 100. Row count mismatches reveal wrong paths or dropped children.

Stable keys are essential when exporting multiple CSV tables. If parent objects lack an ID, create one before exploding child arrays so rows can be joined later. Row order is not a safe relationship in spreadsheets or databases.

For API exports, sample more than one page of results. Later pages may contain optional fields, empty arrays, or object shapes not present in the first response. A flattening script based only on page one often drops columns silently.

Do not discard the original JSON after flattening if the data will be audited. CSV is a projection of one chosen shape. The source JSON is needed when someone later asks whether a missing column was absent, nested elsewhere, or dropped by the flattening rule.

Document the row meaning in the export name or README, such as one row per invoice item rather than one row per invoice.

A final flattening job should emit warnings when arrays are empty, fields are missing, or column names collide. Silent success is dangerous when the CSV is only one possible projection of nested data.