API responses rarely look like a spreadsheet. An order contains a customer object, the customer contains an address, and the order contains a list of items. CSV, on the other hand, is a flat grid: one row per record, one value per cell. Turning one into the other means deciding what to do with every nested object and every array. There is no single right answer, so it helps to know the options.

The example 

[ { "id": 1001, "customer": { "name": "Leila", "address": { "city": "Fes", "zip": "30000" } }, "items": [ { "sku": "A1", "qty": 2 }, { "sku": "B7", "qty": 1 } ], "paid": true }, { "id": 1002, "customer": { "name": "Tom", "address": { "city": "Lyon" } }, "items": [ { "sku": "A1", "qty": 5 } ], "paid": false, "coupon": "WELCOME" } ]

Strategy 1: Nested objects become dot-notation columns

Each nested key is joined to its parent with a dot. customer.address.city becomes a column name, and the value goes in that column. Objects map perfectly to this approach because each object has exactly one value per key.

id,customer.name,customer.address.city,customer.address.zip,paid,coupon 1001,Leila,Fes,30000,true, 1002,Tom,Lyon,,false,WELCOME

Two details matter here. First, the column list must be built from all records, not just the first one — otherwise coupon, which only appears in the second order, disappears. Second, missing values become empty cells rather than shifting the row.

Strategy 2: Arrays become JSON text in one cell

Arrays are harder: an order may have one item or fifty. The simplest lossless option is to keep the array as JSON text in a single cell:

id,customer.name,items 1001,Leila,"[{""sku"":""A1"",""qty"":2},{""sku"":""B7"",""qty"":1}]" 1002,Tom,"[{""sku"":""A1"",""qty"":5}]"

Nothing is lost and the number of columns stays fixed, but the items are not individually sortable or filterable in a spreadsheet. This is what the JSON to CSV converter does by default, combined with dot notation for objects.

Strategy 3: Explode arrays into one row per element

When the array holds the data you want to analyse — order lines, transactions, events — create one row per element and repeat the parent fields on each row:

id,customer.name,sku,qty 1001,Leila,A1,2 1001,Leila,B7,1 1002,Tom,A1,5

This is the right shape for pivot tables and SQL imports: you can now sum quantities per SKU. The trade-off is duplication, and you can only explode one array per table — exploding two independent arrays at once multiplies rows into a meaningless cross product. If a record has several arrays, create one table per array, linked by the parent ID.

Strategy 4: Numbered columns

Some tools write arrays as items.0.sku, items.1.sku and so on. It works for short arrays with a known maximum length, such as a list of up to three phone numbers, but it produces wide, sparse tables for anything longer and the column count changes with the data.

Choosing a strategy

  • Just need everything in Excel? Dot notation plus JSON text cells (strategies 1 and 2). Fast and lossless.
  • Analysing the contents of an array? Explode it (strategy 3), one table per array.
  • Loading into a database? Explode arrays into separate tables with a foreign key — the same idea as strategy 3, normalised.
  • Arrays of a few fixed items? Numbered columns can be acceptable (strategy 4).

Doing it in Python

pandas' json_normalize covers strategies 1 and 3. Without record_path, it flattens objects with dots; with it, it explodes an array and copies the parent fields you list in meta:

import json import pandas as pd with open("orders.json", encoding="utf-8") as f: orders = json.load(f) # Strategy 1: one row per order, nested objects as dot columns pd.json_normalize(orders).to_csv("orders.csv", index=False) # Strategy 3: one row per item, with order fields repeated items = pd.json_normalize( orders, record_path="items", meta=["id", ["customer", "name"]], ) items.to_csv("order_items.csv", index=False)

Pitfalls to watch for

  • Inconsistent records: fields that appear in only some objects must still get a column.
  • Keys containing dots: a key like "v1.2" becomes ambiguous after flattening. Rename such keys first if you need to convert back.
  • Large integers: IDs longer than 15–16 digits lose precision in JavaScript and spreadsheets. Keep them as strings in the JSON.
  • Commas and quotes in values: always write CSV with a proper library or converter so these are quoted correctly.

Summary

Flatten objects with dot-notation column names, keep arrays as JSON text when you need a quick lossless export, and explode arrays into separate rows or tables when you need to analyse them. Try the JSON to CSV converter on your own data to see the default approach in action, or use JSON to Excel to get a workbook directly.

FAQ

Can a flattened CSV be converted back to the original JSON?
Dot-notation columns can be turned back into nested objects, and JSON text cells can be parsed again. Exploded rows need to be regrouped by the parent ID. The round trip is easiest when keys contain no dots.

Why not just use one column per array element?
It works for short, fixed-length arrays, but for variable lists it creates very wide, mostly empty tables whose columns change whenever the data does.

What about deeply nested JSON?
Dot notation handles any depth for objects. For deeply nested arrays, explode one level at a time into separate tables.

Originally published at DataSwitch Pro.