How to Flatten the JSON Data: Explode, Join, or Leave It Nested (2026 Guide)

What it means to flatten the JSON data

CSV is flat: rows and columns, nothing else. JSON is a tree: objects inside objects, lists inside objects, objects inside lists. To flatten the JSON data is to turn that tree into a table without losing the information you actually need. Every flattening tool does the same three moves, and once you can name them, every tool becomes predictable.

The three moves are: nested objects become dot-notation columns (customer becomes customer.name and customer.city); lists of plain values become one joined cell (items becomes book; pen); and lists you need to analyze become one row per item, which is called exploding. Which move applies to which list is the whole skill, and it is a decision, not a default.

One distinction matters before anything else. Our earlier guide on flattening JSON to CSV documents exactly what this site converter flattening toggle does, cell by cell, including its edge cases. This guide is about the job itself: you have nested JSON data, you want a flat table, and you get to choose how each part flattens, in any tool, including ones that have no toggle at all.

The worked example: three orders, nested

Every example in this guide uses the same small fixture, three orders with a nested customer object, an items list, and an empty list in the third order. Small enough to check by hand, nested enough to show every decision.

orders.json, the worked example

[
  { "orderId": "A-101", "customer": { "name": "Ana", "city": "Madrid" }, "items": ["book", "pen"], "total": 19.99 },
  { "orderId": "A-102", "customer": { "name": "Ken", "city": "Osaka" }, "items": ["lamp"], "total": 42.50 },
  { "orderId": "A-103", "customer": { "name": "Lina", "city": "Berlin" }, "items": [], "total": 7.25 }
]

Move 1: nested objects become dot-notation columns

A nested object has named fields, so flattening it is mechanical: walk down the tree and join the key names with dots. customer.name holds Ana, customer.city holds Madrid, and the original nesting is gone but nothing is lost, because the dotted name records the full path back to the source.

This is the move every tool agrees on. The online converter does it, pandas json_normalize does it, jq does it when you name the paths explicitly. Depth is the only judgment call: one or two levels of dots stay readable, four levels (a.b.c.d) usually mean you are flattening a structure that was never meant to be a table row, and that is the subject of the depth section below.

Move 1 applied: objects dotted, lists joined

orderId,customer.name,customer.city,items,total
A-101,Ana,Madrid,book; pen,19.99
A-102,Ken,Osaka,lamp,42.50
A-103,Lina,Berlin,,7.25

Move 2: short lists become one joined cell

A list of plain values, strings or numbers with no nesting of their own, flattens into a single cell by joining the values with a separator. This site converter joins with a semicolon followed by a space (book; pen). The semicolon is a deliberate choice: commas would collide with the CSV delimiter, while a semicolon inside a cell is unambiguous and survives a round trip through Excel.

Joining keeps one row per record, which is what you want when the list is context, not the subject. Tags on an article, skills on a profile, categories on a product: you will read them, maybe filter on them, but you will not sum or count the individual values. Join those.

Joined lists: one row per order, lists in one cell

orderId,items
A-101,book; pen
A-102,lamp
A-103,

Move 3: explode a list into one row per item

Exploding is the move most people miss, because most converters never offer it. Instead of squeezing a list into one cell, you make one row per item and repeat the parent fields on every row. Order A-101, with two items, becomes two rows: one for book, one for pen, each carrying Ana, Madrid, and 19.99. Three orders become four rows, because the empty list in A-103 still yields one row with an empty item, so the order itself is never silently dropped.

Explode when the list is the unit of analysis: line items you will sum, events you will count, transactions you will filter. If you join those lists instead, the per-item numbers are trapped inside a text cell, and no spreadsheet formula recovers them cleanly. The rule of thumb: if you will ever filter, count, or sum on single values inside the list, explode it. If the list is only ever read as a whole, join it.

Exploded, the worked example becomes five lines including the header: A-101 twice (book, then pen), A-102 once (lamp), A-103 once with an empty item. The order fields repeat on every row, which looks redundant and is exactly the point: each row is now one analyzable fact.

Explode or join? Decide by what the list means

The explode-or-join decision is the one place flattening goes quietly wrong, because both outputs look like valid CSV. A joined list of line items opens fine in Excel, and nothing warns you that the per-item prices are now unrecoverable text. Ask one question per list before you flatten it, and the choice makes itself.

