Flatten Nested JSON to CSV for Data Analysis

Why Flatten JSON to CSV

JSON excels at representing hierarchical data: a customer object containing an address object containing a city field. CSV excels at representing tabular data: rows and columns in a flat grid. Most data analysis tools — Excel, Google Sheets, R, pandas, SQL databases — work with tabular data. When your source data is JSON (API responses, NoSQL database exports, log files), flattening it into CSV is the bridge between where the data lives and where you can analyze it.

Simple JSON arrays of flat objects convert to CSV trivially: each object becomes a row, each key becomes a column header. The challenge begins with nesting. An order object with a nested customer object with a nested address: {"order_id": 1, "customer": {"name": "Alice", "address": {"city": "Portland"}}}. This three-level hierarchy must collapse into a single row for CSV representation. The customer's city becomes a column named something like customer.address.city — a dot-notation path that preserves the original structure in the column name.

The flattening process involves trade-offs. Deep nesting produces long column names that are awkward in spreadsheets. Arrays within objects (an order with multiple line items) create a one-to-many relationship that does not map cleanly to a single CSV row. Missing fields in some objects but not others create sparse columns. Understanding these trade-offs helps you choose the right flattening strategy for your specific analysis needs.

Flattening Strategies

Dot-notation flattening converts nested keys into dot-separated column names. {"user": {"name": "Alice", "age": 30}} becomes two columns: user.name and user.age. This is the most common approach and works well for moderately nested data (2-4 levels deep). Beyond 4 levels, the column names become unwieldy: order.items.0.product.category.name is technically correct but hard to work with in a pivot table.

Underscore flattening uses underscores instead of dots: user_name and user_age. This is more compatible with SQL column naming conventions (dots are not valid in most SQL column names without quoting) and looks cleaner in spreadsheet headers. The trade-off is that underscores in original key names become ambiguous — user_name could mean "the name field inside the user object" or "a top-level field literally called user_name."

Prefix stripping removes common prefixes when flattening. If every field in the JSON is nested under a single root object, the prefix adds no information. data.user.name can become user.name by stripping the data. prefix. This keeps column names shorter without losing meaningful context. The technique is particularly useful for API responses that wrap the actual data in a container object ({"data": {...}, "meta": {...}}).

Selective flattening flattens only the fields you need for analysis, ignoring the rest. If a JSON object has 50 fields across 5 levels of nesting but you only need 8 fields for your analysis, flatten those 8 specifically. This produces a clean, manageable CSV with only relevant columns. It requires knowing the JSON structure in advance, which makes it unsuitable for exploratory analysis but ideal for recurring data pipelines.

Handling Arrays in JSON

Arrays are the hardest part of JSON-to-CSV conversion because they represent one-to-many relationships that tables handle differently from tree structures. An order with three line items in JSON is one object. In CSV, it must become either one row (with array data squeezed into it somehow) or three rows (one per line item, with the order data repeated).

Row expansion creates one row per array element, repeating the parent object's fields. An order with 3 items produces 3 CSV rows, each containing the order ID, date, and customer info alongside one item's details. This approach preserves all data and works well for analysis that focuses on the array elements (e.g., "what are the most ordered products?"). The downside is data duplication — the order-level data appears three times, inflating the file and potentially confusing aggregate calculations (summing order totals would triple-count each order).

Column expansion creates numbered columns for array elements: items.0.name, items.1.name, items.2.name. This keeps one row per parent object but creates variable column counts (an order with 1 item has many empty columns; an order with 20 items needs 20 sets of item columns). This approach is manageable for small, fixed-length arrays but breaks down for variable-length arrays or arrays with more than a few elements.

Serialization converts arrays into a string representation within a single cell: "[item1, item2, item3]" or "item1|item2|item3". This preserves the one-row-per-object structure and handles any array length, but the array data is no longer individually accessible in the CSV — it is a text blob that requires further parsing. This approach works when the array data is secondary to the analysis and you just need it preserved for reference.

Dealing with Missing and Inconsistent Fields

Real-world JSON data is rarely uniform. One object has a phone field; another does not. One customer has an address.apartment field; most do not. A robust flattening process must handle these inconsistencies by filling missing fields with empty values (empty string, null, or a chosen default) in the CSV output.

Schema detection — scanning all objects to build a complete list of all fields that appear in any object — is a necessary first step. A naive approach that only looks at the first object misses fields that appear only in later objects, producing a CSV with incomplete columns. Scanning the entire dataset before generating the CSV ensures every field gets a column, even if most rows have empty values for that column.

Type inconsistencies add another layer. The same field might be a string in one object, a number in another, and null in a third. {"age": 30}, {"age": "thirty"}, and {"age": null} all represent age differently. CSV has no type system — everything is text — so this is less problematic than it would be for database import, but it can still cause issues in analysis tools that infer types from column content. A single text value in an otherwise numeric column forces the entire column to be treated as text in many spreadsheet applications.

Large JSON files with inconsistent structures benefit from a two-pass approach: first pass identifies all fields and their types, second pass writes the CSV with the complete schema. Single-pass approaches must either buffer the entire dataset in memory (impractical for very large files) or accept that early rows might have fewer columns than later rows (requiring post-processing to fix).

Flatten Your JSON

Our JSON to CSV Converter handles nested JSON structures by flattening them into tabular CSV format. It detects nested objects and converts them to dot-notation columns automatically. Array handling is configurable. Missing fields receive empty values. Paste your JSON or upload a file, preview the flattened table, adjust column selections if needed, and download the CSV. All processing happens in the browser — your data is not uploaded to any server.