How do I convert nested JSON to CSV format?

Pick a strategy: the decision table

Every nested JSON-to-CSV conversion is really a series of small decisions — one per nested shape in your data. This table maps each shape to the approach that fits it, and to the tool feature that implements it:

Nested shape	Recommended approach	CSV result	jq / pandas mechanism
Object inside object (address.city)	Dot-notation flatten	One column per leaf path	pandas: automatic; jq: name each path explicitly
Small fixed-size array (coords[2])	Numbered columns (coord_0, coord_1)	One column per index	jq: .arr[0], .arr[1]; pandas: index the list
Variable-length array of primitives	Join into one delimited cell	Single column, a;b;c	jq: (.tags | join(";")); pandas: ";".join(...)
Array of objects (one-to-many)	Denormalize into rows	Repeated parent fields per child	jq: .[] | .orders[] | [...]; pandas: record_path + meta
Array of objects you only summarize	Aggregate (count/sum)	One row, derived columns	jq: length, map(.amount) | add; pandas: groupby
Deeply recursive / irregular	Partial flatten, keep leaves as JSON strings	Mix of columns + JSON cells	jq: tostring; pandas: leave nested, to_json
Null / missing property	Blank cell, union of all keys	Empty cell, fixed row width	Both do this by default

The rule of thumb across the whole table: flatten objects, but make a deliberate choice for every array. Objects have exactly one path to each value, so dot notation is unambiguous and reversible. Arrays are where structure is genuinely lost, so the strategy has to match how you'll use the data downstream.

How flattening actually works

Flattening walks the JSON tree and turns every path-to-a-leaf into a column. Objects extend the path with a dot; arrays either extend it with an index or trigger a row explosion:

Understanding JSON Nesting and CSV Limitations

JSON is a hierarchical, flexible format that excels at representing complex, nested data structures. Objects can contain other objects, arrays can hold mixed data types, and the format naturally supports relationships and hierarchies. CSV, by contrast, is fundamentally a flat, two-dimensional format—rows and columns where each cell contains a single value. Converting nested JSON to CSV requires collapsing this hierarchy into a tabular structure, which introduces unique challenges not present when converting simple, flat JSON.

The fundamental incompatibility between JSON's hierarchical nature and CSV's flat structure means you cannot preserve all the nuance of nested JSON in CSV format without some loss or transformation of the data structure. However, with the right strategies, you can convert nested JSON into CSV that maintains data integrity and usability.

Working with flat or moderately nested data? Try our free CSV to JSON Converter to convert between JSON and CSV instantly.

Flattening Simple Nested Objects

The simplest nested structure involves an object containing properties that themselves are objects. For example:

{
  "id": 1,
  "name": "John Doe",
  "address": {
    "street": "123 Main St",
    "city": "Boston",
    "zip": "02101"
  }
}

To convert this to CSV, you "flatten" the nested object by creating column headers that represent the path to each value. The most common approach uses dot notation: address.street, address.city, address.zip. The resulting CSV would have columns:

id,name,address.street,address.city,address.zip
1,John Doe,123 Main St,Boston,02101

This approach scales to multiple levels of nesting. If you had an even deeper structure with address.location.coordinates.latitude, you'd simply create a column named exactly that. Most CSV-to-JSON converters support this dot notation approach, making it straightforward to work with moderately nested objects.

Handling Arrays in Nested JSON

Arrays within JSON objects present more complex challenges. Consider:

{
  "id": 1,
  "name": "John Doe",
  "phone_numbers": ["555-1234", "555-5678", "555-9999"]
}

You have several options for converting array data to CSV:

Option 1: Multiple columns - Create separate columns for each array element: phone_numbers_1, phone_numbers_2, phone_numbers_3. This works well when arrays have a known, limited size.

Option 2: Semicolon-separated values - Put all array values in a single column, separated by a delimiter (often semicolon or pipe): phone_numbers: "555-1234;555-5678;555-9999". This is compact but requires special handling when re-importing.

Option 3: Multiple rows - Create separate CSV rows for each array element, repeating non-array fields. This "denormalization" creates more rows but maintains pure CSV format without special conventions.

