JSON vs CSV — When Engineers Pick the Wrong Export

Finance asks for “the export.” Engineering returns a 4MB JSON array of orders with nested line items. Excel refuses to open it helpfully. The reverse happens too: a CSV of users with a roles column that once meant three values and now means a stringified mess. Format choice is a product decision, not a serializer default.

Strengths at a glance

Concern	JSON	CSV
Nested objects / arrays	Natural	Needs flattening rules
Types (bool, null, numbers)	Preserved	Everything is text until parsed
Excel / Sheets	Awkward	Native
Git diffs for config	Verbose but structured	Painful for wide tables
Streaming huge tables	Possible but heavier	Simple line-oriented

Neither format is “more professional.” They optimize for different consumers. Shipping the wrong one creates busywork; shipping an undocumented flatten creates silent data loss.

When JSON is the right export

• Mobile and web clients already speak JSON
• Records contain optional nested blocks (addresses, line items, metadata)
• You need null versus missing versus empty string distinctions
• Machine-to-machine sync where schema evolves with additive fields
• Schema validation (JSON Schema, OpenAPI) already exists for the payload

Keep exports as NDJSON (one object per line) when files are huge and consumers can stream. A single multi-gigabyte array forces parsers to hold more than they should.

When CSV is the right export

• Accountants will pivot in Excel tomorrow
• Columns are stable and shallow
• Downstream is a warehouse load that expects delimited text
• Non-engineers must filter without a JSON viewer

Add a header row. Document the delimiter. For Japanese Windows Excel, UTF-8 with BOM often fixes mojibake; without BOM, Excel may assume Shift_JIS and destroy names. Put that note in the download UI, not only in an internal wiki.

Flattening without lying

{
  "id": 9,
  "tags": ["a", "b"],
  "addr": { "city": "Osaka", "zip": "5300001" }
}

Honest CSV options:

id,tags,addr_city,addr_zip
9,"a|b",Osaka,5300001

or one row per tag (normalized). Dishonest option: JSON.stringify into a single cell and hope nobody edits it. If you must, name the column tags_json so intent is obvious and round-trips have a fighting chance.

Convert carefully with a JSON ↔ CSV tool when exploring shape — then lock the mapping in code for production jobs. Exploratory conversion and production ETL should share the same documented rules.

Excel footguns engineers forget

1. Leading zeros — zip codes and employee IDs become numbers; prefer text import or explicit string columns over ="00123" hacks when you can.
2. Locale delimiters — some regions default to ; instead of ,.
3. CSV injection — cells starting with =, +, -, or @ can become formulas on open; sanitize untrusted exports.
4. Date autodetection — 3/4 becomes a date; prefer ISO YYYY-MM-DD.
5. Row limits and truncated downloads — huge CSVs get opened partially; warn when row counts exceed what Sheets/Excel handle comfortably.

Offer both when audiences differ

Design the API once, serialize twice:

• Accept: application/json for apps
• Accept: text/csv for exports

Same query filters, different serializers, shared tests that assert column order and JSON field names. Version the flatten mapping the same way you version breaking JSON fields.

Decision rule

If a non-engineer must sort and filter without training → CSV with encoding notes. If structure is a tree or consumers are programs → JSON. If you need both, publish one documented flatten mapping and refuse ticket-by-ticket improvisation. The wrong export format creates busywork; the wrong mapping creates numbers finance will trust until quarter close.

Version the export contract

Treat downloadable exports like APIs: version column sets, document null handling, and changelog breaking changes. A silent rename of user_name to full_name breaks finance macros the same way a JSON field rename breaks clients. Prefer additive columns, keep a schema_version cell or header comment where possible, and store the serializer next to tests that freeze a golden CSV/JSON fixture. Conversion tools help exploration; production jobs need pinned mappings.