Converting Flat CSV to Nested JSON

Why Nested JSON Matters

CSV files are flat — every row stands alone with no inherent parent-child relationships. But most APIs and NoSQL databases expect hierarchical data where objects contain nested objects or arrays. A user record with address, orders, and preferences is a single nested document in MongoDB, but three separate CSV tables in a spreadsheet.

Converting flat CSV to nested JSON bridges this gap, letting you move data from spreadsheets and legacy exports into modern APIs and databases without manual restructuring.

The Structural Gap

Consider a flat CSV representing user data with embedded addresses:

id,name,street,city,zip,country
1,Alice,123 Main St,Springfield,62704,US
2,Bob,456 Oak Ave,Portland,97201,US

The target JSON nests the address inside each user:

[
  {
    "id": 1,
    "name": "Alice",
    "address": {
      "street": "123 Main St",
      "city": "Springfield",
      "zip": "62704",
      "country": "US"
    }
  }
]

This nesting eliminates redundant prefixes (address.street, address.city) and groups related fields under a common parent.

Column Naming Conventions

The most practical approach uses dot notation in column headers to define nesting depth:

A conversion tool parses the dot-separated keys and builds nested objects accordingly. Numeric segments indicate array indices.

Handling One-to-Many Relationships

Flat CSV represents one-to-many relationships through repeated parent rows:

user_id,user_name,order_id,order_total
1,Alice,101,59.99
1,Alice,102,24.50
2,Bob,103,112.00

The desired output groups orders under each user:

[
  {
    "user_id": 1,
    "user_name": "Alice",
    "orders": [
      { "order_id": 101, "order_total": 59.99 },
      { "order_id": 102, "order_total": 24.50 }
    ]
  },
  {
    "user_id": 2,
    "user_name": "Bob",
    "orders": [
      { "order_id": 103, "order_total": 112.00 }
    ]
  }
]

This requires grouping rows by a parent key (user_id) and collecting child fields (order_id, order_total) into an array under each group.

Conversion Strategies

Dot-Notation Headers

The simplest method — rename CSV columns using dots and let a converter build the hierarchy:

id,name,address.street,address.city,address.zip
1,Alice,123 Main St,Springfield,62704

Works well for shallow nesting (1–2 levels). Deep nesting with arrays becomes unreadable.

Multi-Table Join

Maintain separate CSV files for each entity and join them during conversion:

users.csv     → id, name, email
orders.csv    → id, user_id, total
items.csv     → id, order_id, product, qty

The converter reads each file, matches foreign keys, and assembles the hierarchy. This is cleaner for complex relationships but requires more setup.

Pivot Columns

Use a pivot column to signal when a new child record begins. A change in the pivot value (user_id) tells the converter to start a new parent object.

Data Type Considerations

CSV treats everything as strings. During conversion, apply type casting:

• Numbers: "59.99" → 59.99
• Booleans: "true" → true
• Nulls: empty cells → null
• Dates: "2026-05-28" → ISO 8601 string or Date object

Without type casting, all JSON values are strings, which breaks API schemas and database validators.

Common Pitfalls

• Inconsistent nesting depth — mixing address.street and address as a column header creates ambiguity. Choose one pattern and stick to it.
• Missing array indices — orders.id without an index implies a single object, while orders.0.id explicitly creates an array. Be deliberate about which you need.
• Duplicate parent data — in one-to-many CSV, parent columns repeat across rows. The converter must deduplicate parent fields during grouping.

Key Takeaways

• Flat CSV lacks hierarchy; nested JSON groups related data under common parents.
• Dot-notation headers are the simplest way to define nesting structure in CSV.
• One-to-many relationships require grouping rows by a parent key.
• Always cast data types during conversion — CSV is all strings.
• Choose a consistent strategy: dot notation for shallow nesting, multi-table for complex relationships.
• Deduplicate parent fields when converting repeated rows into grouped arrays.

Try It Yourself

Convert your CSV data to nested JSON instantly with the CSV to JSON Converter.