DATATRANSFORMER · GUIDE

How to convert nested JSON to CSV

CSV is a single flat table, while JSON can nest objects inside arrays inside objects. Here is how to choose a conversion strategy that keeps your data usable.

The core problem

A CSV file has rows and columns, and every row has the same columns. JSON data from an API rarely looks like that. Consider a single customer record:

{
  "id": 101,
  "name": "Ada",
  "address": { "city": "Seattle", "zip": "98101" },
  "orders": [
    { "orderId": "A771", "total": 25.5 },
    { "orderId": "A772", "total": 12 }
  ]
}

The address is a nested object, which has a fixed set of fields. The orders are an array, which can have any number of items. These two kinds of nesting need different treatment, and that is why there is no single "correct" CSV for this record. The JSON ↔ CSV converter supports three approaches.

Approach 1: keep nested values as JSON text

The simplest conversion makes one column per top-level key and writes any nested value as JSON text inside the cell:

id,name,address,orders
101,Ada,"{""city"":""Seattle"",""zip"":""98101""}","[{""orderId"":""A771"",...}]"

Nothing is lost, and the file can be converted back later. However, the nested cells are hard to read in a spreadsheet and impossible to filter or sum. Use this approach when the CSV is an intermediate format for another program, or when only the top-level fields matter to you.

Approach 2: flatten objects with dotted column names

Nested objects can be expanded into separate columns whose names record the path to each value:

id,name,address.city,address.zip,orders
101,Ada,Seattle,98101,"[{...}]"

This works well for objects because every record has the same set of nested fields, so the columns stay stable. Analysts can filter by address.city directly in a spreadsheet. The dotted names also let a converter rebuild the original nested structure from the CSV.

Arrays still remain as JSON text in this mode. You could create columns such as orders.0.total and orders.1.total, but the number of columns would depend on the longest array in the file, and calculating a total across all orders would become awkward.

Approach 3: split arrays into related tables

The approach that databases use is to store each array as its own table and connect the rows with keys. In relational mode the converter produces:

A root table with one row per customer, named after your uploaded file (or data for pasted input), containing id, name, address.city and address.zip, plus a generated __row_id.
A data_orders table with one row per order, containing orderId and total, plus a __parent_row_id that matches the customer's __row_id.

data:          __row_id,id,name,address.city,address.zip
               1,101,Ada,Seattle,98101
data_orders:   __row_id,__parent_row_id,orderId,total
               2,1,A771,25.5
               3,1,A772,12

Each table is now a proper rectangle. You can sum order totals, count orders per customer with a pivot table, or join the tables back together with VLOOKUP, XLOOKUP or a SQL JOIN on __parent_row_id = __row_id. Arrays nested inside arrays become further child tables. Arrays of plain values, such as a list of tags, become a child table with a single value column.

The Excel download places each table on its own worksheet and adds a _relationships sheet that lists the parent and child columns. The ZIP download contains one CSV file per table and a schema.json file with the same information.

Which approach should you use?

The CSV will be read by another program that can parse JSON in cells: approach 1.
Records have nested objects but few or no arrays, such as user profiles with addresses: approach 2.
Records contain lists you want to analyse, such as orders, line items or events: approach 3.

Details that affect every approach

Types are lost. CSV stores text only. The number 12, the boolean true and the string "12" all look the same in a CSV file. If you convert back to JSON, plan to restore types afterwards.
Missing fields become empty cells. If only some records have a phone field, the column is still created and the other rows are left blank. A null value is also written as an empty cell, so CSV cannot distinguish null from missing.
Column order follows first appearance. Columns appear in the order keys are first seen across the records.
Formula-like values are escaped. Values beginning with =, +, - or @ are prefixed so spreadsheets do not run them as formulas. See CSV pitfalls for why this matters.

If your JSON fails to convert, first check that it is valid using the JSON formatter. Simple mode also requires the top level to be an array of objects; wrap a single object in square brackets or use nested or relational mode, which accept a single object.