JSON vs CSV: which format to use when

A CSV file is rows of fields separated by a delimiter, with an optional header line naming the columns. Every row has the same columns, every cell is a piece of text, and there is no way to express “this cell contains a list”. That rigidity is the point: the shape is known before you read a single row, which is what spreadsheets, databases and statistics packages need.

JSON allows objects inside objects and arrays of anything. An order with three line items, two tags and a nested shipping address is one natural JSON object and an awkward CSV row. When the data is genuinely hierarchical — configuration, API payloads, documents with optional sections — JSON keeps the hierarchy and CSV forces you to flatten it, with all the choices that entails. When the data is genuinely tabular, JSON's extra brackets and repeated keys are pure overhead.

For a table, CSV is markedly smaller: the column names appear once in the header, while JSON repeats every key on every row. A 100,000-row export with ten short fields is typically 40–60 % larger as formatted JSON, and still 20–30 % larger minified. Compression narrows the gap — gzip is very good at repeated keys — but the raw file in your inbox or your browser tab is what you notice, and there CSV wins.

Streaming is the other side of size. CSV can be processed line by line with almost no memory, which is why it remains the format of choice for multi-gigabyte exports and log-style data. A standard JSON document is a single value: a parser must read to the final closing bracket before it can be sure the document is complete, so tools tend to load the whole thing at once. There is a JSON variant designed for streaming, covered below.

JSON knows the difference between 42, "42", true and null. CSV knows only text. The moment a table goes through CSV, every reader has to guess types again: a spreadsheet will turn 007 into 7, 1e5 into 100000 and 2026-07 into a date, and a numeric ID longer than 15 digits will be silently rounded. Booleans become the strings true and false, or TRUE and FALSE, or 1 and 0, depending on who wrote the file.

If the data has identifiers, leading zeros, precise decimals or a meaningful difference between empty and null, JSON preserves it and CSV needs a documented convention. Converting CSV back to JSON therefore always involves inference or a schema; JSON Loomly infers numbers, booleans and null and keeps zero-padded values as strings, and lets you switch inference off entirely.

The strongest argument for CSV is who can open it: every spreadsheet, every BI tool, every database import wizard, R, pandas and SQL's COPY all speak it natively. If the recipient is a person with Excel or Google Sheets, CSV (with the delimiter chosen for their locale) is the polite choice. If the recipient is a program — an API, a configuration loader, a message queue — JSON is what it expects and what its error messages will be about.

Validation follows the same split. JSON can be checked against a JSON Schema that says which keys are required, what type each has and which strings are dates or emails; you can generate a starting schema from an example document in seconds. CSV has no equivalent standard, so validation lives in whatever imports the file. For any data that will be exchanged repeatedly between systems, that schema is worth more than the size savings.

JSON Lines (also written JSONL or NDJSON) is a file with one complete JSON object per line and no surrounding array. Each line is independent, so the file can be streamed, appended to, split and grepped like CSV, while every record keeps JSON's nesting and types. Logging pipelines, machine-learning datasets and bulk APIs (Elasticsearch, BigQuery, OpenAI's batch endpoints) use it for exactly this reason.

{"id": 1041, "status": "shipped", "tags": ["wholesale", "repeat"]}
{"id": 1042, "status": "pending", "tags": []}
{"id": 1043, "status": "shipped", "tags": ["wholesale"]}

The trade-off is that a JSON Lines file is not itself valid JSON — a standard parser will stop at the end of the first line. To open one in a JSON tool, wrap the lines in [ ] with commas between them, or convert it to a CSV if the records are flat enough. Going the other way, a JSON array of objects becomes JSON Lines by writing each element minified on its own line.

JSON to CSV: start from an array of objects (or a single object that contains one). Flatten nested values into path-named columns, decide how arrays should be handled, and choose the delimiter for the reader. Check the row and column count the converter reports against what you expect — a mismatch usually means the array you wanted was one level deeper than the one that was converted.

CSV to JSON: make sure the first line is a header, pick or auto-detect the delimiter, and choose whether types should be inferred. Then, if the headers are paths such as supplier.name, run Unflatten to restore the nesting. Finally, keep both files and diff them after a round trip whenever the data is important; a structural compare that ignores key order will show in seconds whether the conversion was truly lossless.