Flatten Nested JSON for CSV and SQL
Nested JSON is fine until something wants it flat. A spreadsheet wants columns. A CREATE TABLE wants columns. A BI tool wants columns. What you have is an object three levels deep with an array in the middle, and no obvious way to squeeze it into a row. Flattening nested JSON is the step that gets you there, and the interesting part is not the recursion. It is what you decide to do with the arrays.
What Flattening Actually Does
Flattening walks an object and replaces the nesting with key paths. Every scalar value ends up at the top level under a name that records where it came from.
Flattened, that becomes a single row:
The separator is a choice, and it matters more than it looks. Pick it based on where the data is going, not on taste:
| Separator | Example key | Use it for |
|---|---|---|
| Dot | customer.address.city | CSV, spreadsheets, anything a human reads |
| Underscore | customer_address_city | SQL columns, BigQuery, Postgres identifiers |
| Bracket | customer[address][city] | Form encoding, query strings |
customer.address.city needs quoting in every query you ever write against it, so flatten with underscores when the destination is a database.
A Recursive Flatten in JavaScript
The core is about ten lines. Recurse into plain objects, assign everything else:
Two details in that condition are doing real work. The value !== null check comes first because typeof null is 'object' in JavaScript, and without it a null value sends you recursing into nothing. The !Array.isArray(value) check stops arrays from being treated as objects with numeric keys.
Run it on an order with a line-items array and you get this:
Everything flattened except items, which came through untouched. That is the honest result: the function had no way to know what you want, so it left the decision to you.
Arrays Are the Hard Part
There are three ways to handle an array, and they produce different shapes of output. Choose by what the destination expects.
| Strategy | Result for items | Good for |
|---|---|---|
| Index into the key | items.0.sku, items.1.sku | Fixed-length arrays like coordinates |
| Serialize to a string | items as "A1,B7" or raw JSON | Keeping one row per record |
| Explode into rows | Two rows, order fields repeated | Analytics, joins, most CSV exports |
!Array.isArray guard and mapping array indices to keys. It works, but on variable-length arrays it wrecks your schema: one order with 2 items and another with 40 means your CSV needs 40 sets of columns and most rows are empty.
Exploding is usually right. One row per line item, with the parent fields repeated on each row, is the shape every spreadsheet pivot and SQL join expects. It denormalizes the data, which feels wrong if you are used to writing schemas, but a flat export is not a schema.
Serializing to a string is the pragmatic middle. If nobody is going to query inside the array, "A1,B7" in one cell is fine and keeps your row count honest.
Python: json_normalize Does Most of It
Pandas ships a flattener that handles both the nesting and the explode, which saves you writing the recursion. Default behavior flattens dicts and leaves lists alone:
Same outcome as the JavaScript version: the dicts are flat, the list is still a list sitting in a cell. To explode it, name the array in record_path and list the parent fields you want carried down in meta:
Two rows, parent fields repeated, exactly the export shape. That call left sep at its default, which is why the meta column came out as customer.name. Pass sep="_" and it renames both the record columns and the meta columns, giving you customer_name and SQL-safe identifiers throughout.
The jq One-Liner
For a file on disk, jq flattens without any Python in the loop:
paths(scalars) yields the path to every leaf, getpath fetches the value, and from_entries rebuilds it as one object. The map(tostring) is there because array indices arrive as numbers and join needs strings.
If you find a leaf_paths version of this in an old Stack Overflow answer, it will fail on jq 1.7 with leaf_paths/0 is not defined. It was removed. Use paths(scalars).
Where This Goes Wrong
Flattening is lossy in ways that only show up downstream.
Missing keys and null values collapse into the same empty cell. If half your records lack customer.address.zip, that column is blank for them, and after a CSV round trip you cannot tell an absent field from an explicit null. If that distinction carries meaning in your data, flatten to a format that keeps it and skip CSV.
Ragged records blow up the column count. Flattening a heterogeneous array of objects gives you the union of every key path that appeared anywhere, so an endpoint returning five variants of a record produces a table that is mostly empty. Inspect the shape first in the JSON viewer before you commit to a flat export.
And a key that already contains your separator will collide. {"a.b": 1, "a": {"b": 2}} flattens to one key a.b with whichever value came second. Rare in practice, unrecoverable when it happens, and the reason flatten-then-unflatten is not a safe round trip.
Skip the Code for One-Off Exports
If this is a one-time job, converting straight to the destination format handles the flattening for you. The JSON to CSV converter turns an array of objects into columns, and the JSON to SQL converter generates the CREATE TABLE and INSERT statements with column names already flattened. For dropping a table into documentation or a pull request, the JSON to HTML table converter does the same walk and emits markup.
The rule worth keeping: flatten as late as possible. Every layer of your code that handles the nested structure can work with the real shape, and the flatten runs once at the boundary where the CSV or the INSERT is produced. Flatten early and you spend the rest of the pipeline parsing customer.address.city back apart.