2018-07-08

נושאים מתקדמים ב MySQL: חלק ב' - Generated columns ו json_each

פוסט נוסף לקבוצת הפוסטים בנושא MySQL:

"תסביר לי" - גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א' - עבודה עם JSON



בפוסט הקודם דיברנו על שימוש ב json column ב MySQL.

אחת המגבלות הגדולות של שדות json הם מהירות החיפוש בהם:
  • אם השדות שמורים כ json - יש לקרוא את ה json כולו מהדיסק לזיכרון - על מנת לשלוף ערך בודד.
  • אם השדות שמורים כ text column - אזי יש עוד עבודת CPU של פענוח מבנה ה json - זמן לא זניח ב jsons גדולים. להזכיר: בעמודה מטיפוס json הם שמורים כ key-value עם גישה מהירה יותר לפי מפתח.
הפתרון האפליקטיבי המתבקש שבו מפתחים שונים נוקטים הוא לאחסן "עותק" של מספר מצומצם של שדות עליהם יש חיפוש - ב columns מקבילים לזה של ה json. ההשפעה על הביצועים - תהיה לרוב דרמטית.

הנה סכמה כזו לדוגמה:

[ Id (guid/auto-inc), json_data (json), field_x (varchar(100)), field_y (int) ]

את השדות x ו y - נרצה לרוב להחזיק גם בתוך ה josn וגם בצד לצורך גישה מהירה / שימוש באינדקסים.
למה שכפול נתונים?
אם נאחסן אותם רק מחוץ ל json - העבודה עם אובייקט ה json תהיה מסורבלת יותר.
לפעמים השדה שאנו מוציאים הוא גם ערך וגם מיקום באובייקט - למשל: המחיר של ההזמנה המאוחרת ביותר (כאשר יש לנו מערך של גרסאות של הזמנות).

את התהליך הנ"ל - ניתן לבצע בצורה פשוטה יותר בעזרת יכולת של MySQL שנקראת generated column, או בעברית "עמודות מחוללות" (מלשון לחולל, ולא מלשון חילול).

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

ALTER TABLE policies
  ADD COLUMN state VARCHAR(30) GENERATED ALWAYS
    AS (json_unquote(json_extract(`json_data`,'$.address.state'))) STORED
;

אני מוסיף שערכו יהיה הביטוי (expression) שבסוגריים לאחר המילה השמורה AS.
  • חשוב לי להשתמש ב json_unquote על מנת שהעמודה לא תכיל מירכאות - וכך אוכל לבצע חיפוש יעיל מול האינדקס.
  • הביטוי GENERATED ALWAYS הוא רשות - ועוזר להבליט את העובדה שמדובר ב generated column - עבור קוראים עתידיים.


תזכורת חשובה: אם השאילתה עושה פעולה כלשהי על שדה מאונדקס (נאמר: state) - לא יהיה שימוש באינדקס. למשל:

... 
WHERE my_state = json_unquote(state)

לא טוב!

השינוי הבא יאפשר שימוש באינדקס (כי את הערך "כפי שהוא" ניתן להשוות בינרית לאינדקס):

... 
WHERE json_quote(my_state) = state

אבל הכי טוב זה להכין את האינדקס מראש בצורה הכי "טבעית" לשאילתה שאפשר - וכך לחסוך טעויות מרגיזות.


בגדול יש שני סוגים של generated columns:
  • Virtual - המחושב בזמן הגישה לרשומה. 
    • בסיס הנתונים ישמור את הביטוי החישובי (expression) כ metadata על הסכמה, ובכל גישה לרשומה / הפעלת trigger - יתבצע החישוב מחדש. קונספט דומה מאוד ל "calculated field" במערכות BI.
    • אנו חוסכים מקום בדיסק - אבל מכבידים על ה CPU בכל גישה. 
    • זהו ערך ברירת המחדל - אם לא ציינתם כלום.
  • Stored - הערכת הביטוי תבוצע בזמן יצירה / עדכון של הרשומה, וישמר לדיסק כמו עמודות אחרות.
    • יש מחיר בנפח אכסון, אך לרוב - הרבה פחות עבודת CPU.
    • זו הגישה הטבעית בעבודה עם json.


