JSON היכרות עם

ויקיפדיה מגדירה בעברית את JSON כך (לקחת נשימה עמקוה קודם):

מכיוון שאני יודע מה זה JSON אני איכשהו מבין את ההגדרה, אבל אם לא - בוודאות לא הייתי מבין;

אבל אני מבין: הגדרה אמורה להיות קצרה ומדוייקת, וזה בא על חשבון הבהירות.

אז לקוראינו שבמקרה אינם יודעים במה מדובר, אני הייתי מגדיר את זה בתור דרך מוסכמת להצגת רשומה בתור מחרוזת.

למשל, את הרשומה הבאה:

ניתן להציג בתור JSON כך:

{"Name":"Geri","Family":"Reshef","Occupation":"DBA"}

סוגריים מסולסלים משני הצדדים, שמות העמודות והערכים שלהן (מה שמוגדר למעלה בתור "זוגות מפתח-ערך", ובשפת העם key-value), בין המפתח לערך מפרידות נקודותיים, ובין הזוגות - פסיקים,

ולבסוף - גם "המפתח" וגם "הערך" בתוך מרכאות.

כך ברור מה המפתח ומה הערך, היכן מתחיל ונגמר כל אחד, והיכן עוברים מזוג מפתח-ערך אחד לשני.

למה זה טוב? בשביל מה החכמים המציאו את הטבלאות או את קבצי ה-csv (אם חשקה נפשכם בשמירה בתור קובץ טקסט)?

זה נועד בעיקר לשמירת מידע במבנה לא אחיד.

למשל: יש טבלת עובדים, ולכל עובד יש שם, משפחה, תאריך לידה, ת"ז וכו'; אבל כשמגיעים לעניינים כמו חשבונות ברשתות חברתיות צפוי בלגן: לקשישים יש פיסבוק, לטף יש טיקטוק, לחלק יש אינסטגרם עם סלפי, ולאחרים טוויטר.

מה עושים? בכל פעם שצץ משהו חדש נוסיף עמודה לטבלת העובדים? אולי ניצור טבלת רשתות חברתיות ולכל אחד יהיו מספר שורות שם? ומה יקרה אם למישהו יהיו שני טוויטרים - אחד לדאחקות ואחד מקצועי?

אפשר לכל בעיה כזו למצוא פתרון מתוחכם שישביע את רצונם של פקחי הדטבייס הרלציוני, אבל לא בטוח שכל הסירבול הזה מצדיק את המחיר, כשמדובר בפרטים שחשיבותם שולית (במקרה זה).

במקום זה ניתן ליצור עמודת רשתות חברתיות בטבלת העובדים, ובה לשמור JSON-ים עם המידע: לאחד JSON צנוע ולשני ארוך ומרשים.

מה יהיה סוג הנתון של עמודה זו? בניגוד ל-XML (שיש דמיון מסויים בינו לבין JSON) אין ב-SQL Server סוג נתון JSON, אך אל דאגה: ניתן להשתמש בעמודת טקסט (Varchar), ולשליפת המידע יש תמיכה בשלל פונקציות.

לעבודה: כיצד ניתן לשלוף מה-JSON הנ"ל את המידע בצורת סט?

Declare       @JSN Varchar(Max)='{"Name":"Geri","Family":"Reshef","Occupation":"DBA"}';

 Select * From OpenJSON(@JSN);

אפשר לראות בדוגמה הזו שהרשומה הפכה לסט, כלומר - שלושת הערכים זה מתחת לזה ולא זה לצד זה.

עמודת type מציינת שמדובר במחרוזת (בהמשך נפגוש עוד אפשרויות). בהמשך נראה גם דרך לשמור על הרשומה בתור רשומה(ולא סט של מספר שורות), וגם את הבעיות הכרוכות בכך.

במציאות ה-JSON-ים יהיו בטבלה, שממנה נצטרך לשלוף ולפענח אותם.

ניצור טבלה עם עמודת JSON, נאכלס אותה במספר שורות, ונשלוף את המידע:

Create Table T_JSN

              (ID Int Identity,

              JSN Varchar(Max));

Truncate Table T_JSN;

Insert

Into   T_JSN(JSN)

Values ('{"Name":"Alma","Family":"Cohen","Occupation":"Programmer","Age":39}'),

              ('{"Name":"Beni","Family":"Levi","Occupation":"Developer","Age":null}'),

              ('{"Name":"Carmel","Family":"Mizrahi","Occupation":{"ZRN Ltd.":"Secretary","Anxel":"Manager"},"Age":32.5}'),

        ('{"Name":"Dan","Family":"Namer","Occupation":"Gardener","Age":60,"Hobbies":{"Sport":{"Playing":"Football","Activity":"Gym"},"Music":"Rock","Reading":"Poetry"}}');

Select *

From   T_JSN T

Outer Apply (Select * From OpenJSON(T.JSN)) OA;

בדוגמה זו ניתן לראות שלכל שורה בטבלה (אותה ניתן לזהות לפי עמודת ID) יש מספר שונה של שורות בפלט, לפי המבנה של ה-JSON שלה.

בעמודת type ניתן לראות את סוג הנתון המתקבל: 0 - Null

1 - טקסט

2 - מספר

5 - נתון שהוא בעצמו JSON. כלומר JSON מקונן בתוך JSON..

אז איך מפענחים את ה-JSON המקונן? מפעילים את הפונקצייה OpenJSON הנ"ל על עמודת value כאשר type=5:

Select *

From   T_JSN T

