Flattening Nested JSON for Excel: A Practical Guide

APIs return JSON. Stakeholders want Excel. The gap between them is “flattening” — turning a tree of nested objects into a flat grid.

Strategy 1: Dot-notation flattening (default)

Every leaf field becomes a column, with dots joining the path:

{ "user": { "name": "Ada", "addr": { "city": "London" } } }
↓
user.name | user.addr.city
Ada       | London

This is what our JSON-to-Excel converter does by default.

Strategy 2: Explode arrays into rows

If you have { "id": 1, "items": [{"sku": "A"}, {"sku": "B"}] } and want one row per item, you need to explode:

id | items.sku
1  | A
1  | B

Pre-process with jq:

jq '[.[] | . as $parent | .items[] | $parent + {item: .}]' input.json

Or in Pandas: df.explode('items').

Strategy 3: One sheet per nested array

For deeply nested data, multiple sheets joined by ID is cleaner:

• Sheet 1 (orders): id, customer, total
• Sheet 2 (line_items): order_id, sku, qty, price

Mimics relational database design.

Strategy 4: Filter to leaf fields only

jq '[.[] | { id, name: .user.name, city: .user.addr.city }]' input.json

Cleaner than flattening everything and deleting columns afterwards.

Edge cases

• Heterogeneous keys: missing fields become blank cells.
• Null vs missing: both become empty cells.
• Type coercion: "42" imports as text, not number — cast in JSON first.
• Date strings: ISO dates usually import correctly.

The fast path

For most JSON-to-Excel jobs, the in-browser converter using Strategy 1 is enough. Pre-process with jq only when you need exploded rows or filtered fields.