How to Convert Nested JSON to CSV: A Complete Guide

Converting a simple, flat JSON array into a CSV file is a straightforward task. However, things get complicated quickly when your JSON data includes nested objects and arrays. In this tutorial, we will explore the challenges of flattening hierarchical data and walk through the best practices for converting nested JSON into a clean, tabular CSV format.

The Challenge of Nested JSON

Consider a typical API response for a customer order:

{
  "order_id": "ORD-1029",
  "customer": {
    "name": "Jane Doe",
    "email": "jane@example.com",
    "address": {
      "city": "Seattle",
      "state": "WA"
    }
  },
  "items": [
    { "product": "Widget", "price": 19.99 },
    { "product": "Gadget", "price": 24.50 }
  ]
}

A CSV file is completely flat. It has rows and columns. It does not understand what an object is, and it definitely doesn’t know how to handle an array of items within a single row.

Strategy 1: Flattening Objects (Dot Notation)

The most common approach for nested objects is to flatten the keys using dot notation (or underscores).

Using the example above, the nested customer object can be flattened into individual columns:

• order_id
• customer.name
• customer.email
• customer.address.city
• customer.address.state

This creates a clean, readable header row. Most modern JSON to CSV conversion tools (including ours) offer object flattening as a default feature.

Strategy 2: Handling Arrays

Arrays present a bigger challenge. You have two primary options when dealing with a list of items inside your JSON:

Option A: Stringify the Array

If the array isn’t the primary focus of your analysis, you can simply convert the entire array into a JSON string and store it in a single CSV cell.

Pros: Keeps one row per original JSON object. Cons: The data in that cell is hard to query or aggregate in Excel without further parsing.

Option B: Unwinding (Exploding) the Array

If the array contains crucial data (like the items in our order), you should “unwind” it. This means creating a new row for every item in the array, duplicating the parent data.

Resulting CSV:

order_id, customer.name, items.product, items.price
ORD-1029, Jane Doe, Widget, 19.99
ORD-1029, Jane Doe, Gadget, 24.50

Pros: Makes the array data fully accessible and analyzable in standard tabular tools. Cons: Can result in very large CSV files if you have large arrays, and data duplication occurs.

Code Example: Flattening in JavaScript

If you’re writing a custom script, you can use recursion to flatten nested objects. Here is a basic implementation:

function flattenObject(ob) {
    let result = {};
    for (const i in ob) {
        if ((typeof ob[i]) === 'object' && !Array.isArray(ob[i])) {
            const temp = flattenObject(ob[i]);
            for (const j in temp) {
                result[i + '.' + j] = temp[j];
            }
        } else {
            result[i] = ob[i];
        }
    }
    return result;
}

Conclusion

Converting nested JSON to CSV requires making decisions about how to represent hierarchical data in a flat table. By utilizing object flattening via dot notation and deliberately unwinding or stringifying arrays, you can generate clean, useful CSV files ready for data analysis.

For the easiest experience, use an online tool that gives you configuration options for handling nesting and arrays automatically!