CSV is a rectangle: one row per record, one column per field, one scalar per cell. JSON is a tree of arbitrary depth. Converting between them is therefore a projection, and projections lose information unless you decide explicitly what happens to the parts that do not fit.
Step one: find the rows
A CSV needs an array of records. Most API responses wrap theirs:
{ "status": "ok", "meta": { "page": 1 }, "data": [ { "id": 1 }, { "id": 2 } ] }The rows are data, not the top-level object. The JSON to CSV converter detects the common wrappers — data, results, items, records, rows, list, entries — and otherwise picks the largest array in the document. It tells you which one it chose, so you can check.
If the array you want is somewhere unusual, extract it first with a JSONPath query and convert the result.
Step two: decide about nesting
Given {"user": {"name": "John", "address": {"city": "Chennai"}}} you have two reasonable options.
Flatten into dotted columns
You get user.name and user.address.city. Every scalar becomes its own column, which is what you want if a human is going to read the spreadsheet or pivot it.
The cost is column count. A deeply nested document with a hundred leaf values becomes a hundred columns, and arrays of objects expand to indexed columns — items[0].sku, items[1].sku — which makes the width depend on the longest record in the file.
Keep nested values as JSON text
The cell contains {"name":"John","address":{"city":"Chennai"}}. Column count stays stable, nothing is lost, and a downstream program can parse the cell. Harder for a human, better for a pipeline.
Arrays of primitives are a third case: joining them with a separator — admin; user — is usually the most useful result, and that is what Jsonviewpro does.
Step three: get the CSV rules right
RFC 4180 is short and worth knowing. 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.
id,name,note
1,"Doe, John","He said ""hello"""
2,Sarah,"line one
line two"Both rows above are valid single records. Any correct CSV parser reads the embedded newline as part of the field, not as a row boundary.
Irregular records
Records rarely all have the same fields. The sane approach — and the one Jsonviewpro takes — is to collect the union of every field seen, in first-appearance order, and leave a cell empty where a record has no value for a column.
That is also a signal worth investigating. Run the document through the analyzer: it will tell you which fields are missing from which records, and whether any field changes type between records. A column that is a number in most rows and a string in three of them will cause trouble in whatever reads the CSV next.
The Excel problems
- Long numbers. A 16-digit identifier is converted to scientific notation and the low digits are lost — permanently, if you save the file. Import as text, or prefix with an apostrophe.
- Leading zeros. Postal codes and product codes lose them on import.
- Date coercion. Strings that look like dates are converted, and the format depends on the machine’s locale. The gene-name case is famous:
SEPT2becomes a date. - Delimiters by locale. In locales where the comma is the decimal separator, Excel expects semicolon-delimited files. Switch the delimiter rather than fighting the import dialog.
- Encoding. Excel on Windows may read a UTF-8 file as the system code page unless it starts with a byte order mark.
Converting on the command line
# Header row plus values, in a fixed column order
jq -r '(.data[0] | keys_unsorted) as $k | $k, (.data[] | [.[$k[]]]) | @csv' response.json
# Flatten one level of nesting first
jq -r '.data[] | [.id, .user.name, .user.email] | @csv' response.json@csv handles quoting correctly, which hand-rolled string concatenation does not. Use it.