Option 4: String representation - Convert the array to a JSON string within the CSV cell: phone_numbers: "[\"555-1234\",\"555-5678\",\"555-9999\"]". This preserves the exact structure but requires parsing to use the data.

The best choice depends on your data characteristics and intended use. If arrays are small and consistent in size, multiple columns work well. If arrays are variable-length, the semicolon-separated or multi-row approach might be better.

Working with Arrays of Objects

The most complex scenario involves arrays of objects—a common JSON structure:

{
  "id": 1,
  "name": "John Doe",
  "orders": [
    {
      "order_id": "ORD-001",
      "amount": 99.99,
      "date": "2025-01-15"
    },
    {
      "order_id": "ORD-002",
      "amount": 149.99,
      "date": "2025-01-20"
    }
  ]
}

Converting this to CSV requires deciding how to represent the one-to-many relationship:

Denormalization approach - Create multiple CSV rows, repeating the parent object's fields:

id,name,orders.order_id,orders.amount,orders.date
1,John Doe,ORD-001,99.99,2025-01-15
1,John Doe,ORD-002,149.99,2025-01-20

This flattens the data into CSV format but creates data duplication. It's suitable when the array size is small and consistent.

Aggregation approach - If your analysis doesn't require individual array elements, aggregate the data. For example, calculate the total order amount and count:

id,name,total_orders,total_amount
1,John Doe,2,249.98

This loses the detail but produces a cleaner CSV.

Separate tables approach - Create two CSV files: one for customers, one for orders, with a foreign key relationship:

customers.csv:

id,name
1,John Doe

orders.csv:

order_id,customer_id,amount,date
ORD-001,1,99.99,2025-01-15
ORD-002,1,149.99,2025-01-20

This maintains data integrity and structure but requires multiple files and import discipline.

Recursive Nesting and Deeply Nested Structures

Some JSON structures contain recursively nested data—objects containing arrays of objects containing arrays. Converting these requires deciding how deep to flatten:

{
  "id": 1,
  "name": "Department",
  "teams": [
    {
      "team_id": "T1",
      "members": [
        {"name": "Alice", "role": "Lead"},
        {"name": "Bob", "role": "Developer"}
      ]
    }
  ]
}

You might flatten all the way to the deepest level:

• teams.0.team_id, teams.0.members.0.name, teams.0.members.0.role, etc.

Or you might flatten only partially, keeping deep arrays as JSON strings. The decision depends on your analysis needs and usability requirements. Flattening too much creates a table with overwhelming columns and complexity, while flattening too little maintains JSON within CSV cells.

Handling Null Values and Missing Data

Nested JSON often contains null values and missing properties that need careful handling during conversion:

{
  "id": 1,
  "name": "John",
  "address": null,
  "company": {
    "name": "Acme Corp"
  }
}

When converting, you must decide:

• Should address: null become an empty CSV cell or the literal text "null"?
• For company.phone (missing property), should the cell be empty?
• Should you include columns for all properties from all objects, even if some objects lack certain properties?

Professional converters typically include all possible columns and use empty cells for null or missing values, which is the standard approach. Some allow you to specify how to represent null values—as blank, as "null", as "N/A", or other conventions.

Implementation Strategies

Several approaches work for converting nested JSON to CSV. The two you'll reach for most are jq (command line) and pandas (Python).

jq: flatten on the command line

jq has a built-in @csv filter that produces RFC 4180-compliant output (fields quoted, internal quotes escaped). Use the -r (raw) flag so the rows aren't wrapped in JSON string quotes. jq does not auto-flatten nested objects — you name each column path explicitly, and turn inner arrays into strings with join():

# Array of objects with nested fields -> one row each
jq -r '.[] | [.id, .user.name, .user.email] | @csv' input.json

# Add a header row generated from the first object's keys
jq -r '(.[0] | keys_unsorted) as $keys
       | $keys, (.[] | [.[$keys[]]])
       | @csv' input.json

# Collapse an inner array into a single delimited cell
jq -r '.[] | [.id, (.phone_numbers | join(";"))] | @csv' input.json

# Denormalize an array of objects into repeated parent rows
jq -r '.[] | . as $p | $p.orders[]
       | [$p.id, $p.name, .order_id, .amount] | @csv' input.json

