Convert Nested JSON to Flat CSV Without Losing Data Structure

You call an API. It returns deeply nested JSON. You need to analyze it in Excel.

The problem: Excel only understands flat tables. JSON can have unlimited nesting levels.

Most converters just fail or destroy your data:

• Nested objects become [object Object]
• Arrays get stringified as ["item1","item2"]
• Hierarchical relationships are lost

This guide shows you exactly how to flatten nested JSON into CSV while preserving the structure that matters.

---

The Challenge: JSON Nesting vs CSV Flatness

What JSON Allows

{
  "user": {
    "name": "John",
    "contact": {
      "email": "[email protected]",
      "phone": {
        "home": "555-1234",
        "work": "555-5678"
      }
    },
    "orders": [
      { "id": 1, "total": 100 },
      { "id": 2, "total": 200 }
    ]
  }
}

Nesting levels: 4 deep
Array inside: orders has 2 items
Total unique paths: 7

What CSV Supports

user.name,user.contact.email,user.contact.phone.home,user.contact.phone.work
John,[email protected],555-1234,555-5678

Nesting: None (flat table)
Arrays: Can't represent them (one row per record)

---

Strategy 1: Dot-Notation Flattening (Nested Objects)

Use Case

API returns user profiles with nested address/contact info.

Example Input

{
  "users": [
    {
      "id": 1,
      "name": "Alice",
      "address": {
        "city": "NYC",
        "country": "USA"
      }
    },
    {
      "id": 2,
      "name": "Bob",
      "address": {
        "city": "London",
        "country": "UK"
      }
    }
  ]
}

Desired CSV Output

id,name,address.city,address.country
1,Alice,NYC,USA
2,Bob,London,UK

How to Implement

JavaScript:

function flattenObject(obj, prefix = '') {
  return Object.keys(obj).reduce((acc, key) => {
    const value = obj[key];
    const newKey = prefix ? `${prefix}.${key}` : key;
    
    if (typeof value === 'object' && value !== null && !Array.isArray(value)) {
      // Recursively flatten nested objects
      Object.assign(acc, flattenObject(value, newKey));
    } else {
      acc[newKey] = value;
    }
    
    return acc;
  }, {});
}

// Usage
const user = {
  id: 1,
  address: { city: "NYC", country: "USA" }
};

flattenObject(user);
// Result: { id: 1, "address.city": "NYC", "address.country": "USA" }

Python (pandas):

import pandas as pd
from pandas import json_normalize

data = {
  "users": [
    {"id": 1, "address": {"city": "NYC"}},
    {"id": 2, "address": {"city": "London"}}
  ]
}

# Flatten nested structure
df = json_normalize(data['users'])
df.to_csv('output.csv', index=False)

# Result columns: id, address.city

---

Strategy 2: Array Expansion (One-to-Many Relationships)

Use Case

E-commerce orders where each order has multiple items.

Example Input

{
  "orders": [
    {
      "order_id": 1,
      "customer": "Alice",
      "items": [
        { "product": "Widget", "qty": 2 },
        { "product": "Gadget", "qty": 1 }
      ]
    }
  ]
}

Problem

One order has multiple items. How to represent in CSV?

Solution A: Expand to Multiple Rows

order_id,customer,items.product,items.qty
1,Alice,Widget,2
1,Alice,Gadget,1

Pros: Preserves all detail
Cons: Duplicate order info

Solution B: Keep Array as JSON String

order_id,customer,items
1,Alice,"[{""product"":""Widget"",""qty"":2},{""product"":""Gadget"",""qty"":1}]"

Pros: One row per order
Cons: Can't analyze items in Excel

Implementation (Expand Rows)

Python:

import pandas as pd
from pandas import json_normalize

data = {
  "orders": [
    {
      "order_id": 1,
      "customer": "Alice",
      "items": [
        {"product": "Widget", "qty": 2},
        {"product": "Gadget", "qty": 1}
      ]
    }
  ]
}

# Normalize with array expansion
df = json_normalize(
    data['orders'],
    record_path='items',
    meta=['order_id', 'customer'],
    meta_prefix='order_'
)

df.to_csv('orders_expanded.csv', index=False)

Output:

product,qty,order_order_id,order_customer
Widget,2,1,Alice
Gadget,1,1,Alice

---

Strategy 3: Mixed Nesting (Objects + Arrays)

Use Case

GitHub API response with nested user and commit history.

Example Input

{
  "repo": {
    "name": "my-project",
    "owner": {
      "login": "alice",
      "email": "[email protected]"
    },
    "commits": [
      { "sha": "abc123", "message": "Initial commit" },
      { "sha": "def456", "message": "Add feature" }
    ]
  }
}

Desired Output

Option 1: Expand commits (multiple rows)

repo.name,repo.owner.login,repo.owner.email,commits.sha,commits.message
my-project,alice,[email protected],abc123,Initial commit
my-project,alice,[email protected],def456,Add feature

Option 2: Separate tables

• repos.csv: name, owner.login, owner.email
• commits.csv: repo_name, sha, message

