Flatten Nested JSON to CSV Without Losing or Duplicating Rows

To flatten nested JSON to CSV safely, validate the file first, convert nested objects into dot-notation columns, and move each nested array into its own table linked by a parent ID. Flattening an array into the parent table repeats parent values once per child item and silently removes parents whose array is empty. Splitting the data keeps one row per real record, so counts and totals stay correct.

Why nesting breaks tables

A CSV file is a single two-dimensional grid. JSON is a tree, and a tree can hold a one-to-many relationship inside a single record. An order with three line items is one object in JSON, but a grid has no way to place three items inside one cell without either repeating the order or cramming the items into a string.

Take this export from an orders API:

{
  "orders": [
    { "order_id": 1001, "customer": { "name": "Ana", "country": "BD" }, "total": 59.97,
      "items": [ { "sku": "A1", "qty": 2, "price": 9.99 }, { "sku": "B2", "qty": 1, "price": 39.99 } ] },
    { "order_id": 1002, "customer": { "name": "Raj", "country": "IN" }, "total": 19.00,
      "items": [ { "sku": "C3", "qty": 1, "price": 19.00 } ] },
    { "order_id": 1003, "customer": { "name": "Mei", "country": "SG" }, "total": 0.00,
      "items": [] }
  ]
}

This is 3 orders and 3 line items. If a tool expands every array element into its own row and repeats the parent fields, the output has 3 rows (1001 twice, 1002 once). The row count looks plausible, but order 1003 is gone because its empty array produced zero rows, and SUM(total) returns 138.94 instead of the true 78.97, because the 59.97 on order 1001 is counted twice.

Two distinct failures cause this:

• Row multiplication: every parent-level value is repeated once per child, so any aggregation of parent fields is inflated.
• Row loss: an empty array or a missing key produces no child rows, so an inner-join style expansion drops the parent entirely.

Validating before converting

Converters parse the whole document before producing a single row, so one syntax error stops everything. JSON is defined by RFC 8259, which is stricter than JavaScript object syntax. Many API exports, hand-edited files and log dumps break it in the same few ways.

Control characters from U+0000 to U+001F must be escaped inside strings, which is why a pasted multi-line address field often breaks parsing.

Step 1. Open the JSON Formatter & Validator and paste the raw export.

Step 2. Read the error message. It reports a line and column. For {"order_id": 1003, "total": 0.00,} the validator flags the closing brace because the trailing comma expects another key.

Step 3. Remove the comma, validate again, and confirm the document is valid. Then format it with 2-space indentation so that the array boundaries are visible before you plan the split.

Conversion strategies

There are two valid ways to turn nested JSON into CSV. They apply to different shapes of nesting.

Objects: dot-notation columns. A nested object has a one-to-one relationship with its parent, so it fits in the same row. The path becomes the column name: customer.name, customer.country. Row count does not change.

Arrays: separate tables. An array is one-to-many. Give each array its own CSV and carry the parent key (order_id) into every child row. This keeps one row per real entity, which is what Excel pivots, Power BI relationships and database tables expect.

Here is the workflow for the sample data.

Step 1: Convert the parent table. In the JSON to CSV Converter, paste the orders array. Nested objects become dot-notation columns. Remove the items array from the parent input first, or exclude that column, so it does not appear as a serialized string.

Expected output (3 rows, one per order):

order_id,customer.name,customer.country,total
1001,Ana,BD,59.97
1002,Raj,IN,19.00
1003,Mei,SG,0.00

Step 2: Convert the child table. Build an items array that adds the parent key to each element, then convert it:

[
  { "order_id": 1001, "sku": "A1", "qty": 2, "price": 9.99 },
  { "order_id": 1001, "sku": "B2", "qty": 1, "price": 39.99 },
  { "order_id": 1002, "sku": "C3", "qty": 1, "price": 19.00 }
]

Expected output (3 rows, one per line item):

order_id,sku,qty,price
1001,A1,2,9.99
1001,B2,1,39.99
1002,C3,1,19.00

Step 3: Reconcile counts. The parent file must have 3 rows, matching the number of objects in orders. The child file must have 3 rows, matching the sum of all items lengths (2 + 1 + 0). If either number differs, a record was lost or duplicated.

Step 4: Check the totals in SQL. Import both CSVs into tables named orders and order_items. In the SQL Query Generator, describe the schema and the goal: “Tables orders(order_id, total) and order_items(order_id, sku, qty, price). Count orders with no items and sum order totals once per order.” A correct query looks like this:

SELECT COUNT(*) AS orders_without_items, (SELECT SUM(total) FROM orders) AS revenue
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id
WHERE i.order_id IS NULL;

The result is 1, 78.97. Order 1003 is retained as an order with no items, and revenue is not inflated.

1. NESTED JSON order 1001 customer {…} total 59.97 items [ A1, B2 ] 1 parent → many children

SELECT o.order_id, o.total, o.sku, o.qty, o.price
FROM raw_orders r,
JSON_TABLE(r.doc, '$.orders[*]' COLUMNS (
  order_id INT PATH '$.order_id',
  total DECIMAL(10,2) PATH '$.total',
  NESTED PATH '$.items[*]' COLUMNS (
    sku VARCHAR(20) PATH '$.sku',
    qty INT PATH '$.qty',
    price DECIMAL(10,2) PATH '$.price'
  )
)) AS o;

SELECT (o.order_json->>'order_id')::int AS order_id,
       (o.order_json->>'total')::numeric AS total,
       i.item->>'sku' AS sku,
       (i.item->>'qty')::int AS qty
FROM raw_orders r
CROSS JOIN LATERAL jsonb_array_elements(r.doc->'orders') AS o(order_json)
LEFT JOIN LATERAL jsonb_array_elements(o.order_json->'items') AS i(item) ON true;

SELECT o.order_id, o.total, i.sku, i.qty, i.price
FROM OPENJSON(@json, '$.orders') WITH (
  order_id INT '$.order_id',
  total DECIMAL(10,2) '$.total',
  items NVARCHAR(MAX) '$.items' AS JSON
) AS o
OUTER APPLY OPENJSON(o.items) WITH (
  sku NVARCHAR(20) '$.sku',
  qty INT '$.qty',
  price DECIMAL(10,2) '$.price'
) AS i;