How the conversion works
JSON to CSV: every object in the array becomes a row. The columns are all the keys that appear in any object, in the order they first show up, so an object without a key just gets an empty cell. Nested objects are flattened into dot columns such as host.name, and arrays are written as JSON text in one cell, because a list does not fit into a single spreadsheet column. Numbers are kept exactly as written, so long IDs are not rounded.
Quoting follows RFC 4180, the CSV standard: a value that contains the delimiter, a double quote or a line break is wrapped in double quotes, and a quote inside it is written twice. Other values stay as they are. Pick a semicolon if your spreadsheet uses a comma for decimals, as Excel does in much of Europe.
CSV to JSON: the parser reads quoted fields, doubled quotes, line breaks inside quotes and CRLF, LF or CR line endings. It detects the delimiter from the first rows by finding the one that splits them into the same number of fields. With a header row you get one object per row; without it, one array per row. Typing turns 42, 3.5, true, false and null into JSON values but leaves 007 and very long numbers as text, so ZIP codes and IDs survive.
Example
Two servers, one with a nested host object and a list of ports, the other without an IP address and with a comma in its note:
[
{ "id": 1, "host": { "name": "web-1", "ip": "203.0.113.10" }, "ports": [80, 443] },
{ "id": 2, "host": { "name": "db-1" }, "note": "primary, eu" }
]The columns are the union of all keys. host becomes host.name and host.ip, the ports array stays JSON text and is quoted because it contains a comma, the missing IP and ports are empty cells, and the note is quoted for its comma:
id,host.name,host.ip,ports,note
1,web-1,203.0.113.10,"[80,443]",
2,db-1,,,"primary, eu"
Converting that CSV back with typing and nesting on gives the objects again. The id is a number once more and host is an object, but the ports come back as the text "[80,443]" and the empty cells as empty strings, because a CSV cell cannot say it was a list or missing:
[
{"id":1,"host":{"name":"web-1","ip":"203.0.113.10"},"ports":"[80,443]","note":""},
{"id":2,"host":{"name":"db-1","ip":""},"ports":"","note":"primary, eu"}
]Questions
How do I convert JSON to CSV?
Paste the JSON above or open a .json file, keep JSON → CSV selected and pick a delimiter. The CSV appears at once with a table preview; copy it or download it as a .csv file and open it in Excel, Google Sheets or LibreOffice. The JSON should be an array of objects, but a single object, an API answer with one array inside and JSON Lines work too.
How do I convert CSV to JSON?
Switch the direction to CSV → JSON, then paste the CSV or open a .csv file. The delimiter is detected for you. With the first row as column names, each row becomes an object; turn that off to get arrays. Turn on typing to get numbers and true or false instead of strings, and nesting to turn columns like server.cpu back into objects.
What happens to nested JSON objects and arrays?
Nested objects are flattened into dot columns: a host object with a name becomes a host.name column, as deep as it goes. Arrays are written as JSON text in a single cell, such as [80,443], because a list has no natural place in one cell. Turn flattening off to keep nested objects as JSON text too.
Why does Excel put my CSV in one column or break the accents?
Excel uses your system's list separator: where the decimal mark is a comma, as in Germany or France, it expects semicolons. Choose Semicolon here and the columns split. Broken accents mean Excel read the file as an old code page instead of UTF-8; tick the byte-order mark option before downloading, or import the file with Data → From Text/CSV and pick UTF-8.
How do I convert JSON to CSV on the command line?
With jq, for an array of flat objects: jq -r '(.[0] | keys_unsorted) as $k | $k, (.[] | [.[$k[]]]) | @csv' data.json > data.csv. It takes the columns from the first object only. For CSV to JSON, Python needs no extra package: python3 -c "import csv,json,sys; print(json.dumps(list(csv.DictReader(sys.stdin))))" < data.csv.