Python Convert Nested JSON Records into Flat Rows

Flatten nested JSON objects into table-shaped dictionaries for export.

How It Works

A JSON record can contain dictionaries inside dictionaries, while CSV expects one scalar value per column. Choose meaningful flattened names such as address_city, and decide how to represent lists or missing values. Keep the mapping explicit to avoid silently losing fields.

Flattening is a design choice; list-valued fields may require a second table rather than joining them into ambiguous strings.

Step-by-Step Example

import csv
from io import StringIO

people = [{"id": 7, "name": "Ada", "address": {"city": "Oslo", "country": "NO"}}]
rows = [{"id": item["id"], "name": item["name"],
         "address_city": item["address"]["city"],
         "address_country": item["address"]["country"]} for item in people]
output = StringIO(newline="")
writer = csv.DictWriter(output, fieldnames=list(rows[0]))
writer.writeheader()
writer.writerows(rows)
print(output.getvalue().strip())

Understanding the Example

• The expression '[{"id": 7, "name": "Ada", "address": {"city": "Oslo", "country": "NO"}}]' supplies the value stored as 'people'. This value is then used or checked by the later statements.
• The expression '[{"id": item["id"], "name": item["name"],' supplies the value stored as 'rows'. This value is then used or checked by the later statements.

A successful run of this example prints the following output:

id,name,address_city,address_country
7,Ada,Oslo,NO

Where This Fits in a Real Program

In a real import pipeline, parsing is only the first stage. Convert each field to the intended type, reject missing or unexpected values and report which record failed. Keep the original source file unchanged until the normalized data has passed validation and an export has been checked.

Practice: Try It Yourself

Add a second address and decide how missing countries should be represented.

For this exercise, predict the result before running the changed code. Then explain the difference between the original and modified cases using the actual values, not just whether the program completed.

Further Reading

Read the official Python documentation for complete parameters, return types and exceptions.

Common Beginner Mistakes

• Trying to write a nested dictionary into a CSV cell and expecting separate columns automatically.
• Ignoring optional nested keys, causing a KeyError during export.

Key Takeaways

• Flatten nested JSON objects into table-shaped dictionaries for export.
• A JSON record can contain dictionaries inside dictionaries, while CSV expects one scalar value per column.
• Flattening is a design choice; list-valued fields may require a second table rather than joining them into ambiguous strings.