Flatten Nested JSON to CSV
How do I flatten nested JSON into CSV?
Turn nested objects into columns with dot notation, so address.city becomes the column address.city, and choose a strategy for arrays. Join merges array elements into one cell, explode duplicates the row once per element, and index expands arrays into numbered columns such as tags.0 and tags.1. The right choice depends on how you will use the data downstream.
Why nested JSON is hard
CSV is two-dimensional, with one row per record and one value per cell. JSON can nest objects and arrays at any depth, so something must give when you flatten it.
Nested objects become dot columns
{"user":{"name":"Alice","id":7}} flattens to the columns user.name and user.id. This is unambiguous because objects have exactly one path to each value.
Array strategy 1: join
["a","b","c"] becomes a single cell such as a;b;c. This is best for tag lists where the elements are only listed, not counted or filtered individually.
Array strategy 2: explode
One record with three tags becomes three rows, each carrying one tag and a copy of the parent fields. This is best for arrays of objects such as line items, because analytics tools can then filter and pivot on each element.
Array strategy 3: index
tags.0, tags.1, and tags.2 become separate columns. This is best when array position matters and lengths are consistent, such as coordinates.
A worked example
Take an API response where each employee has a nested location and an array of projects. Flatten the location into location.city columns, then explode the projects so each row is one project with the employee repeated.
Failure story: Stripe lines.data
Stripe webhook {"lines":{"data":[{"sku":"a","qty":1},{"sku":"b","qty":2}]}} flattened with join gives one row a;b and loses line-item grain; downstream SUM(qty) becomes a string, not a number. We switched to explode plus indexed tags.0 in src/lib/csv/helpers.ts:58 flattenJson and got 1 row to 2 rows with correct qty 1, 2 and parent id duplicated, audit trail preserved. With 10 levels and 3-way branching, indexed creates lines.data.0.sku but explodes to 59049 columns and hits Excel 16384 limit, so we default to stringify (jsonToCsvFormatter.ts:44) unless you opt into explode.
FAQ
What does dot notation mean in CSV headers?
Nested object keys are joined with dots, so user.address.city becomes one column named user.address.city.
Should I join or explode arrays?
Join when you only need to list the values. Explode when you need one row per element, such as for filtering, pivoting, or one-to-many relationships.
Can flattened CSV be converted back to JSON?
Yes. Dot-notation headers and index columns are reversible, which is why this shape is standard for round-tripping.
What if my JSON is nested ten levels deep?
The flatten walker visits every leaf path and creates a column per path, so ten levels become long dot-notation headers. You can cap the depth to leave deeper objects as JSON strings.