JSON から SQL へ：INSERT 文、テーブル設計、型マッピングの罠

要点：テーブルを設計する前に 1 行がどの業務エンティティを表すかを決め、単一トランザクション内で明示的な複数行 VALUES を書きましょう。フィールド名と型マッピングは SQL で後から直すのではなく、取り込み前に決めます。

JSON は形を記述し、SQL は制約も記述します。前者を後者に移すとき、書き留めなかった制約は後から汚れたデータとして戻ってきます。

1. 先に決める：テーブル一つか、JSON の 1 行ごとか

テーブルを設計する前にデータの最外層を見ます。この判断を誤ると、以降のすべての INSERT を書き直すことになります。

基準はただ一つ。1 行はどの業務エンティティを表すか。 1 行が注文なら、明細は自前のテーブルに置かなければなりません。1 行がユーザーなら、タグは子テーブルか JSON 列に属し、カンマ連結の文字列ではありません。

2. 配列から INSERT へ：最小の実行可能テンプレート

最も安全な形は常に列名を明示的に列挙し、列順に依存しません。

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

三つの詳細が重要です。

• すべての列名を書く。後でスキーマに列が増えても、何かが誤ったフィールドにずれません。
• 複数行 VALUES は SQLite、PostgreSQL、MySQL で動き、単一行 INSERT のループに比べて往復回数を大幅に削減します。
• NULL はキーワードで、引用符で囲みません。'NULL' と書くと 4 文字の文字列を保存します。

JSON のキーが列名と一致しない場合（userId と user_id）、マッピング層を足します。SQL 内で COALESCE で推測しないでください。データ層でフィールド名を直す方が、クエリで後からパッチするよりずっと安上がりです。

3. ネストしたオブジェクト：親テーブルと子テーブル

注文は古典的な一対多の例です。

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

これは JSON の {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]} に対応します。

重要な点：

• items 配列の長さが子行の数を決め、親テーブルはちょうど 1 行になります。
• 金額は整数のセント（total_cents）で保存し、FLOAT は決して使いません。
• 子テーブルに order_id 外部キーを与え、両方の INSERT を 1 トランザクションでコミットします。さもないと親が存在するのに明細が欠けた状態がありえます。
• データが読み取り専用で、明細でのフィルタが誰にも必要ないなら、JSON 列も許容できます（PostgreSQL の jsonb、MySQL の JSON、json1 関数付きの SQLite TEXT）。ただしコストは受け入れます。インデックスがなく、クエリが扱いにくいです。

user.address のような固定のネストしたオブジェクトは列に平坦化します（address_city、address_zip）。テーブルに分割するのはネストがさらに増えうるときだけです。固定の形を分割すると JOIN が増えるだけです。

4. NULL と欠落フィールド：SQL に「キー不在」はない

JSON はキーを完全に省略できます。SQL の列は固定です。取り込み後、両者は同じ NULL に見えますが、意味が異なることがあります。

よくある二つの間違い：

1. NULL の代わりに空文字列を使う。 後で WHERE email IS NULL がそれらの行を取りこぼし、「メールのないユーザー」指標が静かに過少になります。
2. col = NULL でテストする。 SQL の三値論理では NULL = NULL は NULL と評価され、TRUE になりません。IS NULL と書く必要があります。

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

キーが元々存在したかどうかを保つ必要があるなら、NULL から推測するのではなくブールのマーカー列を足します。API 契約でもこれを明記すべきです。JSON API 設計とフィールド命名を参照してください。

5. 引用とエスケープ：SQL 文字列を連結しない

JSON 文字列には単一引用符、二重引用符、バックスラッシュ、改行、さらには \u0000 を含められます。手組みの文字列では、エスケープを一つ逃すと文の残りがずれ、最悪の場合 SQL インジェクションになります。

標準 SQL では、単一引用符は二重にすることでエスケープします。

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

本番コードはパラメータバインディングを使い、エスケープと型をドライバに任せるべきです。

复制// PostgreSQL は $1/$2、MySQL は ? を使う。この例は node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

.sql ファイルをエクスポートしなければならないときだけ手でエスケープし、そのときも最低限：

• ' を '' に置換する。
• 改行を保つなら、対象データベースが複数行文字列リテラルを受け入れるか確認する。
• \u0000 を除去する。ほとんどのデータベースは文字列内の NUL を拒否します。
• MySQL は既定でバックスラッシュをエスケープ文字として扱うので、\ を \\ にするか、標準モードに切り替える。

