Flat JSON Converts to CSV Without Problems. Nested JSON Does Not. Here Is What Breaks.

Two Formats, Four Decades Apart

CSV has roots in the early 1970s. IBM's Fortran compiler under OS/360 supported list-directed input and output with comma-separated values as early as 1972. The term "comma-separated value" and the abbreviation CSV were in common use by 1983. The format was designed for one purpose: getting data in and out of programs that operated on tables. A row meant one record. A column meant one field. A cell held one value. There was no concept of hierarchy because the underlying data model did not need one.

RFC 4180, published in 2005, attempted to formalize what had been a loose de facto standard for three decades. It defined things like how to quote fields containing commas, how to handle line endings, and how to signal that the first row contains headers. Even after formalization, CSV implementations differ: some use semicolons instead of commas for locales where the comma is a decimal separator, some handle Unicode inconsistently, and Excel's handling of quoted fields differs from what many parsers expect.

JSON's origin is more precisely dated. Douglas Crockford and Chip Morningstar sent the first JSON message in April 2001. Crockford had been working on a browser-based state management solution when he recognized that JavaScript already had a way to represent structured data as text. He stripped the executable parts from JavaScript object syntax and was left with a notation for nested data: objects, arrays, strings, numbers, booleans, and null. Crockford registered the json.org domain and documented the format the same year. RFC 4627 formalized it as an internet standard in 2006. ECMA-404 defined a language-independent grammar in 2013. RFC 8259 replaced RFC 4627 in 2017 and remains the current specification.

The gap between the formats is structural: CSV was designed for flat tabular data; JSON was designed for hierarchical structured data. Converting between them is not a simple encoding change. It requires decisions about how to represent one model in terms of the other.

What Flat JSON Looks Like

A JSON array of objects with a simple, flat structure converts to CSV without difficulty. Each object in the array becomes one row. Each key in the objects becomes one column. Each value, as long as it is a primitive (string, number, boolean), fits naturally into a cell.

This structure:

[{"name": "Alice", "age": 32, "city": "Toronto"}, {"name": "Bob", "age": 28, "city": "Berlin"}]

becomes:

name,age,city Alice,32,Toronto Bob,28,Berlin

Nothing is lost. The shape of the data maps directly onto the shape of the table. If your JSON has this structure, conversion is a mechanical operation.

The complexity begins the moment an object contains another object, an array, or values of mixed types across records.

When Nesting Breaks the Conversion

The fundamental problem is that CSV has one dimension of nesting: rows sit inside a table. JSON has no limit on nesting depth. The mismatch becomes obvious with a user object that contains an embedded address:

{"name": "Alice", "address": {"street": "120 Main St", "city": "Toronto"}}

A converter has three options. It can flatten: split the nested object into columns named address_street and address_city, preserving all the data but changing the key structure. It can discard: drop the nested object entirely and only include top-level fields. Or it can stringify: collapse the nested object into a single JSON blob stored as a string in one cell, which preserves data but makes it opaque to spreadsheet tools.

Flattening works well when nesting is one level deep and consistent across all records. It breaks when nesting depth varies, when keys at the same nesting level differ between records, or when arrays appear as values.

Arrays inside objects are harder than nested objects. If a user record contains an array of phone numbers:

{"name": "Alice", "phones": ["+1-416-555-0100", "+1-416-555-0200"]}

There is no clean single-row representation. You can create multiple columns (phones_0, phones_1) but this imposes a maximum array length on all records and wastes columns when most records have fewer entries. You can create multiple rows, one per phone number, which multiplies rows and requires the parent fields to repeat. Neither approach preserves the array structure without side effects.

The RFC 4180 Quoting Rules That Catch People Off Guard

Even flat data has edge cases. RFC 4180 specifies that fields containing commas, double-quote characters, or line breaks must be wrapped in double quotes. A double-quote character inside a quoted field must be escaped by preceding it with another double-quote. Many converters handle this correctly, but importing the result into Excel, Google Sheets, or a database loader introduces another opportunity for mishandling.

JSON string values can contain any Unicode character, including characters that look like commas but are not (the unicode full-width comma U+FF0C appears in some East Asian text). JSON can represent numbers with arbitrary precision that floating-point CSV representations may not reproduce exactly. JSON booleans (true, false) have no standard CSV equivalent; some converters write TRUE or 1, others write true or 0.

Type information disappears in CSV. A JSON file knows that age is an integer and that id is a string even if it contains only digits. A CSV file has no types: everything is a string that a downstream tool may reinterpret. Loading a CSV into a database or a pandas DataFrame requires either explicit type declarations or trusting automatic inference, which makes mistakes on fields like zip codes that look numeric but should be strings.

Practical Preparation Before Converting

Before running any conversion, inspect the JSON structure. Determine whether the data is flat, one-level nested, or deeply nested. Identify any fields that contain arrays. Decide in advance how nested objects should be flattened and what to name the resulting columns.

For any field containing an array where the number of elements varies across records, consider whether you want multiple rows or multiple columns. If neither works for your analysis, JSON may simply be the better format for this particular data and CSV is not the right target.

Check for type issues: boolean fields, numeric fields that should remain strings, null values. Decide how nulls should appear in the output, since CSV has no null literal and an empty field, the string "null", and the string "NULL" are all common choices with different implications downstream.

If the JSON source is an API response, look at the full response structure before sampling a few records. APIs often wrap the actual data array in a container object: {"status": "ok", "data": [...]}. A converter that receives the outer object rather than the inner array will attempt to create columns named status and data, where data is a stringified array.

Conclusion

The CSV format has been in use for more than fifty years because tables are a natural way to organize data for analysis, and simplicity is durable. JSON has become the dominant data interchange format on the web over the past two decades because hierarchy and nesting are natural for APIs and application state. They are different tools for different problems, and converting between them requires understanding what each can and cannot represent.

For flat data, the conversion is direct and reliable. For nested data, the quality of the result depends on the decisions made before the conversion starts. ToolHQ's JSON to CSV converter handles flat structures and one-level nesting cleanly, and makes those structural decisions visible so you know what the output represents.

Frequently Asked Questions

Try These Free Tools