JSON to CSV: How to Flatten Nested Objects for Spreadsheets

The gap between API output and spreadsheet input is almost always a nesting problem. JSON can represent complex hierarchical data — objects within objects, arrays of objects, multi-level trees. A spreadsheet has rows and columns. Flattening is the process of collapsing that hierarchy into a flat table where every value has its own column.

Why JSON Is Nested

JSON is nested because the data it represents is inherently hierarchical. A customer object has an address object inside it. An order object has a line items array inside it. An API response has a metadata wrapper around the actual data. When developers design JSON structures, they prioritise representing relationships clearly, not making the data easy to paste into Excel.

What Flattening Does

Flattening takes a nested structure and creates column names by joining the key path with dots. A JSON object like this:

{
  "user": {
    "name": "Alice",
    "email": "alice@example.com"
  },
  "account": {
    "plan": "pro",
    "created": "2024-01-15"
  }
}

becomes four columns: user.name, user.email, account.plan, account.created. Each nested level adds a dot prefix to the parent key name.

Arrays of Objects in Nested JSON

When a JSON array contains multiple objects, flattening typically creates one row per object, repeating the parent fields. A customer with three orders in a nested orders array becomes three rows, each with the customer fields repeated and one order's fields per row. Whether this is the right output depends on your use case — sometimes you want one row per customer, other times one row per order.

How to Flatten in Practice

Python with pandas json_normalize:

import pandas as pd
import json

with open('data.json') as f:
    data = json.load(f)

df = pd.json_normalize(data, sep='.')
df.to_csv('output.csv', index=False)

This handles one level of nesting automatically. For deeply nested structures, the record_path and meta parameters let you specify which nested array to expand into rows and which parent fields to include.

Browser-based JSON to CSV converters: Upload your JSON file, choose whether nested arrays should expand to rows or stay collapsed, and download the CSV. Best for one-off conversions without coding.

Choosing the Right Flattening Strategy

The right strategy depends on the relationship between the nested objects:

One-to-one nesting (customer → address): Flatten completely. Each customer gets one row with address fields appended as additional columns.

One-to-many nesting (customer → orders): Decide on your analysis unit. If you want to analyze customers, one row per customer and aggregate order data (count, total, last date). If you want to analyze orders, one row per order with customer fields repeated.

Variable-depth nesting: Some APIs return structures where nesting depth varies per record. A flattening tool that handles missing keys as nulls (not errors) is required.

Sohovi lets you upload your CSV and get an instant data quality report — no setup, no code required. After flattening JSON to CSV, profile it to check that all expected columns are present and completeness rates look right.

Common Flattening Problems

Column name collisions: Two nested objects both have a "name" field. After flattening, you get "user.name" and "company.name" — which is correct. But some converters produce "name" and "name_1", which is less clear.

Large file size after flattening: A one-to-many expansion that repeats parent fields on every row can inflate file size significantly. A customer with 50 orders means 50 rows with the customer fields repeated. For analysis purposes this is fine; for storage, consider whether you need the full expansion.

Inconsistent array lengths: If one customer has 3 orders and another has 50, the flattened CSV will have a row count equal to the total number of orders across all customers — which can be surprising if you're expecting one row per customer.

Missing keys in some records: If not all records have all nested fields, some rows will have nulls in those columns. This is expected and correct; just verify the nulls are intentional before using the data.

When to Use Flattening vs. Alternative Approaches

Flattening is the right approach when: you need all the nested data in a single table for analysis or reporting, you're importing into a system that only accepts flat files, or you want to profile the complete structure of the JSON.

Flattening is not the right approach when: the nested relationship is many-to-many (leads to extreme row count inflation), you only need a subset of the nested data, or the nesting represents a relationship better handled through a database foreign key.

Checking the Quality of Your Flattened Output

After flattening, verify the output before using it:

• Row count: does it match your expectation? If you flattened a one-to-many relationship, the row count should equal the total number of child objects across all parents.
• Column count: are all expected columns present? Missing columns usually mean the nesting path was inconsistent across records.
• Completeness: are there unexpected null values in columns that should always be populated? This often indicates records where the nested key was missing.

A 5-minute profile of the flattened CSV prevents downstream analysis errors caused by silent flattening failures.

If you're ready to stop guessing about your data quality, Sohovi is built for exactly this. Upload your first CSV free — no credit card, no IT team, no code needed.