How to Convert Nested JSON to CSV Without Losing Data

The problem, in one example

Take a typical API response: two records, each with a nested address object, a coordinate object nested inside that, and an array of tags.

[
  {
    "id": 1,
    "name": "Ada Lovelace",
    "address": { "city": "London", "geo": { "lat": 51.5, "lng": -0.12 } },
    "tags": ["math", "computing"]
  },
  {
    "id": 2,
    "name": "Grace Hopper",
    "address": { "city": "New York", "geo": { "lat": 40.7, "lng": -74.0 } },
    "tags": ["compilers"]
  }
]

A naive exporter — JSON.parse followed by joining each record's values with commas — produces this:

id,name,address,tags
1,Ada Lovelace,[object Object],"math,computing"
2,Grace Hopper,[object Object],compilers

Two things went wrong. The address object was stringified by the language rather than expanded, so its contents are gone. And tags was joined with a comma, the same character that separates columns, so any spreadsheet that opens this file without respecting the quotes will split the row incorrectly. Both problems come from the same root cause: nesting has to be resolved before encoding, not during it.

Strategy 1 — dot notation for nested objects

For objects, the answer is settled: walk the record recursively and name each leaf column by its full path, joined with dots. address.geo.lat is unambiguous, readable in a spreadsheet header, and — importantly — reversible, because a converter reading that column name can rebuild the original object.

id,name,address.city,address.geo.lat,address.geo.lng,tags.0,tags.1
1,Ada Lovelace,London,51.5,-0.12,math,computing
2,Grace Hopper,New York,40.7,-74.0,compilers,

Note what happened to row 2: Grace Hopper has one tag, Ada has two, so tags.1 is empty for her. That is correct behaviour, and it hints at the array problem below.

Strategy 2 — pick the right handling for arrays

Arrays have no single right answer, because three different data shapes hide behind the same bracket. Choose per column, based on what the array holds:

• Expand into indexed columns (tags.0, tags.1) when the array is a fixed-size tuple — coordinates, a start/end pair, three fixed scores. With variable-length arrays this explodes the column count and fills the file with blanks: one record with 200 elements adds 200 columns to every other row.
• Join into one cell with a separator that is not your delimiter — semicolon and pipe are the usual picks — when the array is a set of labels. This is the safest default for tags, categories, and permissions, and it keeps the file narrow.
• Keep it as raw JSON in a single quoted cell when the array holds objects, or when the CSV is an intermediate step and something downstream will parse the field again. You lose spreadsheet-level filtering on that column, but you lose no data.

id,name,address.city,tags
1,Ada Lovelace,London,"math; computing"
2,Grace Hopper,New York,compilers

There is a fourth option that is not flattening at all: explode the array into rows. If each element is a real entity — line items in an order, comments on a post — the honest table has one row per element with the parent fields repeated. That is a reshape, not a flatten, and it is what record_path does in pandas below.

Strategy 3 — take the union of keys, not the first record

Real exports are ragged. Optional fields appear only on some records, API versions add keys mid-file, and a nullable object is sometimes null and sometimes a full subtree. Any converter that reads the column headers from record one will silently drop every field introduced later.

The correct approach is to walk all records, collect the union of every flattened key, and use that as the column set — leaving cells empty where a record has no value. It costs one extra pass over the data and is the difference between a complete export and one that looks fine until someone notices missing data.

Doing it in the browser

All three strategies are options in the JSON to CSV converter: nested objects flatten to dot notation by default, arrays can expand, join with a separator you choose, or stay as JSON, and the column set is always the union across records. NDJSON is detected automatically, and if your records sit under a wrapper key like {"data": [...]} you can point the tool at that key instead of the root.

Conversion runs in your own tab — nothing is uploaded — which matters when the nested JSON is a customer export or an API dump with credentials in it. If the result is headed for Microsoft Excel, the JSON to Excel page is the same engine with the BOM, CRLF line endings, and formula-safe cells already switched on.

Doing it in Python with pandas

pd.json_normalize is the standard tool, and it implements strategy 1 correctly — but it does not touch lists, which is the single most common surprise.

import json
import pandas as pd

with open("data.json") as f:
    records = json.load(f)

# sep="." gives address.geo.lat instead of the default address_geo_lat
df = pd.json_normalize(records, sep=".")

# json_normalize does NOT flatten lists — without this, tags is written
# to CSV as the Python repr "['math', 'computing']"
df["tags"] = df["tags"].apply(lambda v: "; ".join(map(str, v)) if isinstance(v, list) else v)

# utf-8-sig writes the BOM Excel needs to detect UTF-8
df.to_csv("data.csv", index=False, encoding="utf-8-sig")

Two flags worth knowing: max_level=1 stops the recursion at a given depth, which is how you avoid a thousand columns from a deeply nested config blob, and errors="ignore" keeps a missing meta path from raising on ragged data. When the array is the thing you actually want rows for, use record_path:

# One row per line item, with the order and customer fields repeated
df = pd.json_normalize(
    payload,
    record_path=["orders", "items"],
    meta=["customer_id", ["orders", "id"]],
    sep=".",
)

Doing it with jq

For flat arrays of objects, jq gets you a CSV in one line. For nested input, use leaf_paths to build the dot-notation keys first, then run the flat conversion on the result.

# Flat records: take the column set from the first object
jq -r '(.[0] | keys_unsorted) as $cols
       | $cols, (.[] | [.[$cols[]]])
       | @csv' data.json > data.csv

# Nested records: flatten every leaf to a dot-notation key first
jq -r '[.[] | [leaf_paths as $p | {key: ($p | join(".")), value: getpath($p)}] | from_entries]' \
   data.json > flat.json

The caveat is the same one as strategy 3: keys_unsorted on the first element takes the column set from one record. On ragged data, collect the union first with [.[] | keys] | add | unique.

Edge cases that still bite

• Keys containing dots. A literal key like user.name is indistinguishable from a nested user → name once flattened. It converts fine; it just cannot round-trip back to the original shape.
• Empty objects and empty arrays. They have no leaves, so they produce no columns at all — the field disappears rather than becoming an empty cell. If the presence of the key matters downstream, keep those branches as JSON.
• null versus empty string. CSV has no null. Decide explicitly whether null becomes an empty cell or a literal text like NULL, and stay consistent — database import tools treat the two very differently.
• Numbers that are really identifiers. Long IDs and zero-padded codes survive the CSV intact but get mangled by Excel's type detection on open. Import via Data → From Text/CSV and mark those columns as Text.
• Quoting. Any value containing the delimiter, a double quote, or a newline must be wrapped in quotes with embedded quotes doubled, per RFC 4180. This is where hand-rolled exporters break most often, and joined array cells make it more likely.

The short version

Flatten objects with dot notation, choose array handling per column based on what the array means, and build your column set from every record rather than the first one. Get those three right and nested JSON converts to CSV without losing anything you cared about.

Going the other direction later? The CSV to JSON converter reads those same dot-notation headers and rebuilds the nested objects, so a flatten and a reload are a genuine round trip.