An API gives you nested orders, and a spreadsheet wants rows and columns. Flattening can expose paths such as customer.name, but it does not automatically decide how a list of line items should become rows. That distinction determines whether the resulting table answers your question or quietly loses relationships.
Flattening paths is not the same as exporting a table
A path flattener turns nested objects into a single-level object with keys that encode their location. It may produce customer.name and items[0].sku. That is still JSON: one object with flat keys. CSV or a spreadsheet table needs a row policy and column policy. The tool does not choose which repeated array element becomes which row.
{
"orderId": "o-14",
"customer": { "name": "Mina" },
"items": [{ "sku": "MUG-02", "qty": 2 }, { "sku": "BOOK-01", "qty": 1 }]
}Array indexes preserve position, not table relationships
Flattened object paths
orderId: "o-14"
customer.name: "Mina"
items[0].sku: "MUG-02"
items[1].sku: "BOOK-01"One possible item-per-row view
orderId | customer.name | sku | qty
o-14 | Mina | MUG-02 | 2
o-14 | Mina | BOOK-01 | 1Indexes keep item positions available, which is helpful when you need to inspect or transform the original shape. An item-per-row table repeats parent fields and expands each line item. A different report may instead keep the items as a JSON column. Decide the relationship before flattening; do not discard indexes if they distinguish values.
Choose the row shape deliberately
For pandas, json_normalize can normalize nested records. Its record_path identifies a list to turn into records, while meta carries parent fields onto each row. The right path depends on the payload and can fail when a record is missing that list. The pandas documentation shows the available options.
import pandas as pd
rows = pd.json_normalize(
orders,
record_path="items",
meta=["orderId", ["customer", "name"]],
)For a one-off spreadsheet, inspect a small sample first and confirm that IDs repeat as intended. Nested arrays can require multiple tables or an explicit explode step; flattening every branch into one row can multiply records unexpectedly.
A safe preparation workflow
- 1Inspect the original nesting
- 2Choose which array defines each row
- 3Flatten paths or normalize records
- 4Check IDs, row counts, and empty values
Empty arrays, nulls, and absent keys deserve separate treatment. An empty list may mean “no records”; null may mean “unknown”; a missing field may mean “not returned.” A spreadsheet conversion can collapse these distinctions if you let defaults fill every blank the same way.
Start with JSON Flatten to inspect path-key output. Use the JSON Viewer when you need to trace the original hierarchy, and review how to read nested JSON before deciding where arrays become records.
Choose the table’s grain first
A row should represent one thing: an order, a line item, or a customer. If one order contains several items, an item-level table repeats order identifiers on each row. If you instead make one row per order, item fields need a separate representation. Naming the row grain before transformation prevents accidental duplication and confusing totals.
Preserve identifiers and missing-value meaning
Carry stable parent IDs into expanded child rows so they can be joined back later. Keep null, absent, and empty-list cases distinct if the analysis depends on them. Validate a small output by comparing its row count and a few records against the source JSON before loading the full export into a workbook.
Tags
- flatten JSON for spreadsheet
- nested JSON to Excel
- JSON array to rows
- JSON to flat key values
- flatten JSON for CSV
- pandas json_normalize
- nested fields to columns
- JSON data preparation