Nested JSON to Excel — Flattening, Limitations, and What to Expect

Nested JSON — where objects contain other objects or arrays — is the most common structure returned by REST APIs. Converting it to Excel cleanly requires understanding how nesting becomes columns. Our free browser converter uses dot-notation flattening: nested objects become parent.child column headers. Here is exactly what converts automatically and when you need to pre-process first.

How Nested JSON Gets Flattened to Excel Columns

Take this example nested JSON:

[
  {
    "id": 1,
    "name": "Alice",
    "address": {
      "city": "Boston",
      "state": "MA",
      "zip": "02134"
    },
    "score": 95
  }
]

After flattening, the Excel columns will be: id, name, address.city, address.state, address.zip, score.

Each nested key path becomes a column header using dot notation. This is identical to how Python's pd.json_normalize() works and how Excel Power Query flattens nested JSON records.

Multiple levels of nesting work the same way: {"a": {"b": {"c": "value"}}} becomes a column named a.b.c.

What Happens to Arrays Inside Nested Objects

When a JSON object contains an array, the behavior is different from nested objects:

[
  {
    "id": 1,
    "tags": ["developer", "admin"],
    "scores": [85, 92, 78]
  }
]

The tags and scores arrays get stringified into a single cell. The tags cell will contain ["developer","admin"] as text.

This is a deliberate trade-off: keeping the row structure intact (one JSON object = one Excel row) is more useful for most analysis than trying to expand arrays and potentially multiplying rows.

If you need the array values in separate columns, pre-process the JSON before converting. For example, to split a tags array into individual columns:

// Preprocess in browser console
const data = JSON.parse(yourJsonString);
const processed = data.map(row => {
  const result = {...row};
  if (row.tags) {
    row.tags.forEach((tag, i) => { result['tag_' + i] = tag; });
    delete result.tags;
  }
  return result;
});
console.log(JSON.stringify(processed));

Sell Custom Apparel — We Handle Printing & Free Shipping

Real-World API Responses: What Converts Cleanly

Most REST API responses fall into a few patterns:

Flat array (best case): Every property is a simple value. Converts perfectly with no pre-processing.

[{"id": 1, "name": "Product A", "price": 29.99, "stock": 150}]

One level of nesting (common, converts well): Objects with one level of nested sub-objects. The dot-notation output is readable and useful.

[{"id": 1, "product": {"name": "A", "sku": "P001"}, "qty": 5}]

Deeply nested (2+ levels, converts but complex): Works, but column headers like order.customer.address.city can be unwieldy. Consider pre-processing to extract just the fields you need.

Arrays of arrays (challenging): If the data contains arrays of objects (line items, sub-records), these get stringified. If you need this data expanded, the converter alone is not sufficient — use Python or pre-process the JSON.

For most common API responses (user records, product lists, order summaries), the direct conversion works without any pre-processing.

How to Pre-Process Complex Nested JSON Before Converting

If your JSON has deep nesting or array expansion needs, the easiest pre-processing approach on any platform is in the browser's developer console:

1. Open DevTools (F12).
2. Go to the Console tab.
3. Paste your JSON into a variable: const data = [your json here];
4. Flatten it with a custom function or use a one-liner for simple cases.
5. Copy the result: copy(JSON.stringify(result));
6. Paste into the converter.

For Python users, pd.json_normalize() offers fine-grained control over nesting depth and array handling:

import pandas as pd, json

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

# Normalize with max depth 2, expand specific array keys
df = pd.json_normalize(
    data,
    record_path='line_items',   # expand this array
    meta=['order_id', 'date'],  # bring these fields into each row
    max_level=1
)
df.to_excel('output.xlsx', index=False)

Frequently Asked Questions

I have JSON with arrays of objects inside each record (like order line items). Can this convert them?

The converter will stringify those inner arrays as text in the cell. If you want each line item as its own row, you need to pre-process the JSON first — either by flattening in Python using pd.json_normalize() with record_path, or by writing a JavaScript snippet in the browser console to expand the inner arrays into separate records before converting.

How many levels of nesting can the converter handle?

There is no hard limit on nesting depth. The converter recursively flattens nested objects into dot-notation keys. The practical limit is readability — a column named a.b.c.d.e is technically valid but hard to work with in Excel. For more than 3 levels of nesting, pre-processing is usually more practical.

What is the difference between this and Python pd.json_normalize()?

The behavior is very similar for nested objects — both use dot notation. The main differences are that Python's json_normalize offers more control (max_level, record_path for array expansion) while the browser converter is faster for one-off tasks with no setup. For complex nested JSON that needs careful flattening, Python is more powerful.