Flattening Nested JSON into Relational Tables

1. Problem: object trees and relational rows differ

An order can contain a customer object and many item objects. Turning items[0].sku and items[1].sku into columns makes schema depend on array length. A stable model uses customers, orders, and order_items, with order_id linking child rows to the parent.

2. Minimal reproduction: an array becomes rows

The two input items should produce two detail rows while retaining the parent order identity.

const input = { id: "ord-100", customer: { id: "cus-7", name: "Ada" }, items: [{ sku: "book", quantity: 1 }, { sku: "pen", quantity: 2 }] };
// customers: [{ id: "cus-7", name: "Ada" }]
// orders: [{ id: "ord-100", customer_id: "cus-7" }]
// order_items: [{ order_id: "ord-100", sku: "book", quantity: 1 }, ...]

3. Root cause: recursion cannot decide business relationships

A nested object may be a one-to-one profile or an entity with its own lifecycle. An array often means one-to-many, but it may also be an unordered tag set. Paths cannot decide keys, uniqueness, deletion, or history; define identity and relationships first.

4. Recommendation: define table boundaries and keys first

Use stable source IDs as keys when available. Otherwise generate import IDs and preserve a source mapping; an array index is not durable identity. Insert parents before children, and keep a position column only when order is meaningful.

5. Complete code: map an order tree to rows

This TypeScript creates rows but executes no SQL. It validates required fields and separates customers, orders, and items. Database writes should use parameterized statements separately.

type OrderInput = { id: string; customer: { id: string; name: string }; items: Array<{ sku: string; quantity: number }> };
type Tables = { customers: Array<{ id: string; name: string }>; orders: Array<{ id: string; customer_id: string }>; order_items: Array<{ order_id: string; position: number; sku: string; quantity: number }> };
function flattenOrder(order: OrderInput): Tables {
  if (!order.id || !order.customer?.id) throw new Error("order and customer IDs are required");
  if (!Array.isArray(order.items)) throw new TypeError("items must be an array");
  return {
    customers: [{ id: order.customer.id, name: order.customer.name }],
    orders: [{ id: order.id, customer_id: order.customer.id }],
    order_items: order.items.map((item, position) => {
      if (!item.sku || !Number.isInteger(item.quantity) || item.quantity < 0) throw new TypeError("invalid item");
      return { order_id: order.id, position, sku: item.sku, quantity: item.quantity };
    }),
  };
}
console.log(flattenOrder({ id: "ord-100", customer: { id: "cus-7", name: "Ada" }, items: [{ sku: "book", quantity: 1 }] }));

6. Incorrect approaches

A wide table made from every path turns variable arrays into column explosions. Saving children without a parent key loses ownership. Using array indexes as permanent IDs breaks after sorting or deletion, and copying the customer into every item creates update anomalies.

7. Boundaries: empty arrays, null, and repeated entities

An empty items array should create zero child rows, not a fake row full of NULLs. Missing and null customers need separate policies. Deduplicate a customer by its business ID across batches. Preserve position when order matters; otherwise document that the collection is a set.

8. Verification

Test one customer, one order, and two items, then empty items, repeated customer IDs, missing order IDs, negative quantities, and nulls. Assert every item foreign key resolves to an order and that an allowed reconstruction of the JSON retains relationships.

9. FAQ

Must every object become a table? No; a small non-queryable object can remain JSON or become columns.

Is every array one-to-many? Often, but tags, fixed values, and ordered events need different constraints.

What if there is no ID? Generate an import ID and keep a source mapping; do not use an array index as durable identity.

10. Summary

Flattening is relational modeling: arrays become rows, object boundaries become tables, and child rows keep parent keys. Decide identity, uniqueness, and null policy before generating rows. Use parameterized SQL for writes and reconstruction tests to prove relationships were preserved.