ネストされたJSONオブジェクトをSQL INSERT用にフラット化する

ネストされたJSONからフラットなSQL行へ

リレーショナルデータベースはフラットな行でデータを格納しますが、JSONにはネストされたオブジェクトが含まれることが多いです。ネストされたJSONをSQLに変換するには、データの関係を保持するフラット化戦略が必要です。

JSONの例

{
  "id": 1,
  "name": "Alice",
  "address": {
    "street": "123 Main St",
    "city": "Portland",
    "state": "OR",
    "zip": "97201"
  },
  "preferences": {
    "theme": "dark",
    "language": "en"
  }
}

戦略1：ドット記法フラット化

アンダースコアまたはドットを使用してネストされたキーをカラム名にフラット化：

INSERT INTO users (id, name, address_street, address_city, address_state, address_zip, preferences_theme, preferences_language)
VALUES (1, 'Alice', '123 Main St', 'Portland', 'OR', '97201', 'dark', 'en');

最もシンプルなアプローチですが、カラムが非常に多い幅広いテーブルになる可能性があります。

戦略2：別テーブルへの挿入

外部キーを持つ複数テーブルにデータを正規化：

INSERT INTO users (id, name) VALUES (1, 'Alice');

INSERT INTO addresses (user_id, street, city, state, zip)
VALUES (1, '123 Main St', 'Portland', 'OR', '97201');

INSERT INTO user_preferences (user_id, theme, language)
VALUES (1, 'dark', 'en');

戦略3：JSONカラム格納

最新のデータベースはJSONカラムをサポートしています：

-- PostgreSQL
INSERT INTO users (id, name, address, preferences)
VALUES (1, 'Alice',
  '{"street":"123 Main St","city":"Portland","state":"OR","zip":"97201"}'::JSONB,
  '{"theme":"dark","language":"en"}'::JSONB);

どの戦略を選ぶか？

戦略	最適な用途	欠点
フラット化	シンプルなネスト、1階層のみ	幅広いテーブル、命名の衝突
別テーブル	深いネスト、多対一の関係	複数文、外部キー
JSONカラム	柔軟なスキーマ、可変構造	クエリが複雑、インデックスの制限

深さの制限

深くネストされたJSON（3レベル以上）では、フラット化は非実用的です。コンバーターは設定可能な深さ制限を提供し、Nレベルまでフラット化して、より深い構造はJSONカラム値として格納すべきです。