לצורך העניין generated column יכול לשמש לצרכים נוספים מלבד json.

למשל:

CREATE TABLE periods 
  id VARCHAR(32), 
  start_date_millis INT, # WARN: https://en.wikipedia.org/wiki/Year_2038_problem  
  end_date_millis INT,   # WARN: https://en.wikipedia.org/wiki/Year_2038_problem  
  start_date_sec AS (state_date_millis * 1000) VIRTUAL 
;

יש כמה מגבלות על שימוש ב generated columns שכדאי להכיר. בביטוי של עמודה מחוללת לא ניתן להשתמש ב:
  • פונקציות לא דטרמיניסטיות - כאלו שיציגו ערכים שונים אם הופעלו פעם שניה. למשל: ()now או ()current_user.
  • sub-queries
  • Parameters / Variables או User Defined Functions.
  • עמודת AUTO_GENERATED או תכונת AUTO_INCREMENT.
  • שדה generated יכול להיות מבוסס רק על שדות generated המופיעים לפניו.
  • לא ניתן להשתמש על ה stored generated column באילוצים של FK מסוגי ON_UPDATE / ON_DELETE

עוד פרט מעניין: ניתן להשתמש באינדקס (משני) גם על virtual generated column מעל innoDB. הוא יהיה מסוג BTree בלבד (כלומר: לא FULLTEXT או GIS).

בהוספת האינדקס ייתכן שתצטרכו להוסיף הוראת WITH VALIDATION - על מנת לא לקבל שגיאה שאורך השדה המחושב שהגדרתם הוא קטן מדי.



קצת יותר על השימוש בפונקציות ה JSON של MySQL



התבקשתי ע״י קורא לפרט קצת יותר איך עם פונקציות ה json של MySQLשהזכרתי בפוסט הקודם. אתן דוגמה מבוססת על מקרה שהשתמשתי בו לאחרונה.


רקע: אני רוצה לבצע מיגרציה לשם של אלמנט בתוך מבנה json שאנו עושים בו שימוש. שם האלמנט היום הוא breakdown אבל השם לא כ"כ נכון - ואני רוצה לשנות אותו ל amounts. הנה דוגמה פשוטה למבנה ה json הקיים לפני השינוי:

[{"policyId":"policy_id","lob":"GL","breakdown":{"PREMIUM":{"amount":423.17}}}]  

וכך אני רוצה שמבנה יראה לאחר השינוי:

[{"policyId":"policy_id","lob":"GL","amounts":{"PREMIUM":{"amount":423.17}}}] 


איך לעזאזל ניגשים למיגרציה "עמוקה" בתוך שדה json? האם זה חייב להיות פרויקט מורכב?

ברור שלא.

אז הנה אני אעשה את השינוי בכמה שלבים פשוטים:

ראשית, אני רוצה ליצור שדות כפולים: גם breakdown וגם amounts - כי באופן טבעי בזמן deployment ירוצו 2 גרסאות של הקוד (גרסה חדשה שמחפשת אחר amounts וגרסה ישנה שמחפשת אחר breakdown). אני צריך להתכונן לכפילות הזו.
אני דואג לכך שבזמן ה deploy (כמה דקות) - לא יווצרו רשומות חדשות (במקרים אחרים ניתן למצוא אותן בעזרת זמן יצירה, ולתקן ידנית אח״כ). 

בניית שאילתה של SQL היא עניין של ניסוי וטעיה, והדבר הזה נכון גם בעבודה עם  json.

אני מתחיל בשאילתה פשוטה של SELECT על מנת לראות אם קלעתי נכון ב query.
בחרתי לי רשומה שהמפתח שלה הוא '009277a371b8c3def40996a754085030' על מנת לבצע את הניסויים ב scale קטן.

SELECT Json_insert(financial_attribution, '$[0].foo', 2) 
FROM   `payments` 
WHERE  id = '009277a371b8c3def40996a754085030'; 

במבט ראשון זה קצת מבלבל שאני משתמש ב ()JSON_INSERT בפקודת SELECT.
מה קורה כאן?

אני שולף שדה json בשם `financial_attribution` ואז מבצע עליו מניפולציה בזיכרון. המניפולציה שבחרתי היא הכנסה של ערך. מפתח מוזר בשם foo עם ערך של 2. רק לצורך הבדיקה. 

