ネストされたJSONオブジェクトを複数のSQLテーブルに正規化する

ネストされたJSONから正規化テーブルへ

ネストされたJSONはAPIには便利ですが、リレーショナルデータベースの正規化ルールに違反します。コンバーターはネストされたオブジェクトを外部キーでリンクされた個別のテーブルに分解します。

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" }
  ]
}

生成される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);

ネストから関係へのマッピング

JSON構造	SQLの関係	例
ネストされたオブジェクト	1対1	user → address
オブジェクトの配列	1対多	user → orders
スカラー値の配列	中間テーブルまたは配列カラム	user → tags

1対1の分解

addressのようなネストされたオブジェクトは、親へのUNIQUE外部キーを持つ個別のテーブルになります。user_idのUNIQUE制約により、各ユーザーが最大1つの住所を持つことが保証されます。

1対多の分解

ordersのようなオブジェクトの配列は、ユニークでない外部キーを持つ個別のテーブルになります。各注文行が親ユーザーを参照し、ユーザーごとに複数の注文を許可します。

正規化の利点

1. データの重複なし — 住所は1回だけ保存され、すべての注文行で繰り返されません。
2. 更新の一貫性 — 市名の変更は1行の更新だけで済み、数十行ではありません。
3. 柔軟なクエリ — ユーザーとは独立して注文をクエリできます。
4. スキーマの進化 — 住所にフィールドを追加してもusersテーブルに影響しません。

非正規化すべき場合

すべてのネストされたオブジェクトが独自のテーブルを必要とするわけではありません。サブオブジェクトが以下の場合：

• 常に親と一緒に読み取られる
• 独立してクエリされることがない
• 再利用のない1:1の関係を持つ

...その場合、JSONカラムまたは親テーブルのインラインカラムとして保存する方がシンプルかもしれません。PostgreSQLのJSONB型は半構造化サブドキュメントに優れています。