JSON to CSV

One tree, one rectangle

Everything awkward about this conversion follows from a single mismatch. CSV is a rectangle: one row per record, one column per field, one scalar per cell. JSON is a tree of arbitrary depth. Turning one into the other is a projection, and projections lose information unless you decide explicitly what happens to the parts that do not fit.

Two decisions do all the work: which array supplies the rows, and what happens to anything nested. The rest is quoting rules.

Finding the rows

A CSV needs an array of records. Most API responses wrap theirs in metadata:

{
  "status": "ok",
  "meta": { "page": 1 },
  "data": [
    { "id": 1001, "user": { "name": "John", "city": "Chennai" } },
    { "id": 1002, "user": { "name": "Sarah", "city": "Berlin" } }
  ]
}

The rows are data, not the top-level object. Jsonviewpro detects the common wrappers — data, results, items, records, rows, list, entries — and otherwise picks the largest array in the document. It then tells you which one it chose, shown under the output as source $.data, so you can check it rather than trust it.

A bare top-level array is used directly. A lone object becomes a single row. If the array you want is somewhere unusual, pull it out first with a JSONPath query and convert that.

Flatten, or keep the nesting

This is the decision that shapes the output. Both answers are defensible; they serve different readers. Here is the same document converted each way.

Flatten on — one column per leaf value

id,user.name,user.city,roles,note,vip
1001,John,Chennai,admin; user,"Doe, John",
1002,Sarah,Berlin,,"said ""hi""",true

Nested objects become dotted column names. Arrays of primitives are joined with a semicolon. Arrays of objects expand into indexed columns such as items[0].sku, which means the column count depends on the longest record in the file. Choose this when a person will read the spreadsheet or pivot it.

Flatten off — nested values stay as JSON

id,user,roles,note,vip
1001,"{""name"":""John"",""city"":""Chennai""}","[""admin"",""user""]","Doe, John",
1002,"{""name"":""Sarah"",""city"":""Berlin""}",[],"said ""hi""",true

The column count stays stable no matter how irregular the data is, and nothing is lost — a downstream program can parse the cell. Harder for a human, better for a pipeline.

The quoting rules, and why they matter

RFC 4180 is short and worth knowing, because hand-rolled CSV writers get it wrong constantly. A field must be quoted if it contains the delimiter, a double quote, or a line break. A literal double quote inside a quoted field is written twice.

Records that do not match

Records rarely share the same fields. In the example above only the second row has vip. Jsonviewpro collects the union of every field it sees, in first-appearance order, and leaves a cell empty where a record has no value — so no data is dropped and no column is invented.

It is also a signal worth reading. Run the same document through the analyzer: it reports which fields are missing from which records, and which change type between records. A column that holds numbers in most rows and strings in a few will break whatever imports the CSV, and it is better to learn that here than there.

What Excel will do to your file

Excel applies type inference on import, silently, and several of its guesses destroy data. If a person is going to open this file, plan for it.

If the recipient is a program rather than a person, none of this applies: use commas and UTF-8 and ignore the whole table.

Converting on the command line

# Header row plus values, in a stable column order
jq -r '(.data[0] | keys_unsorted) as $k | $k, (.data[] | [.[$k[]]]) | @csv' response.json

# Pick specific fields, including nested ones
jq -r '.data[] | [.id, .user.name, .user.city] | @csv' response.json

# Tab separated instead
jq -r '.data[] | [.id, .user.name] | @tsv' response.json

Use @csv rather than string concatenation — it applies the quoting rules above. A hand-built interpolation breaks the moment a value contains a comma.