私が扱うJSONのほとんどはAPIレスポンスとして生命を開始し、データベースに終わる必要があります。 postgresqlを介してExpressとTypeORMで実行されるToolz.devバックエンドでは、私はクイッククエリ、移行シード、またはバグのローカル再現のためにテーブルにロードしたい1 つのサービスからJSONエクスポートを持っていた時間のカウントを手で行うことは厳しいです: あなたはaを書きます CREATE TABLE、その後、あなたハンドタイプ INSERT ステートメント、すべての文字列の引用、すべてのアポストロフィの 2 倍化、どのフィールドが数値であるかを記憶し、欠落しているキーを に変換します NULL。 200 行中 1 行間違えると、バッチ全体が実行できなくなります。
JSON から SQL へのコンバーターがその機械的な作業を行います。オブジェクトの配列を貼り付け、データベース方言を選択すると、 が生成されます CREATE TABLE 賢明な列タイプと を加えたもの INSERT すべての行をロードするステートメント このガイドでは、変換の仕組み、型と引用に関する決定、午後を保存する実際の状況について説明します。
tl;dr: ザ・ JSON から SQL Converter オブジェクトの JSON 配列を に変換します
CREATE TABLEあんどINSERTmysql、postgresql、sqlite、または標準sqlのステートメント。 オブジェクトキーを列として読み込み、データから各列タイプを推測し、文字列をエスケープし、欠落しているキーとヌルを に変換しますNULL、およびネストされたオブジェクトを JSON として保存します。すべてがブラウザで実行されるため、実際のデータを含む API レスポンスはマシンから離れることはありません。その逆または表形式のソースについては、を参照してください SQL に CSV あんど JSONからCSVへ。
JSONからSQLへのコンバーターは何をしますか?
JSONとSQLのモデルデータは根本的に異なる方法で JSONは動的な値ごとの型を持つネストされたオブジェクトと配列のツリーです 関係テーブルは固定された型付きの列のフラットグリッドから他のものへの変換は小さな決定のシリーズであり、ツールの価値は一貫してそれらの決定を行っています。
オブジェクトのキーを列セットとして読み込み、各オブジェクトを行として扱い、すべての列に SQL タイプを選択してからステートメントを書き出します。 出力には 2 つの部分があります。オプションです CREATE TABLE これにより、列とその推論された型が宣言されます INSERT 値を運ぶステートメント 結果をデータベースクライアントに貼り付けたり、マイグレーションの一部として保存したり、パイプしたりできます mysql、 psql、または sqlite3 コマンドラインから.
入力は通常、次のようなオブジェクトの配列です [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }].1 つのオブジェクトも動作し、1 つの行になります.受け入れられないのは, などの裸の値の配列です. [1, 2, 3]、列に変換するキーがないので、推測ではなく、そう伝えます。
列の種類はどのように推測されますか?
これは、テキスト列にすべてをダンプするコンバータから便利なコンバータを分離する部分です。各列について、ツールはすべての行の値をスキャンし、それらすべてに適合する最も狭いタイプを選択します。
列の現在値が全て整数であれば列は整数型になります 値が数値でも小数点を持つものがあると浮動小数点型になります 全ての値が true や false、ブール値になる。 every 値がネストされたオブジェクトまたは配列の場合、JSON 型になる。 値が一致しない列を含む他のものはテキストに戻るので、 INSERT タイプの不一致で失敗することはできません。
null そして欠落しているキーは型を決める時にスキップされます; それらは実際に列をnullableにするだけです. つまり,ほとんどの行に数字を保持する列と, null いくつかはまだ数字として入力されており、それが必要なものです。
具体的な型名は方言に依存する。 neutral kinds がどのように写像するかはこうである:
| 推論された種類 | mysql | PostgreSQL | SQLite を | Standard SQL |
|---|---|---|---|---|
| 整数 | INT |
INTEGER |
INTEGER |
INTEGER |
| 小数 | DOUBLE |
DOUBLE PRECISION |
REAL |
REAL |
| ブール値 | TINYINT(1) |
BOOLEAN |
INTEGER |
BOOLEAN |
| ネストされた JSON | JSON |
JSONB |
TEXT |
TEXT |
| テキスト | VARCHAR(255) |
TEXT |
TEXT |
TEXT |
型推論を完全にオフにすることができ、その場合、すべての列がテキストになります。これは、ステージング テーブルにデータをロードし、後でキャストする予定があるときに必要な場合があります。
実際のデータの厄介な部分をどのように処理するのでしょうか?
実際の JSON が均一であることはほとんどなく、変換は対応する必要があります。このツールは、単純なコンバータをトリップする 3 つのケースを処理します。
まずキーの異なるオブジェクトです. もし1 つのオブジェクトが を持っていたら, email フィールドと別のフィールドはそうではありません。列セットは、最初に表示された順序で保持され、キーが欠落しているオブジェクトを取得して表示されるすべてのキーの結合です NULL その列で。 tool warns you when the objects are not uniform so you are not surprised.ツールは、オブジェクトが均一でないと警告するので、驚かない。
第二に、ネストされたオブジェクトと配列です。 SQL列はフラットなので、 のような値です { "street": "1 Main St", "city": "London" } 形状を決定せずに自動的に数列になることはできません。代わりに、コンバータはネストされた値を JSON テキストにシリアル化し、MySQL 上の JSON 列、PostgreSQL 上の JSONB 列、または他の場所のテキスト列に保存します。構造はそのまま維持され、後で database's JSON 関数を使用してクエリを実行できます。
第三に、1 つの列に混合型があります。フィールドが 1 つのレコードの数値で、別のレコードの文字列である場合、数値型を強制すると挿入の半分が失敗します。ツールは不一致を検出し、列をテキストとして入力し、すべての値を文字列リテラルとして引用して、バッチがクリーンに実行されます。
価値観はどのように逃れるのでしょうか?
正しいエスケープは、実行されるスクリプトと行 47 で構文エラーがスローされるスクリプトの違いです。コンバータが適用するルールは、標準の SQL ルールです。
テキストの値は一重引用符で包まれており、値内の任意の単一引用符は 2 倍になります O'Brien なる 'O''Brien'。 ANSI SQL文字列のエスケープであり、4 つの方言すべてで動作する。 numbers と booleans は引用符なしで記述される、なぜなら引用符で囲まれた数値はほとんどのエンジンで文字列だからである。 booleans は としてレンダリングする TRUE あんど FALSE postgresql および標準 SQL 上、およびとして 1 あんど 0 mysql と SQLite では、各エンジンの保存方法に一致します。 null そして、欠落しているキーは引用されていないキーワードになります NULL。
テーブルと列の名前を意味する識別子も引用され、MySQLではバックティックが、それ以外の場所ではダブルクォーテーションが表示されます。 that keeps a column named order や select 予約語との衝突から.
JSON to SQL Converterの使い方
ステップ1:JSONを貼り付けます
オブジェクトの配列をドロップインするか、Load Sample キーを押して予想される形状を確認します。各オブジェクトは 1 行になります。
ステップ 2: テーブルに名前を付け、方言を選択します
ターゲットテーブル名を入力し、MySQL、PostgreSQL、SQLite、または標準SQLを選択します。方言は、引用スタイル、型名、ブール値とネストされたJSONの書き方を制御します。
ステップ3: 出力オプションを設定します
を含めるかどうかを決定します CREATE TABLE、列型を推測するか、多行を1 つ出すか INSERT または、行ごとに 1 つのステートメント。 1 つの複数行の挿入がより速く読み込まれます。行ごとに 1 つのステートメントで差分対応のファイルが生成され、行を個別に実行できます。
ステップ4:SQLをコピーします
生成されたステートメントを読み、コピーします。 database クライアントにスクリプトを貼り付けるか、コマンドラインから実行してテーブルを構築し、ロードします。
よくある使用例
API レスポンスからデータベースをシードします
機能を構築していて、それに対抗する現実的な行が必要です。 jsonレスポンスをアップストリームAPIからプルし、貼り付け、そしてあなたはaを持っています CREATE TABLE そして、ローカルデータベースをシードする準備ができているインサート 手入力なし、見逃しクォートなし。
コンフィグまたはエクスポートをテーブルに変換します
サービスはそのデータを JSON 配列としてエクスポートします。 javascript を記述して配列をフィルタリングするのではなく、アドホック SQL クエリを実行したいと考えています。それを SQL に変換し、SQLite にロードし、まさにそのために構築された言語でクエリします。
移行シードの構築
フレームワークの移行には、多くの場合、参照データを挿入するシード ステップが必要です。 を生成します INSERT 既知の JSON フィクスチャからのステートメントを使用すると、データを手作業で変換するのではなく、移行に貼り付けるための安定したレビュー可能なブロックが得られます。
現地での制作バグ再現
バグは特定のレコードセットに対してのみ表示されます。それらのレコードの JSON を取得し、挿入に変換し、ローカルデータベースにロードし、実稼働データベースをコピーせずに問題を再現します。
多列 vs 1 列 INSERT: どちらを選択しますか?
複数行の挿入により、すべての行が 1 つのステートメントにバンドルされ、多数のステートメントが生成されます VALUES タプル。データベースは数百文ではなく 1 つの文を解析して計画するため、コンパクトでロードが最も速いです。一括ロード用に選択します。
行ごとに 1 つのステートメントはより冗長ですが、2 つの利点があります。各行が独自の行であるファイルを生成し、コード レビューできれいに読み取られ、バージョン管理で適切に差分が生成されます。また、1 つの行が制約に違反している場合、他の行は依然として成功します。これは、乱雑なデータをロードしていて、バッチ全体を失うのではなくどの行が失敗するかを確認したい場合に便利です。コミットするシード用、またはまだ完全に信頼していないデータ用に選択します。
コマンドラインから生成されたSQLを実行します
スクリプトが揃ったら、ロードするのは各エンジンでワンライナーです。 SQLiteの場合は、出力をファイルに保存し、パイプでつなぎます: sqlite3 mydata.db < insert.sql テーブルを作成し、行を新鮮なデータベースファイルにロードします。 PostgreSQLの場合、 psql -d mydb -f insert.sql 名前付きデータベースに対してスクリプト全体を実行します。 MySQLの場合、 mysql -u user -p mydb < insert.sql 同じことを行います。ジェネレーターは識別子を引用し、選択した方言の値をエスケープするため、手書きの SQL で通常必要な手動修正を行わずにスクリプトが実行されます。
単一の複数行の挿入ではなく、行ごとに 1 つのステートメントを生成した場合は、トランザクションでロードをラップすることは価値があります。 を追加します BEGIN; 上 と COMMIT; 下部では、数百の個々のインサートを 1 つのアトミック操作に変換します。これは、より高速かつ安全です。後の行が制約に失敗した場合、テーブルに半分だけ入力したままにする代わりに、負荷全体がロールバックします。複数行のインサートの場合、これは単一のステートメントであるため、すでに事実上アトミックです。
大規模な配列でのパフォーマンスと制限
変換自体は入力のサイズが線形であり、ブラウザで完全に実行されるため、数千行が即座に変換されます 実用的な制限はツールではなく、出力を使って行うことです 単一のマルチ行 INSERT 何万ものタプルで、データベース' s の最大ステートメント サイズまたはパケット制限、MySQL' s を超える可能性があります max_allowed_packet 最初にヒットする人になる 非常に大きな配列を読み込んでいる場合は、各行が小さくなるように 1 行あたり 1 つのステートメントに切り替えるか、入力を数百行のバッチに分割して個別に変換します。
知っておく価値のある精度の注意点もあります。 JSON 番号は browser's JSON エンジンによって標準の倍精度フロートに解析されるため、JavaScript's の安全な整数範囲を超える約 9 京を超える整数は、ツールがそれを見る前に精度を失う可能性があります。これは、コンバーターではなく、あらゆる環境で JSON が解析する特性ですが、巨大な数値 id はソース JSON で文字列として表現される方が安全であることを意味し、ツールはそれをテキストとして保存し、正確に保存します。
これが他のツールに適合する場合
JSON to SQL は小さなファミリーの一方向です。ソースが JSON ではなくスプレッドシートまたは CSV エクスポートである場合、 SQL に CSV 同じ型推論とエスケープを持つ表形式の入力から同じジョブを行います データベースではなくフラットファイルとしてデータが必要な場合、 JSONからCSVへ 配列を行に平坦化します CSVからJSONへ 逆に戻ります.何かを変換する前に, JSON フォーマッタ は pretty-print を行い、JSON を検証するので、パースエラーになる前に迷走カンマをキャッチします。 そして、SQL を取得したら、 SQL フォーマッタ コミットするのが得意な移行ファイルのステートメントをインデントして位置合わせします。
データをコンバータ サービスにアップロードするのではなく、ブラウザでこれらすべてを実行する背後にある理由については、次のようになります オンライン ツールのデータ プライバシー ガイドでは、クライアント側のモデルと、 Web 開発者ツールキット データ変換作業と適切に組み合わせるユーティリティを収集します。
フェイク
JSONからSQLに変換する方法は?
オブジェクトの JSON 配列を貼り付け、テーブル名を設定し、SQL 方言を選択します。ツールはオブジェクト キーを列名として読み取り、各列タイプを推測し、データベースでコピーして実行できる CREATE TABLE プラス INSERT ステートメントを生成します。
どのような JSON 形状を受け入れますか?
オブジェクトの配列は通常の入力で、各オブジェクトは 1 行です。単一のオブジェクトも動作し、1 行を生成します。プリミティブまたは深く不一致な形状の配列は、何が予想されるかを説明するエラーとともに拒否されます。
どのSQLデータベースがサポートされていますか?
コンバータは、mysql、postgresql、sqlite、および標準 SQL のステートメントを出力します。選択した方言は、識別子の引用符、型とブール構文、ネイティブ JSON 型を制御するため、スクリプトは編集なしでそのデータベースで実行されます。
列の種類はどのように決定されますか?
各列はすべてのオブジェクトに対してチェックされます。すべての現在値が整数である場合、整数型になり、すべての数値が 10 進数型になり、すべての true または false 値がブール型になり、すべてのオブジェクトまたは配列が JSON 型になり、混合されたものはすべてテキストになります。
ネストされたオブジェクトや配列はどうなるのでしょうか?
これらは JSON テキストにシリアル化され、MySQL 上の JSON 列、PostgreSQL 上の JSONB 列、または SQLite および標準 SQL 上の TEXT 列に保存されます。これにより、構造がそのまま維持されるため、後でデータベース JSON 関数でクエリを実行できます。
オブジェクトのキーが異なる場合はどうなりますか?
それは問題ありません。列は、最初に表示された順序で保持された、表示されるすべてのキーの結合です。これらのキーの 1 つが欠落しているオブジェクトはその列で NULL になり、オブジェクトが均一でない場合はツールが警告します。
文字列や引用符はどのようにエスケープされますか?
テキスト値はシングル クォートでラップされ、値内のシングル クォートは 2 倍になります。これは、標準の SQL エスケープです。数値とブール値は引用符で囲まれず、null は NULL になり、識別子は MySQL のバックティックまたは他の方言のダブル クォートで引用されます。
私の JSON はどこかにアップロードされていますか?
いいえ 解析とSQL生成はブラウザでJavaScriptとして実行されます 何も送信、ログ、保存されず、ツールはロードされるとオフラインで動作し続けます。変換中にネットワークタブを見て確認できます。
