What a row means when JSON becomes CSV

A rectangle and a tree

CSV is a rectangle. Each record is a row, each separator moves to the next column, and the optional first row gives those positions names. A field may contain a separator or a line break when quoted, but the structure remains flat: row, column, cell.

JSON is a tree. Its two containers have different meanings:

• An object maps names to values.
• An array keeps values in order.

Either may contain another object or array, and no rule says two objects beside each other must carry the same names. This flexibility is why JSON works for an API response and why “convert JSON to CSV” is not a complete instruction. A converter must choose where to cut the tree into rows and how to name the paths that reach each leaf.

Three shapes that are honestly tabular

There are three common top-level shapes with defensible row rules.

One object

{ "name": "Ada", "city": "London" }

This can be one row with two columns. It is a small table, but still a table.

An array of objects

[
  { "name": "Ada", "city": "London" },
  { "name": "Grace", "city": "New York", "language": "COBOL" }
]

Each object is a row and the union of keys is the header: name, city, then language. Ada’s language cell is empty. Taking headings only from the first object would silently discard Grace’s field, which is why a correct conversion has to inspect every record before writing the header.

An array of arrays

[
  ["Ada", "London"],
  ["Grace", "New York"]
]

Each inner array is a row and position is the column. What position zero means is absent, so a generic converter can call it column_1 but cannot honestly invent the heading name. Arrays are compact and useful when a schema travels separately. They are brittle when a person has only the file.

Before you read on

The first JSON object has keys name and city. The hundredth also has language. When may the CSV header be written safely?

• AImmediately after the first object.

  That is fast, but it loses language without warning.

• BAfter every object has been checked for new keys.

  Yes. Sparse records make the header a union across the complete set.

• COnly if every object has exactly the same keys.

  Uniform records are simpler, but empty cells are an honest representation of missing fields.

After the complete set of records has been checked. A JSON object is not constrained by the keys on the object before it, and sparse data is normal: one customer has a middle name, one invoice has a tax number, one API result carries an optional warning. If the first object alone defines the header, later fields disappear and the output still looks perfectly valid. The safe header is the union of keys, kept in first-seen order so its result is deterministic and readable. Records that lack one of those keys receive an empty cell under that heading.

Why objects are usually the safer JSON row

Compare a record after one field is added.

// positional
["Ada", "Lovelace", "London"]

// named
{ "first": "Ada", "last": "Lovelace", "city": "London" }

Now insert a middle name. In the array, every consumer must know that the city moved from position two to position three. An older consumer can read "Byron" as the city without throwing an error. In the object, adding "middle": "Byron" does not change what city means and a consumer that does not need the new field can ignore it.

Arrays win on repetition: thousands of rows do not repeat the same key names. That saving matters inside a high-volume protocol with a versioned schema. It usually does not matter in a 20 MB file being handed to a person, where the ability to read and diagnose the data is worth more than a few compressed kilobytes.

This is why CSV to JSON sensibly defaults to one object per headered row and offers arrays as an explicit alternative. The header already supplied names; discarding them should be a choice, not a side effect.

The inverse case is worth noticing too. A ratio table is already a rectangle: each multiplier is a row and the two scaled quantities are stable columns. It can become CSV without inventing a schema because the calculation itself has already decided what every row and column means.

Nesting is where the model becomes visible

Consider one value:

{
  "name": "Ada",
  "address": {
    "city": "London",
    "postcode": "SW1A"
  },
  "roles": ["mathematician", "writer"]
}

A rectangle has no nested cell. Two common answers are both legitimate and not equivalent.

Serialise the nested value. The address cell contains compact JSON and the roles cell contains the JSON array. The values remain recoverable, but a spreadsheet cannot sort directly by city without parsing the cell again.

Flatten object paths. The headings become address.city and address.postcode. That is convenient for filtering and preserves the parent name in each heading. Arrays should usually remain one JSON cell: expanding roles[0], roles[1] and onward makes list length dictate schema and implies that the first item has a stable meaning across every row.

Neither answer can be inferred as universally correct. JSON to CSV exposes the choice because hiding it behind “automatic” would make one use case silently wrong.

Types disappear at the CSV boundary

These JSON values are distinct:

{ "number": 42, "word": "42", "nothing": null, "missing": "not present at all" }

CSV has only characters between boundaries. Both 42 values become the two characters 42; null and a missing field both become an empty cell under the usual convention. A spreadsheet may infer a number, a date or a formula when it opens the file, but that inference is the importer’s behaviour, not type information carried by CSV.

That also explains formula protection. A JSON string beginning with =, +, - or @ is explicitly text. CSV cannot say so, and spreadsheet software may interpret it as an expression. Prefixing the field with an apostrophe preserves the source’s intent — text — while a genuine JSON number such as -2 remains numeric text without the prefix.

Formatting JSON is not converting its model

Pretty-printing braces and indentation looks like the least consequential step in this workflow, but the usual parse-and-stringify shortcut crosses the same model boundary as a converter. Once a JSON number becomes a binary64 language number, an integer beyond the exact range can acquire different digits. Once an object with duplicate names becomes a normal application map, one of those members can disappear. A serialiser is then writing its own values, not merely laying out the source.

A token-preserving formatter takes a narrower route: validate the complete document, track quoted strings and escapes, and change only the four whitespace characters the JSON grammar permits between tokens. That keeps a readable file readable without deciding that key order, duplicate names or exponent spelling were accidental. Use model conversion when the model should change; use a formatter when layout is the only requested change.

A round trip preserves decisions, not bytes

Suppose a headered CSV becomes an array of JSON objects and then becomes CSV again. The table may still have the same values, but byte identity is not a reasonable expectation:

• Quotes may be added or removed while remaining equivalent.
• Line endings may be normalised to CRLF.
• A semicolon or tab input may come back as comma-separated output.
• The JSON stage cannot preserve whether 42 was deliberately text.
• Null, missing and an empty string may all meet at an empty cell.
• A UTF-8 marker may be added for Excel compatibility.

Use a checksum when two files are supposed to be identical. Use a structural test — the same headings, rows and declared value conventions — when one data model has intentionally passed through another.

The practical rule

Use an array of objects when people inspect the file, records may be sparse, or the schema will evolve. Use arrays of arrays when position is already a formal contract and compact transfer matters. Keep nested values as JSON when round-tripping matters; flatten object paths when spreadsheet analysis matters.

Most importantly, write those choices down beside the artifact. Conversion is not merely replacing braces with commas. It is the moment a tree is made into a rectangle, and the decisions made there determine whether later rows still mean what the source meant.