Flattening nested JSON for spreadsheets

A row in a spreadsheet has a fixed set of columns, and every row has the same ones. A JSON object can contain any keys, nested to any depth, and two objects in the same array need not agree. Flattening reconciles the two by walking the tree once, recording every path to a leaf, and using the union of all paths as the column list. Rows that lack a path simply get an empty cell.

That last rule already hints at a cost. An export of 1,000 orders where one order has 40 line items produces columns for items[0] through items[39], and 999 rows leave most of them blank. Flattening is lossless, but it is not always the right shape for analysis, which is why the array question deserves its own section.

There are two common notations for the same path. Dot notation joins every segment with a period: customer.address.city and items.0.sku. Bracket notation, borrowed from JavaScript, keeps dots for keys but puts array indexes in square brackets: customer.address.city and items[0].sku. Both are unambiguous as long as keys themselves contain no dots or brackets.

{ "customer": { "address": { "city": "Tallinn" } }, "items": [{ "sku": "WRP-01" }] }

dot:      customer.address.city   items.0.sku
bracket:  customer.address.city   items[0].sku

Choose by destination. Dot paths are what most ETL tools, pandas' json_normalize and BigQuery expect, and they type easily into a spreadsheet formula. Bracket paths are what developers read most naturally and what JSON Loomly can reverse without guessing, because [0] is unmistakably an index while .0 could in principle be a key named "0". When a key does contain a dot, bracket notation quotes it — data["first.name"] — and dot notation has no escape, so bracket paths are the safer choice for odd data.

Plain flattening turns an array into numbered columns: tags[0], tags[1], items[0].sku, items[1].sku. This keeps one row per top-level object, preserves everything and round-trips perfectly. It is the right choice for short, fixed-length arrays — a pair of coordinates, two or three tags, a handful of phone numbers — and for any case where you need to get back to the original JSON.

The alternative is to explode the array into rows: one row per line item, with the parent's fields repeated on each. Analysts prefer this shape because it pivots and sums naturally — total quantity per SKU is a one-line formula instead of a sum across forty columns. The price is that the parent is now duplicated, the row count changes, and the sheet can no longer be turned back into the original document without knowing which columns came from the parent.

A practical middle path: flatten once to see the shape, then decide. If the longest array in the document has a length under ten (the Stats action reports it), indexed columns are usually fine. If it runs into the dozens, extract that array into its own sheet keyed by the parent's id, the way a relational database would.

JSON distinguishes three situations that a spreadsheet cell cannot: a key set to null, a key that is absent, and an empty string. After flattening, all three usually become an empty cell. If the distinction matters — for example when null means “explicitly cleared” and absent means “never asked” — write the distinction into the data before converting, such as a separate boolean column, because it will not survive the CSV.

Empty objects and empty arrays are a similar edge. JSON Loomly keeps them as literal {} and [] cells so that unflattening restores them; other tools drop them silently. Neither behaviour is wrong, but you should know which one you are getting before you promise someone a lossless round trip.

Unflatten reads the column names as paths and rebuilds the tree. Each segment becomes a nested object, except that a group of siblings whose names are all non-negative integers becomes an array. This is why bracket notation is easier to reverse and why a column called "0" in the middle of a spreadsheet is ambiguous in dot notation.

Types are the other thing to watch on the way back. CSV stores everything as text, so a converter has to decide whether 10111 is a number or a postal code. JSON Loomly's CSV → JSON action infers numbers, booleans and null by default and leaves values with leading zeros as strings; turn inference off in the CSV → JSON menu when the sheet contains identifiers you want kept verbatim. Then run Unflatten, and compare the result against the original with Compare to confirm nothing drifted.

CSV has one real rule, defined in RFC 4180: a field that contains the delimiter, a double quote or a line break is wrapped in double quotes, and any double quote inside it is doubled. A product name like Teal warp thread, 200 m therefore becomes "Teal warp thread, 200 m" in the file. A converter that gets this wrong shifts every column after the comma — the most common cause of “my spreadsheet is misaligned”.

The delimiter itself is a regional matter. Excel in locales that use a decimal comma (most of continental Europe) expects a semicolon between fields and will show a comma-separated file as a single column. Pick the delimiter for the person who will open the file, not for the tool that made it. Dates are a final trap: a column of 2026-08-01 values is safe, but 01/08/2026 will be read as January or August depending on the machine, so keep ISO dates as they come out of the JSON.