JSON as a table
An array of objects becomes rows and columns; an array of arrays becomes rows; a {"data": […]}
envelope is opened at its data. Nested objects become dotted columns — address.city — and lists are
joined into one cell. Numbers keep the digits they were written with.
The reader is forgiving about what people do to JSON by hand — // and /* */ comments,
trailing commas, keys without quotes, single-quoted strings — and what it writes back is always strict JSON.
JSON Lines, YAML, TOML, XML and more
- JSON Lines (
.jsonl,.ndjson): one object per line, one row per line. - YAML: a sequence of mappings reads as a table, and the app writes YAML back.
- TOML: an array of tables —
[[row]]over and over — is a table, every value kept as it was written:1.50stays1.50,0xDEADstays0xDEAD. - GeoJSON: a feature to a row, its properties the columns, its geometry as WKT —
POINT (8.54 47.37)— with a longitude and a latitude beside it. A table with places goes back out as GeoJSON. - XML: the repeated element becomes the row, child elements and attributes become columns.
- Property lists: XML or binary, as macOS and iOS write them.
- BSON and MessagePack: the binary cousins of JSON, a document to a row, whole numbers to the digit.
JSON inside a cell
A cell often holds JSON of its own. ⌥⌘J opens it as a tree — objects and lists that open, keys you can rename,
values whose type you can change — with a text view a click away. Key order is kept, 1.50 stays
1.50, and a twenty-digit identifier does not become a float. Format and Minify work over a
whole column at once. In SQL, JSON_EXTRACT, -> and ->> reach inside.
Writing JSON back
Saving a JSON file keeps the document around the rows: the rows usually sit inside something like
{"data": [ … ]} beside other keys, and a hand-edited file has comments saying what each block is for.
The wrapper, the other keys and the comments are all written back, with only the rows replaced.
Export as an array of objects or of arrays, as JSON Lines, or as nested JSON, which puts the nesting back
from dotted column names — address.city becomes {"address": {"city": …}}. YAML, TOML,
GeoJSON, XML and property lists are in the same export sheet.
