DEV Community

WTSolutions
WTSolutions

Posted on

Flat vs Nested JSON Conversion - Deep Dive into Conversion Modes

Welcome to part 6 of our JSON to Excel series! In our previous post, we explored the JSON formats that JSON to Excel supports. Today, we're diving deep into the two conversion modes: Flat JSON Mode and Nested JSON Mode. Understanding these modes is crucial for getting the best results from your JSON to Excel conversions.

The Two Conversion Modes

JSON to Excel offers two distinct conversion modes, each designed for different types of JSON structures:

  1. Flat JSON Mode: For simple, non-nested JSON structures
  2. Nested JSON Mode: For complex JSON with nested objects

Choosing the right mode can make the difference between a clean, usable Excel spreadsheet and one that's difficult to work with.

Flat JSON Mode

What is Flat JSON Mode?

Flat JSON Mode is designed for simple JSON structures where each object contains only primitive values (strings, numbers, booleans, null) and no nested objects or arrays that need to be expanded.

When to Use Flat JSON Mode

Use Flat JSON Mode when your JSON:

  • Has simple key-value pairs
  • Contains no nested objects
  • Has arrays that you want to keep as strings
  • You want a straightforward, one-level Excel structure

Example JSON for Flat Mode 

[
  {
    "id": 1,
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30,
    "active": true
  },
  {
    "id": 2,
    "name": "Jane Smith",
    "email": "jane@example.com",
    "age": 25,
    "active": false
  }
]
Enter fullscreen mode Exit fullscreen mode

Flat Mode Conversion Result

id name email age active
1 John Doe john@example.com 30 TRUE
2 Jane Smith jane@example.com 25 FALSE

Handling Arrays in Flat Mode

When your JSON contains arrays, Flat Mode converts them to string representations:

[
  {
    "id": 1,
    "name": "John",
    "tags": ["developer", "manager"],
    "scores": [85, 92, 78]
  }
]
Enter fullscreen mode Exit fullscreen mode

Result:

id name tags scores
1 John ["developer","manager"] [85,92,78]

Handling Nested Objects in Flat Mode

Nested objects are converted to JSON string representations:

