How to flatten nested JSON into columns

Flattening turns nested JSON into a flat table: each record becomes one row, and each nested key becomes a column named by its path from the root, joined with dots. Take this record:

{
  "id": 101,
  "user": {
    "name": "Ada",
    "address": {
      "city": "Utrecht"
    }
  },
  "tags": ["sql", "json"]
}

Objects flatten cleanly because each key holds one value. Arrays hold several, so they stay a list column until you explode them into one row per item.

Open or drop a .json, .jsonl or .ndjson file, or click Paste data, and DuckDB-WASM flattens it inside your browser tab. The grid has no row limit and loads 100 rows at a time as you scroll.

• Nested objects become dot-path columns such as user.address.city, up to 8 levels deep and 200 child fields per object. A deeper or wider object stays whole in one column.
• Arrays stay list columns instead of becoming tags.0 and tags.1; the grid shows the first 6 items and +N.
• Click a row to open it in a side drawer with a JSON/Fields toggle and Copy as JSON. There is no whole-file tree view.
• A .json file over 1.5 GB is refused ("Too big for this browser"). A JSONL file over 1.5 GB loads its first rows with the note "Loaded the first N rows: the file is X, more than the browser can hold."

To get one row per array item, type SQL into the filter box above the table. One statement starting with SELECT, WITH or FROM runs as DuckDB SQL against a table called data:

SELECT id, "user.address.city", unnest(tags) AS tag FROM data

• Quote dot-path names: "user.address.city". Unquoted, DuckDB reads user as a table and fails with Referenced table "user" not found!.
• To keep every column, write SELECT * EXCLUDE (__rid, tags), unnest(tags) AS tag FROM data. __rid is a hidden row-number column that * would otherwise include.
• For an array of objects such as items: [{"sku": "A1", "qty": 2}], unnest(items) returns one struct column, shown as JSON text. unnest(items, recursive := true) splits it into sku and qty columns.
• Results stop at the first 1,000,000 rows ("first 1M").

The Export menu saves the current view: filters, sort, hidden columns or your SQL result. Filtered views export every matching row; SQL results stop at 1,000,000 rows.

• CSV: comma-delimited, header row, UTF-8 without a BOM. Lists become JSON text such as ["sql","json"], and cells starting with =, +, -, @, a tab or a carriage return get a leading apostrophe so spreadsheets don't run them as formulas.
• Parquet: zstd compression.
• JSON (one array of objects) or JSONL (one object per line): keys keep their dot paths, such as "user.name", and lists stay arrays.
• Markdown: a table of the first 1,000 rows, with lists written as JSON text.

There is no delimiter, encoding or BOM option, and no Excel .xlsx export.

For a pipeline, or a file over 1.5 GB, flatten on your own machine. pandas json_normalize flattens every level, with no depth or field limit:

import json
import pandas as pd

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

df = pd.json_normalize(records, sep=".")
df["tags"] = df["tags"].map(json.dumps)
df.to_csv("out.csv", index=False)

Without the json.dumps line, to_csv writes lists as Python text such as ['sql', 'json'], which is not valid JSON. For a wrapped file like {"data": [...]}, pass record_path="data"; for JSONL, build records with [json.loads(line) for line in f if line.strip()]. pandas holds the whole file in memory.

COPY (
  SELECT id, unnest(user, recursive := true), tags
  FROM read_json('in.json')
) TO 'flat.parquet';

The DuckDB CLI reads the file from disk, and recursive := true flattens every object inside user in one pass. Columns take the last key (name, city), not the dot path, so a second name key comes out as name_1, and lists inside stay lists. For dot-path names, list each field: user.address.city AS "user.address.city".

• Late keys: DuckDB's read_json picks columns from the first 20,480 rows by default, so a key that first appears later is dropped without an error. Add sample_size = -1 to scan the whole file. rows.page scans files up to 50 MB in full; in bigger files a late key can be missing.
• Key collisions: a literal "user.name" key and a nested user object with a name key flatten to the same name. rows.page keeps both and renames one user.name (2); pandas keeps a single user.name column and silently drops one value.
• Empty arrays: unnest(tags) drops rows whose list is empty or null. Unnest CASE WHEN len(tags) > 0 THEN tags ELSE [NULL] END instead to keep them with a null tag.