JSON to CSV Flatten
How it works
JSON's hierarchical structure and CSV's flat tabular structure are fundamentally incompatible — flattening bridges this gap by projecting nested JSON keys into dot-notation column headers. A record like {"user": {"name": "Alice", "age": 30}} becomes two columns: user.name and user.age.
**Flattening strategies** Dot notation: nested keys are concatenated with periods. {"a": {"b": {"c": 1}}} → column "a.b.c". Underscore notation: same but using underscores ("a_b_c") for compatibility with SQL column names (which treat dots as schema separators). Array handling: arrays can be expanded into multiple rows (one per element) or collapsed into a single delimited string value ("item1; item2; item3") — the choice depends on whether you need to query individual items.
**Handling mixed-depth arrays** JSON API responses often contain arrays of objects: {"orders": [{"id": 1, "amount": 50}, {"id": 2, "amount": 75}]}. Flattening with array expansion produces two rows per parent record, with a generated index column: orders.0.id=1, orders.1.id=2. Flattening with collapse preserves one row per record but loses queryability of individual items.
**Null and missing keys** In heterogeneous JSON (where not every object has every key), flattening produces sparse rows. Missing keys should produce empty cells rather than errors. The tool discovers all keys across all records first, then generates a complete column set.
Frequently Asked Questions
- Two strategies: Array expansion — each array element becomes a separate row (the parent record is duplicated for each element). Array collapse — the entire array is serialized as a JSON string in one cell, e.g., '[1,2,3]'. Expansion preserves queryability of individual elements but multiplies row count. Collapse preserves one row per parent record but makes array contents opaque. Choose expansion for analytics, collapse for archival/roundtrip use cases.
- Nested keys are joined with a dot (or underscore) separator: {"user": {"address": {"city": "NYC"}}} → column name 'user.address.city'. Array indices are included for expanded arrays: [{"id": 1}, {"id": 2}] → 'items.0.id' and 'items.1.id'. SQL column names should use underscore notation (dots are interpreted as schema separators in SQL); the tool offers both dot and underscore modes.
- The tool first scans all records to discover the complete set of keys, then generates a column for every key found across all records. Records missing a particular key produce an empty cell for that column. This sparse-row behavior is correct for heterogeneous JSON (where fields are optional). The output is a rectangular CSV with all discovered columns.
- The tool recursively flattens any nesting depth. Deep nesting (5+ levels) produces very long column names like 'level1.level2.level3.level4.field' — which may exceed column name length limits in some databases (PostgreSQL: 63 characters, MySQL: 64 characters). For very deeply nested structures, consider manually selecting which levels to flatten and which to collapse into JSON strings.