Outer Apply (Select * From OpenJSON(T.JSN)) OA1

Outer Apply (Select * From OpenJSON(OA1.value) Where OA1.type=5) OA2;

הבעייה לכאורה נפתרה ונוספו לנו עוד 3 עמודות key-value-type מצד ימין,

אלא שבאחת מהן יש JSON נוסף שמקונן בתוך המקונן הקודם.

לכאורה אפשר להוסיף עוד Outer Apply, אבל מה יקרה אם תהיה עוד רמת קינון ואיך נוכל לדעת זאת מראש ולהיערך בהתאם?

לשם כך המציאו חכמינו את ה-CTE הרקורסיבי שבעזרתו נשלוף ברקורסיה את כל השורות שעבורן type=5:

With T As

(Select       T.ID,

              T.JSN JSN_Orgn,

              T.JSN,

              OA.[key],

              OA.value,

              OA.type,

              1 Lvl

From   T_JSN T

Outer Apply (Select * From OpenJSON(T.JSN)) OA

Union All

Select T.ID,

              T.JSN_Orgn,

              Cast(T.value As Varchar(Max)),

              OA.[key],

              OA.value,

              OA.type,

              T.Lvl+1

From   T

Outer Apply (Select * From OpenJSON(T.value)) OA

Where  T.type=5)

Select *

From   T

--Where       type<>5

Order By ID,

              Lvl;

במקרה זה נקבל 24 שורות, ואם נפעיל את תנאי ה-Where שהשארתי בתור הערה - נקבל רק את ערכי "הקצה" בלי ה-JSON-ים המקוננים שבדרך.

היתרון שבשימוש ב-OpenJSON הוא שלא חשוב מה מבנה ה-JSON, והוא שולף את הכל;

אם כי כפי שציינתי - כל JSON הופך לסט של שורות ולא נשאר בתור רשומה אחת.

החלופה היא שימוש בפונקציות אחרות שמאפשרות לשמור על הרשומות המקוריות, ולהציג את הערכים השונים בתור עמודות נוספות (ולא שורות נוספות), למשל כך:

Select *,

              JSON_VALUE(T.JSN,'$.Name') Name,

              JSON_VALUE(T.JSN,'$.Family') Family,

              JSON_VALUE(T.JSN,'$.Occupation') Occupation,

              JSON_VALUE(T.JSN,'$.Occupation."ZRN Ltd."') Occupation,

              JSON_VALUE(T.JSN,'$.Age') Age,

              JSON_VALUE(T.JSN,'$.Hobbies.Music') Hobbies_Music,

              JSON_QUERY(T.JSN,'$.Hobbies') Hobbies,

              JSON_QUERY(T.JSN,'$.Hobbies.Sport') Hobbies_Sport

From   T_JSN T;

כפי שניתן לראות השתמשנו בשתי פונקציות:

האחת JSON_Value שנועדה להציג ערכים, והשנייה JSON_Query שמאפשרת להציג JSON-ים מקוננים.

נשמר כאן המבנה המקורי של הטבלה בת 4 השורות (במקרה זה), אבל המחיר הוא שעלינו לדעת מה מבנה ה-JSON ולציין זאת במפורש ובמפורט בפונקציות.

בעייה נוספת שיש לפונקציות הנ"ל הוא שהן מטפלות בערכים עד 4000 תווים, למשל:

Declare       @JSN1 Varchar(Max)=Concat('{"Name":"',Replicate('0',4000),'"}'),

              @JSN2 Varchar(Max)=Concat('{"Name":"',Replicate('0',4001),'"}');

Select JSON_VALUE(@JSN1,'$.Name') Name1;

Select JSON_VALUE(@JSN2,'$.Name') Name2;

Select * From OpenJSON(@JSN1);

Select * From OpenJSON(@JSN2);

למשתנה JSN1 שירשרתי 4000 אפסים, ולמשתנה JSN2 שירשרתי 4001 אפסים.

הפונקציה JSON_Value הצליחה לפענח רק עד 4000, ועם 4001 היא נכשלה והחזירה Null.

הפונקצייה OpenJSON לעומת זאת, טיפלה בהצלחה בשניהם.

לסיום נסגור את המעגל ונראה כיצד יוצרים JSON-ים.

המבנה די ברור, ולכן ניתן באופן עצמאי לשרשר ערכים המבנה הנכון כפי שעשיתי בדוגמה האחרונה,

אבל יש בכל זאת שני כלי עזר לציבור הרחב.

ניתן לשלוף ולהציג את הסט בתור JSON כך:

Select name,database_id,create_date,collation_name

From   sys.databases

Where  name In ('master','tempdb','msdb','model');

Select name,database_id,create_date,collation_name

From   sys.databases

Where  name In ('master','tempdb','msdb','model')

For JSON Path;

התא הבודד שבפלט הוא מערך (array) של JSON-ים (כל שורה בפלט היא JSON נפרד), ולכן הם בתוך סוגריים מרובעים, ומופרדים בפסיקים.

אם רוצים לכל שורה JSON נפרד ניתן כך:

Select name,database_id,create_date,collation_name,

              OA.*

From   sys.databases

Outer Apply (Select name,database_id,create_date,collation_name For JSON Path) OA(JSN)

Where  name In ('master','tempdb','msdb','model');

או כך:

SELECT name,database_id,create_date,collation_name,

              JSON_OBJECT('Name':name, 'ID':database_id, 'CreateDate':create_date,'Collation':collation_name) [JSN]

From   sys.databases

Where  name In ('master','tempdb','msdb','model');