[
  {
    "id": 1,
    "name": "John",
    "contact": {
      "email": "john@example.com",
      "phone": "555-1234"
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

Result:

id name contact
1 John {"email":"john@example.com","phone":"555-1234"}

Pros of Flat Mode

  • Simplicity: Easy to understand and use
  • Preserves Structure: Nested data is kept intact as strings
  • Fast Processing: Quick conversion for simple structures
  • Predictable Output: Consistent, one-level Excel structure

Cons of Flat Mode

  • Limited Analysis: Nested data can't be easily analyzed
  • String Manipulation: Need to parse strings to access nested data
  • Not Ideal for Complex Data: Doesn't leverage Excel's tabular structure for nested data

Nested JSON Mode

What is Nested JSON Mode?

Nested JSON Mode is designed for complex JSON structures with nested objects. It flattens nested structures into a tabular format, making them easy to analyze in Excel.

When to Use Nested JSON Mode

Use Nested JSON Mode when your JSON:

  • Contains nested objects that you want to analyze
  • Has hierarchical data structures
  • You want to access nested properties as separate columns
  • You need to perform calculations on nested values

Example JSON for Nested Mode 

[
  {
    "id": 1,
    "name": "John Doe",
    "contact": {
      "email": "john@example.com",
      "phone": "555-1234",
      "address": {
        "street": "123 Main St",
        "city": "New York",
        "zip": "10001"
      }
    }
  },
  {
    "id": 2,
    "name": "Jane Smith",
    "contact": {
      "email": "jane@example.com",
      "phone": "555-5678",
      "address": {
        "street": "456 Oak Ave",
        "city": "Boston",
        "zip": "02101"
      }
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

Nested Mode Conversion Result (Default Dot Delimiter)

id name contact.email contact.phone contact.address.street contact.address.city contact.address.zip
1 John Doe john@example.com 555-1234 123 Main St New York 10001
2 Jane Smith jane@example.com 555-5678 456 Oak Ave Boston 02101

Nested Delimiters (Pro Feature)

Nested JSON Mode allows you to customize how nested properties are named using different delimiters:

Dot (.) - Default

contact.email, contact.phone, contact.address.street

Underscore (_)

contact_email, contact_phone, contact_address_street

Double Underscore (__)

contact__email, contact__phone, contact__address__street

Forward Slash (/)

contact/email, contact/phone, contact/address/street

Max Depth Control (Pro Feature)

You can control how deep the converter processes nested objects:

Unlimited Depth (Default)

All nested levels are flattened:

{
  "level1": {
    "level2": {
      "level3": {
        "value": "deep"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Becomes: level1.level2.level3.value

Limited Depth (1-20)

Set a specific depth limit. Objects beyond that depth are converted to strings:

With Max Depth = 2:

{
  "level1": {
    "level2": {
      "level3": {
        "value": "deep"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Becomes: level1.level2 = {"level3":{"value":"deep"}}

Pros of Nested Mode

  • Better Analysis: Nested data becomes easily analyzable
  • Excel-Friendly: Leverages Excel's tabular structure
  • Flexible: Customizable delimiters and depth
  • Powerful: Handles complex hierarchical data

Cons of Nested Mode

  • Complexity: More complex than Flat Mode
  • Long Column Names: Can result in very long column headers
  • Pro Features Required: Some features require subscription
  • Learning Curve: Takes time to understand all options

Comparing Flat vs Nested Mode

Side-by-Side Comparison

Let's compare both modes with the same JSON:

Input JSON:

[
  {
    "id": 1,
    "name": "John",
    "contact": {
      "email": "john@example.com",
      "phone": "555-1234"
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

Flat Mode Result:
| id | name | contact |
|----|------|---------|
| 1 | John | {"email":"john@example.com","phone":"555-1234"} |

Nested Mode Result (Dot Delimiter):
| id | name | contact.email | contact.phone |
|----|------|----------------|---------------|
| 1 | John | john@example.com | 555-1234 |

Decision Guide

Scenario Recommended Mode Reason
Simple key-value pairs Flat Mode No nesting needed
Need to analyze nested data Nested Mode Makes nested data accessible
Want to preserve nested structure Flat Mode Keeps nested data intact
Have complex hierarchical data Nested Mode Flattens for analysis
Quick, simple conversion Flat Mode Faster and simpler
Need Excel calculations on nested values Nested Mode Values are in separate columns

Practical Examples

Example 1: E-commerce Product Data

JSON:

[
  {
    "productId": "P001",
    "name": "Laptop",
    "price": 999.99,
    "specs": {
      "cpu": "Intel i7",
      "ram": "16GB",
      "storage": "512GB SSD"
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

Use Nested Mode if you want to:

  • Filter products by CPU type
  • Calculate average RAM across products
  • Create pivot tables by storage type

Use Flat Mode if you want to:

  • Keep specs as a single field
  • Store the complete specs as-is
  • Import into systems that expect simple structures

Example 2: Customer Data with Multiple Levels

JSON:

[
  {
    "customerId": "C001",
    "name": "John Doe",
    "contact": {
      "email": "john@example.com",
      "phone": "555-1234",
      "address": {
        "street": "123 Main St",
        "city": "New York",
        "state": "NY",
        "zip": "10001"
      }
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

Nested Mode with Max Depth = 2:
| customerId | name | contact.email | contact.phone | contact.address |
|------------|------|----------------|---------------|-----------------|
| C001 | John Doe | john@example.com | 555-1234 | {"street":"123 Main St","city":"New York","state":"NY","zip":"10001"} |

Nested Mode with Unlimited Depth:
| customerId | name | contact.email | contact.phone | contact.address.street | contact.address.city | contact.address.state | contact.address.zip |
|------------|------|----------------|---------------|------------------------|----------------------|---------------------|---------------------|
| C001 | John Doe | john@example.com | 555-1234 | 123 Main St | New York | NY | 10001 |

Example 3: API Response with Mixed Data

JSON:

[
  {
    "id": 1,
    "title": "Post 1",
    "author": {
      "id": 101,
      "name": "John"
    },
    "tags": ["tech", "programming"],
    "metadata": {
      "views": 1000,
      "likes": 50
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

Nested Mode Result:
| id | title | author.id | author.name | tags | metadata.views | metadata.likes |
|----|-------|-----------|--------------|------|----------------|----------------|
| 1 | Post 1 | 101 | John | ["tech","programming"] | 1000 | 50 |

Note: Arrays like tags are still converted to strings even in Nested Mode.

Tips for Choosing the Right Mode

1. Analyze Your Data Structure

Look at your JSON and identify:

  • How many levels of nesting exist
  • Whether you need to analyze nested data
  • If nested data should be preserved as-is

2. Consider Your Use Case

Think about what you'll do with the Excel data:

  • Will you need to filter/sort nested values?
  • Do you need to perform calculations on nested data?
  • Are you importing into another system?

3. Test Both Modes

When in doubt, try both modes:

  • Convert with Flat Mode first
  • Convert with Nested Mode
  • Compare the results
  • Choose the one that better fits your needs

4. Use Pro Features Wisely

If you have Pro access:

  • Experiment with different delimiters
  • Try different Max Depth settings
  • Find the combination that works best for your data

Common Pitfalls

Pitfall 1: Using Flat Mode for Complex Data

Problem: You use Flat Mode for deeply nested JSON, then struggle to analyze the data.

Solution: Switch to Nested Mode to flatten the structure.

Pitfall 2: Using Nested Mode for Simple Data

Problem: You use Nested Mode for simple JSON, creating unnecessarily complex column names.

Solution: Use Flat Mode for simple structures.

Pitfall 3: Not Setting Max Depth

Problem: Very deep nesting creates extremely long column names.

Solution: Set an appropriate Max Depth to limit flattening.

Pitfall 4: Wrong Delimiter Choice

Problem: Your chosen delimiter conflicts with your data or naming conventions.

Solution: Choose a delimiter that works with your data and team conventions.

Next Steps

Now that you understand the differences between Flat and Nested JSON conversion modes, you're ready to explore the advanced features available in JSON to Excel. In our next post, we'll cover Pro features and customization options that can take your JSON to Excel conversions to the next level.

Ready to try different conversion modes? Visit the JSON to Excel Web App and experiment with both modes!

Top comments (0)