One of the biggest challenges when converting JSON to CSV is handling nested objects. While JSON thrives on hierarchical structures, CSV is inherently flat. This guide shows you exactly how to flatten nested JSON for clean CSV exports.

Whether you're dealing with API responses, configuration files, or complex data structures, you'll learn multiple flattening strategies and when to use each.

Why Nested JSON is a Problem for CSV

CSV (Comma-Separated Values) is a flat, tabular format. Each row represents a record, and each column represents a field. There's no concept of hierarchy or nesting.

Simple JSON (works fine):

{
  "name": "Alice",
  "age": 30,
  "city": "Paris"
}

Simple CSV:

name,age,city
Alice,30,Paris

Nested JSON (problematic):

{
  "name": "Alice",
  "age": 30,
  "address": {
    "city": "Paris",
    "country": "France",
    "postalCode": "75001"
  }
}

Question: How do we represent address.city in CSV?

The answer depends on your flattening strategy.

Flattening Strategy 1: Dot Notation

The most common and intuitive approach is dot notation - use dots to represent nesting levels.

Basic Example

Nested JSON:

{
  "user": {
    "name": "Alice",
    "email": "[email protected]"
  },
  "role": "Developer"
}

Flattened CSV:

user.name,user.email,role
Alice,[email protected],Developer

How it works:

user.name becomes a column header
Nested path: object.property.subproperty
Preserves full data structure
No information loss

Deep Nesting (3+ Levels)

JSON:

{
  "company": {
    "name": "TechCorp",
    "location": {
      "address": {
        "street": "123 Main St",
        "city": "Paris",
        "coordinates": {
          "lat": 48.8566,
          "lng": 2.3522
        }
      }
    }
  }
}

Flattened CSV:

company.name,company.location.address.street,company.location.address.city,company.location.address.coordinates.lat,company.location.address.coordinates.lng
TechCorp,123 Main St,Paris,48.8566,2.3522

Pros:

Complete data preservation
Predictable column names
Easy to reverse (CSV back to JSON)

Cons:

Very long column names for deep nesting
Can be hard to read in spreadsheets
May hit column name length limits in some tools

When to use: API responses, configuration data, any scenario where you need to preserve exact structure

Flattening Strategy 2: Underscore Notation

Similar to dot notation, but uses underscores instead.

JSON:

{
  "user": {
    "profile": {
      "firstName": "Alice",
      "lastName": "Smith"
    }
  }
}

Flattened CSV:

user_profile_firstName,user_profile_lastName
Alice,Smith

Pros:

More database-friendly (no escaping needed in SQL)
Easier to use in some programming languages
Visually cleaner than dots for some users

Cons:

Ambiguous if original keys contain underscores
Example: user_name could be {user: {name}} OR {user_name}

When to use: Database imports, SQL-heavy workflows, when dot notation causes issues

Flattening Strategy 3: Custom Separator

Use your own separator for clarity.

Options:

Colon: user:profile:name
Slash: user/profile/name
Pipe: user|profile|name

When to use: Rare cases where both dots and underscores appear in original keys

Handling Arrays in Nested JSON

Arrays add another layer of complexity to flattening.

Array of Primitives

JSON:

{
  "name": "Alice",
  "skills": ["JavaScript", "Python", "React"]
}

Option 1: Join into string

name,skills
Alice,"JavaScript,Python,React"

Option 2: Index-based columns

name,skills.0,skills.1,skills.2
Alice,JavaScript,Python,React

Option 3: Multiple rows (one per array item)

name,skill
Alice,JavaScript
Alice,Python
Alice,React

Recommendation: Option 1 (joined string) for most cases - compact and reversible

Array of Objects (Complex Case)

JSON:

{
  "company": "TechCorp",
  "employees": [
    {
      "name": "Alice",
      "role": "Developer",
      "salary": 80000
    },
    {
      "name": "Bob",
      "role": "Designer",
      "salary": 75000
    }
  ]
}

Challenge: Can't fit multiple employees in one row

Solution: Expand to multiple rows

company,employees.name,employees.role,employees.salary
TechCorp,Alice,Developer,80000
TechCorp,Bob,Designer,75000

Result: Company name repeats for each employee (unavoidable in flat CSV)

Alternative: Keep as JSON string

