רוב JSON אני עוסק מתחיל את החיים כתגובת API וצריך בסופו של דבר במסד נתונים. על Toolz.dev backend, אשר פועל על Express ו TypeORM על PostgreSQL, איבדתי את הספירה של פעמים היה לי ייצוא JSON משירות אחד שרציתי לטעון לתוך טבלה עבור שאילתה מהירה, זרע הגירה, או שחזור מקומי של באג עושה את זה ביד הוא קודר: אתה כותב א CREATE TABLE, אז אתה מקליד ביד INSERT הצהרות, ציטוט של כל מחרוזת, הכפלת כל אפוסתרוף, זכירת אילו שדות הם מספרים והפיכת מפתחות חסרים ל NULL. טעו בשורה אחת מתוך מאתיים וכל האצווה לא מצליחה לפעול.
ממיר JSON ל-SQL עושה את העבודה המכנית הזו בשבילך. אתה מדביק מערך של אובייקטים, בוחר דיאלקט של מסד נתונים, והוא מייצר א CREATE TABLE עם סוגי עמודות הגיוניים בתוספת ה INSERT הצהרות לטעון כל שורה מדריך זה מסביר כיצד ההמרה עובדת, ההחלטות שהיא מקבלת על סוגים וציטוט, ואת המצבים האמיתיים שבהם זה חוסך אחר הצהריים.
TL;DR: ה ממיר JSON ל-SQL הופך מערך JSON של אובייקטים ל
CREATE TABLEוINSERTהצהרות עבור MySQL, 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 בכמה עדיין מוקלדת כמספר, וזה מה שאתה רוצה.
שם סוג הבטון תלוי בניב. הנה איך מפת הסוגים הנייטרליים:
| סוג מוסכם | MySQL | 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 ומאחסן אותו בעמודה JSON ב-MySQL, בעמודה JSONB ב-PostgreSQL או בעמודה טקסט במקום אחר המבנה נשאר שלם ותוכל לבצע שאילתות עליו מאוחר יותר עם מסד הנתונים שלך '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 שלך
זרוק מערך של אובייקטים, או הקש Load Sample כדי לראות את הצורה הצפויה כל אובייקט הופך לשורה אחת.
שלב 2: תן שם לשולחן ובחר ניב
הקלד את שם טבלת היעד ובחר MySQL, PostgreSQL, SQLite או SQL סטנדרטי. הניב שולט בסגנון הציטוט, בשמות הסוג ובאופן כתיבת בוליאנים ו-JSON מקוננים.
שלב 3: הגדר את אפשרויות הפלט
להחליט אם לכלול את CREATE TABLE, האם להסיק סוגי עמודות, והאם לפלוט שורה אחת INSERT או משפט אחד בכל שורה. הוספה אחת מרובת שורות נטענת מהר יותר; משפט אחד בכל שורה מייצר קובץ ידידותי ל-diff ומאפשר לך להריץ שורות בנפרד.
שלב 4: העתק את ה-SQL
קרא את ההצהרות שנוצרו והעתק אותן הדבק את הסקריפט בלקוח מסד הנתונים שלך או הפעל אותו משורת הפקודה כדי לבנות ולטעון את הטבלה.
מקרי שימוש נפוצים
זריעת מסד נתונים מתגובת API
אתה בונה תכונה וצריך שורות מציאותיות לעבוד נגד. משוך תגובת JSON מה-API במעלה הזרם, הדבק אותה ויש לך א CREATE TABLE ותוספות מוכנות לזריעה של מסד הנתונים המקומי שלך אין הקלדה ידנית, אין הצעות מחיר שלא נענו.
הפיכת תצורה או ייצוא לטבלה
שירות מייצא את הנתונים שלו כמערך JSON.אתה רוצה להריץ נגדו שאילתות SQL אד-הוק במקום לכתוב JavaScript כדי לסנן את המערך. המר אותו ל-SQL, טען אותו ל-SQLite ושאילתה עם השפה הבנויה בדיוק לזה.
בניית זרע נדידה
הגירות מסגרת זקוקות לרוב לשלב זרע שמכניס נתוני התייחסות. יצירת ה INSERT הצהרות ממתקן JSON ידוע-טוב נותנות לך בלוק יציב וניתן לבדיקה להדביק בהגירה, במקום לתרגם את הנתונים ביד.
שחזור באג ייצור באופן מקומי
באג מופיע רק עבור קבוצה מסוימת של רשומות קח את ה-JSON עבור רשומות אלה, המר אותו לתוספות, טען אותם למסד הנתונים המקומי שלך ושחזר את הבעיה מבלי להעתיק מסד נתונים של ייצור.
הוספה מרובת שורות לעומת שורה: באיזה לבחור?
הוספה מרובת שורות מאגדת כל שורה להצהרה אחת עם רבות VALUES tuples.הוא קומפקטי ונטען הכי מהר, כי מסד הנתונים מנתח ומתכנן הצהרה אחת במקום מאות בחר אותו לטעינה בתפזורת.
משפט אחד בכל שורה הוא יותר מילולי אבל יש לו שני יתרונות הוא מייצר קובץ שבו כל שורה היא שורה משלה, שקוראת בצורה נקייה בסקירת קוד ומתבדלת היטב בבקרת גרסאות ואם שורה אחת מפרה אילוץ, האחרות עדיין מצליחות, וזה שימושי כשאתה טוען נתונים מבולגנים ורוצה לראות אילו שורות נכשלות במקום לאבד את כל האצווה בחר אותו עבור זרעים שתתחייב או עבור נתונים שאתה עדיין לא סומך עליהם במלואם.
הפעלת ה-SQL שנוצר משורת הפקודה
ברגע שיש לך את הסקריפט, טעינתו היא one-liner בכל מנוע. עבור 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 עם עשרות אלפי tuples יכול לחרוג ממסד נתונים 's גודל הצהרה מקסימלי או מגבלת מנות, MySQL's max_allowed_packet להיות זה שאנשים מכים ראשון אם אתה טוען מערך גדול מאוד, או לעבור להצהרה אחת בכל שורה כך שכל אחד קטן, או לפצל את הקלט לקבוצות של כמה מאות שורות ולהמיר אותם בנפרד.
יש גם אזהרה מדויקת שכדאי לדעת מספרי JSON מנותחים על ידי הדפדפן 's מנוע JSON לתוך מצופים סטנדרטיים ברמת דיוק כפולה, כך שמספר שלם גדול מתשעה קוודריליון בערך, מעבר ל-JavaScript' טווח מספרים שלמים בטוחים, עלול לאבד דיוק לפני שהכלי יראה אותו אי פעם. זה מאפיין של ניתוח JSON בכל סביבה, לא של הממיר, אבל זה אומר שמזהה מספרי ענק מיוצג בטוח יותר כמחרוזת במקור JSON, שהכלי יאחסן כטקסט וישמר בדיוק.
איפה זה מתאים לכלים אחרים
JSON ל SQL הוא כיוון אחד של משפחה קטנה.When המקור שלך הוא גיליון אלקטרוני או ייצוא CSV ולא JSON, CSV ל-SQL עושה את אותה עבודה מתוך קלט טבלאי עם אותו סוג הסקה ובריחה כאשר אתה צריך את הנתונים כקובץ שטוח במקום מסד נתונים, JSON ל-CSV משטח את המערך לשורות, ו CSV ל-JSON חוזר לכיוון השני לפני שאתה ממיר משהו, פורמט JSON האם די ידפיס ויאמת את ה-JSON כך שתתפוס פסיק תועה לפני שהוא יהפוך לשגיאת ניתוח. וברגע שיש לך את ה-SQL, פורמט SQL יכניס ויישר את ההצהרות עבור קובץ הגירה שאתה גאה להתחייב.
להנמקה מאחורי ביצוע כל זה בדפדפן במקום להעלות נתונים לשירות ממיר, ה פרטיות נתונים בכלים מקוונים המדריך מפרט את המודל בצד הלקוח, ואת ערכת כלים למפתחי אינטרנט אוסף את כלי השירות שמתחברים היטב לעבודת המרת נתונים.
שָׁוא
כיצד אוכל להמיר JSON ל-SQL?
הדבק מערך JSON של אובייקטים, הגדר את שם הטבלה ובחר את ניב ה-SQL שלך הכלי קורא את מפתחות האובייקט כשמות עמודות, מסיק כל סוג עמודה ויוצר הצהרות CREATE TABLE פלוס INSERT שתוכל להעתיק ולהפעיל במסד הנתונים שלך.
איזו צורת JSON היא מקבלת?
מערך של אובייקטים הוא הקלט הרגיל, כאשר כל אובייקט הוא שורה אחת אובייקט בודד גם עובד ומייצר שורה אחת מערכים של פרימיטיבים או צורות לא תואמות עמוקות נדחים עם שגיאה המסבירה את הצפוי.
אילו מסדי נתונים של SQL נתמכים?
הממיר מוציא הצהרות עבור MySQL, PostgreSQL, SQLite ו-SQL סטנדרטי. הניב שאתה בוחר שולט בציטוט מזהה, בסוג ובתחביר הבוליאני ובסוג JSON המקורי, כך שהסקריפט פועל במסד הנתונים הזה ללא עריכות.
כיצד מחליטים על סוגי עמודות?
כל עמודה נבדקת מול כל אובייקט אם כל הערכים הנוכחיים הם מספרים שלמים זה הופך לסוג מספר שלם, כל המספרים הופכים לסוג עשרוני, כל הערכים האמיתיים או הכוזבים הופכים לסוג בוליאני, כל האובייקטים או המערכים הופכים לסוג JSON, וכל דבר מעורב הופך לטקסט.
מה קורה לאובייקטים ומערכים מקוננים?
הם מסודרים בחזרה לטקסט JSON ומאוחסנים בעמודה JSON ב-MySQL, בעמודה JSONB ב-PostgreSQL, או בעמודה TEXT ב-SQLite וב-SQL סטנדרטי. זה שומר על המבנה שלם כדי שתוכל לבצע שאילתות עם פונקציות JSON של מסד הנתונים שלך מאוחר יותר.
מה אם לחפצים שלי יש מפתחות שונים?
זה בסדר העמודות הן האיחוד של כל מפתח שמופיע, נשמר בסדר שנראה לראשונה כל אובייקט שחסר אחד מהמפתחות האלה מקבל NULL בעמודה הזו, והכלי מזהיר אותך כשהאובייקטים אינם אחידים.
איך נמלטים מחרוזות וציטוטים?
ערכי טקסט עטופים במרכאות בודדות וכל ציטוט בודד בתוך ערך מוכפל, שהוא ה-SQL escape הסטנדרטי. מספרים ובוליאנים נותרים ללא ציטוט, null הופך ל-NULL, ומזהים מצוטטים עם backticks עבור MySQL או מרכאות כפולות עבור הניבים האחרים.
האם ה-JSON שלי מועלה במקום כלשהו?
מס 'ניתוח ויצירת SQL לרוץ כמו JavaScript בדפדפן שלך שום דבר לא מועבר, נרשם, או מאוחסן, והכלי ממשיך לעבוד במצב לא מקוון לאחר הטעינה, אשר אתה יכול לאשר על ידי צפייה בכרטיסייה הרשת בזמן שאתה להמיר.
