How to Flatten Nested JSON

JSON is a tree. Spreadsheets, CSV files and SQL tables are grids. Flattening is the process of turning the first into the second, so that every value in the tree ends up in exactly one cell. It is the most important step in any JSON-to-table conversion, and it is easy to get subtly wrong.

What flattening does

Take this record:

{
  "id": 17,
  "name": "Ada",
  "address": {
    "city": "Leeds",
    "geo": { "lat": 53.8, "lng": -1.55 }
  }
}

Flattened, it becomes a single row:

Each path from the root to a value becomes a column name, joined with dots. Objects disappear as structure and survive as prefixes. This is the convention used by most tools, including pandas’ json_normalize and Json Works, so the column names you get are predictable and easy to search.

Rules worth knowing

Every record can have different keys. The table’s columns are the union of all keys seen. A record that lacks a key simply gets an empty cell.

Key order follows first appearance. Columns appear in the order keys are first seen, scanning records from the top.

Nulls and empty values stay distinct. null, an empty string and a missing key are three different things in JSON. Most tools show all three as an empty cell, so check the source if the difference matters to you.

Dots in key names cause ambiguity. A key literally named "a.b" and a nested {"a": {"b": ...}} both flatten to a.b. If you expect that in your data, use a different separator such as __ or / when you flatten in code.

Arrays are the hard part. Objects flatten cleanly because each key appears once. Arrays can have any length, so they cannot become a fixed set of columns without a decision. See below.

Flattening arrays

There are three common approaches, and the right one depends on what you will do with the table.

1. Index into columns. tags: ["a","b"] becomes tags.0 and tags.1. It works for short arrays of fixed meaning, such as coordinates. It produces ragged, sparse tables when lengths vary.
2. Keep as text. The whole array is serialised into one cell as JSON. Nothing is lost, and the table stays narrow.
3. Explode into rows. One output row per item, with parent fields repeated. This is the right shape for analysis such as “total quantity per product”, at the cost of repeating parent values.

Handling arrays in JSON when converting to a table walks through these with a worked example. In the JSON to Table tool, nested arrays stay compact as an expandable chip and you choose the export strategy when you download.

Flatten with a tool (no code)

1. Paste your JSON into JSON to Table.
2. Nested objects are flattened automatically into dotted columns.
3. Click a row’s arrow to see its arrays as sub-tables.
4. Export CSV or Excel.

Flatten in code

Python with pandas:

import json, pandas as pd

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

df = pd.json_normalize(records, sep=".")
df.to_csv("flat.csv", index=False)

To explode an array of line items while keeping parent fields:

df = pd.json_normalize(
    records,
    record_path="items",
    meta=["id", ["customer", "name"]],
)

JavaScript:

function flatten(value, prefix = "", out = {}) {
  if (value && typeof value === "object" && !Array.isArray(value)) {
    for (const [key, v] of Object.entries(value)) {
      flatten(v, prefix ? `${prefix}.${key}` : key, out);
    }
  } else {
    out[prefix] = value;
  }
  return out;
}

const rows = records.map((r) => flatten(r));

This version leaves arrays in place as values. Serialise them with JSON.stringify when you write the CSV, or extend the function to handle them with one of the strategies above.

jq on the command line:

jq -r '(.[0] | [paths(scalars) | join(".")]) as $h
       | $h, (.[] | [paths(scalars) as $p | getpath($p)]) | @csv' data.json

That one-liner assumes every record has identical structure. Real-world data rarely does, which is a good reason to prefer a tool that takes the union of keys.

Pitfalls

• Deep nesting creates very wide tables. A record nested six levels deep might yield hundreds of columns. Hide the ones you do not need, or extract only the paths you want with JSONPath.
• Mixed types in one column. If price is a number in some records and a string in others, the column holds both. Check before running calculations.
• Huge numbers. Integers beyond 2^53 lose precision in JavaScript. Make sure your tool preserves them as text, as Json Works does.
• Information loss. Flattening is not always reversible. If you may need the original structure back, keep the source JSON.

When not to flatten

If your data is genuinely a graph, with many-to-many links or deep recursion, a flat table is the wrong target. Consider generating SQL tables with a proper relational split, or keep it as JSON and query it with JSONPath.