CSV vs JSON: How to Pick the Right Data Format

Every data pipeline eventually hits the same fork: ship the rows as CSV or as JSON. The choice is not cosmetic. CSV is an untyped flat grid with no formal standard; JSON is a typed, nested format with an Internet Standard behind it. Picking wrong means either losing type information or writing a flattener you did not budget for. This post covers CSV vs JSON on the axes that actually matter.

#TL;DR

• CSV is a flat grid with zero types; JSON has four primitive types plus null.
• JSON has an Internet Standard (RFC 8259). CSV only has an informational RFC.
• CSV wins on raw size and streaming; that lead mostly vanishes after gzip.
• CSV cannot express nesting or distinguish empty string from null.
• For streamed records, JSON Lines gives you JSON types with CSV-like streaming.

#CSV vs JSON: what each format actually guarantees

Most format arguments go badly because both sides are arguing about tooling, not about the format's guarantees. Start with what the specs commit to.

#What CSV guarantees (and what it does not)

RFC 4180 is titled Common Format and MIME Type for Comma-Separated Values (CSV) Files, and its status line is honest about its scope: it is Informational, not Standards Track, and it describes "the format that seems to be followed by most implementations." It gives you records separated by CRLF, fields separated by commas, optional double-quoting, and doubled quotes as the escape for a literal quote. That is the whole contract.

Note what is absent. There is no type system — the ABNF grammar treats every field as TEXTDATA. There is no null. There is no schema, no nesting, no required character encoding (the registration lists charset as an optional parameter), and no guarantee that the delimiter is a comma once locale settings get involved.

id,name,city,note
1,Ada,London,"Said ""yes"""
2,Grace,,

Row 2 raises the question CSV cannot answer: is city an empty string, or is it missing?

#What JSON guarantees

RFC 8259 is an Internet Standard, and it is far more prescriptive. JSON has four primitive types — string, number, boolean, null — and two structured types, object and array. Strings are Unicode. Section 8.1 requires UTF-8 for text exchanged between systems that are not part of a closed ecosystem, and forbids emitting a byte order mark.

[
  { "id": 1, "name": "Ada",   "city": "London", "note": "Said \"yes\"" },
  { "id": 2, "name": "Grace", "city": null,     "note": "" }
]

Same data, no ambiguity: city is explicitly null, note is explicitly an empty string.

#The one-line difference

The Frictionless Table Schema spec has a useful framing here: it distinguishes the physical representation (bytes on disk) from the logical representation (typed values). CSV only has a physical representation, so the logical one has to be reconstructed by whoever reads it. JSON ships both at once.

#Should I use CSV or JSON for my API?

This is the most common form of the question, and it has a boring answer with one interesting exception.

#Why APIs almost always return JSON

API payloads are rarely rectangular. A single order has a customer object, a shipping address, and an array of line items. Encoding that as CSV means inventing a flattening convention, and your clients have to know it to reverse it. JSON's media type is application/json, registered in RFC 8259, and every HTTP client on every platform can parse it without a configuration decision.

The type argument matters more than the shape argument. In JSON, "quantity": 3 and "sku": "0031" are unambiguously a number and a string. In CSV both are five characters of text, and the first tool to read them will guess — usually turning 0031 into 31. That failure mode is why CSV type inference is its own topic.

#When a CSV endpoint is still the right call

Bulk export. If the caller's next move is opening the file in Excel, loading it into a warehouse, or feeding it to COPY, CSV is what they want and JSON is an extra conversion step. Reporting endpoints, billing exports and "download all my data" buttons are all legitimately CSV.

The tell is the consumer: a program that will traverse the structure wants JSON, a person or a loader that wants a table wants CSV.

#Serving both without duplicating logic

Serialise from one internal representation and switch on Accept:

const rows = await report.run();       // array of flat objects
if (accepts("text/csv")) {
  return csv(rows);                    // header + rows
}
return json(rows);                     // application/json

Keep the CSV path restricted to already-flat data. The moment you find yourself flattening inside the serialiser, you have two different reports, not one report in two formats.

#Is CSV faster than JSON for large datasets?

Partly, and for two distinct reasons that people tend to merge into one.

#Where the size difference comes from

CSV writes each field name once, in the header. JSON repeats every key on every record. For a wide table of short values — twenty columns of integers and codes — the keys can be the majority of the bytes, and CSV lands two to three times smaller.

Then gzip runs. Repeated keys are the single most compressible pattern there is, and after compression the gap narrows sharply. If your data crosses a network with Content-Encoding: gzip, raw size is a weak argument for CSV.

#Streaming line-by-line vs parsing a whole document

This is the difference that survives compression. A CSV file is line-oriented: wc -l tells you the row count, head gives you the first records, and a reader can process row n without having seen row n+1. A single JSON array is one value — a naive JSON.parse has to hold the entire document in memory before you can touch the first element.

You can stream JSON, but it requires an incremental parser rather than the built-in one. That is real work you should not discover at 4 GB.

#JSON Lines: types and streaming at the same time

JSON Lines resolves the trade-off. One JSON value per line, \n as the terminator, UTF-8, no BOM, .jsonl by convention:

{"id":1,"city":"London","tags":["eu"]}
{"id":2,"city":null,"tags":[]}

Every line parses independently, so you keep JSON's type system and nesting while getting CSV's line-oriented streaming. Unix tools work on it — head, tail, split, grep — and files concatenate cleanly. For logs, events and any append-only record stream in 2026, JSONL is usually the correct answer to "CSV or JSON?"