keys_unsorted preserves key order as it appears in the JSON (plain keys sorts alphabetically).

Python with pandas: json_normalize

Python's pandas.json_normalize() flattens nested dicts into dot-notation columns automatically, which makes it the fastest path for irregular or large files:

import pandas as pd
import json

with open('data.json') as f:
    data = json.load(f)

# Nested objects -> dot-notation columns automatically
df = pd.json_normalize(data)
df.to_csv('output.csv', index=False)

When records contain an array of objects, pass record_path to explode that array into rows and meta to carry parent fields down onto each row:

# One row per order, with the parent id/name repeated on each
df = pd.json_normalize(
    data,
    record_path='orders',   # the array to explode into rows
    meta=['id', 'name'],    # parent fields to keep on every row
)
df.to_csv('output.csv', index=False)

record_path also accepts a list of keys to reach a deeper array (e.g. ['teams', 'members']), and meta accepts nested paths like ['id', ['address', 'city']]. Missing keys become blank cells and every row is padded to the full column set, which is the standard null-handling behavior.

Online converters - Many free online tools handle nested JSON conversion with configurable options. These are convenient for one-off conversions but may have file size limits.

ETL tools - Enterprise tools like Apache Nifi, Talend, or custom Apache Spark jobs provide robust handling of complex transformations with monitoring and error handling.

Programming libraries - Use established JSON-to-CSV libraries specific to your language ecosystem. These handle edge cases and provide configurable flattening strategies.

Preserving Data Integrity During Conversion

When converting nested JSON to CSV, prioritize data integrity:

Test with sample data - Always test conversion with a small representative sample before processing large datasets. Verify that converted CSV contains all expected fields and values.

Validate row counts - If using denormalization (multiple rows per parent object), ensure the row count matches expectations. Count array elements to verify they're all represented.

Compare values - Spot-check specific values in the converted CSV against the source JSON to ensure nothing was corrupted or truncated.

Document the conversion - Record what flattening strategy you used, how arrays were handled, and what fields map to what columns. This documentation is essential if you need to re-convert or restore data.

Keep source files - Always retain the original nested JSON alongside the converted CSV. If issues emerge later, having the source allows re-conversion with different parameters.

Handling Special Cases

Certain JSON structures require special consideration:

Duplicate keys - Some systems generate JSON with duplicate keys (technically invalid but encountered in practice). Conversion behavior is unpredictable; validate the JSON is well-formed first.

Mixed-type arrays - Arrays containing different data types (strings and numbers, objects and primitives) complicate conversion. Clarify what to do with mixed types or consider cleaning the source JSON first.

Very large arrays - JSON with arrays containing thousands of elements can create impractically large CSV files (if denormalized) or very complex structures (if flattened with array indices). Consider aggregation or filtering before conversion.

Unicode and special characters - Nested JSON might contain Unicode characters, emojis, or special characters. Ensure your conversion tool preserves proper encoding.

Best Practices for Nested JSON Conversion

Choose the right strategy for your data shape - Understand your JSON structure before converting. Different shapes (flat objects vs. arrays of objects vs. deeply nested) require different approaches.

Document your choices - Record whether you denormalized, aggregated, or used separate tables. This documentation guides future work and helps others understand your data decisions.

Validate the output - Never assume conversion succeeded. Spot-check values, verify row counts, and validate structure matches expectations.

Consider the conversion flow - Think about whether you'll need to convert back to JSON later. Some flattening approaches are reversible; others are lossy.

Use appropriate tools - Don't reinvent the wheel with custom string manipulation. Use proper libraries or converters designed for nested JSON, which handle edge cases and RFC standards.

Test with your specific data - Generic advice helps, but your data might have unique characteristics. Test thoroughly with actual data before relying on converted results.

Conclusion

Converting nested JSON to CSV requires understanding your data structure and choosing an appropriate flattening strategy. Whether you flatten completely with dot notation, denormalize arrays into multiple rows, aggregate data, or use separate tables depends on your specific data shape and analytical needs. By understanding your options, testing thoroughly, and documenting your approach, you can reliably convert complex nested JSON into usable CSV format that maintains data integrity and serves your downstream processes effectively.