הנה התוצאה:

[{"foo": 2, "lob": "GL", "policyId": "policy_id", "breakdown": {"PREMIUM": {"amount": 423.17}}}]   

השדה נוסף בהצלחה (לי לקח ניסיון או שניים עד שזה עבד) - אך שום מידע לא השתנה בבסיס הנתונים.

עכשיו ננסה משהו יותר אמיתי:

SELECT Json_insert(financial_attribution, '$[0].amounts'                   financial_attribution -> '$[0].breakdown') 
FROM   `payments` 
WHERE  id = '009277a371b8c3def40996a754085030'; 


לקחתי מתוך ה json את אובייקט ה breakdown - והכנסתי אותו בחזרה כ amounts:

[{"lob": "GL", "amounts": {"PREMIUM": {"amount": 423.17}}, "policyId": "policy_id", "breakdown": {"PREMIUM": {"amount": 423.17}}}] 


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

אנסה בצד דוגמה שגם שאילתת ה update תקינה:

UPDATE `payments` 
SET    `financial_attribution` = Json_insert(`financial_attribution`, '$[0].amounts', 
                                 financial_attribution -> '$[0].breakdown') 
; 

ואז הוסיף אותה ל db migrations של השינוי.

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

עוד דרך מהירה (ולעתים יותר יעילה) לבצע תיקונים ב jsons היא בעזרת ()REPLACE פשוט על טקסט - אבל חשוב מאוד להיזהר מהחלפות לא-צפויות.



השלמות ליכולות ה json של MySQL 5.7


אם אתם זוכרים את הפוסט הקודם - הבטחתי לכם workarounds למחסור של MySQL 5.7 ב-2 פונקציות שימושיות בעבודה עם json.

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



קריאת הערך האחרון במערך שבתוך json


אני רוצה לקבל את השדה האחרון במערך שבתוך json:

SELECT JSON_EXTRACT(`from`,CONCAT("$[",JSON_LENGTH(`from`)-1,"]")) FROM `table`;


זה קצת תרגיל: אני מוצא את אורך המערך (במקרה שלנו, תחת תכונה בשם from) ואז מרכיב שאילתה בעזרת ()CONCAT - ומריץ אתה. הנה קלט לדוגמה שעליו תעבוד השאילתה:

{ from: ["a","b","c"], "to": [ "d", "e" ] }

התוצאה תהיה ״c״.

הנה דוגמה ב DB Fiddle שאפשר קצת ״לשחק״ איתה:


שימו לב ל-2 מקרי-קצה בתחתית הדוגמה.
לי זה מעולם לא הפריע, כי עבדתי עם מבנים ידועים שהם מערך - אבל אפשר גם לבנות פונקציה מורכבת יותר, ולהתגונן בפני המקרים הללו (או פשוט לשדרג ל MySQL 8).




גרסה מאולתרת ל json_each - טיפול בכל איבר במערך שבתוך json


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

הנה דוגמה לשאילתה כזו:

select  n, 
        JSON_EXTRACT(`data`, concat('$.items[', n - 1, '].price')) as price 
from `my_table` 
  join numbers on (n <= JSON_LENGTH(`data`->'$.items') and n > 0) 
;

כאשר התוצאה נראית כך:


n הוא מספר האיבר ברשימה (אינדקס על בסיס 1), ו price הוא... המחיר.

והנתונים נראים כך:


עשינו join עם טבלה של מספרים (0-255 לרוב מספיקה) ואז, עבור כל מספר באורך המערך של ה items - ביצענו פעולת שליפה מתוך ה json על המקום הזה, בהתבסס על ״תרגיל״ ה CONCAT שהשתמשנו בו קודם לכן.

הכי-אלגנטי-בעולם? - לא.
עובד ושימושי - כן!

הנה אתם יכולים לשחק ב fiddle שיצרתי לקוד הזה: https://www.db-fiddle.com/f/dmA8af4CHJ3xkx4fzV99Zw/0


בוודאי שמתם לב למבנה קצת לא-שגרתי, שנועד בכדי ליצור את ה View המספק את המספרים. גם כאן יש תרגיל ״מוכר״ בעולם של MySQL:

