我處理的大部分 JSON 都是以 API 回應開始,最終需要進入資料庫。在工具z。dev 後端(透過PostgreSQL 在Express 和TypeORM 上運行)上,我已經記不清從一項服務匯出JSON 的次數,我想將其載入到表中以進行快速查詢、遷移種子或本地複製錯誤。手工做是嚴峻的:你寫一個 CREATE TABLE,然後你手工打字 INSERT 語句,引用每個字串,將每個撇號加倍,記住哪些欄位是數字,並將遺失的鍵轉換為 NULL. 兩百個中誤了一行,整批都跑不了。
JSON 到 SQL 轉換器為您完成機械工作。您貼上物件數組,選擇資料庫方言,它會產生一個 CREATE TABLE 具有合理的列類型加上 INSERT 載入每一行的語句。本指南解釋了轉換的工作原理、對類型和報價所做的決定以及節省下午的真實情況。
TL;DR: 的 JSON 到 SQL 轉換器 將 JSON 物件數組轉換為
CREATE TABLE和INSERTmysql、postgresql、sqlite 或標準 SQL 的語句。它將物件鍵讀取為列,從資料推斷每個列類型,轉義字串,將遺失的鍵和空轉換為NULL,並將嵌套物件儲存為 JSON。所有內容都在您的瀏覽器中運行,因此具有真實資料的 API 回應永遠不會離開您的電腦。有關相反或表格來源,請參閱 CSV 到 SQL 和 json 到 csv.
JSON 到 SQL 轉換器有什麼作用?
JSON 和 SQL 以根本不同的方式對資料進行建模。 JSON 是一棵嵌套物件和陣列樹,具有動態的每值類型。關係表是固定類型列的平面網格。從一個決策轉換到另一個決策是一系列小決策,而該工具的價值是一致地做出這些決策。
它將物件的鍵讀取為列集,將每個物件視為一行,為每個列選擇 SQL 類型,然後寫出語句。輸出有兩個部分:可選 CREATE TABLE 它聲明列及其推斷類型,以及 INSERT 攜帶值的語句。您可以將結果貼到資料庫用戶端中,將其作為遷移的一部分保存,或將其傳送到資料庫用戶端 mysql, psql,或者 sqlite3 從命令列。
輸入通常是一個物件數組,例如 [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }]。 單一物件也起作用並變成一行。它不會接受一系列裸值,例如 [1, 2, 3],因為沒有鍵可以變成列,它告訴你這一點而不是猜測。
如何推斷列類型?
這是區分有用轉換器和將所有內容轉儲到文字列的轉換器的部分。對於每一列,工具都會掃描每一行中的值並選擇適合所有值的最窄類型。
如果一列中的每個現值都是整數,則該列變成整數類型。如果值是數字但有些值有小數,則它變成浮點類型。如果每個值都是 true 或者 false,它變成布林值。如果每個值都是巢狀物件或數組,則它變成 JSON 類型。任何其他內容,包括值不一致的列,都會回退到文本,因此 INSERT 不能因類型不匹配而失敗。
null 在決定類型時跳過遺失的鍵;實際上,它們只會使該列無效。這意味著在大多數行中保存數字的列 null 有幾個仍然被輸入為數字,這就是您想要的。
具體的類型名稱取決於方言。以下是中性類型映射的方式:
| 推斷類型 | 我的SQL | PostgreSQL | SQLite | 標準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 很少是統一的,轉換必須應對。該工具處理三個觸發樸素轉換器的案例。
首先,具有不同鍵的物件。如果一個物件有一個 email 欄位和另一個欄位不符,列集是出現的每個鍵的並集,按照首先看到的順序保存,並且任何缺少鍵的物件都會得到 NULL 在那一欄中。當物件不均勻時,工具會警告您,這樣您就不會感到驚訝。
其次,嵌套物件和數組。 SQL 列是平的,因此值類似 { "street": "1 Main St", "city": "London" } 不決定形狀就無法自動變成多列。相反,轉換器將嵌套值序列回 JSON 文本,並將其儲存在 MySQL 上的 JSON 列、PostgreSQL 上的 JSONB 列或其他地方的文字列中。結構保持完整,您可以稍後使用資料庫和#39;s JSON 函數查詢它。
第三,一列混合類型。如果一個欄位是一筆記錄中的數字,另一筆記錄中的字串,則強制使用數字類型將使一半的插入失敗。該工具檢測分歧並將該列輸入為文本,然後將每個值引用為字串文字,以便批次乾淨地運行。
價值觀是如何逃脫的?
正確的轉義是運行的腳本和在第 47 行拋出語法錯誤的腳本之間的區別。轉換器應用的規則是標準 SQL 規則。
文字值被包裹在單引號中,並且值內的任何單引號都會加倍,因此 O'Brien 成為 'O''Brien'. 這是 ANSI SQL 字串轉義,適用於所有四種方言。數字和布林值寫成時不帶引號,因為在大多數引擎中引用的數字是字串。布林值渲染為 TRUE 和 FALSE 在 PostgreSQL 和標準 SQL 上,以及 1 和 0 在 MySQL 和 SQLite 上,匹配每個引擎儲存它們的方式。 null 遺失的鍵將成為未引用的關鍵字 NULL.
也引用了標識符,即表格和列名稱,MySQL 上有反引號,其他地方也有雙引號。這會保留一個列命名 order 或者 select 來自與保留字的衝突。
如何使用 JSON 到 SQL 轉換器
第 1 步:貼上您的 JSON
放入物件數組中,或按載入範例以查看預期形狀。每個物件變成一行。
第 2 步:為表格命名並選擇方言
輸入目標表名稱並選擇 MySQL、PostgreSQL、SQLite 或標準 SQL。方言控制引用樣式、類型名稱以及布林值和嵌套 JSON 的編寫方式。
步驟 3:設定輸出選項
決定是否包括 CREATE TABLE(、是否推斷列類型、是否發出多行) INSERT 或每行一個語句。單一多行插入載入速度更快;每行一個語句會產生一個差異友善文件,並允許您單獨運行行。
第四步:複製SQL
閱讀產生的語句並複製它們。將腳本貼上到資料庫用戶端或從命令列運行它以建立和載入表格。
常見用例
從 API 回應中播種資料庫
您正在建立一個功能,需要逼真的行來對抗。從上游 API 拉出 JSON 回應,將其貼上,然後您就得到了 CREATE TABLE 並插入準備播種的本地資料庫。無需手動打字,也無需錯過報價。
將配置或匯出轉換為表
服務將其資料匯出為 JSON 陣列。您想要針對它執行臨時 SQL 查詢,而不是編寫 JavaScript 來過濾該數組。將其轉換為 SQL,將其載入到 SQLite 中,並使用為此構建的語言進行查詢。
建構遷移種子
框架遷移通常需要插入參考資料的種子步驟。生成 INSERT 來自已知良好的 JSON 夾具的語句為您提供了一個穩定、可審查的區塊來貼上到遷移中,而不是手動翻譯資料。
本地複製生產錯誤
錯誤僅針對一組特定記錄出現。取得這些記錄的 JSON,將其轉換為插入,將它們載入到本機資料庫中,並在不複製生產資料庫的情況下重現問題。
多行與每行插入:選擇哪一個?
多行插入將每一行捆綁為一個語句,其中包含許多 VALUES 元組。它結構緊湊,加載速度最快,因為資料庫解析和規劃一個語句而不是數百個語句。選擇它進行批量加載。
每行一個語句比較冗長,但有兩個優點。它產生一個文件,其中每一行都是自己的行,在程式碼審查中讀起來很乾淨,在版本控制中差異很好。如果一行違反了約束,其他行仍然會成功,當您加載混亂的資料並想看看哪些行失敗而不是丟失整批資料時,這很方便。選擇它作為您將提交的種子或您尚未完全信任的資料。
從命令列執行產生的 SQL
一旦您擁有腳本,在每個引擎中載入它就是一行。對於 SQLite,將輸出儲存到檔案中並將其輸入: sqlite3 mydata.db < insert.sql 建立表格並將行載入到新的資料庫檔案中。對於 PostgreSQL, psql -d mydb -f insert.sql 針對命名資料庫運行整個腳本。對於 MySQL, mysql -u user -p mydb < insert.sql 做同樣的事情。因為生成器引用標識符並轉義您選擇的方言的值,因此腳本運行時無需手寫 SQL 通常需要的手動修復。
如果您每行產生一個語句而不是單一多行插入,則將載入包裝在事務中是值得做的。添加 BEGIN; 在頂部和 COMMIT; 在底部將數百個單獨的插入變成一個原子操作,這既更快又更安全:如果後面的行未能滿足約束,則整個負載會回滾,而不是讓表格填滿一半。對於多行插入,這已經是有效的原子,因為它是單一語句。
大型陣列的效能和限制
轉換本身與輸入的大小呈線性關係,並且完全在瀏覽器中運行,因此數千行會立即轉換。實際限制不在於工具,而在於您如何處理輸出。單一多行 INSERT 擁有數萬個元組可以超過一個資料庫'最大語句大小或資料包限制,mysql's max_allowed_packet 成為最先命中的人。如果您要載入一個非常大的數組,請切換到每行一個語句,以便每個語句都很小,或者將輸入分成幾百行的批次並單獨轉換它們。
還有一個精確的警告值得一提。 JSON 編號由瀏覽器解析'將 JSON 引擎轉換為標準雙精度浮點數,因此整數大於約 9 兆,超出 JavaScript'安全整數範圍,可能會在工具看到它之前失去精度。這是每個環境中 JSON 解析的屬性,而不是轉換器的屬性,但這意味著巨大的數字 id 在來源 JSON 中更安全地表示為字串,然後工具將儲存為文字並準確保存。
這與其他工具相符
JSON 到 SQL 是小家庭的一個方向。當您的來源是電子表格或 CSV 匯出而不是 JSON 時, CSV 到 SQL 透過相同類型的推理和轉義從表格輸入執行相同的工作。當您需要資料作為平面檔案而不是資料庫時, json 到 csv 將數組展平成行,並且 csv 到 json 以另一種方式返回。在轉換任何東西之前, json 格式化程式 將漂亮地列印並驗證 JSON,以便您在雜散逗號變成解析錯誤之前捕獲它。一旦你有了 SQL, SQL 格式化程式 將縮排並對齊您自豪地提交的遷移文件的語句。
為了在瀏覽器中執行所有這些操作而不是將資料上傳到轉換器服務背後的原因, 線上工具中的資料隱私 指南列出了客戶端模型,並且 web 開發人員工具包 收集與資料轉換工作完美配對的實用程式。
問號
如何將 JSON 轉換為 SQL?
貼上物件的 JSON 數組,設定表格名稱,然後選擇 SQL 方言。該工具將物件鍵讀取為列名稱,推斷每種列類型,並產生 CREATE TABLE 加 INSERT 語句,您可以在資料庫中複製和運行。
它接受什麼JSON形狀?
物件數組是正常輸入,其中每個物件都是一行。單一物件也可以工作並產生一行。基元或嚴重不匹配的形狀數組被拒絕,並會錯誤地解釋預期的內容。
支援哪些 SQL 資料庫?
此轉換器輸出 MySQL、PostgreSQL、SQLite 和標準 SQL 的語句。您選擇的方言控制標識符引用、類型和布林語法以及本機 JSON 類型,因此腳本無需編輯即可在該資料庫中運行。
列類型是如何決定的?
每列都會針對每個物件進行檢查。如果所有目前值都是整數,則它變成整數類型,所有數字變成十進制類型,所有真值或假值變成布林類型,所有物件或數組變成JSON 類型,任何混合的內容都變成文字。
嵌套物件和陣列會發生什麼變化?
它們被序列化回 JSON 文本並儲存在 MySQL 上的 JSON 列、PostgreSQL 上的 JSONB 列或 SQLite 和標準 SQL 上的 TEXT 列中。這可以保持結構完整,以便您可以稍後使用資料庫 JSON 函數查詢它。
如果我的物件有不同的鍵怎麼辦?
那很好。這些列是出現的每個鍵的並集,並按首次看到的順序保存。任何缺少這些鍵之一的物件在該列中都會為 NULL,當物件不均勻時,工具會警告您。
字串和引號是如何逃脫的?
文字值以單引號包裹,值內的任何單引號都會加倍,這是標準的 SQL 轉義。數字和布林值不加引號,null 變為 NULL,標識符用 MySQL 的反引號或其他方言的雙引號引用。
我的 json 上傳到任何地方了嗎？
解析和 SQL 生成不會在瀏覽器中以 JavaScript 形式運行。任何內容都不會傳輸、記錄或存儲,並且該工具在加載後會保持離線工作,您可以在轉換時透過查看網路標籤來確認這一點。
