Working with Nested JSON: Flattening, Querying, and Transforming Data
JSON nests objects inside objects, arrays inside objects, and objects inside arrays, because programs model data that way. Spreadsheets, CSV files, and SQL tables don't nest at all; they have two dimensions, rows and columns. Flattening bridges the two, and most of the work with nested JSON comes down to knowing when to flatten and when to leave the structure alone.
Why deeply nested JSON resists spreadsheets and SQL
A JSON object with three levels of nesting and a buried array has no single obvious row/column mapping. Which field becomes a column, and what happens to a field that repeats per array item? Paste nested JSON into a naive 'JSON to CSV' converter or Excel's JSON import, and it either drops the nested fields or dumps them into one cell as a stringified blob. Both outcomes throw away the structure you wanted to filter, sort, or query on.
The tools aren't broken; the representations don't match. Nested JSON encodes a tree, and a spreadsheet or SQL table encodes a flat list of records. To get from one to the other, you have to decide how to handle that mismatch, and a format conversion alone won't decide it for you.
What flattening means
Flattening turns a nested structure into a single-level object where each nested path becomes one key, 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`), so a list becomes a set of individually addressable keys. The result has no nesting: each value is a plain string, number, or boolean, and each key identifies one leaf value in the original document.
A spreadsheet column, a SQL row, or a simple key/value store needs that property: one key per leaf value, no nesting.
A worked example
Take a 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 an unambiguous path to a single original value. You can use them as six spreadsheet columns or six columns in one SQL row.
When to flatten vs. when to keep nesting
Flatten when the destination is tabular (a CSV export, a spreadsheet, a single SQL table row, an analytics tool that understands only flat columns) and when arrays are short lists of scalars (like `roles` above) where an index-numbered key per item is a fair trade-off.
Keep the nesting, or restructure, when an array holds full objects whose count varies per record. Picture an `orders` array where each user has a different number of orders, each with several fields. Flattening that in place either gives each record a different, ragged set of columns (`orders.0.total`, `orders.1.total`, ... `orders.14.total` for your biggest customer) or collapses the array into one stringified-JSON cell, which defeats the purpose. For arrays of objects, use classic relational normalization: pull the array into its own table/sheet with a foreign key back to the parent record. JsonForge's JSON to Table tool does this for you. It flattens scalar fields into table columns and breaks arrays of objects out into their own related tables instead of mangling them into index-numbered columns.
Path-based access: querying without flattening everything
Sometimes you need one value out of a deeply nested structure, not a flattened document. Dot-path notation (`user.address.city`) or a JSONPath-style expression (`$.user.address.city`, `$.roles[0]`) addresses a single nested value directly. Flattening applies the same idea to the whole document; a path applies it to one lookup. `lodash.get(obj, 'user.address.city')`, most templating engines' variable interpolation, and JSON query tools all work this way.
The two techniques pair up. JsonForge's JSON Flatten tool applies the dot-path transform across a whole document at once and turns each nested value into a flat key. Use it when the destination is a spreadsheet, a simple key/value store, or any tool that can't walk nested structures.
FAQ
- How does flattening differ from stringifying nested fields?
- Flattening turns each nested value into its own top-level scalar key, so you can still query, sort, and filter on each one. 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. Stringifying postpones the problem that flattening solves.
- How do arrays get represented when flattening JSON?
- The standard convention appends the array index to the path, so `roles: ["admin", "editor"]` becomes `roles.0: "admin"` and `roles.1: "editor"`. That works for short, fairly fixed lists of scalars. For arrays whose length varies a lot between records, it produces a ragged, growing set of keys, and a separate related table fits better.
- Can a flattened JSON object be turned back into its original nested shape?
- Yes. 'Unflattening' splits each dot-path key into its parts and rebuilds the nested objects and arrays, as long as you applied the separator convention consistently and no original key contained 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 tools also use bracket notation (`user[address][city]`) and underscore-joined keys (`user_address_city`), especially where dots in column names cause trouble (some SQL dialects, certain spreadsheet tools). Pick any convention and apply it 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.