JSON の二重引用符は SQL の単一引用符文字列内ではエスケープ不要です。余分なエスケープ層を足すと、データに不要なバックスラッシュが残ります。

6. 型マッピング：六つの JSON 値が SQL のどこに落ちるか

JSON には文字列、数値、真偽値、null、オブジェクト、配列しかありません。SQL の型システムはずっと細かいです。間違いは通常、構文ではなく精度と意味論で起きます。

真偽値は最も微妙なバグを隠します。JSON の false は MySQL の TINYINT(1) で 0 になり、往復は正常に見え、誰も気づきません。しかしどこかのコードが文字列 "false" に対して if (value) をした瞬間、真値として評価されます。変換スクリプトは明示的に Boolean(value) を呼ぶべきです。

PostgreSQL の JSONB はキーをパースして重複を排除し、最後の出現を残すのでクエリに向きます。JSON は元のテキストとキー順を保ちます。SQLite の JSON は TEXT と関数群にすぎず、実質的な型制約はありません。選ぶ前に、このテーブルが JSON 内部のフィールドでフィルタする必要があるかを問いましょう。

7. 一括挿入とトランザクション：バッチ、コミット、再実行可能にする

量が増えると、取り込みを制御可能に保つ三つのことが決まります。

1. バッチ化。 N 件の単一行 INSERT のループは N 回のネットワーク往復を要します。500〜1000 行を一つの複数行 VALUES にまとめるか、データベース自身の一括ローダー（PostgreSQL COPY、MySQL LOAD DATA、SQLite トランザクション内の複数行挿入）を使います。
2. トランザクションを使う。 全か無かで、半分だけ読み込まれたテーブルがエラーの後に残りません。

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. 冪等にする。 取り込みスクリプトは頻繁に再実行されます。削除してから挿入するのではなく upsert を使います。

ON CONFLICT は一意制約か主キーを必要とし、なければ決して発火しません。自然キーがないときは email のような業務キーに一意インデックスを作ります。キーがまったくないときは JSON 側に安定した id を足します。冪等性のために自動増分 id に頼らないでください。再実行が重複を挿入します。

8. 方言差の一覧

一つの SQL ファイルを三つすべてで動かすには、少なくとも ON CONFLICT、バッククォート、SERIAL を避けます。データベースをまたぐプロジェクトでは、スキーマと upsert 文を別々に生成してください。一つのスクリプトですべてを賄えると期待しないでください。

9. 取り込み後の三つのスポットチェック

ソースファイルを削除する前に、三つを確認します。

1. 行数。 SELECT COUNT(*) FROM users; が JSON 配列の長さと等しいはずです。1 行足りない場合はたいてい最終レコードが切り詰められています。
2. NULL 率が最も高い列。 キー名の不揃いや展開されていないネストを示します。ソースが userId と user_id の両方を使っていないか確認してください。
3. 金額と長い識別子。 最大値と最小値を取り、浮動小数点や整数変換が切り詰めていないことを確認します。

先に表形式の形を理解する必要があるなら、JSON、CSV、表形式データの変換の平坦化方法を使ってください。読者がデータを読むだけの同僚なら、JSON から Excel への四つの方法の方がデータベースを立てるより手間が少ないです。

JSON を SQL に取り込むと数値の精度が失われるのはなぜですか？

2^53 を超える整数は JavaScript の JSON.parse の段階ですでに精度を失っており、BIGINT に書いても復元できません。そのような id は JSON では文字列にし、取り込み時に BIGINT や TEXT へ変換すべきです。

JSON に id がないとき、主キーには何を使うべきですか？

データベースに生成させるか、取り込み前に安定した UUID を作ります。配列インデックスを id にしないでください。再実行でデータの順序が変わり、誤った行が誤ったレコードを指します。業務キー（email、注文番号）の一意インデックスは upsert も可能にします。

一括 INSERT が一意キーと衝突したらどうすればよいですか？

通常の INSERT をその方言の upsert に置き換え、衝突する列に一意制約があることを確認します。先に削除してから挿入する方法も動きますが、1 トランザクション内で行い、行が一時的に空になる期間を受け入れる必要があります。

ネストした配列は常に子テーブルが必要ですか？

いいえ。サブ項目でのクエリがなく読み取り専用なら、JSON 列の方が単純です。タグや SKU でフィルタや集計が必要になった時点で、インデックス付きの子テーブルが持続可能な選択です。決め手はデータの形ではなく、クエリの仕方です。