Flatten JSON

Flattening turns a nested document into a single level of dotted paths: {"a":{"b":1}} becomes {"a.b":1}. Unflattening turns it back.

It is what you do before loading JSON into anything rectangular: a spreadsheet, a data frame, a feature flag store, an environment file, a form.

The round trip, and the one case that breaks it

Flatten then unflatten returns the original document, including empty objects and empty arrays, which several implementations quietly drop.

There is exactly one case where it cannot: a key that itself contains the separator. Given {"a.b": 1} the flattened path "a.b" is indistinguishable from a nested {"a":{"b":1}}. This tool detects that and warns you rather than producing something that will not come back. Choose a different separator when it happens.

Arrays: index notation or bracket notation

Dot notation gives tags.0 and tags.1. Bracket notation gives tags[0] and tags[1]. Both round-trip here, and the input parser accepts either.

Dot notation is what pandas json_normalize produces and what most CSV pipelines expect. Bracket notation is easier to read when a key could plausibly be numeric, because tags[0] and tags.0 are ambiguous in a way tags["0"] is not.

Where flattening loses information

A numeric object key becomes indistinguishable from an array index once flattened. {"2024": {"total": 1}} flattens to "2024.total", and unflattening that with array detection on produces an array with 2024 empty slots.

Turn off numeric-keys-as-arrays when your keys are genuinely numeric strings, which is common for anything keyed by year, by HTTP status code or by ID.

How to do this in code

Flattening in code.

import pandas as pd

# The workhorse. sep defaults to '.'
df = pd.json_normalize(records)

# Explode a nested array into one row per element
df = pd.json_normalize(
    records,
    record_path='items',
    meta=['id', 'created_at'],
)

# Every leaf as a dotted path
jq -r 'paths(scalars) as $p | "\($p | join(".")) = \(getpath($p))"' in.json

# A flat object rather than lines
jq '[leaf_paths as $p | {(($p | map(tostring) | join("."))): getpath($p)}] | add' in.json

function flatten(value, prefix = '', out = {}) {
  if (value && typeof value === 'object') {
    const entries = Array.isArray(value)
      ? value.map((v, i) => [i, v])
      : Object.entries(value);
    if (entries.length === 0) {
      out[prefix] = value;      // preserve {} and []
      return out;
    }
    for (const [k, v] of entries) {
      flatten(v, prefix ? `${prefix}.${k}` : String(k), out);
    }
    return out;
  }
  out[prefix] = value;
  return out;
}

Questions

Which separator should I use?

A dot, unless your keys contain dots. Underscore is the usual second choice, and a slash is useful when the result is going somewhere that already thinks in paths.

Can I flatten only part of the document?

Set a depth limit. Everything beyond it is left as a nested value, which is what you want when the deep part is an opaque blob you are storing rather than querying.

What happens to null?

It is kept by default, as a flat key with a null value. There is an option to omit nulls, which is useful for a diff and dangerous for a round trip.