JSON to CSV When the Structure Is Nested: Why There Is No Right Answer

The same two orders, twice

Here is the input, and it will stay the same for the whole article. Two orders. The first has id 1, a customer object holding name Emma and city Paris, a tags array of two strings, and a lines array of two objects, each with a sku and a qty. The second has id 2, a customer object holding Liam and Berlin, a tags array of one string, a lines array of one object, and one extra key the first record does not have: note, set to urgent. Nothing exotic — this is the shape of every order, invoice and event payload that ever came out of an API.

Run it through the CSV / JSON / YAML converter and you get five columns: id, customer, tags, lines, note. The customer cell of the first row contains the characters {""name"":""Emma"",""city"":""Paris""} — the object serialised back to JSON, then quoted as a CSV field, which doubles every quote inside it. The lines cell contains the whole array the same way. Open that in a spreadsheet and you have two rows, five columns, and three cells you cannot sort, filter or sum.

Run exactly the same JSON through the JSON to CSV converter with nested values set to flatten, and you get ten columns: id, customer.name, customer.city, tags.0, tags.1, lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty, note. The second order has one tag and one line, so tags.1, lines.1.sku and lines.1.qty are empty on that row. Every value is now a scalar in its own column. The row count did not change — it is still two — and the file is now shaped by the largest record rather than by the schema.

Neither output is a bug. Serialising is right when the CSV is a transport format and something will parse those cells later: the record shape is preserved exactly, and a round trip through a machine gives back what went in. Flattening is right when a person is going to open the file: every leaf is sortable, filterable and summable. What is a bug is doing either one without knowing which one you did, and then discovering three weeks later that the analyst has been counting rows in a file whose row count answers a different question than they think.

Dotted paths, bracket indices, and the key that already contains a dot

Once you decide to flatten, you have to name the leaves. Two spellings are in wide use. Dotted paths write an array index like any other key: tags.0, lines.1.sku. Bracket indices distinguish the two kinds of step: tags[0], lines[1].sku. The dedicated JSON flattener on this site offers both, plus a choice of separator — dot, underscore or slash — because an underscore survives a trip through systems that treat a dot as a path operator, and a slash matches the pointer syntax people already know from JSON Pointer. The JSON to CSV converter's flatten mode always uses dots for both, which is the more compact of the two and the one spreadsheets are least likely to mangle.

There is a real failure hiding in the dotted spelling, and it is worth stating plainly because it costs data. A dotted path is ambiguous: the column customer.name could mean the key name inside the object customer, or a top-level key whose literal name is customer.name. JSON allows both, in the same object. Feed the flatten mode a record that contains a literal key a.b with the value 1 alongside a nested object a whose key b holds 2, and the output has one column, a.b, containing 2. The first value is gone, no warning, no second column. It is rare, but it is not hypothetical: keys with dots appear in log fields, analytics event names and anything derived from a namespaced identifier.

The defence is the separator choice. Flatten with an underscore or a slash instead of a dot and the collision needs a key that literally contains that character, which is far less likely. If you cannot choose the separator — and in the CSV converter's flatten mode you cannot — then check your keys for dots before you flatten, not after.

Arrays: one column each, one cell, or one row each

An array of scalars has three sane answers. Serialise it — the tags cell becomes the characters ["vip","eu"]. Give each element a column — tags.0 and tags.1. Or join the elements with a separator that does not appear in them, so the tags cell reads vip|eu and a spreadsheet formula can split it back. The converters here do the first two and neither does the third, so if you want a joined string you have to produce it before conversion. The joined form is the most human-readable and the only one whose column count does not change when the data does, which is why so many exports use it despite it being the least well defined.

An array of objects is where the tools and the databases part company. Given lines with two entries, the flatten mode widens the table: lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty. A database person would expect the opposite — one output row per line item, with the order fields repeated down the block, which is what a join produces and what a pivot table wants. Neither converter does that, and the difference is not cosmetic. Widening keeps one row per order, so a row count is an order count. Exploding gives one row per line item, so a row count is a line count and the order fields are duplicated. Both are used in the wild; only one of them answers the question how many orders did we ship.

One consequence of widening is worth planning for: the column layout is set by the largest array anywhere in the file, and it changes when the data changes. Two records whose tag arrays hold one and three elements produce columns t.0, t.1 and t.2, with two empty cells on the short record. Export the same query tomorrow with a four-tag record in it and the file gains a column, silently. Anything downstream that reads columns by position rather than by name breaks that day, and the export that broke it looks identical to the one before.

Records that do not agree on their keys

JSON has no schema, so an array of objects is not a table until you make it one. Both converters take the union of every key they see and leave a hole where a record does not have one. Three records holding {id, a}, {id, b} and {id, a, c} give four columns — id, a, b, c — with empty cells where each record is silent. That is the only answer that loses nothing, and it is why a CSV exported from a document store is usually much wider than any one document.