CREATE OR REPLACE VIEW generator_16 
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL 
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL 
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL 
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15 
; 

CREATE OR REPLACE VIEW numbers
AS SELECT ( hi.n * 16 + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi
;


עזבו לרגע את המבנה העמוס. אם תקראו את הקוד - הוא נראה מאוד הגיוני.
מדוע הסרבול הזה? האם אין דרך יותר פשוטה? - הנה פוסט יפה עם כל הפרטים.

אפשר למצוא את Fiddle ממקוד של ה generator על מנת ״לשחק״ איתו: https://www.db-fiddle.com/f/jCRetSiTaKqz5SUiQQG8Py/0




סיכום


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


שיהיה בהצלחה!




2018-06-30

נושאים מתקדמים ב MySQL: חלק א' - עבודה עם JSON

MySQL הוא בסיס-נתונים פשוט.

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

גרסה 8 שיצאה לא מכבר הייתה עשויה להיקרא גרסה 5.8 - היא הוסיפה כמות נאה של תוספות, אבל בוודאי לא מהפיכה. (במיוחד לאחר שכמה שינויים זכו ל down-port לגרסה 5.7). לא ניתן להשוות אותה לחידושים של גרסה 5.

MySQL עדיין בסיס הנתונים הפופולרי ביותר בעולם אחרי Oracle המסחרי, ובפער גדול גם על PostgreSQL שזכה לצמיחה יפה בשנים האחרונות. MariaDB - ה fork של MySQL שמשוחרר מההשפעה של חברת אורקל, נמצא במקום 13 ברשימה למטה, ואפשר להחשיב אותו כעוד פלח-שוק של MySQL - וכנראה כמחליף העתידי.


מקור: DB-engines.com


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

הרבה פעמים נתקלתי בטיעונים שעדיף להשתמש ב MongoDB או PostgreSQL על פני MySQL.
כשניסיתי לחקור מדוע, קיבלתי מגוון תשובות שלא שיכנעו אותי:
  • "בסיסי-נתונים רלציונים הם העבר"
  • "ל PostgresSQL יש יותר שיטות לבצע join - אז הביצועים שלו טובים יותר"
  • "בכנס חשוב-כלשהו היו 3 הרצאות על PostgreSQL ורק אחת על MySQL"
  • "ל MySQL אין רפליקציה טובה (כמו ל Mongo, לכאורה)". "הוא לא בנוי ל Scale".
  • "ל Postgres יש פי 3 יכולות מאשר ל MySQL".
אלו דוגמאות לטיעונים לא לא עקרוניים. יש הרבה רצון לחדש ולעבוד עם "בסיס נתונים חדש יותר" - אבל גם המוכר והלא buzzy יכול להיות מוצלח מאוד.

נוכחתי לאורך הקריירה בכמה יוזמות אימוץ של "בסיס נתונים מתקדם יותר" - שנגמרו במפח-נפש.

שלא תבינו לא נכון: PostgreSQL ו MongoDB (ועוד אחרים) הם Databases מרשימים וטובים - אבל גם להם יש חסרונות, ואם אתם עושים מעבר - חשוב מאוד שתהינה לכן סיבות עמוקות ומבוססות. חבל להשקיע חודשים במעבר ואז לגלות שחיסרון חדש מעיב על כל המאמץ שהושקע. מעבר של בסיס נתונים במערכת "חיה" הוא שינוי לא-קל. הכלל הזה נכון גם לגבי מעבר ל MySQL - כמובן.


דיאגרמה (לא בהכרח מאלה) של Databases בתחום הרלציוני. מקור: 451 Research.


בכמה פוסטים הקרובים הייתי רוצה להתעמק קצת ביכולות של MySQL. יחסית לפופולריות השימוש שבו, MySQL לא "מסופר" בצורה טובה , כבר כמה שנים טובות. הרבה יכולות טובות נוספו בכמה שנים הללו - אך נראה שחלק נכבד מקהל השמתמשים לא מכיר אותן.
אמנם עכשיו עם השחרור של MySQL גרסה 8, הולכים ויוצאים ספרים חדשים - אך חלק גדול מהפוקוס שלהם הוא על מה שחדש בגרסה החדשה, ופחות במה שאפשר לעשות עם גרסה 5.7 - שהיא כנראה תישאר הגרסה הדומיננטית בשנתיים הקרובות (בסיס נתונים לא משדרגים כ"כ מהר - וטוב שכך).

הנה רשימת של נושאים שנראים לי מעניינים:
  • עבודה עם JSON וה Document Store.
  • Generated columns
  • פיצוי על יכולות חסרות ב MySQL כמו json_each או fist_value/last_value - איך אפשר להסתדר בלעדיהם.
  • מנועי Storage וההבדלים ביניהם: InnoDB ל MyISAM, וכו' (לא חדש בכלל - אך ידע חסר, ממה שנתקלתי).
  • סטטיסטיקות של אינדקסים וטבלאות - ואיך זה משפיע עלינו (גם לא חדש).
  • Full Text indexes
  • Partitioning
  • Large Hadron Migrator- https://github.com/soundcloud/lhm, ביצוע migrations גדולים ללא downtime.
כל הנושאים הנ"ל הם נושאים שלי יצא באופן אישי, או לאנשים מסביבי להשתמש בהם. הם נושאים ישימים ופרקטיים - עד כמה שאני יכול לומר.

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

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



JSON וה MySql Document Store


לפני כעשור, עולם ה Databases התחלק ל-2 קבוצות דומיננטיות של שימוש עיקרי:
  • בסיסי-נתונים רלציוניים
  • בסיסי-נתונים מבוססי-מסמכים (Document-Based)
* נכון, יש גם K/V DB שנמצאים בשימוש נרחב, וגם columnar, wide-column, וגם graph ו time series - אך עדיין בסיסי נתונים רלציוניים ו document-based אחראים למגוון הגדול יותר של הנתונים והשימושים.
ההרכב הנפוץ הוא שארגון מחזיק את רוב האובייקטים בבסיס נתונים רלציוני / מסמכים, והיכן שצריך scale גבוה יותר - פונה לפתרונות יותר ממוקדים לצורך הספציפי.

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

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

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

בסיסי הנתונים הרלציוניים, החלו לספק גם יכולות של ניהול מסמכים (או JSON snippets, לפחות. ה"מסמכים" הכי שימושיים), והיום יש יכולות Document כאלו ואחרות ברוב בסיסי-הנתונים הרלציוניים המוכרים. השילוב הזה - מאוד מוצלח, לטעמי.

מאז MySQL גרסה 5.7.12 (אמצע 2016, בעזרת plugin) יש ל MySQL ממשק עבודה שדומה מאוד לעבודה מול בסיס-נתונים מבוסס מסמכים, מה שנקרא The MySQL Document Store:
  • "מסמכים" (כלומר: JSON), מאוחסנים ב Collections.
    • מאחורי הקלעים, לאכסון collection של מסמכים, נוצרות טבלאות בנות שתי-עמודות id_ ו doc (כמו שהיינו עושים פעם, בעצמנו...)
  • בעזרת API (או ה shell החדש, mysqlsh) ניתן לגשת ל"מסמכים" ב API המוכר מבסיסי-נתונים מבוססי-מסמכים. למשל:
    • ("db.product.find('_id = "iphone XI
    • (...)db.createCollection
    • (...)add(...), sort(...), modify, וכו'
  • את המסמכים ניתן לאנקס
    • מאחורי הקלעים MySQL יוצר generated columns - נושא שארצה לכסות בהרחבה.
  • ל API יש clients זמינים ל JavaScript ול Python - אם כי כמה פעולות, תחזוקה וטיפול בעיקר, עדיין יש לעשות ב SQL.

אף פעם לא "נפלתי" מהממשק של ה Documents Stores ולכן מעולם משך אותי לנסות ולהשתמש ב MySQL Document Store. 

אני אישית מעדיף בהרבה לעבוד בסגנון מעורב (רלציוני-Document).

הייתי שמח מאוד להיות מסוגל לעשות מניפולציות על json ב js או פייטון המקונן בתוך שאילתת SQL - אך לצערי לא נראה שהשוק הולך לשם...
עדכון: תודה לנדב נווה שעדכון אותי שכן יש plugin ל User Defined Functions ב js עבור MySQL. מעניין!
עדיין, לא נראה שה plugin הזה נתמך ע"י AWS RDS. חבל...



JSON ב MySQL דרך SQL


column מטיפוס JSON הוסף ב MySQL גרסה 5.7.8 (אוג' 2015), אם כי ניתן להשתמש ביכולות ה JSON שנוספו לבסיס הנתונים גם על גבי עמודות מסוג text, varchar, BLOB וכו'. עמודות טקסטואליות.

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

יתרון נוסף בשימוש ב"מסמך" JSON הוא האטומיות: הכל משתנה ביחד - הכל, או לא כלום.
בקבוצה של סכמות יהיה עליכם להתעסק עם טרנזקציות בכדי לקבל הגנה בפני 2 תהליכים שמשנים באופן לא-עקבי שני חלקים של האובייקט. טרנזקציות על עדכון של שורה של טבלאות (פעם ראיתי אובייקט שמופה ל 35 טבלאות שונות בבסיס הנתונים) - הוא לא דבר פשוט.

החיסרון הגדול של JSON הוא הקושי לבצע שאילתות הנוגעות לערכים המקוננים בתוך ה JSON והביצועים שנובעים מכך:
  • יש ב MySQL תחביר המאפשר לאמוד אם שדה x בתוך אובייקט o שבתוך ה JSON כחלק מפקודת WHERE.
    כמו שנראה בהמשך, כשמבנה ה json הוא מורכב יותר - זה הולך והופך להיות קשה יותר.
  • ביצועים: כאשר אנו רוצים להשוות שדה אחד מתוך אובייקט עם 50 שדות - עלינו לטעון לזיכרון את כל 50 השדות בכל פעם, שזה הרבה I/O מיותר (מדד חשוב מאוד בביצועים של בסיסי-נתונים).
    הגישה המקובלת להתמודד עם בעיית הביצועים היא להוציא לעמודות מקבילות לעמודת ה JSON "שכפול" של שדות אותן נרצה לתשאל בצורה תדירה (ולכן גם לאנדקס).
    בהמשך נראה כיצד MySQL יכול לסייע להפוך לעשות את התהליך הזה לפשוט יותר בעזרת Generated Columns.

טיפוס ה json ב MySQL שונה מ text בשתי תכונות עיקריות:
  1. בהכנסת ערך לעמודה מסוג json - בסיס הנתונים יוודא את תקינות ה format של ה json.
  2. בעמודה מסוג json ולא text - בסיס הנתונים ידחוס את ה json לפורמט בינארי דחוס יותר, בו המפתחות ממוינים (בדומה לפורמט bson שנעשה בו שימוש ב MongoDB).
json תקין הוא כמובן אובייקט ({}), מערך ([]), מחרוזת (""), או null.

שווה לציין שמחרוזת ה json שהוכנסה לעמודה מסוג json לא תחזור כמחרוזת זהה: במעבר לפורמט בינארי ינקו את ה whitespace וסדר המפתחות ישתנה.
כמו כן, אם יש אובייקטים עם מפתחות "כפולים" - אזי המפתח הראשון הוא זה שישמר, עד לגרסה 8.0.3 ממנה המפתח האחרון הוא זה שישמר (מה שיותר עקבי עם רוב המימושים של javaScript).
כל עוד אנחנו עובדים עם json בצורה תקינה - זה פרט שלא נשים אליו לב.

טיפוס ה json של mySQL הוא optimized לקריאות, כך שאם אנחנו הולכים לכתוב יותר (למשל: audit) - יכול להיות שיהיה עדיף, מבחינת ביצועים, להשתמש בעמודה מסוג text.


הפקודה הבסיסית בעבודה עם json ב MySQL היא JSON_EXTRACT:

SELECT c, JSON_EXTRACT(c, "$.id"), g 
FROM some_table
WHERE JSON_EXTRACT(c, "$.id") > 1 
ORDER BY JSON_EXTRACT(c, "$.name");

+-------------------------------+-----------+------+ | c | c->"$.id" | g | +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | | {"id": "4", "name": "Betty"} | "4" | 4 | | {"id": "2", "name": "Wilma"} | "2" | 2 | +-------------------------------+-----------+------+



יש גם תחביר מקוצר:

SELECT c, c->>'$.id', g 
FROM some_table 
WHERE c->"$.id" > 1 
ORDER BY c->'$.name';

+-------------------------------+-----------+------+ | c | c->"$.id" | g | +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | 3 | 3 | | {"id": "4", "name": "Betty"} | 4 | 4 | | {"id": "2", "name": "Wilma"} | 2 | 2 | +-------------------------------+-----------+------+


כאשר <<- הוא תחליף ל  ((JSON_UNQUOTE( JSON_EXTRACT(column, path. הפונקציה JSON_UNQUOTE מסירה את ה quotes - אם קיימים.

ניתן להשתמש בביטויים מורכבים יותר כמו 'column->'$[2].person.pets[1].name
  • את כל הביטוי יש לעטוף במירכאות בודדות או כפולות - כי זו מחרוזת ב SQL.
  • יש לציין את ה $ - המתאר את ה root של ה json (לפי תקן ה json path - ה $ נקרא "context").
  • כאשר יש שמות של keys המשתמשים בסימנים מסוימים - יש לעטוף אותם ב quotes, למשל:
     'column->'$[2].person."my-pets"[1].name
  • ניתן להשתמש ב * בכמה מצבים:
    • [*]$ - יחזיר את כל האיברים במערך (או null אם הפעלתם על אובייקט או מחרוזת)
    • price.*.$ יחזיר מערך של כל שדות ה price בכל האובייקטים שבתוך העמודה.
    • price.**.$ יחזיר מערך של כל שדות ה price בכל האובייקטים, או תתי-האובייקטים, שבתוך העמודה.
  • יש פונקציות כגון ()JSON_KEYS ו ()JSON_SEARCH - שיחזירו בהתאמה את רשימת ה keys באובייקט, או רשימת האובייקטים המכילים ערכים מסוימים.

יש פעולות שלא ניתן להשיג בעזרת ה path כפי שמתאפשר היום ב MySQL 5.7.x. דוגמה נפוצה בשימוש: בחירת האיבר האחרון מתוך רשימה, או פעולות מיון / סינון על מפתחות מסוימים.

תמיד ניתן לעשות את הניתוח ברמה האפליקטיבית, שם ה json הוא "כחומר ביד היוצר" אם כי עבור שאילתות ניתוח /ואו כלי BI - עדיין יהיה שימושי מאוד להיות מסוגלים לעשות את כל הניתוחים בשפת SQL.

יכולות ה JSON של PostgreSQL הן מתקדמות יותר משל MySQL - אך נראה ש PostgreSQL הוא פשוט פחות סטנדרטי. מקור/2016.


ישנן עוד סדרה של פונקציות המאפשרות פעולות על json ב MySQL - אני רק אזכיר אותן בקצרה, בידיעה שתמיד אפשר לפנות לתיעוד (שהוא ברמה טובה):
  • יצירה של אובייקטי json כחלק משאילתה, בעזרת הפונקציות ()JSON_ARRAY(), JSON_OBJECT ו ()JSON_MERGE_PRESERVE.
  • שינוי של ה json מתוך SQL בעזרת הפונקציות:
    JSON_APPEND(), JSON_ARRAY_APPEND() JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET.
  • פונקציות עזר שימושיות הן:
    ()JSON_UNQUOTE(), JSON_QUOTE(), JSON_DEPTH(), JSON_PRETTY(), JSON_LENGTH(), JSON_TYPE ו ()JSON_VALID

פונקציה שלי מאוד חסרה ב MySQL אך קיימת ב PostgreSQL היא json_each ההופכת מערך או זוגות מתוך עמודת json לרשומות רלציוניות עליהן ניתן לבצע פעולות ב SQL שונות.

בפוסט המשך אני אראה "תרגיל" ב SQL בו אני משתמש בכדי לעשות זאת גם על MySQL. 

הערה: יש פתרון לשליפת האיבר האחרון במערך ב MySQL 8 בצורת:
JSON_EXTRACT(JsonData, '$[last]')

או שליפת האיבר לפני האחרון בעזרת last-1.
אני אראה גם "תרגיל" איך ניתן לעשות זאת גם בגרסה 5.7, וללא התמיכה של operator ה last.



סיכום


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


שיהיה בהצלחה!