Implementation

pandas (Option 1 - expand):

df = json_normalize(
    [data['repo']],
    record_path='commits',
    meta=[['name'], ['owner', 'login'], ['owner', 'email']],
    meta_prefix='repo_'
)

---

Common Pitfalls and Fixes

Pitfall 1: Inconsistent Schema

Problem:

[
  { "id": 1, "contact": { "email": "[email protected]" } },
  { "id": 2, "contact": { "phone": "555-1234" } }  // Different keys!
]

CSV Result:

id,contact.email,contact.phone
1,[email protected],
2,,555-1234

Fix: All rows get all columns. Missing values become empty cells.

---

Pitfall 2: Deeply Nested (5+ Levels)

Problem:

{
  "a": {
    "b": {
      "c": {
        "d": {
          "e": "value"
        }
      }
    }
  }
}

CSV Column: a.b.c.d.e

Issue: Column name too long, hard to read.

Fix:

• Manually pick which levels to keep
• Create custom flattening logic that stops at level 3

---

Pitfall 3: Empty Arrays

Problem:

{ "order_id": 1, "items": [] }

If expanding rows: Order disappears entirely (0 rows)

Fix: Check for empty arrays before expanding:

if len(order['items']) == 0:
    # Create one row with null items
    order['items'] = [{"product": None, "qty": None}]

---

Tools Comparison

Tool	Nested Objects	Arrays	Custom Logic	Speed
Python pandas	✅ Excellent	✅ Expand or keep	✅ Full control	Fast (1M rows)
jq (CLI)	✅ Good	⚠️ Manual	✅ Full control	Very fast
Online converters	⚠️ Limited	❌ Usually stringify	❌ No control	Slow
Excel Power Query	✅ Good	⚠️ Manual expand	⚠️ Limited	Slow (>10k rows)

---

Real-World Examples

Example 1: Twitter API → CSV

API Response:

{
  "data": [
    {
      "id": "123",
      "text": "Hello world",
      "author": {
        "username": "alice",
        "followers": 1000
      },
      "entities": {
        "hashtags": [
          {"tag": "coding"},
          {"tag": "python"}
        ]
      }
    }
  ]
}

Flatten Strategy:

• Flatten author with dot notation
• Expand hashtags to multiple rows

Result:

id,text,author.username,author.followers,entities.hashtags.tag
123,Hello world,alice,1000,coding
123,Hello world,alice,1000,python

---

Example 2: Shopify Orders API

API Response:

{
  "orders": [
    {
      "id": 1001,
      "customer": {"name": "Bob"},
      "line_items": [
        {"title": "T-Shirt", "price": 20},
        {"title": "Jeans", "price": 50}
      ]
    }
  ]
}

Business Need: Analyze items sold (not orders).

Solution: Expand line_items.

Python:

df = json_normalize(
    data['orders'],
    record_path='line_items',
    meta=['id', ['customer', 'name']]
)

Result:

title,price,id,customer.name
T-Shirt,20,1001,Bob
Jeans,50,1001,Bob

---

Decision Matrix: When to Expand vs Keep Arrays

Scenario	Expand Arrays	Keep as JSON String
Analyze array items individually	✅ Yes	❌ No
Arrays have 1-3 items max	✅ Yes	Either
Arrays can have 100+ items	⚠️ Maybe	✅ Yes (avoid huge files)
Need to import to Excel	✅ Yes	❌ No
Need to re-import to API	❌ No	✅ Yes

---

FAQ

Q: What if my JSON is 10MB+ with deep nesting?
A: Use streaming JSON parsers (ijson in Python) to process in chunks. Don't load entire file into memory.

Q: Can I reverse the process (CSV back to nested JSON)?
A: Yes, but you need to know which columns were originally nested. Store metadata about the schema.

Q: How do I handle null vs empty string vs missing key?
A: pandas treats all three as NaN by default. Use keep_default_na=False if you need to distinguish.

Q: What about arrays of primitives (not objects)?

{"tags": ["red", "blue", "green"]}

A: Join them into a single string: "red,blue,green" or "red|blue|green".

---

Best Practices

✅ Document your flattening strategy (which arrays expanded vs kept)
✅ Validate row count (did expanding arrays create expected number of rows?)
✅ Check for data loss (compare unique IDs in JSON vs CSV)
✅ Use consistent delimiters (commas in CSV data → use pipes | as separator)
✅ Handle missing keys (set defaults to avoid errors)
✅ Test with edge cases (empty arrays, null values, very deep nesting)

---

Conclusion

Converting nested JSON to CSV isn't "just flatten everything." You need to decide:

1. Which nested objects to flatten (with dot notation)
2. Which arrays to expand (creating multiple rows)
3. Which arrays to keep as strings (when explosion would be too large)

The right strategy depends on your use case:

• Analysis in Excel? Expand arrays.
• Import to database? Keep structure, use multiple tables.
• Just need a quick look? Dot notation only.

When in doubt, use Python + pandas for full control.