Nested JSON to a flat table
JSON is a tree and a table is a rectangle. Translating one into the other is always a compromise, and it is important to understand which compromise you are choosing.
Nested objects: easy
An object inside an object is expanded into columns joined by a dot:
{"id": 1, "owner": {"last": "Smith", "first": "Anna"}} → columns id, owner.last, owner.first.
Nothing is lost and the names stay clear. The only subtlety is depth: at the fifth level of nesting names become unreadable, so it makes sense to stop at a reasonable depth and leave the remainder as it is.
Arrays: this is where the choice begins
An array of primitives — "tags": ["a", "b"] — is sensibly joined into a string a, b. It reads well, can be found by search and does not breed columns.
An array of objects — "items": [{...}, {...}] — is already a second table inside the first. There are three options:
- leave it as a JSON string in the cell — nothing is lost, but you cannot work with it;
- expand into columns
items.0.name,items.1.name— fine when there are exactly two or three elements and always the same; - make the array a table of its own — the right answer when there are many elements.
The third option is the choice of “what counts as a row”. It also solves the same task in XML and is described in the article on flattening XML: the mechanics are identical, only the syntax differs.
Different keys in different objects
Nobody promised that every element of an array has the same set of fields. Half of the records may lack the owner field altogether.
Columns are built as the union of keys: if a field occurs in at least one element, the column appears, and stays empty for the rest. This is more honest than taking the keys of the first element, which would make part of the data vanish silently.
A practical consequence: when you see a column filled in only 3% of rows, do not rush to call it an error. That is most likely how the source is built.
Where the table actually lives
API exports are rarely a bare array. More often they are an object with metadata, and the data is somewhere inside:
{"status": "ok", "payload": {"total": 1500, "items": [ ... ]}}
The array has to be searched for across the whole document, not only at the root, and with several candidates the largest array of objects should be proposed — usually that is the data. The decision must still stay with the person: sometimes you need precisely the small lookup list. In Tabulens you pick it in File parsing.
FAQ
Why is there a column with values like {"a":1}?
That is a nested object that was not expanded — either expansion is switched off or the depth limit was exceeded.
Can I turn the table back into JSON?
Yes, export to JSON and JSON Lines is available. Flat dotted columns stay flat, though: the original nesting is not restored.
What if the keys in my JSON are not in English?
Nothing special: they become column names as they are. Problems appear only on export to DBF, where a field name cannot exceed ten characters.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.