JSON to Table: Flattening Nested Fields

An API returns JSON and a non-developer needs to read it. A screenshot works once — it cannot be filtered, sorted, or summed.

A table is the right answer. The only real decision is what happens to the nesting.

First: is the top level an array?

A table is rows × columns, so the data has to be "a set of objects with the same shape":

[
  { "id": 1, "name": "Ada", "team": "Platform" },
  { "id": 2, "name": "Grace", "team": "Compiler" }
]

Ideal: each key becomes a column, each object a row.

If the top level is an object ({ "code": 0, "data": [...] }), pull out the array first. Treating code and message as columns produces rows that mean nothing. The test: only the repeating structure can define a row.

Three nesting shapes, three treatments

1. Object inside object

{ "id": 1, "user": { "name": "Ada", "age": 36 } }

Approach	Column names	Trade-off
Path style	user.name, user.age	Nothing lost, reversible
Flattened	name, age	Prettier, but collisions silently overwrite

Default to path style. Collisions are the reason: if user.name and company.name both flatten to name, the later one wins and nobody can see what went missing.

Flatten only when you are certain names cannot collide.

2. Array inside an object

{ "id": 1, "tags": ["new", "vip"] }

One cell holding ["new","vip"] is honest but unfilterable. Splitting into tag1, tag2 creates as many columns as the longest row and leaves the rest empty — and "the third tag" is a fragile concept.

The practical compromise is a joined string (new | vip). Readable, filterable with a contains check, and no empty column sprawl.

3. Array of arrays (the hard one)

[
  { "order": "A1", "items": [{ "sku": "x", "qty": 2 }, { "sku": "y", "qty": 1 }] },
  { "order": "A2", "items": [{ "sku": "z", "qty": 5 }] }
]

What a row means is a business decision:

• Per order → one row per order; join items into text or sum the quantity
• Per item → one row per item; repeat the order fields (Cartesian expansion)

The second turns 2 rows into 3. Add a second array field and the counts multiply. Compute the row count before expanding — a 200-order dataset can explode into tens of thousands of rows.

A table can only express one level of one-to-many. Beyond that, the correct move is two tables (orders + line items) joined by an ID, not one wide table.

Output format depends on the audience

Audience	Use	Why
Colleague, email, screenshot	JSON to HTML Table	Opens in any browser, no software needed
Docs, technical write-up	JSON to Markdown Table	Plain text, version-control friendly, readable diffs
Further work in a spreadsheet	JSON to CSV	Opens directly in Excel / Sheets
Writing code against the data	JSON to TypeScript	Define the shape instead of hand-typing interfaces

Before converting to CSV

CSV has no types. null, true and the number 30 all become strings, and Excel then guesses — 00123 loses its leading zeros, an 18-digit ID becomes scientific notation.

If the CSV will be read back by a program, do not let a spreadsheet touch it. If it is for humans, accept that the table is a snapshot.

Three checks before you send it

1. Column count. Two or three columns usually means you flattened the wrong level.
2. Wide empty columns. If most cells in a column are blank, some records have that field and some do not — and blank looks identical to "explicitly null".
3. Right-aligned numbers. Left-aligned numbers are text; sums over them return 0.

The summary

A table carries one level of one-to-many. Decide what a row represents before flattening; use path-style column names so merges fail loudly instead of silently. If you also need the reverse direction — spreadsheet to JSON — see CSV ↔ JSON, and note that a header with a stray space becomes a key nobody can look up.