Will I filter, count, or sum on single values inside this list? If yes, explode it. Line items, events, transactions, and measurements all belong here.
Is the list only ever read as a whole? If yes, join it. Tags, categories, skills, and labels all belong here.
Am I unsure? Explode. An exploded table can always be grouped back into joined cells, but a joined cell cannot be reliably split back into rows, because the separator may also appear inside a value.
Does the list contain objects, not plain values? Then neither move applies directly: an object list collapses to a JSON-text cell when joined, or needs its fields dotted before exploding. See the depth section for that case.

How deep should you flatten?

Flattening all the way to the leaves feels thorough, and it produces the worst tables: dozens of columns with four-level dotted names, most of them empty, none of them readable. Flatten to the depth you will actually use. If you will filter or sort on a field, give it a column. If you will only ever read it in context, leave that subtree as one JSON-text cell.

A practical stopping rule: flatten two to three levels deep, and keep anything deeper as JSON text. A tracking list of shipment objects, for example, is real data, but it rarely belongs in columns: one tracking cell holding the JSON text keeps the table readable, and the original JSON file keeps the full detail. The flattened CSV is a derived view of your data, not a replacement for it, so keep the original JSON file.

Ragged data: missing keys, empty lists, and nulls

Real JSON is ragged: one record has a field the next one lacks, one list is empty, one value is null. Flattening handles all three with the same boring rule, and boring is what you want: the header row is the union of every key found anywhere in the data, and any record missing a key gets an empty cell there. No error, no dropped row, just an empty cell you can see.

Empty lists and nulls follow the same rule in both moves. Joined, an empty list becomes an empty cell. Exploded, an empty list still yields one row with an empty item, so the parent record survives. A null inside a list becomes an empty segment between the separators: a list of mug, null, and plate joins to mug; ; plate, with the gap visible exactly where the null was.

Nulls and empty segments, verified against the real engine

orderId,items
A-101,book; pen
A-103,mug; ; plate

Recipe 1: Python, json_normalize plus explode

Python with pandas is the most forgiving route, because json_normalize flattens the objects and explode handles the lists in two readable lines. Run on the three-order fixture, json_normalize produced the columns orderId, items, total, customer.name, and customer.city, and explode on items turned 3 rows into 4, with the empty list leaving one row with an empty item.

flatten.py, json_normalize plus explode (verified)

import json
import pandas as pd

rows = json.load(open('orders.json'))
df = pd.json_normalize(rows)
flat = df.explode('items')
flat.to_csv('orders-flat.csv', index=False)

If you want the join move instead of explode, skip the explode line: json_normalize leaves the list as a list, and a one-line join turns it into the semicolon-separated cell. The same script therefore covers both decisions, which is why it is the recipe to reach for when the flattening is not a one-off.

Recipe 2: jq, explode in one command

jq explodes a list in a single command, with no script file and no dependencies beyond jq itself. The recipe below binds each order to a variable, checks whether its items list is empty, and either emits one row with an empty item or one row per item. Run on the worked example it produced exactly the five-line output described in the explode section: A-101 twice, A-102 once, A-103 once with an empty item.

jq: explode items into one row per item (verified)

jq -r '. as $rows | (["orderId","name","city","item","total"] | @csv), ($rows[] | . as $o | if (.items | length) == 0 then [$o.orderId, $o.customer.name, $o.customer.city, "", $o.total] | @csv else (.items[] | [$o.orderId, $o.customer.name, $o.customer.city, ., $o.total] | @csv) end)' orders.json

The empty-list branch is the part most jq explode recipes online omit, and it is the part that loses data: without it, orders with no items vanish from the output entirely. Keep it, even when your current data has no empty lists, because the next file will.

Recipe 3: Excel Power Query, no code

If your destination is Excel anyway, Power Query flattens JSON without any code at all. Import with Data, then Get Data, then From File, then From JSON, and Power Query shows each record as a row with nested columns still folded. The two expand operations map exactly onto the two list moves in this guide, and picking the wrong one is the same mistake as in any tool.

Expand a record column (customer) with the expand arrow, then tick the fields you want: name and city become plain columns. This is Move 1.
Expand a list column (items) with the expand arrow and choose Expand to New Rows to explode it: one row per item, parent fields repeated. This is Move 3.
Expand a list column and choose Extract Values, with a semicolon-space separator, to join it into one cell. This is Move 2.
Repeat for each nested column until the table is flat at the depth you need, then Close and Load.

