JSON to CSV when there are nested arrays

JSON is a tree. CSV is a rectangle. Most of the pain in converting between them comes from one specific mismatch: nested objects have an obvious flat representation, and nested arrays do not.

This is not a tooling limitation you can configure your way out of. It is a modeling decision, and the right answer depends on what you plan to do with the result. Here is how to make it deliberately instead of accepting whatever a converter picked for you.

Nested objects: flatten with dotted paths

An object has named keys, so every leaf has a unique path and the path becomes a column name. Given:

{
  "id": 1,
  "customer": { "name": "Ann Lee", "address": { "city": "Tokyo", "zip": "104-0061" } }
}

You get four columns: id, customer.name, customer.address.city, customer.address.zip. Nothing is lost, and the transformation is reversible.

One practical warning about the dots. A column literally named customer.address.city is legal in CSV and awkward in SQL, where the dot reads as a table qualifier unless the whole name is quoted. We hit this in our own grid, which had to be taught to render columns whose names contain dots as a specific fix. If the output is heading into a database, replacing the dots with underscores at conversion time saves a lot of quoting later.

Fix it: convert JSON to CSV with nested objects flattened →

Nested arrays: pick one of three

Now the hard case:

{
  "order_id": 1001,
  "customer": "Ann Lee",
  "items": [
    { "sku": "A-1", "qty": 2, "price": 9.99 },
    { "sku": "B-7", "qty": 1, "price": 24.50 }
  ]
}

There are exactly three sensible outputs and no fourth.

The index strategy looks appealing and usually is not. It only works if every record has the same number of elements. The moment one order has fourteen items, the table has fourteen sets of columns and most of the cells are empty.

Exploding: one row per element

Our order becomes two rows:

order_id,customer,items.sku,items.qty,items.price
1001,Ann Lee,A-1,2,9.99
1001,Ann Lee,B-7,1,24.50

This is right when the elements are the thing you care about. Revenue by SKU, units per product, average basket size: all of those need one row per line item.

The cost is that the grain of the table has changed. COUNT(*) now counts line items, not orders, and summing an order-level total across the exploded rows double counts it. If your records carry an order_total field, this is exactly how a revenue report ends up 40 percent too high, and it looks completely plausible while being wrong.

The discipline is simple. After exploding, count distinct on the parent key instead of counting rows, and take a MAX of any parent-level measure within a group rather than a SUM.

SELECT COUNT(DISTINCT order_id)          AS orders,
       SUM("items.qty" * "items.price") AS revenue
FROM data;

Fix it: unnest an array column into one row per element →

Joining: one row per parent

When the array is descriptive rather than analytical, collapse it:

order_id,customer,item_skus,item_count
1001,Ann Lee,"A-1; B-7",2

Two details make this work in practice. Choose a separator that is not the CSV delimiter, so a semicolon or a pipe inside a comma-delimited file. And keep a count column next to the joined string, because the count is what lets you filter and sort later without parsing the string back apart.

Tags, categories, participant lists and permission sets are all good candidates. A cell reading urgent; billing; escalated is perfectly readable and does not multiply your row count.

Fix it: work out the JSONPath for the array before you convert →

Records that do not all have the same keys

JSON requires no schema, so record 1 can have a discount_code and record 2 can omit it entirely. CSV needs one header row covering every record.

A converter has to choose between two behaviors: scan the whole file and take the union of all keys, which is correct but requires reading everything before writing anything, or take the keys from the first record, which is fast and silently drops fields that only appear later.

This matters most with API responses, where optional fields are the norm. If a converter produced a CSV with fewer columns than you expected, first-record behavior is the usual culprit. Convert a sample that contains a record you know has the rare field and see whether the column appears.

A related trap: CSV cannot distinguish an explicit null from an absent key. Both become an empty cell. In an order feed, a null cancelled_at means "not cancelled" while an absent one may mean "this record predates the field", and after a CSV round trip those are the same thing. If that distinction matters, use Parquet or keep the JSON.

Fix it: convert JSON Lines, where each record is independent →

Check what is really in the file

Before converting anything, spend two minutes understanding the shape. It changes which strategy is right, and it is much cheaper than discovering the answer from a wrong report.

1. Is the top level an array of records, or one object? An object with a data key wrapping the array is very common in API responses, and pointing a converter at the wrong level produces exactly one row.
2. Which fields are arrays? Each one needs its own explode-or-join decision. Two arrays exploded at once produce a cross product, which is almost never what anyone wants.
3. How deep does the nesting go? Three levels is common and fine. Beyond that, flattening produces column names nobody can read, and reshaping in JSON first is usually easier.
4. Do all records share a schema? Compare the key sets of the first and last record. If they differ, you need union-of-keys behavior.

A JSON viewer that shows the tree with types answers all four faster than reading the raw file, especially when the document is 40 MB of minified output on a single line.

Fix it: open the JSON and inspect its structure first →