company,employees
TechCorp,"[{""name"":""Alice"",""role"":""Developer"",""salary"":80000},{""name"":""Bob"",""role"":""Designer"",""salary"":75000}]"

When to use each:

Expand to rows: When you need to analyze individual array items
Keep as JSON string: When the array is metadata you rarely access

Nested Arrays

JSON:

{
  "user": "Alice",
  "projects": [
    {
      "name": "Project A",
      "tags": ["urgent", "backend"]
    },
    {
      "name": "Project B",
      "tags": ["frontend", "design"]
    }
  ]
}

This creates a matrix:

User has multiple projects
Each project has multiple tags

Flatten strategy:

user,project.name,project.tags
Alice,Project A,"urgent,backend"
Alice,Project B,"frontend,design"

Or fully expanded:

user,project.name,tag
Alice,Project A,urgent
Alice,Project A,backend
Alice,Project B,frontend
Alice,Project B,design

Trade-off: More rows = easier to query, but more duplication

Practical Flattening Examples

Example 1: E-commerce Order

Nested JSON:

{
  "orderId": "ORD-123",
  "customer": {
    "name": "Alice Smith",
    "email": "[email protected]",
    "address": {
      "street": "123 Main St",
      "city": "Paris",
      "postalCode": "75001"
    }
  },
  "items": [
    {
      "product": "Laptop",
      "quantity": 1,
      "price": 1299.99
    },
    {
      "product": "Mouse",
      "quantity": 2,
      "price": 29.99
    }
  ],
  "total": 1359.97
}

Flattened CSV (one row per item):

orderId,customer.name,customer.email,customer.address.street,customer.address.city,customer.address.postalCode,item.product,item.quantity,item.price,total
ORD-123,Alice Smith,[email protected],123 Main St,Paris,75001,Laptop,1,1299.99,1359.97
ORD-123,Alice Smith,[email protected],123 Main St,Paris,75001,Mouse,2,29.99,1359.97

Note: Order details and customer info repeat - this is normal for CSV

Example 2: GitHub API Response

API Response:

{
  "name": "awesome-project",
  "owner": {
    "login": "alice",
    "id": 12345,
    "url": "https://github.com/alice"
  },
  "stargazers_count": 1250,
  "forks_count": 340,
  "topics": ["javascript", "react", "opensource"]
}

Flattened CSV:

name,owner.login,owner.id,owner.url,stargazers_count,forks_count,topics
awesome-project,alice,12345,https://github.com/alice,1250,340,"javascript,react,opensource"

Use case: Analyze multiple GitHub repos in Excel

Example 3: Nested Configuration

Config JSON:

{
  "server": {
    "host": "localhost",
    "port": 8080,
    "ssl": {
      "enabled": true,
      "certPath": "/etc/ssl/cert.pem"
    }
  },
  "database": {
    "host": "db.example.com",
    "port": 5432,
    "credentials": {
      "username": "admin",
      "password": "secret"
    }
  }
}

Flattened CSV:

server.host,server.port,server.ssl.enabled,server.ssl.certPath,database.host,database.port,database.credentials.username,database.credentials.password
localhost,8080,true,/etc/ssl/cert.pem,db.example.com,5432,admin,secret

Use case: Compare configurations across environments

Advanced Techniques

Selective Flattening

Don't flatten everything - keep some nested data as JSON strings.

JSON:

{
  "id": 1,
  "name": "Alice",
  "metadata": {
    "created": "2026-01-15",
    "updated": "2026-02-10",
    "tags": ["user", "active"],
    "preferences": {
      "theme": "dark",
      "language": "en"
    }
  }
}

Flatten only top level:

id,name,metadata
1,Alice,"{""created"":""2026-01-15"",""updated"":""2026-02-10"",""tags"":[""user"",""active""],""preferences"":{""theme"":""dark"",""language"":""en""}}"

When to use: When nested data is rarely accessed or too complex to flatten usefully

Maximum Depth Limit

Flatten only to a certain depth, keep deeper levels as JSON.

JSON (5 levels deep):

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

Flatten to depth 2:

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

When to use: Very deep nesting (4+ levels) where full flattening creates unusable column names

Smart Column Naming

Shorten predictable paths for readability.

Before:

api_response_data_user_profile_contact_email

After (simplified):

user.email

How: Drop redundant prefixes that add no information

Tools for Flattening JSON

Online Tools

jconvert.dev (recommended)

