Nested JSON ⇔ CSV structure flattening converter | ZeroTools

JSON (JavaScript Object Notation) is a hierarchical data format that can represent objects and arrays nested in multiple layers. Since a single key can store multiple key-value pairs, it intuitively models data with complex relationships, like a user profile containing a nested address object.

In contrast, CSV (Comma-Separated Values) is two-dimensional tabular data consisting of rows and columns. Each row corresponds to a single record, and each column represents one attribute.

Because of this flat, homogeneous structure, CSV is highly suitable for aggregation and list displays in relational databases or spreadsheets.

While flat, non-hierarchical JSON can be mapped directly to CSV without issue, deeply nested JSON requires a process called "flattening" to expand those nested structures into multiple columns.

Flattening uses recursive traversal to visit JSON properties, combining parent and child property names with a chosen separator—such as a dot, slash, underscore, or hyphen—to generate a single string key.

You can also select commas, semicolons, or actual tabs as your CSV delimiter. Through this process, even deeply nested objects are reconstructed into flat column identifiers that are easy to handle in spreadsheets.

However, because this conversion forces multi-dimensional data into two dimensions, it inherently involves challenges like information loss or interpretation ambiguities during reconstruction.

When using this flattening converter, you must understand the structural differences between these formats and carefully consider how the dimension reduction affects your data.

When flattening nested objects in JSON, this tool concatenates parent and child keys using a specified separator (defaulting to a dot).

For instance, a child key name under a parent key user becomes a single header named user.name. Deeper hierarchies are sequentially chained, resulting in strings like user.profile.address.city.

To prevent hierarchy and type collisions, keys containing the chosen separator or empty keys are rejected. Handling arrays is also vital for maintaining data structure.

Since CSV has no concept of an array, individual columns must be assigned to each array element. This implementation incorporates the array index into the key name, producing headers like items.0 and items.1.

This unfolds multi-dimensional list data into tabular columns, with array indices supported up to 10000. During CSV generation, headers are properly escaped, and the union of repeated field names across records is deduplicated; duplicate headers are rejected only on CSV input.

The resulting header set retains structural context while enforcing technical limits, such as a maximum nesting depth of 64, an input limit of 2,000,000 UTF-16 code units, and bounds of 10,000 rows and 200,000 cells to keep processing safe and orderly.

During the JSON to CSV conversion, extracted keys are arranged as a header row, and corresponding property values are converted to text and output sequentially.

While JSON clearly defines data types like strings, numbers, booleans, and nulls, CSV fundamentally treats all values as plain text strings.

This tool's implementation converts each property's value into a string and places it in columns separated by your chosen delimiter.

If a value contains special characters like delimiters, line breaks, or double quotes, the field is enclosed in double quotes to prevent data boundary corruption, and headers are escaped appropriately.

In this textualization process, several type characteristics of the original data are lost or altered. Empty objects and empty arrays are converted into their respective string representations, while null and empty strings are exported as empty CSV fields.

Therefore, even if the CSV output looks orderly, it lacks the definitive data type information parsable by programming languages.

You must utilize this one-way JSON to CSV conversion with the understanding that it is a textual process where strict typing is lost.

In the reverse conversion from CSV to JSON (unflattening), the tool parses headers separated by dots or other delimiters to reconstruct the original hierarchy.

Because headers intentionally use separators as nested paths on reverse conversion, separator-containing literal keys are rejected during JSON flattening rather than rejecting separators in CSV headers.

If a header contains numbers like items.0.id, the tool infers it as an array index, supporting array indices up to 10000. Objects are built using null-prototype containers and own-property checks to explicitly reject unsafe keys like __proto__, prototype, and constructor, as well as rejecting structural and type collisions and empty keys.

The strict CSV parser preserves whitespace within fields while handling quotes, consistent column widths, BOM, and line breaks, and rejects duplicate headers.

The tool applies basic type inference where missing or empty fields become null, booleans are recognized, and numeric strings are parsed into numbers.

For example, numerical strings like product codes or phone numbers will lose leading zeros and be converted to numeric types. This unflattening feature reconstructs hierarchy within limits such as 64 levels of depth, but you must account for the fact that type inference does not yield completely reversible data types.

While this tool offers two-way conversion between JSON and CSV, fundamental format characteristics and implementation constraints create several irreversible cases.

It is vital to understand these limitations. The provided sample CSV is consistently generated from the JSON sample, but round-trip data restoration is not completely reversible.

First, leading zeros are lost. If the original JSON defines a string value like "001", it is written out as characters in the CSV.

However, upon returning to JSON, the CSV parser's type inference converts it to the number 1, permanently losing the zero-padded string representation.

Second, representations of empty strings and null become ambiguous. In JSON, both empty strings and null are exported as empty cells in the CSV.

When reverse-converting, the tool parses missing or empty fields as null, making it impossible to distinguish and restore original empty strings.

Third, original keys containing the chosen separator or empty keys are rejected to avoid hierarchy and type collisions rather than silently conflicting.

Furthermore, a literal string "true" is converted to a boolean type by type inference, erasing the distinction between string values and boolean values.

As these examples illustrate, this tool relies on structural conversion rules and does not guarantee state reversibility for all data.

Flattened CSV data can be loaded into standard spreadsheet software like Excel for easy filtering, sorting, and aggregation. However, spreadsheets have their own type inference features that may convert escaped values or long numeric strings into exponential notation or unintended date formats, and formula strings are not automatically sanitized.

You must also manage data volume within the tool's bounds. The implementation enforces safety limits including 2,000,000 UTF-16 code units per input, a maximum nesting depth of 64, up to 10,000 rows, and up to 200,000 cells.

Furthermore, reverse conversion enforces cumulative limits across all records: expanded output nodes including sparse-array null padding are capped at 200,000, and estimated pretty-JSON output is capped at 5,000,000 characters, distinct from the CSV cell limit.

Created download URLs are properly revoked after use. Regarding security and confidentiality, the parsing and conversion are executed locally in JavaScript without transmitting data to external servers.

When handling personal information, always verify your organization's security guidelines. If using the converted data for critical purposes, thoroughly verify that type inference hasn't distorted critical values.