JSON to CSV Conversion Guide

A practical reference for converting JSON data to CSV format. Covers flattening strategies, array handling, encoding, delimiter selection, and debugging common conversion failures.

Introduction

JSON and CSV serve opposite ends of the data pipeline. APIs, databases, and web applications produce and consume JSON — it handles nested structures, arrays, and mixed types natively. Spreadsheets, BI tools, and data import wizards expect CSV — flat, tabular, one row per record. Converting between them bridges two ecosystems that don't share a data model.

The core challenge: JSON is hierarchical (objects within objects, arrays within arrays), while CSV is a two-dimensional grid. A converter must decide how to collapse that hierarchy into rows and columns without losing information the user needs. This guide walks through each decision point and explains the trade-offs.

Understanding Nested JSON

Flat JSON converts cleanly: each top-level key becomes a column, each value fills a cell. The difficulty starts when values are themselves objects or arrays.

{
  "name": "Alice",
  "email": "[email protected]",
  "address": {
    "street": "123 Main St",
    "city": "Boston",
    "zip": "02108"
  },
  "roles": ["admin", "editor"]
}

The standard approach is dot-notation flattening: nested object paths become compound column names joined by a delimiter.

This preserves the full path to every value. The trade-off is wide CSV files — a deeply nested JSON object with 50 leaf fields produces 50 columns, even if only a handful have values in any given record.

Handling Arrays in JSON

Arrays are the trickiest part of JSON-to-CSV conversion. The converter must decide: does this array represent multiple records (expand into rows) or a single multi-value field (collapse into a cell)?

Array of Objects → Multiple Rows

When a JSON document is a top-level array of uniform objects, each object becomes one CSV row:

[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Carol"}
]

This is the simplest case and maps directly to CSV.

Array as a Nested Field → Single Cell or Multiple Rows

When an array appears as a field value inside an object, there are two strategies:

1. Stringify the array — keep it as one cell: ["admin","editor"] or admin;editor. Simple, but the cell is no longer a single value.
2. Explode into multiple rows — duplicate the parent row for each array element. A record with 3 roles becomes 3 CSV rows, one per role. This preserves row-level atomicity but multiplies row count.

Array of Primitives vs Array of Objects

An array of strings or numbers (["red", "blue", "green"]) stringifies cleanly with a separator. An array of objects ([{"street": "123 Main"}, {"street": "456 Oak"}]) typically requires row expansion because each element has its own structure.

Encoding and Character Sets

JSON is defined as UTF-8 (RFC 8259 §8.1). CSV has no mandated encoding, which creates compatibility issues. Follow these rules:

• Always output UTF-8. It handles every character in every language and is the default for modern applications.
• Add a BOM (Byte Order Mark) for Excel compatibility. Microsoft Excel on Windows does not detect UTF-8 without a BOM (\uFEFF) at the start of the file. Without it, non-ASCII characters like é, ü, or 中文 render as garbled text.
• Escape special characters that overlap with CSV syntax: commas, double-quotes, and newlines. Double-quote the entire field and double any literal double-quotes inside it.

Delimiter Choices

Our JSON to CSV tool defaults to comma but lets you switch to tab or semicolon with one click.

Common Pitfalls

• Missing fields across records. JSON objects in an array don't always have the same keys. If the first object has 5 keys and the second has 8, the converter must scan all objects to collect the full set of columns. Converters that only inspect the first object will silently drop fields.
• Inconsistent types. A field called age that is 42 in one record and "unknown" in another creates a column with mixed types. Most converters pick one type and coerce the rest — which can mean "unknown" becomes 0 or an empty cell.
• Deeply nested structures. JSON with 5+ levels of nesting produces unwieldy column names like order.items.discounts.seasonal.code. At some depth, flattening stops being useful. Consider extracting nested arrays into separate CSV files or pre-processing the JSON to reduce nesting.
• Circular structures in source data. Circular references exist in in-memory objects (e.g., a parent node referencing a child that references the parent), but JSON text cannot represent them — serializers like JSON.stringify() throw a TypeError before any JSON file is produced. If you see a circular-reference error, it comes from your programming language's serializer, not from the converter or the JSON format itself.

Before and After: JSON → CSV Conversion

Related Guides