CSV to JSON: Header Mapping, Nested Payloads, and What Round-Trips Badly

CSV has one structure — a rectangle of strings. JSON has a tree. Every conversion is therefore a decision, not a parse, and the decisions are: where the header goes, what an empty cell means, and what happens to types.

Shape 1: array of objects

The default, and what the converter here produces:

[ { "id": "1", "name": "Sarah Connor", "role": "admin" },
  { "id": "2", "name": "Connor, John", "role": "leader" } ]

One object per record, header names as keys. This is what an API expects and what INSERT-style tooling consumes. Costs: keys repeat on every row (a 1M-row file can be 40% key bytes), and the format has no place for a type, an order-of-columns guarantee, or a description of the columns.

Shape 2: object of column arrays

Columnar — closer to how a dataframe or a Parquet file holds it:

{ "id": ["1","2"], "name": ["Sarah Connor","Connor, John"], "role": ["admin","leader"] }

Smaller (each key appears once), preserves column order explicitly, and maps directly onto Arrow/Pandas constructors. Ragged input becomes obvious: a short column has fewer elements, which is a loud failure instead of a padded one.

Convert between them in one line with jq:

# rows (one object per line) -> columns
jq -s 'reduce .[] as $r ({}; reduce ($r|keys)[] as $k (.; .[$k] += [$r[$k]]))' rows.jsonl

# columns -> rows (any column set, no hard-coded key names)
jq -s '.[0] as $c | ($c|keys) as $k | ($c[$k[0]]|length) as $n
       | [ range($n) as $i | reduce $k[] as $kk ({}; .[$kk] = $c[$kk][$i]) ]' cols.json

Both verified on jq 1.6+; the -s slurp flag is required in each direction, and the second one derives the key list and row count from the file rather than assuming the columns are called id and name.

Empty cell vs missing value

These are three different facts and CSV only has one representation:

The converter fills missing keys with "" rather than null, because inventing null would be inventing a semantic. If the distinction matters, use a sentinel you control (\N, the Postgres default for NULL in COPY CSV) and map it after parsing. See patterns and gotchas for how the SQL path differs — a short row becomes a short VALUES list there, not a padded one.

Types do not survive, and that is the point

JSON has numbers, booleans, and null; CSV has text. A blind conversion produces strings for everything, which is the safe direction:

• true in CSV becomes "true" in JSON — a truthy non-empty string, not a boolean.
• NULL becomes "NULL" — again a string, and .isNull logic that checks == null silently passes.
• {"nested": {"a": 1}} cannot be expressed at all; see below.

Convert deliberately at the far end, per column, with a named target type. pandas.read_csv(dtype={'zip': str}), d3-dsv with an accesor(row), DuckDB read_csv('f.csv', types={'zip':'VARCHAR'}). Every auto-cast option in every library is a bet that no column contains a leading zero, a 19-digit id, or the string NA.

Nested JSON → CSV, and back

Flatten with dotted paths:

# header row for a flattened CSV: one dot-path per scalar leaf
jq -r '[paths(scalars) | map(tostring) | join(".")] | @tsv' payload.json
# -> a.b	c.0	c.1     for {"a":{"b":1},"c":[1,2]}

Un-flattening needs a real tool — jq 'reduce …' with setpath, or json2csv/flat. Two things break:

1. Arrays of objects. items: [{sku:"a"},{sku:"b"}] has no faithful CSV cell. Either one row per item with the parent columns repeated (a denormalised join), or one cell holding escaped JSON — and a downstream reader that does not know to re-parse it treats it as a string.
2. Keys that collide after flattening. {"a": {"b": 1}, "a.b": 2} both flatten to a.b. Rare, and always discovered in production.

Rule: flatten only what is one level deep and stable. Anything deeper is a document; keep it a document and ship NDJSON instead — keeping it a document and shipping one JSON object per line instead.

Round-trip test worth running

Convert CSV → JSON → CSV and diff. Three failure classes show up immediately: column order (object key order follows header order, so an added column lands wherever the code put it), the empty-value collapse above, and whitespace — a parser that trims fields has already rewritten " x " before JSON ever sees it. Details in encoding and the BOM, which covers the case where the first key arrives as \ufeffid and every lookup misses.

Paste a few thousand rows into the converter to get the array-of-objects form with a ragged-row warning attached; anything that runs nightly belongs in a script, and alternatives lists the libraries that do this in CI.