Convert JSON to CSV

Flattening a tree into a grid

JSON is a tree and CSV is a rectangle, and the whole conversion is the question of how one becomes the other. The clean case is an array of objects that all share the same keys — each object is a row, each key a column, and nothing is lost. That is what most API responses and exports look like, which is why this usually just works. Everything awkward comes from documents that are not that shape.

Nested objects are flattened by joining their keys, so an address object inside a customer becomes columns like address.city and address.postcode — a convention every spreadsheet handles and no standard defines. Keys that appear in some objects and not others become columns that are empty in the rows that lacked them, which is the right answer: the alternative is dropping data that only some records carry.

Nested arrays are where a grid genuinely cannot follow. One record with three order lines inside it is one row containing a list, not three rows — turning it into three would duplicate every other field and quietly change what the data means. So one array of objects is expanded into rows and any others are written into their cell as text. If what you need is the order lines rather than the orders, point the conversion at that array instead; a tree with two lists side by side has no single correct rectangle, and picking one is a decision only you can make.

Two details bite on the way out. Values containing a comma, a quote mark or a line break are quoted so they survive — which is correct CSV and looks odd if you inspect the raw file. And a long numeric identifier is a hazard once the CSV reaches a spreadsheet: Excel will read it as a number, round it beyond fifteen digits and strip any leading zero, which is a problem introduced by the spreadsheet rather than by the conversion.

When you want something else

jq is the right tool when the shape needs deciding rather than guessing: jq -r '.orders[] | [.id, .customer.name, .total] | @csv' picks exactly the fields you want from exactly the level you want them, which is the control an automatic flattener cannot offer. It also streams, so file size stops being a consideration.

For nested data generally, Miller — mlr --ijson --ocsv flatten — and Python's pandas.json_normalize give you explicit control over how deep to flatten and what to do with lists. And if the JSON is genuinely hierarchical rather than tabular, converting it to CSV may be the wrong idea altogether — a database or a line-delimited JSON file keeps the structure that a grid is about to discard.