JSON保存テーブル

1. 目的

• スキーマが不定形または頻繁に変化するデータ（アンケート回答、ユーザー設定、商品属性など）がある時、これを柔軟かつ効率的に保存・管理する

2. 課題

• スキーマが不定形または頻繁に変化するデータはデータ構造の変更のたびにDDL変更やマイグレーションが必要になる
• 事前にすべてのフィールドを定義できない場合、カラムが際限なく増える
• アンケートのようなユースケースでは、案件ごとにテーブルを作ると保守が困難になる

3. 解決策

JSONをデータに持つフィールドを作成する

• 不定形なデータは正規化を諦めJSONで保存する

CREATE TABLE answers (
    id int AUTO_INCREMENT PRIMARY KEY,
    survey_id int NOT NULL,
    user_id  int NOT NULL,
    answers text NOT NULL,  -- JSONが保存されるカラム
    submitted_at DATETIME DEFAULT NOW()
);

• 検索・JOIN・外部キー制約が必要なフィールドは通常のカラムとして定義
• 可変・不定形のデータはJSONフィールドに格納

4. メリット

• スキーマ変更なしで新しいデータ構造に対応できる
• マイグレーション不要でデプロイが容易
• ネストや配列など複雑な構造を自然に表現できる
• アプリケーション側でスキーマ定義とバリデーションを一元管理できる

5. デメリット

• JSON内のデータに対するクエリは通常カラムより遅い
• 外部キー制約やユニーク制約をJSON内のフィールドに適用できない
• データの整合性をアプリケーション層で担保する必要がある
• 不適切に使うとデータ構造が把握しにくくなる

6. 注意

インデックス設計

• 頻繁に検索するJSON内のキーにはインデックスを作成する

CREATE INDEX idx_answers_q1 ON responses ((answers->>'q1'));
CREATE INDEX idx_answers_gin ON responses USING GIN (answers);

外部キー制約が必要な場合

• JSON内ではなく、通常カラムとして切り出して制約を適用する
• 関連先が可変の場合はNULLABLEカラムまたは関連テーブルで対する

バリデーション

• JSON Schemaまたはアプリケーション層（Zod、Yupなど）で入力検証を行う必要がある
• DBに保存する前に必ず検証しておく必要がある

集計・分析用途

• 大規模な集計が必要な場合は、ETLで分析用DBに展開するか、集計用の非正規化テーブルを別途用意することを検討する必要がある

参考資料

• N/A