Convert JSON to CSV

Overview

Converting JSON to CSV bridges structured API responses with spreadsheet-friendly formats. You’ll reach for it whenever an API response has to land in Excel, feed a BI pipeline, or move data to a tool that only understands flat files. The real work isn’t the format swap — it’s flattening JSON’s nested structure into rows and columns without losing data. If you need the opposite direction, the Convert CSV to JSON recipe covers it.

When to Use

Use this resource when:

• Exporting API response data to Excel or Google Sheets for non-technical stakeholders
• Feeding ETL pipelines that load into BI tools or data warehouses
• Generating reports from NoSQL databases that store JSON documents
• Sharing ad-hoc analysis with teams that work in spreadsheets

Solution

Python

import json
import csv

# Flat JSON array: each object becomes a row
json_data = '[{"name":"Alice","age":30},{"name":"Bob","age":25}]'
records = json.loads(json_data)

with open('output.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.DictWriter(f, fieldnames=records[0].keys())
    writer.writeheader()
    writer.writerows(records)

# Flatten nested JSON with pandas
# pip install pandas
import json
import pandas as pd

nested = '[{"user":{"name":"Alice"},"orders":[{"id":1}]}]'
df = pd.json_normalize(json.loads(nested), sep='.')
df.to_csv('output.csv', index=False)

# JSON Lines (one object per line) to CSV
import json
import csv

with open('data.jsonl', encoding='utf-8') as src, open('output.csv', 'w', newline='', encoding='utf-8') as out:
    records = (json.loads(line) for line in src if line.strip())
    first = next(records)
    writer = csv.DictWriter(out, fieldnames=first.keys())
    writer.writeheader()
    writer.writerow(first)
    writer.writerows(records)

# Stream large files without loading them into memory
# pip install ijson
import csv
import ijson

# 'item' yields each object inside a top-level JSON array
with open('big.json', 'rb') as src, open('output.csv', 'w', newline='', encoding='utf-8') as out:
    writer = None
    for record in ijson.items(src, 'item'):
        if writer is None:
            writer = csv.DictWriter(out, fieldnames=record.keys())
            writer.writeheader()
        writer.writerow(record)

The streaming version assumes consistent keys across records. For irregular shapes, collect the union of keys in a first pass and pass it as fieldnames.

JavaScript

// Manual conversion for flat arrays
const records = [{ name: 'Alice', age: 30 }, { name: 'Bob', age: 25 }];
const headers = Object.keys(records[0]);
const rows = records.map(r => headers.map(h => JSON.stringify(r[h])).join(','));
const csv = [headers.join(','), ...rows].join('\n');
console.log(csv);

// Using @json2csv for reliable conversion
// npm install @json2csv/plainjs
import { Parser } from '@json2csv/plainjs';

const parser = new Parser();
const csv = parser.parse(records);
console.log(csv);

Java

// Jackson + commons-csv
// Maven: com.fasterxml.jackson.core:jackson-databind, org.apache.commons:commons-csv
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import java.io.StringWriter;
import java.util.List;
import java.util.Map;

public class JsonToCsv {
    public static void main(String[] args) throws Exception {
        String json = "[{\"name\":\"Alice\",\"age\":30},{\"name\":\"Bob\",\"age\":25}]";
        ObjectMapper mapper = new ObjectMapper();
        List<Map<String, Object>> records = mapper.readValue(json, List.class);

        StringWriter sw = new StringWriter();
        try (CSVPrinter printer = new CSVPrinter(sw, CSVFormat.DEFAULT.withHeader("name", "age"))) {
            for (Map<String, Object> record : records) {
                printer.printRecord(record.get("name"), record.get("age"));
            }
        }
        System.out.println(sw.toString());
    }
}

Explanation

The core challenge is flattening hierarchical data into a two-dimensional table. Flat JSON arrays map directly to rows. Nested objects need a strategy: flatten keys into dot-notation columns (user.name), unwind arrays into repeated rows, or split related entities into separate CSV files joined by an ID column — the same trade-off you make with database normalization.

Two details decide whether the output file is actually usable:

• Header derivation: records[0].keys() tells you the fields of the first record and nothing more — a later record with extra keys loses those columns, and one with missing keys shifts everything sideways. Compute the union of keys across the whole set before writing.
• Encoding: always write UTF-8 explicitly, and prepend a BOM (\ufeff) when the file is headed to Excel; skip it and non-ASCII characters show up as mojibake.

For irregular records, the defensive version collects the key union first and fills gaps with a default:

fieldnames = sorted({key for r in records for key in r})
writer = csv.DictWriter(f, fieldnames=fieldnames, restval='', extrasaction='ignore')
writer.writeheader()
writer.writerows(records)

The restval='' keeps missing fields as empty cells instead of raising, and extrasaction='ignore' protects against keys that were not caught in the union pass.

pandas.json_normalize handles flattening automatically, with record_path and meta options for arrays. @json2csv supports custom fields, transforms, and unwind for arrays. Java has no built-in JSON-to-CSV converter, so you iterate manually with Jackson plus a CSV writer like commons-csv or univocity-parsers.

Keep in mind that CSV is a text format: numbers, booleans, nulls, and dates all become strings, and whoever reads the file has to infer types again. If round-trip fidelity matters, serialize values with an explicit convention (ISO 8601 dates, null as empty cells) and document it for consumers.

Variants

Technology	Library	Approach	Notes
Python	csv (stdlib)	DictWriter	Zero deps, requires flat JSON
Python	pandas	json_normalize()	Handles nesting; heavy dependency
Python	ijson + csv	Streaming parse	Constant memory for large files
JavaScript	@json2csv	Parser	Custom fields, transforms, unwind
JavaScript	Manual	Object.keys() + join()	Zero deps, brittle for complex data
Java	Jackson + commons-csv	Manual iteration	Enterprise-grade, verbose boilerplate
Java	univocity-parsers	CsvWriter	High-performance alternative

What Works

• Derive headers from the union of all record keys, not just the first record — missing keys produce misaligned or truncated columns.
• Sanitize headers: spaces and special characters in column names break downstream parsers — strip or replace them before writing.
• Escape commas and quotes in string values to produce RFC 4180-compliant CSV; dedicated libraries handle this automatically.
• Decide the array strategy upfront: unwind arrays into repeated rows, or keep them as JSON strings inside cells.
• Stream large datasets: for files bigger than available memory, use ijson in Python or a transform stream in Node — see Read Large Files as a Stream in Node.js.
• Keep values raw: don’t stringify numbers or booleans before writing, or Excel loses the ability to sort and compute on them.

Common Mistakes

• Assuming all records have identical keys: missing fields cause misaligned columns; compute the key union first.
• Not handling nested objects: produces [object Object] in JS or LinkedHashMap in Java cells instead of real values.
• Forgetting to quote values containing commas: breaks naive parsers that split on commas — RFC 4180 requires quoting.
• Ignoring CSV formula injection: cells starting with =, +, -, or @ execute as formulas when opened in Excel. Prefix them with ' or sanitize on export, especially with user-generated data (see OWASP CSV Injection).
• Loading multi-GB files into memory: converting a large array in memory kills the process — stream it instead.
• Relying on Excel’s default delimiter: many locales expect semicolons, not commas — set the delimiter explicitly for international users.

When Not to Use This Approach

• Irregular or deeply recursive JSON: documents with wildly different shapes lose information in a flat table — keep JSON Lines or convert to Parquet instead.
• Machine-to-machine exchange: CSV drops types and nesting; use JSON, Parquet, or Arrow for service-to-service data.
• Data already in a database: go straight from SQL to CSV with COPY or SELECT INTO OUTFILE — detouring through JSON only adds a serialization step.
• Multi-entity documents: when one JSON file holds several related entities, a set of linked CSVs — or a relational format — beats one giant flattened table.

See Also

• Flatten and Unflatten Nested Objects — how to turn dotted column names like user.name back into nested JSON.
• Validate JSON Schema — keep the source data under a contract before converting it.
• Runnable companion on GitHub — the companion repo for this recipe.