JSON配列を複数のSQL行に変換する

JSON配列からSQL行へ

JSONオブジェクトに配列フィールドが含まれる場合、リレーショナルの同等物は通常、外部キーを持つ別テーブルです。これらの配列を変換するには、親テーブルと子テーブルの両方のINSERT文を生成する必要があります。

JSONの例

{
  "id": 1,
  "name": "Alice",
  "skills": ["Python", "SQL", "Kotlin"],
  "projects": [
    { "name": "API Gateway", "role": "lead" },
    { "name": "Data Pipeline", "role": "contributor" }
  ]
}

正規化されたSQL出力

-- 親テーブル
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- スカラー配列 -> 子テーブル
INSERT INTO user_skills (user_id, skill) VALUES
  (1, 'Python'),
  (1, 'SQL'),
  (1, 'Kotlin');

-- オブジェクト配列 -> 子テーブル
INSERT INTO user_projects (user_id, project_name, role) VALUES
  (1, 'API Gateway', 'lead'),
  (1, 'Data Pipeline', 'contributor');

代替案：配列カラム

PostgreSQLはネイティブの配列型をサポートしています：

INSERT INTO users (id, name, skills)
VALUES (1, 'Alice', ARRAY['Python', 'SQL', 'Kotlin']);

MySQLにはネイティブの配列カラムがありません。JSONカラムまたは正規化を使用してください。

多対多の中間テーブル

スキルがユーザー間で共有される場合、中間テーブルがより適切です：

INSERT INTO skills (id, name) VALUES (1, 'Python'), (2, 'SQL'), (3, 'Kotlin')
  ON CONFLICT (name) DO NOTHING;

INSERT INTO user_skills (user_id, skill_id) VALUES (1, 1), (1, 2), (1, 3);

既存配列のUNNEST

データが既にPostgreSQLのJSONカラムにある場合、インラインで配列を展開できます：

SELECT id, name, UNNEST(skills) AS skill
FROM users;

適切なアプローチの選択

シナリオ	アプローチ
シンプルな文字列配列、単一ユーザーのコンテキスト	配列カラム（PostgreSQL）
エンティティ間で共有される参照データ	中間テーブル
複数フィールドを持つオブジェクト配列	別の子テーブル
MySQLターゲット	常に子テーブルに正規化