JSON を SQL に変換

出力は INSERT 文の並びだけで、CREATE TABLE は含まれません。これは機能不足ではなく意図した仕様です。JSON は値が数値だと教えてくれますが、それが整数なのか小数点2桁の数なのか、NULL を許すのか、どれが主キーなのかまでは教えてくれません。

それらはスキーマが決める事柄で、推測して生成された CREATE TABLE は一行ずつ確認しないと信用できず、自分で書くより手間がかかります。テーブルはあらかじめ用意しておき、最初の INSERT 文から列名を読み取って合わせるのが結局いちばん早い流れです。

JSON 自体には自分の名前がありません。ですからアップロードしたファイル名がその役目を担います。orders.json は INSERT INTO orders を生み、SQL の識別子として使えない文字はアンダースコアに置き換えられます。

変換したあとで名前を直そうとすると、すべての文にわたる検索と置換が必要になり、値の中にたまたま同じ単語が含まれていた場合に誤って書き換えてしまう危険もあります。変換する前にファイル名を整えておくのが、いちばん安全で早い方法です。

リレーショナルなテーブルは平らですが JSON はそうではないので、入れ子になったオブジェクトは列名に畳み込まれます。customer オブジェクトの中に city があれば、customer_city という列になります。

1段の入れ子ならたいてい問題のないテーブルになりますが、2段を超えると生成された列名はむしろ警告として読むべきです。4段も入れ子になった構造は本来いくつかのテーブルに分けて外部キーでつなぐべき関係を表しているので、それを1つの横に長いテーブルへ詰め込むと、その後のあらゆるクエリが余計に難しくなります。

タグを3つ持つレコードは tags_0、tags_1、tags_2 という列になります。値は失われませんが、形はどんどん悪くなっていきます。5つのタグを持つ次のレコードが来ると列がさらに2つ増え、しかも「2番目のタグ」に対するまともなクエリは書けません。

リレーショナルデータベースにはこれに対する普通の答えがあり、それは別テーブルです。JSON のエクスポートからそこへ持っていくには、配列を取り出したうえでもう一度変換するか、横長のテーブルをいったん経由して正規化のクエリをかけるかのどちらかになり、どちらも1回の変換より手間がかかります。行き先のデータベースが JSON 型の列を持てるなら、配列をそのまま1列に収めるのが3つ目の選択肢で、値をクエリせず保持するだけなら最も理にかなっています。

1つの JSON エクスポートの中でも、レコードごとにキーがそろっているとは限りません。値のない項目を省略する API はよくあるので、1000件のレコードが十数種類の異なるキーの組み合わせを持つこともあります。変換はファイル全体のキーの和集合を取り、値のないレコードには NULL を書き込むことで、この違いをならします。

この均一さのおかげで、出力をひとまとまりのバッチとして実行しても安全になります。列名はどの文でも同じだからです。テストのために10件だけ変換すると、ファイル全体を変換したときより列が少なくなることがあるのはこのためで、まれにしか出ない項目がサンプルにたまたま含まれていなかっただけです。CREATE TABLE を書くときはサンプルからではなく、ファイル全体を変換した結果から書いてください。

数値はそのまま、真偽値は TRUE と FALSE、JSON の null は NULL、それ以外は内部の引用符を二重にしたうえで単一引用符で囲んだ文字列として書かれます。二重化は標準的な SQL の書き方で、どのエンジンでも通じます。

バックスラッシュはそのまま素通りで書かれ、これは標準としては正しいものの、MySQL の既定の読み方とは違います。MySQL ではバックスラッシュがエスケープの始まりとして扱われるからです。Windows のパスや正規表現を含むデータであれば、MySQL と Postgres とで読み込み結果が変わってしまうので、該当しそうな場合はセッションの設定を変えてから実行し、1行だけ確認しておくと安心です。

出力は1レコードにつき1文で、トランザクションでは包まれておらず、複数行の VALUES 句もありません。数万件の文をクライアント経由で1つずつ実行するのは遅く、1文ごとに通信の往復が発生するからです。ファイル全体を BEGIN と COMMIT で挟むだけで、多くの場合いちばん大きな改善が得られます。

文そのものは意図的に平易で移植性が高く、エンジン固有の引用符や ON CONFLICT 句、スキーマの接頭辞は入っていません。使うエンジンに合わせた調整は INSERT INTO の部分を検索置換すればよく、いちばん素直な形から始めることが、その後の調整を予測しやすくしています。

シードデータやフィクスチャ、数千行程度であれば文の形が便利です。読みやすく、リポジトリにコミットでき、クライアントが接続できる場所ならどこでも実行できます。

ある規模を超えると計算が変わります。どのエンジンにもバルク読み込みの経路があり、区切り文字付きのファイルを個々の INSERT よりずっと速く読み込みます。100万行を読み込むなら分単位と時間単位ほどの差が出ます。その場合は同じエクスポートを CSV や TSV に変換するか、行き先がデータウェアハウスなら NDJSON に変換するほうが向いています。

文はこのタブの中の JavaScript で生成されます。アップロードはなく、アカウントもキューもなく、無料枠は100 MBまでのファイルを受け付け、ファイル全体を一度パースしてから書き出す都合上メモリが実質的な上限になります。

これはこの変換にとってささいな利点ではありません。データベースに挿入しようとしているものは、たいてい組織がいちばん責任を負う種類のデータ、つまり顧客や注文や取引のようなものだからです。引用符を二重にするためだけに第三者の変換サービスへそれを通すのは割に合わない取引で、ここではそもそもその取引をする必要がありません。