merge-json-files.com

JSON Flattener

Analyzing deeply nested JSON in Excel or a database is nearly impossible. Our flattener turns complex JSON structures into simple key-value pairs that are easy to read and export. It's a quick way to clean up your data for spreadsheets without writing a single line of code.

Try Flattening with Sample Data

Download Sample JSON Files

Before & After

Nested to flat conversion

Nested JSON
json
{
  "user": {
    "name": "Alice",
    "address": {
      "city": "Portland",
      "zip": "97201"
    },
    "tags": ["admin", "editor"]
  }
}
Flattened Output
json
{
  "user.name": "Alice",
  "user.address.city": "Portland",
  "user.address.zip": "97201",
  "user.tags[0]": "admin",
  "user.tags[1]": "editor"
}

Delimiter Comparison

Choose the right separator

Comparison of common delimiter strategies for flattening nested JSON keys
DelimiterNested PathFlattened KeyBest For
Dot (.)user > address > cityuser.address.cityJavaScript / general use
Underscore (_)user > address > cityuser_address_citySQL column names / Python
Bracket ([])user > address > cityuser[address][city]PHP / form data
Slash (/)user > address > cityuser/address/cityFile paths / REST APIs
Double underscore (__)user > address > cityuser__address__cityDjango / environment vars

Programmatic Flattening

Python and JavaScript

Pythonflatten.py
import json

def flatten_json(obj, parent_key="", sep="."):
    items = {}
    for key, value in obj.items():
        new_key = f"{parent_key}{sep}{key}" if parent_key else key
        if isinstance(value, dict):
            items.update(flatten_json(value, new_key, sep))
        elif isinstance(value, list):
            for i, item in enumerate(value):
                arr_key = f"{new_key}[{i}]"
                if isinstance(item, dict):
                    items.update(flatten_json(item, arr_key, sep))
                else:
                    items[arr_key] = item
        else:
            items[new_key] = value
    return items

# Usage
with open("nested.json") as f:
    data = json.load(f)

flat = flatten_json(data)
print(json.dumps(flat, indent=2))
JavaScriptflatten.js
function flattenJSON(obj, parentKey = "", sep = ".") {
  const result = {};

  for (const [key, value] of Object.entries(obj)) {
    const newKey = parentKey ? `${parentKey}${sep}${key}` : key;

    if (value !== null && typeof value === "object" && !Array.isArray(value)) {
      Object.assign(result, flattenJSON(value, newKey, sep));
    } else if (Array.isArray(value)) {
      value.forEach((item, index) => {
        const arrKey = `${newKey}[${index}]`;
        if (item !== null && typeof item === "object") {
          Object.assign(result, flattenJSON(item, arrKey, sep));
        } else {
          result[arrKey] = item;
        }
      });
    } else {
      result[newKey] = value;
    }
  }

  return result;
}

// Usage
const nested = { user: { name: "Alice", scores: [10, 20] } };
console.log(flattenJSON(nested));

FAQ

Common questions

Complete Guide

In-depth walkthrough

When you actually need to flatten JSON

You need to flatten JSON when exporting to CSV or Excel because those formats require a flat structure with one value per cell. Nested objects don't fit into rows and columns without flattening first.

Loading into a SQL database or Google Sheets also requires flattening. Column names come from the dot-notation keys (user.address.city becomes a column header), and each nested value gets its own column.

Data analytics tools like Pandas don't handle nested dictionaries well. They expect flat DataFrames where each column is a simple value, not another dictionary. Flattening before loading into Pandas makes analysis much easier.

Most developers flatten JSON because their next tool doesn't speak nested JSON. If your destination is a spreadsheet, database, or analytics tool, you'll need to flatten.

Choosing your delimiter: dot, underscore, or custom

Dot notation (user.address.city) is the most common and readable delimiter. It's what most people expect when they see flattened JSON, and it works well for general use.

Underscore (user_address_city) is better when the output will become column headers in a database or spreadsheet. Dots in column names cause issues in SQL (they're interpreted as table.column syntax) and some BI tools don't handle them well.

Use a custom delimiter only if your key names already contain dots or underscores. For example, if you have keys like user.name or user_id, you'll need a different delimiter like a dash or double underscore to avoid confusion.

For most cases, stick with dots for readability or underscores for database compatibility. Custom delimiters are rarely needed unless you have naming conflicts.

Arrays inside nested JSON - what the flattener does

Arrays get indexed when flattened. If you have user.orders with two orders, the flattened output will have user.orders.0.id, user.orders.1.id, and so on. Each array element gets its own set of keys.

This creates one row per object if you export to CSV, which may not be what you want. If you have 10 orders in an array, you'll get 10 rows for that one user, each with a different order.

If you need one row per top-level object with arrays as a single value, use JSON.stringify on the array field before flattening. That way the entire array becomes a single string value in one cell instead of being split across multiple rows.

More Articles

Related Articles