Normalize Nested JSON Objects into Multiple SQL Tables

From Nested JSON to Normalized Tables

Nested JSON is convenient for APIs but violates relational database normalization rules. The converter decomposes nested objects into separate tables linked by foreign keys.

Example JSON

{
  "id": 1,
  "name": "Alice",
  "email": "alice@example.com",
  "address": {
    "street": "123 Main St",
    "city": "Springfield",
    "state": "IL",
    "zip": "62701"
  },
  "orders": [
    { "order_id": 101, "total": 59.99, "status": "shipped" },
    { "order_id": 102, "total": 149.50, "status": "pending" }
  ]
}

Generated SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE,
  street VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  state VARCHAR(50) NOT NULL,
  zip VARCHAR(20) NOT NULL,
  CONSTRAINT fk_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES orders(id) ON DELETE CASCADE
);

CREATE INDEX idx_orders_user_id ON orders (user_id);

How Nesting Maps to Relationships

JSON structure	SQL relationship	Example
Nested object	One-to-One	user → address
Array of objects	One-to-Many	user → orders
Array of scalars	Junction table or array column	user → tags

One-to-One Decomposition

A nested object like address becomes a separate table with a UNIQUE foreign key to the parent. The UNIQUE constraint on user_id ensures each user has at most one address.

One-to-Many Decomposition

An array of objects like orders becomes a separate table with a non-unique foreign key. Each order row references the parent user, allowing multiple orders per user.

Normalization Benefits

1. No data duplication — The address is stored once, not repeated in every order row.
2. Update consistency — Changing a city name updates one row, not dozens.
3. Flexible queries — You can query orders independently of users.
4. Schema evolution — Adding a field to addresses does not affect the users table.

When to Denormalize

Not every nested object needs its own table. If the sub-object is:

• Always read together with the parent
• Never queried independently
• Has a 1:1 relationship with no reuse

...then storing it as a JSON column or inline columns in the parent table may be simpler. PostgreSQL's JSONB type is excellent for semi-structured sub-documents.