One caveat worth knowing: the application/jsonl media type is still not formally registered, so servers commonly ship it as application/x-ndjson or plain text.

#How to convert CSV to JSON without losing data

Conversion is where the guarantee mismatch shows up. Going CSV → JSON, you must add information the file never had. Going JSON → CSV, you must drop information CSV cannot hold.

#Types: the receiving side has to decide

Because CSV has no types, converting to JSON means every column gets a type assigned by the converter. Three columns cause almost all real-world damage:

• Identifiers with leading zeros — ZIP codes, SKUs, account numbers — become integers and lose the zeros.
• Integers beyond 2^53 − 1 lose precision, since RFC 8259 notes interoperability is only guaranteed inside the IEEE 754 double-precision safe range.
• Values like 1e5, TRUE, NaN and Inf get coerced when they were meant as text.

The rule that survives contact with production: if summing the column would be meaningless, keep it a string.

#Nulls versus empty strings

PostgreSQL's COPY documentation states the problem plainly — the CSV format has no standard way to distinguish a NULL from an empty string — and then shows the workaround: COPY writes a NULL as an unquoted empty field, and an actual empty string as "".

COPY orders TO '/tmp/orders.csv'
  WITH (FORMAT csv, HEADER, NULL '');

That is a PostgreSQL convention, not a CSV rule. Any other tool reading the file is free to interpret both as "". If null-versus-empty is semantically important in your data, JSON or JSONL removes the guesswork; if you must stay in CSV, agree on an explicit sentinel and document it.

#Nesting and the flattening problem

JSON → CSV needs a flattening strategy, and there is no standard one. The common convention is dotted paths for objects and indexed paths for arrays:

{ "id": 1, "address": { "city": "London" }, "tags": ["eu", "gb"] }

becomes columns id, address.city, tags.0, tags.1. Objects flatten predictably. Arrays do not — variable-length arrays either produce ragged columns or get serialised into one cell, and both choices are lossy in a way the receiving tool cannot detect. This is the core of flattening nested JSON for CSV output.

The practical check: run the file through a CSV ↔ JSON converter, inspect the result in a JSON decoder, then convert back and diff against the original. If the round-trip is clean, your conventions hold. Both steps run in the browser, so customer data never leaves the machine.

#Choosing between CSV and JSON in practice

Strip away the format politics and the decision comes down to who or what reads the file next.

#Pipelines, databases and warehouses

Bulk loaders are built for CSV. COPY, LOAD DATA INFILE, COPY INTO and every managed-warehouse equivalent take delimited text, parse it in the server process, and skip the per-record allocation that JSON ingest costs. If the destination is a table with a fixed schema, CSV is the fast path — the schema lives in the database, so you are not relying on the file to carry types. A SQL converter is useful in the other direction, when a dump needs to become a spreadsheet.

#Spreadsheets and non-technical recipients

If a human opens the file, use CSV — but be deliberate about encoding and delimiter. Excel needs a UTF-8 BOM to render non-ASCII correctly, which is why CSVs look garbled, and locales where the comma is the decimal separator expect semicolon-delimited files. Those two settings cause more support tickets than the format choice itself.

#Config, logs and event streams

Config files want structure and comments, so JSON — or better, YAML if humans edit it, which is what a JSON ↔ YAML converter exists for. Logs and events want per-record independence and typed fields, so JSONL. Neither belongs in CSV, and neither belongs in a single giant JSON array.

A short decision list:

• Fixed schema, table destination, loader on the other end → CSV
• Nested shape, API consumer, types matter → JSON
• Append-only stream, one record at a time, types matter → JSONL
• A human will open it in Excel → CSV, with BOM and the right delimiter
• You cannot answer "who reads this next?" → answer that first

#References

• RFC 4180 — Common Format and MIME Type for Comma-Separated Values (CSV) Files — CSV's Informational status, the ABNF treating fields as TEXTDATA, and the optional charset/header parameters.
• RFC 8259 — The JavaScript Object Notation (JSON) Data Interchange Format — JSON's type set, the UTF-8 requirement in Section 8.1, and the IEEE 754 interoperability note on numbers.
• JSON Lines — the three JSONL requirements, the .jsonl convention, and the unregistered application/jsonl media type.
• PostgreSQL: Documentation: 18: COPY — the NULL-versus-empty-string handling in CSV mode and the FORMAT/NULL/HEADER options used in the SQL example.
• Table Schema — the physical-versus-logical representation framing and the missingValues approach to nulls in text formats.

#Related on iKit

• Start with the end-to-end mechanics of moving CSV into JSON — the conversion guide this comparison sits on top of, including headers, quoting and output shape.
• Going the other way means flattening nested objects first — dotted-path keys, arrays and the lossy cases behind the flattening section above.
• Every type you see after parsing a CSV was invented by the parser — the detailed version of the leading-zeros and 2^53 problems.
• The parsing edge cases most CSV tools get wrong — embedded newlines, doubled quotes and trailing commas, straight from RFC 4180.
• Garbled characters in Excel are an encoding problem, not a format problem — the UTF-8 BOM fix referenced in the spreadsheet section.
• Semicolon delimiters change what the parser even sees as a field — locale delimiter rules that break otherwise valid CSV.
• Reading XLSX directly skips a whole round of type guessing — when the source is a spreadsheet rather than a text file.
• Turning a SQL dump into a spreadsheet — the database-to-table direction of the same pipeline question.