Working with Nested JSON: Flattening, Querying, and Transforming Data
JSON nests naturally — objects inside objects, arrays inside objects, objects inside arrays — because that's how programs model data. Spreadsheets, CSV files, and SQL tables don't nest at all; they're strictly two-dimensional, rows and columns. The gap between those two worlds is where 'flattening' comes in, and knowing when to flatten and when to leave structure alone is most of what makes nested JSON manageable.
Why deeply nested JSON resists spreadsheets and SQL
A JSON object with three levels of nesting and an array buried inside has no single obvious row/column mapping — which field becomes a column, and what happens to a field that repeats per array item? Pasting nested JSON into a naive 'JSON to CSV' converter or Excel's JSON import either drops the nested fields entirely or dumps them into a single cell as a stringified blob, and both outcomes throw away exactly the structure you'd want to filter, sort, or query on.
This isn't a tooling failure so much as a real representational mismatch: nested JSON encodes a tree, and a spreadsheet or SQL table encodes a flat list of records. Getting from one to the other requires an explicit decision about how to handle that mismatch, not just a format conversion.
What flattening actually means
Flattening transforms a nested structure into a single-level object where every nested path becomes one key, typically joined with a dot (`user.address.city`) or written in bracket notation (`user[address][city]`). Array items get an index in the path (`roles.0`, `roles.1`), turning a list into a set of individually-addressable keys. The result has no nesting at all — every value is a plain string, number, or boolean, and every key uniquely identifies exactly one leaf value in the original document.
That property — one key per leaf value, no nesting — is exactly what a spreadsheet column, a SQL row, or a simple key/value store needs.
A worked example
Take a typical nested user record with an address object and an array of role strings.
{
"user": {
"id": 42,
"name": "Ada Lovelace",
"address": {
"city": "London",
"postcode": "W1"
},
"roles": ["admin", "editor"]
}
}{
"user.id": 42,
"user.name": "Ada Lovelace",
"user.address.city": "London",
"user.address.postcode": "W1",
"user.roles.0": "admin",
"user.roles.1": "editor"
}Six keys, zero nesting, and each one is a direct, unambiguous path to a single original value — trivially usable as six spreadsheet columns or six columns in a single SQL row.
When to flatten vs. when to keep nesting
Flatten when the destination is genuinely tabular — a CSV export, a spreadsheet, a single SQL table row, an analytics tool that only understands flat columns — and when arrays are short lists of scalars (like `roles` above) where an index-numbered key per item is a reasonable trade-off.
Keep the nesting, or restructure rather than flatten, when an array holds full objects whose count varies per record — an `orders` array where each user has a different number of orders, each with several fields of its own. Flattening that in place either produces a different, ragged set of columns per record (`orders.0.total`, `orders.1.total`, ... `orders.14.total` for your biggest customer) or gives up and collapses the whole array into one stringified-JSON cell, which defeats the entire point of flattening. The better move for arrays-of-objects is classic relational normalization: pull the array out into its own table/sheet with a foreign key back to the parent record, rather than trying to force it into columns on the parent row. This is exactly what JsonForge's JSON to Table tool does automatically — it flattens scalar fields into table columns and breaks out arrays of objects into their own related tables instead of mangling them into index-numbered columns.
Path-based access: querying without flattening everything
Sometimes you don't need to flatten an entire document — you just need one value out of a deeply nested structure. Dot-path notation (`user.address.city`) or a JSONPath-style expression (`$.user.address.city`, `$.roles[0]`) lets you address a single nested value directly, which is the same underlying idea as flattening applied to one lookup instead of the whole document. It's what powers `lodash.get(obj, 'user.address.city')`, most templating engines' variable interpolation, and JSON query tools generally.
In practice the two techniques pair up: JsonForge's JSON Flatten tool applies this dot-path transform across an entire document at once, turning every nested value into an addressable, flat key — useful whenever the destination is a spreadsheet, a simple key/value store, or any tool that can't natively walk nested structures.
FAQ
- What's the difference between flattening and just stringifying nested fields?
- Flattening turns every nested value into its own top-level scalar key, so each value stays independently queryable, sortable, and filterable. Stringifying a nested field collapses it into a single opaque text blob — you can display it, but you can't filter on a value inside it or sort by it without parsing the string again first. Flattening preserves queryability; stringifying just defers the problem.
- How do arrays get represented when flattening JSON?
- The standard convention is to append the array index to the path, so `roles: ["admin", "editor"]` becomes `roles.0: "admin"` and `roles.1: "editor"`. This works cleanly for short, fixed-ish lists of scalars, but produces a ragged, ever-growing set of keys for arrays whose length varies a lot between records — in that case a separate related table is usually a better fit than flattened index keys.
- Can a flattened JSON object be turned back into its original nested shape?
- Yes — 'unflattening' reverses the process by splitting each dot-path key back into its component parts and rebuilding the nested objects and arrays from them, as long as the separator convention was applied consistently and no original key happened to contain a literal dot.
- Is dot notation the only way to represent a flattened key?
- No. Dot notation (`user.address.city`) is the most common because it mirrors how you'd access the value in JavaScript or Python, but bracket notation (`user[address][city]`) and underscore-joined keys (`user_address_city`) are also used, especially by tools targeting environments where dots in column names are inconvenient (some SQL dialects, certain spreadsheet tools). The convention matters less than applying it consistently across the whole document.
Try these tools
Related articles
What is JSON? A Complete Beginner's Guide →
JSON (JavaScript Object Notation) is the lightweight data-interchange format behind nearly every API, config file, and NoSQL database. Learn the syntax, the six data types, how parsing behaves in code, and the mistakes everyone makes hand-writing it.
The History of JSON: From 2000 to Industry Standard →
Trace JSON from Douglas Crockford's idea in 2001, through Yahoo's adoption, to becoming the ECMA-404 standard that powers 90% of modern APIs.
The Complete JSON Schema Guide (Draft 7) →
JSON Schema is the standard for describing and validating JSON structure. Learn the core keywords, build a real API schema, compose and reuse schemas with $ref, and run validation in code with Ajv.