merge-json-files.com

JSON Flattener: Flatten & Unflatten JSON

Spreadsheets and databases expect flat columns, not nested objects. Flatten turns nested JSON into single-level keys like user.address.city and user.tags[0], and Unflatten rebuilds the nested structure from those keys. Choose the delimiter, cap the depth, and decide whether arrays get indexed.

Before & After

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

Comparison of common delimiter strategies for flattening nested JSON keys. Array indexes always use brackets, e.g. user_tags[0].
DelimiterNested PathFlattened KeyBest For
Dot (.)user > address > cityuser.address.cityJavaScript / general use
Underscore (_)user > address > cityuser_address_citySQL column names / Python
Colon (:)user > address > cityuser:address:city.NET configuration keys
Slash (/)user > address > cityuser/address/cityFile paths / REST APIs
Double underscore (__)user > address > cityuser__address__cityDjango / environment vars

Programmatic Flattening

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

Complete Guide

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.

In a CSV or spreadsheet those keys become columns, not rows. If a user has 10 orders, you'll get 10 sets of order columns (user.orders[0].id through user.orders[9].id) on that user's single row, and records with different array lengths end up with different columns. To see how your records lay out as rows and columns before exporting, preview them in JSON to Table.

If you'd rather keep each array as a single value, open Advanced and turn off "Flatten arrays". Arrays then stay intact under their parent key (user.orders) while objects around them are still flattened. Note that a top-level array is flattened as one object too, with keys like [0].name and [1].name. To get one spreadsheet row per record, use the JSON to Excel converter, which flattens each record separately.

Unflattening: turning flat keys back into nested JSON

Switch the tool to Unflatten mode to go the other way. Paste a flat object such as {"user.name": "Ali", "user.tags[0]": "admin"} and you get back {"user": {"name": "Ali", "tags": ["admin"]}}. Bracket indexes become arrays again, including nested ones like matrix[0][1] and top-level keys like [0].name.

Set the same delimiter you flattened with. The input has to be a single flat JSON object, and the round trip is exact as long as none of your original keys contained the delimiter or bracket indexes of their own. If the flat keys are spread across several files, combine them into one object with the JSON Merger first.

Related Articles