Automatic dot notation flattening
Handles arrays intelligently
100% client-side (privacy-first)
Free, no registration

Try it now:

{
  "user": {
    "name": "Alice",
    "address": {
      "city": "Paris"
    }
  }
}

Convert to flattened CSV →

Command-Line Tools

jq (flatten to depth 1):

jq -r '[leaf_paths as $path | {"key": $path | join("."), "value": getpath($path)}] | from_entries' data.json

Python (custom flattener):

def flatten_json(nested_json, separator='.'):
    def flatten(x, name=''):
        if type(x) is dict:
            for key in x:
                flatten(x[key], name + key + separator)
        elif type(x) is list:
            for i, item in enumerate(x):
                flatten(item, name + str(i) + separator)
        else:
            out[name[:-1]] = x
    out = {}
    flatten(nested_json)
    return out

# Usage
import json
with open('data.json') as f:
    nested = json.load(f)
flat = flatten_json(nested)
print(json.dumps(flat, indent=2))

JavaScript:

function flattenJSON(obj, prefix = '', result = {}) {
  for (let key in obj) {
    const newKey = prefix ? `${prefix}.${key}` : key;
    if (typeof obj[key] === 'object' && obj[key] !== null && !Array.isArray(obj[key])) {
      flattenJSON(obj[key], newKey, result);
    } else if (Array.isArray(obj[key])) {
      result[newKey] = obj[key].join(',');
    } else {
      result[newKey] = obj[key];
    }
  }
  return result;
}

// Usage
const nested = {user: {name: "Alice", address: {city: "Paris"}}};
const flat = flattenJSON(nested);
console.log(flat);
// {user.name: "Alice", user.address.city: "Paris"}

Best Practices

1. Choose Consistent Separator

Pick one separator and stick with it across your project:

Dot notation (.) - Most common, works well everywhere
Underscore (_) - Better for SQL
Custom - Only if you have specific constraints

2. Document Your Flattening Rules

When working in a team, document:

Which separator you use
How you handle arrays (join? expand? index?)
Maximum depth for flattening
Which fields stay nested as JSON strings

3. Test with Real Data

Simple examples always work. Real data contains:

Inconsistent nesting depths
Missing fields (not all objects have same keys)
Null values
Empty arrays
Mixed types

4. Consider Reversibility

Can you convert the CSV back to the original JSON?

Dot notation: Yes, easily reversible
Joined arrays: Yes, can split on commas
Expanded rows: Harder - requires aggregation
Truncated depth: No - data is lost

5. Optimize for Your Use Case

For Excel analysis: Keep it simple, max 2-3 nesting levels
For data migration: Preserve everything with dot notation
For database import: Use underscores, consider normalization
For reporting: Expand arrays to rows for easy aggregation

Common Mistakes to Avoid

Mistake 1: Not Handling Null/Undefined

JSON:

{
  "user": {
    "name": "Alice",
    "address": null
  }
}

Bad flattening (crashes):

// TypeError: Cannot read property 'city' of null
const city = data.user.address.city;

Good flattening (safe):

user.name,user.address
Alice,

Mistake 2: Inconsistent Array Handling

Mixing strategies in same dataset:

name,skills,projects.name
Alice,"JS,Python",Project A
Bob,"Ruby,Go",Project B
Charlie,"Rust",  # Wrong - should be consistent

Pick one approach and stick with it.

Mistake 3: Losing Type Information

JSON:

{"id": 123, "code": "00456", "active": true}

Problem: In CSV, all become strings

id,code,active
123,00456,true

Excel may interpret 00456 as 456 (losing leading zero).

Solution: Prefix with quote or keep as-is and handle in consuming application

Mistake 4: Over-Flattening

JSON:

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

Result:

a.b.c.d.e.f
value

This column name is useless. Consider limiting depth.

Conclusion

Flattening nested JSON for CSV export requires strategic thinking. Key takeaways:

Dot notation is the gold standard for most use cases
Arrays require decisions - join, expand, or index
Deep nesting (3+ levels) may need selective flattening
Consistency matters - document your approach
Test with real data - edge cases always appear

Whether you're exporting API responses, converting configuration files, or preparing data for Excel analysis, understanding flattening strategies will save you hours of manual data manipulation.

Ready to flatten your JSON? Try jconvert.dev →

Related Guides

Last updated: March 3, 2026