How to Handle Arrays in JSON When Converting to a Table

A table has a fixed number of columns. A JSON array has any number of items. That mismatch is the root of nearly every awkward JSON-to-spreadsheet conversion, and there is no single correct answer. There are three good ones, each right for a different purpose.

The example

Here is an order with a nested array of line items:

[
  {
    "orderId": "A-100",
    "customer": "Ada",
    "items": [
      { "sku": "PEN", "qty": 3, "price": 1.5 },
      { "sku": "INK", "qty": 1, "price": 9.0 }
    ]
  },
  {
    "orderId": "A-101",
    "customer": "Grace",
    "items": [
      { "sku": "PEN", "qty": 10, "price": 1.5 }
    ]
  }
]

Two orders, three line items. What should the table look like?

Strategy 1: Keep the array as one cell

orderId | customer | items
A-100   | Ada      | [{"sku":"PEN","qty":3,"price":1.5},{"sku":"INK",...}]
A-101   | Grace    | [{"sku":"PEN","qty":10,"price":1.5}]

Each order stays one row, and the array is stored as JSON text.

Use it when the arrays are incidental, you want a compact overview, or you plan to process the cell programmatically later. Avoid it when you need to filter or total the inner values in the spreadsheet itself, because the cell is just text.

In the JSON to Table viewer this is how arrays look on screen too: a compact chip you can click to expand the row’s items as a small table, so you see the detail without widening the grid.

Strategy 2: Explode into one row per item

orderId | customer | sku | qty | price
A-100   | Ada      | PEN | 3   | 1.5
A-100   | Ada      | INK | 1   | 9.0
A-101   | Grace    | PEN | 10  | 1.5

The parent’s values repeat for each item, so each row is complete and self-describing.

Use it when you will analyse the items: totals per SKU, a pivot table, a chart, a filter by product. This is the shape analysts and BI tools expect, often called a denormalised or “long” table.

Watch out for double counting. If the order had a field orderTotal, repeating it on every line and then summing the column counts the total once per item. Sum item-level fields only, or keep order-level fields in a separate sheet.

When exporting from Json Works, choose the option to repeat the parent row per item.

Strategy 3: One table per array

Sheet "orders":      orderId | customer
Sheet "orders.items": orderId | sku | qty | price

The parent and children are separate tables joined on a key. This is how a relational database stores the same data.

Use it when the data is truly one-to-many and you want to keep it normalised, for example to load it into a database or build lookup formulas. In Excel it is easiest to use a separate sheet for each array, which Json Works can do on export. You can then use XLOOKUP or a pivot table’s data model to join them back together.

Watch out for the join key. Each child row needs something that identifies its parent. If your records have no natural ID, the row position will do, but make sure it is exported.

Strategy 4 (sometimes): Spread into columns

For short arrays of a fixed meaning, such as "coords": [53.8, -1.55], spreading to coords.0 and coords.1 is neat and loses nothing. Do not use it for arrays that vary in length. A record with 40 tags would force 40 columns, and most rows would have empty cells in all of them.

Arrays of simple values

An array of plain strings or numbers, like "tags": ["red", "large", "sale"], is easiest as a single cell joined with a separator: red; large; sale. Pick a separator that cannot appear in the values. If you need to filter by tag, explode into rows instead.

Arrays of arrays

A matrix such as [[1,2],[3,4]] is already a table. Treat each inner array as a row. If the data is a CSV-like payload where the first inner array holds the headers, convert it with the CSV to JSON tool in reverse, or promote the first row to headers by hand.

Choosing quickly

Mixed shapes

Real APIs are inconsistent. The same field may be an array in one record and a single object, or null, in another. A good converter treats a missing or null array as zero items and a lone object as a one-item array, so rows still line up. If you see unexpected empty cells after an explode, check the source for these variants with the JSON Formatter or by querying with JSONPath.

Next steps

For the object half of the problem, see how to flatten nested JSON. To get the result into a workbook, follow JSON to Excel: step by step.