The column order is not sorted and not stable across exports. Both converters use first-seen order: the keys appear in the order the first record that contains them presents them. Two records {b, a} and {a, b} produce the columns b then a, because the first record was read first. Change the sort of your query and the column order changes with it, even though the data is identical. If anything downstream depends on column order, sort the keys yourself before exporting.

One thing is lost no matter which strategy you pick, and CSV is to blame rather than the converter. A key set to null, a key set to the empty string and a key that is simply absent all become the same empty cell. Read that CSV back and every one of them returns as an empty string. If the distinction matters — and in a partial update or a nullable database column it always does — CSV is the wrong format for that field, and no flattening option will rescue it.

What the CSV side detects, and what it does not

Start with the direction that used to embarrass this converter. Give the CSV / JSON / YAML converter the JSON [1,2,3] and it returns value / 1 / 2 / 3: a single synthesised column, because a number has no key of its own to become a column name and value is the only honest thing to call it. A top-level scalar, 42, gives value / 42. A mixed array, [1,{"a":2}], gives the header value,a and two rows — 1 followed by an empty cell, then an empty cell followed by 2 — the scalar in the invented column, the object in its own. That is the same answer the sibling JSON to CSV converter gives, so the two tools on this site now agree about the case that most often arrives from an API returning a bare list of ids.

Reading CSV, the converter now sniffs its delimiter instead of assuming a comma: it counts commas against semicolons on the first record, ignoring anything inside quotes, so name;city over Emma;Paris arrives as {"name":"Emma","city":"Paris"} — which matters, because Excel writes semicolon CSV by default in five of this site's six markets. Repeated column names are renamed rather than dropped: name,name,name over a,b,c returns name, name_2 and name_3. A row that runs past the header keeps the extra cell under an invented name: a,b over 1,2,3 returns a, b and column3. What it still does not do is score a tab or a pipe. The dedicated CSV to JSON tool weighs four candidates and lets you force one; this converter weighs two and has no delimiter control in its interface at all, so a tab-separated file still arrives as a single column whose key is the whole header line. And whatever went in, the CSV it writes back out is comma-delimited.

Frequently asked questions

Should I serialise the nested values or flatten them?

Ask who opens the file. If the answer is a program that will parse it again, serialise: the record shape is preserved exactly and a round trip returns what went in. If the answer is a person in a spreadsheet, flatten: they need to sort by customer.city and sum lines.0.qty, and they cannot do either with a JSON blob in a cell. If the answer is both, produce two files rather than compromising, because the compromise — flattening one level and serialising the rest — is the version nobody can reason about six months later. And if the file is an archive rather than a report, serialise, because flattening bakes today's array lengths into the column layout and next month's export will not match it.

Can I get one row per line item instead of extra columns?

Not from either converter — both widen the table and neither explodes an array into rows. The reason is that exploding is not a formatting choice, it is a change of grain: the resulting file answers a different question, and the converter would have to decide which array to explode when a record contains two of them. Do it before the conversion, in whatever produced the JSON: emit one object per line item, each carrying the order fields it needs. Then the flattening question disappears, because the array is gone. If the JSON is all you have, a short script that maps each order to its line items and concatenates the results is a five-line job and leaves the decision visible in your own code rather than buried in a tool's defaults.

Why did my file gain a column between two exports of the same query?

Because the column layout of a flattened export is a property of the data, not of the query. The columns are the union of every path present, and array paths are numbered up to the length of the longest array anywhere in the result. One record with four tags where the previous run had at most three adds tags.3 to every row. The same mechanism adds a column when a single record contains an optional key nobody had used before. There are two defences: read columns by name rather than by position everywhere downstream, and if a stable layout genuinely matters, define the column list explicitly and project onto it, rather than letting the exporter derive it from whatever happened to be in the result set.

Can I turn the flattened CSV back into the original JSON?

Partly, and the gaps are predictable. The JSON flattener has an unflatten mode that rebuilds nesting from path keys: a segment that is a bare number builds an array, anything else builds an object, so customer.name and tags.0 come back as an object and an array. Three things do not come back. Types are gone, because every cell of a CSV is text — a number written 1 returns as the string "1" unless you convert it. The distinction between null, an empty string and an absent key is gone, as covered above. And an empty array or an empty object leaves no path at all in a dotted flattening, so it cannot be reconstructed; the flattener writes a visible [] or {} for exactly that reason, but only if you flattened with that tool. Round-tripping through the serialising strategy instead loses none of this, which is its whole argument.

My CSV uses semicolons. Do I need to convert it first?

No. The CSV / JSON / YAML converter counts commas against semicolons on the first record, outside any quoted field, and takes the winner, so a French, German, Spanish, Italian or Portuguese Excel export reads correctly with nothing set. The case that still fails is a tab or a pipe: neither is among the candidates it scores, so a tab-separated file becomes one field per row whose key is your entire header line. The symptom is unmistakable once you know it — a single key with tabs in its name. Two ways out. Convert the delimiter first with the delimiter converter, which parses properly and re-quotes anything that needs it. Or use the dedicated CSV to JSON tool, which weighs four candidates and lets you force one. Note also that whatever goes in, the CSV this converter writes back out is comma-delimited.