Recipe 4: the online converter, joins but never explodes

The free converter on this site flattens with its flattening toggle switched on: nested objects become dot-notation columns and lists join into semicolon-separated cells, exactly Moves 1 and 2. Switch the toggle off and nested values stay as JSON text inside their cells, which is the leave-it-nested option from the title. What the converter does not do is explode: there is no option that turns one order into one row per item.

That is an honest limitation, not a bug. Exploding changes the row count, which is a data-modeling decision, and a one-click converter should not make it silently. If your list needs exploding, use the Python or jq recipe above, and use the converter for the many files where joining is the right call.

Open the free JSON to CSV converter

Can you unflatten it back?

Flattening is easy to reverse for objects and hard to reverse exactly for exploded lists, so plan for it. Dotted columns split back into nested objects with a small script, and nothing is lost, because the dots recorded the full path. Exploded rows group back with a group-by on the parent key, collecting the items into a list again, but the original item order survives only if you kept a row index, and the original empty lists are indistinguishable from missing ones.

The practical rule follows from that: never treat the flattened CSV as your only copy. Keep the original JSON file, treat the CSV as a derived view built for one analysis, and re-flatten from the JSON when the analysis changes. Storage is cheap, and re-deriving from the source beats debugging a CSV that has silently lost structure.

Flattening checklist

Before you flatten any JSON file, run through these six checks. They take a minute, and they catch every failure mode in this guide.

Name the unit of analysis first: one row per order, per item, or per event? That answer chooses explode or join for every list.
Flatten objects to the depth you will filter or sort on, and leave deeper subtrees as JSON text.
Check every list: plain values join or explode cleanly, but a list of objects needs its fields dotted before either move makes sense.
Expect ragged data: missing keys become empty cells, empty lists become one empty row or cell, nulls become empty segments.
Watch for dots inside key names: a key like version.2 collides with a nested path of the same name, and one of them wins silently.
Keep the original JSON. The flattened CSV is a derived view, not the source of truth.

Related guides

Frequently asked questions

What does it mean to flatten JSON data?

It means turning a nested JSON tree into a flat table of rows and columns. Nested objects become dot-notation columns (customer.name), lists of plain values join into one cell (book; pen) or explode into one row per item, and anything you do not flatten stays as JSON text in a cell.

Should I flatten nested JSON before converting to CSV?

Yes, unless you want JSON text inside your CSV cells. CSV has no nesting, so nested data must flatten, join, or stay as text. Flattening with the toggle on gives you real columns you can filter and sort; leaving it off keeps nested values as readable JSON text in single cells.

How do I flatten a JSON array into rows, one per item?

Explode it. In Python: pd.json_normalize(rows) then df.explode('items'). In jq: bind each record to a variable and emit one row per item, with an explicit branch for empty lists so records with no items are not dropped. Both recipes are in this guide and were verified on the worked example.

How do I flatten JSON in Python?

Use pandas: json.load the file, pd.json_normalize(rows) to flatten nested objects into dot-notation columns, then either join list columns with a separator or df.explode('items') to get one row per item. For nested objects without pandas, a small recursive flatten function that builds dotted keys does the same job.

How do I flatten JSON in Excel?

Use Power Query: Data, Get Data, From File, From JSON. Expand record columns to flatten nested objects, and expand list columns with Expand to New Rows to explode or Extract Values to join. Repeat per nested column, then Close and Load.

What happens to empty lists and nulls when flattening JSON?

They become empty cells or empty segments, never errors. A missing key gives an empty cell, an empty list joins to an empty cell or explodes to one row with an empty item, a null becomes an empty cell, and a null inside a list becomes an empty segment between separators (mug; ; plate).

Can I flatten JSON online without installing anything?

Yes. Paste JSON or upload a file to a free online converter with flattening switched on: nested objects become dot-notation columns and lists join into one cell. If you need to explode a list into one row per item, use the Python or jq recipe in this guide instead, because online converters join lists and do not explode them.

Nested JSON? Flatten it in the browser, free

Paste JSON or upload a file: nested objects flatten to dot-notation columns, lists join into clean cells, and the download is plain UTF-8. Free, no sign-up, runs entirely in your browser.

Open the JSON to CSV converter

All guides