Six records, four rows: how JSON to CSV quietly loses data

A converter that turns three JSON records into one CSV row has not made a formatting decision. It has deleted two records, and it will not tell you, because nothing in the output of a successful conversion distinguishes "this record had no fields" from "this record was never here".

If you already know what JSON is, you can skip this. If you do not, the JSON guide has the format basics. What is left is the moment a tree becomes a table, which is where data quietly goes missing.

The JSON to CSV converter on this site reports three numbers after every conversion: how many records it read, how many rows it wrote, and how many columns those rows have. The third number is the one to check.

The moment a tree becomes a table

RFC 8259 defines a JSON object as "an unordered collection of zero or more name/value pairs" and an array as "an ordered sequence of zero or more values". Read the "zero or more" in both definitions. An object with no members, written {}, is valid JSON: not malformed input, not an empty string, not a null, but a well-formed object that happens to have nothing in it.

CSV has no matching concept. Per RFC 4180's grammar, a file is an optional header, then one or more records, then nothing: [header CRLF] record *(CRLF record) [CRLF]. Every row that is present is complete. There is no syntax for a row that should be there and is not, and the spec's guidance on the matter is simply that "each line should contain the same number of fields throughout the file". Nothing in that grammar lets a reader count what is missing.

The IANA text/csv record notes that, for lack of a single specification, "there are considerable differences among implementations" — there is no single master specification for CSV. A format with no agreed definition has no agreed place to lose a record.

So every converter performs the same two-step move. First it walks the tree and projects each record into a flat set of key/value pairs. Second it builds rows from those pairs, one row per record. The first step is a translation. The second is where records go to die: a record that flattens to zero cells has no row to attach to.

Fixture one: three records in, one row out

Start with the smallest input that breaks a converter: an array of three records, two of which are empty objects.

[{}, {"a":1}, {}]

There are three records, and jq will confirm it:

$ echo '[{},{"a":1},{}]' | jq 'length'
3

Now apply the projection step every converter does, walking each record for the paths to its scalar values. The jq manual defines paths(f) as a filter that "outputs the paths to any values for which f is true", and scalars is the filter for non-container values:

$ echo '[{},{"a":1},{}]' | jq -c '[.[] | [paths(scalars)]]'
[[],[["a"]],[]]

Record 0 flattens to []. Not to something containing an empty marker, not to a null: to nothing at all. Record 1 flattens to [["a"]]. Record 2 flattens to [], same as record 0.

Here is the filter in isolation, the whole mechanism in one line:

$ echo '[1,[],{},"x",null]' | jq -c '[.[]|scalars]'
[1,"x",null]

The empty array and the empty object are gone. They are not represented as null, not represented as empty strings. Not represented. A record built from no paths has no cells, and a row builder gets nothing to write.

Add the select(length>0) a real row builder uses to skip empty rows, and the loss is total:

$ echo '[{},{"a":1},{}]' | jq -c '[.[] | [paths(scalars)] | select(length>0)] | length'
1

Three records in. One row out. Nothing in that count says a record was dropped.

The parser is not the problem

It would be easy to write this up as a jq bug, and it would be wrong. jq parses {} correctly, and it will hand the record to you if you ask in the right way.

JSON.parse() parses a string according to the JSON grammar and throws a SyntaxError when the input is not valid JSON. It gives you the object or it fails loudly. There is no third outcome where it quietly hands you fewer things than you sent.

Ask jq the right way and the empty object is in the first two characters of the output:

$ echo '[{},{"a":1}]' | jq -c --stream
[[0],{}]
[[1,"a"],1]
[[1,"a"]]
[[1]]

[[0],{}] is the empty object at path [0], emitted as its own leaf value. The manual describes --stream as parsing "in streaming fashion, outputting arrays of path and leaf values (scalars and empty arrays or empty objects)": in that mode, empty containers are leaves. The two trailing lines close the containers that were opened.

So the parser kept the record. The loss happens one step later, in the flatten-then-build-rows projection that builds rows out of scalar paths only. The bug is not in jq, and it is not in JSON. That projection is the step every converter that flattens before it tabulates takes by default: the simplest thing that works on the overwhelming majority of payloads, and the one nobody tests when records are quietly dropping rows.

Fixture two: six records in, four rows out

Here is the same failure in an independent implementation: the flattener a developer writes in ten minutes. Recurse into objects, index into arrays, and if the walk reaches a scalar, write it under its dotted path. No else branch for empty containers.

def flatten(obj, prefix="", out=None):
    if out is None:
        out = {}
    if isinstance(obj, dict):
        for k, v in obj.items():
            flatten(v, f"{prefix}.{k}" if prefix else k, out)
    elif isinstance(obj, list):
        for i, v in enumerate(obj):
            flatten(v, f"{prefix}[{i}]", out)
    else:
        out[prefix] = obj
    return out

Run that against six records, wrapped in an array so there is something countable. That wrapping is not fussiness: Python's own json documentation notes that JSON is not a framed protocol, so serialising several objects back to back into one stream "will result in an invalid JSON file". An explicit array gives you a count to check against.

The flattener is not where this breaks; the row builder is:

[
  {"id":1,"name":"Ada Lovelace","user":{"address":{"city":"London"}},"tags":["founder","math"]},
  {"id":2,"name":"Grace Hopper","user":{"address":{"city":"New York"}},"tags":["navy","cobol"]},
  {},
  {"id":4,"name":"Alan Turing","user":{},"tags":[]},
  {"id":5,"name":"Katherine Johnson","user":{"address":{}},"tags":["nasa","orbit"]},
  {}
]

