YAML, JSON and TSV: picking a format for config and interchange

Three formats, all used for "structured data in a text file", all with a failure mode that only shows up once real data hits them.

JSON is a wire format wearing a config format's clothes

JSON is excellent at what it was designed for: unambiguous machine-to-machine interchange. Every parser agrees on what it means, it maps cleanly onto the types most languages have, and there is nothing to argue about.

It is a poor configuration format for exactly the reasons that make it a good wire format. No comments, so you cannot explain why a value is what it is. No trailing commas, so adding a line means editing the line above. No multi-line strings, so a long value becomes an unreadable single line with \n escapes in it.

Those are not oversights. Douglas Crockford removed comments deliberately, because people were using them to carry parsing directives. The result is a format that is exactly specified and unpleasant to maintain by hand.

Where it is unbeatable: API payloads, anything generated and consumed by code, and anything that has to be parsed identically everywhere.

YAML is readable and has sharp edges

YAML fixes the human-editing problems: comments, multi-line strings, no brackets, references to avoid repetition. It is a superset of JSON, so any JSON document is valid YAML.

The cost is that it guesses types, and its guesses are occasionally alarming.

The famous one is the Norway problem. In YAML 1.1, these unquoted values are booleans: yes, no, on, off, y, n, true, false. So a list of country codes containing NO yields false where Norway should be. A spreadsheet column of yes/no answers becomes booleans, which is usually what you wanted and occasionally destroys the value N meaning something else entirely.

Version numbers suffer too. Unquoted 1.20 parses as the number 1.2, and 1.2.3 stays a string because it is not a valid number, so two adjacent lines get different types.

And sexagesimal notation: in YAML 1.1, 22:30 can parse as 1350, because colons were interpreted as base-60 digits. YAML 1.2 removed this, but which version your parser implements is not always obvious.

Two more YAML gotchas. Indentation must be spaces, never tabs, and a tab is an invisible parse error. And YAML is whitespace-significant to a degree that makes a misplaced two-space indent change the structure rather than fail, which is worse.

A YAML formatter that round-trips to JSON is the fastest way to check what your file actually means, because the JSON output shows you the types the parser inferred. If NO comes back as false, you have found it.

TSV is CSV with the hard part removed

CSV's real problem is quoting. A comma inside a value means the field must be quoted, a quote inside a quoted field must be doubled, and every producer implements this slightly differently. Add locales where the list separator is a semicolon and the format stops being one format.

TSV separates on tabs, and tabs essentially never appear inside ordinary data. Names, addresses and descriptions contain commas constantly and tabs almost never. So the quoting problem largely evaporates, parsing is a split on \t, and files are unambiguous.

The trade-off is the mirror image: TSV has no standard way to represent a value that contains a tab. There is no escaping mechanism in common use. If your data contains tabs or newlines inside fields, TSV cannot carry it and CSV with proper quoting can.

Tabs are also invisible, so a TSV file that has been through an editor that converts tabs to spaces is silently destroyed. Never open one in something that reformats whitespace.

A TSV converter is most useful in the moment where something produced tab-separated output and the next tool wants commas, which happens constantly when moving between database clients, spreadsheets and scripts.

Flattening JSON into rows

The other common conversion is going from nested JSON to something tabular, which is what a spreadsheet or a BI tool wants.

The difficulty is that JSON is a tree and a table is a rectangle, so nesting has to be flattened. The usual convention is dotted paths: {"user": {"name": "Asha"}} becomes a column called user.name.

Arrays are where it gets ambiguous, and there is no right answer, only choices:

• Join into one cell. "tags": ["a","b"] becomes a;b. Compact, loses structure.
• One column per index. tags.0, tags.1. Breaks when rows have different lengths.
• One row per element. Duplicates every other field. Correct for analysis, larger.

A JSON to CSV conversion has to pick one, so check which it used before trusting the output. Also watch for rows with different keys: the header must be the union of all keys, and a converter that reads only the first object will silently drop columns that appear later.

Choosing

Whichever format you pick, validate it before it ships: the YAML and JSON formatters catch indentation and syntax errors, and the TSV converter shows exactly how a table will split before you hand it to a spreadsheet.