Columns are the union of every path any record produced. Rows are the records that produced at least one path. That gives this CSV:

id,name,user.address.city,tags[0],tags[1]
1,Ada Lovelace,London,founder,math
2,Grace Hopper,New York,navy,cobol
4,Alan Turing,,,
5,Katherine Johnson,,nasa,orbit

The file is syntactically valid. It opens in anything that opens CSV. It is also missing two records, and the file has no way to say so, because RFC 4180 has no way to say so.

The counters say what happened:

RECORDS IN : 6
COLUMNS    : ['id', 'name', 'user.address.city', 'tags[0]', 'tags[1]']
ROWS OUT   : 4
DROPPED    : 2 records with zero cells
MISMATCH?  : True

Six in, four out. The two missing records are records 3 and 6, both {}.

Three ways a record disappears

Those six records contain three kinds of emptiness, and they do not all fail the same way.

Record	Fate	Why
{} (records 3 and 6)	Row entirely gone	Flattens to zero cells
{"id":4,"user":{},"tags":[]}	Row kept, three cells empty	Nested empties vanish but siblings survive
{"id":5,"user":{"address":{}}}	Row kept, user.address.city empty	The column exists in the union because records 1 and 2 populated it

The first case is obvious once you have seen it. The second nearly as obvious: record 4 has an id and a name, so it flattens to two cells and earns a row, while its empty user and empty tags contribute no paths and produce blank cells. The row survives, partly blank, which looks fine.

The third is the one that costs an afternoon. Look again at row 5: 5,Katherine Johnson,,nasa,orbit. The user.address.city cell is empty, and it reads entirely reasonably. Katherine Johnson plainly had an address field; it was simply blank this time.

She did not. Her address is {}. She has an address field holding an empty object, and the output cannot tell you which it was. Worse, the column exists in the header only because records 1 and 2 populated it. Had every record held an empty address, that column would not exist at all, and record 5 would have been dropped along with the others.

That is the trap: absence of a value is not evidence of absence of a field. A blank cell tells you a record had nothing under that path. It cannot tell you whether the record held an empty container there, was missing the key, or was never asked the question, because once the CSV exists the answer is the same blank in all three cases.

A successful conversion is not a complete conversion

Two beliefs survive a lot of silent data loss, and both of them are load-bearing.

A successful conversion is not a complete conversion. An HTTP 200, a well-formed CSV, a header row matching your schema exactly: all three are compatible with records going missing, because none is a statement about how many records went in. Success means "I produced a valid CSV". Completeness means "I produced a CSV representing every input record". No successful response distinguishes the two. You have to compare two numbers yourself.

A row in the output is not a record from the input. Row 5 above is a record. Row 3 is not, and neither is row 6. Records map one-to-one onto rows only for those that flatten to at least one cell, and that condition is invisible from inside the CSV.

So compare the number of records you handed the tool against the number of rows you got back. If those numbers differ, the difference is your data, and you should find out what the dropped records held before going any further.

Count what went in against what came out

This is why the JSON to CSV converter reports three numbers instead of one. recordCount is how many records the input held, rowCount how many rows the output has, columnCount the size of the union of all paths. When the first two disagree, the tool shows an amber banner with the delta rather than a file that looks fine.

Point it at the six-record fixture and it reports six records, four rows, and names the two-record gap. You still have a data-loss problem, because a CSV row genuinely cannot represent a record with no fields. But you know that before the file reaches a spreadsheet, an import, or a colleague.

That is the whole difference between a converter and a data-loss bug: whether it tells you. Neither program here is wrong about what it produced. One emits fewer rows than records and says nothing. The other emits the same four rows and says it started with six.

The round trip, and what it guarantees

Headers like user.address.city and tags[0] look like a flat naming scheme. They are a contract, and the reason the column union is computed this way.

The path syntax a JSON to CSV converter emits is the exact syntax its inverse consumes. Feed that CSV into the CSV to JSON converter with Dot notation selected, and each header is re-parsed into the path it came from: user.address.city becomes an object three levels deep, tags[0] and tags[1] a two-element array. A JSON document with no empty containers survives JSON to CSV to JSON unchanged.

That word "unchanged" is doing real work. The round trip holds where the input is representable at all: records with at least one scalar leaf, nested objects that are not empty, arrays with at least one element. A record like {} has no path to write and no way back. The contract is about header syntax, not about rescuing records that had nothing to say.

So you can treat user.address.city as a stable column name rather than a display string.

What this post does not cover

Two related questions are untested here, and neither is caught by the row-count check, which counts records lost in projection, not records mangled in serialisation. The first is type fidelity: CSV has no types, so a JSON integer and a JSON string of the same characters land in the same cell. The second is CSV formula injection, where a spreadsheet reads a cell as a formula rather than as text. It is a live concern for any converter writing untrusted input into a file people open, and the specifics depend on each implementation's quoting and escaping.

Related tools and further reading

• JSON to CSV converter — the tool this post is about. Reports record, row and column counts, and flags the mismatch when they disagree.
• CSV to JSON converter — the inverse direction, and the other half of the header-syntax contract.
• JSON guide — the format basics, if the object-versus-array distinction above landed without context.
• Converting HTML back to Markdown: what you lose and why — the same shape of problem in another format, where what vanished is visible in the output rather than absent from a row count.
• CSV splitter and merger — a different job on the same file format, for when the row count is right and the row size is not.
• HTML table generator from CSV — for when the CSV is the input rather than the output.