CRUD עם סוג הנתונים JSON במסד נתונים mySQL - שאילתות לקריאה, הכנסה, עדכון ומחיקה
mySQL מצויד ביכולת לטפל בסוג הנתונים JSON שמעניק גמישות רבה, בדומה לסכמות של NoSQL. במדריך קצר זה הסבר על עבודה עם JSON: כתיבה למסד הנתונים, קריאה, עדכון ומחיקה.
נוסיף את הטבלה
נוסיף את הטבלה הבאה למסד הנתונים:
CREATE TABLE `cars` (
`id` int(11) UNSIGNED NOT NULL,
`name` varchar(255) DEFAULT '',
`attributes` JSON DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
ALTER TABLE `cars`
ADD PRIMARY KEY (`id`);
ALTER TABLE `cars`
MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
- סוג הנתונים הוא JSON.
- הערך ברירת המחדל היחיד שהוא יכול לקבל הוא NULL (באותיות גדולות).
כיצד להזין רשומה חדשה הכוללת JSON?
דרך אחת, היא לרשום את ה-JSON על פי הסכמה המדויקת (json.org):
INSERT INTO `cars`(`id`, `name`, `data`)
VALUES
(NULL, 'BMW R80G/S','{"class":"dual-sport","wheelbase":"57.7 in", "weight":186, "is_motorcycle":true, "steering wheel":null, "dimensions":[2230,820,1150]}');
לאחר ההזנה, כשאני עומד עם הסמן בתוך השדה אני יכול לראות את ה-JSON שהזנתי:
{ "class": "dual-sport", "weight": 186, "wheelbase": "57.7 in", "dimensions": [ 2230, 820, 1150 ], "is_motorcycle": true, "steering wheel": null }
חשוב להקפיד על הפורמט המדויק:
- את אובייקט ה- JSON חובה להקיף בסוגריים מסולסלים.
- את המפתחות צריך לשים בין מרכאות כפולות.
- את סוגי הנתונים: true, false, null חייבים לכתוב באותיות קטנות.
- את הסוג מחרוזת מקיפים מרכאות כפולות.
- האובייקט יכול להכיל מערכים כמו גם אובייקטים מקוננים של JSON.
הזנה של JSON בשיטה הידנית עשויה להיות מייגעת ורגישה לטעויות, לפיכך נעדיף להשתמש בפונקציה JSON_OBJECT כדי להזין את המידע באופן שיטתי ומסודר.
הפונקציה JSON_OBJECT מקבלת מפתחות וערכים מופרדים בפסיקים ע"פ התחביר:
JSON_OBJECT(key1, value1, key2, value2, ...)
ומחזירה אובייקט JSON.
INSERT INTO `cars`(`id`, `name`, `data`)
VALUES
(NULL, 'BMW R80G/S',
JSON_OBJECT(
"class",
"dual-sport",
"wheelbase" ,
"57.7 in",
"weight",
186,
"is_motorcycle",
true,
"steering wheel",
null,
"dimensions",
JSON_ARRAY(2230,820,1150)
)
);
הפונקציה JSON_ARRAY יוצרת רשימות של פריטים מופרדים בפסיקים.
{ "class": "dual-sport", "weight": 186, "wheelbase": "57.7 in", "dimensions": [ 2230, 820, 1150 ], "is_motorcycle": true, "steering wheel": null }
השאילתה הבאה מדגימה 3 רמות של אובייקטים מקוננים:
INSERT INTO `cars`(`id`, `name`, `data`)
VALUES
(NULL, 'BMW R80G/S',
JSON_OBJECT(
"class",
"dual-sport",
"wheelbase" ,
"57.7 in",
"weight",
186,
"is_motorcycle",
true,
"steering wheel",
null,
"dimensions",
JSON_ARRAY(
JSON_OBJECT(
"length",
2230,
"width",
820,
"height",
1150
)
)
)
);
{ "class": "dual-sport", "weight": 186, "wheelbase": "57.7 in", "dimensions": [ { "width": 820, "height": 1150, "length": 2230 } ], "is_motorcycle": true, "steering wheel": null }
הפונקציה JSON_MERGE לוקחת מספר אובייקטים של JSON ומחזירה אובייקט יחיד:
INSERT INTO `cars`(`id`, `name`, `data`)
VALUES
(NULL, 'BMW R80G/S',
JSON_OBJECT(
"class",
"dual-sport",
"wheelbase" ,
"57.7 in",
"weight",
186,
"is_motorcycle",
true,
"steering wheel",
null,
"dimensions",
JSON_MERGE(
JSON_OBJECT("length",2230),
JSON_OBJECT("width",820),
JSON_OBJECT("height",1150)
)
)
);
{ "class": "dual-sport", "weight": 186, "wheelbase": "57.7 in", "dimensions": { "width": 820, "height": 1150, "length": 2230 }, "is_motorcycle": true, "steering wheel": null }
כיצד לקרוא רשומה?
כדי לקרוא רשומה של JSON ממסד הנתונים חשוב קודם להבין את הרעיון path expression המציין את הנתיב של המפתחות שמובילים לביטוי.
הקריאה בפועל מתבצעת באמצעות הפונקציה JSON_EXTRACT המקבלת path expression.
SELECT * FROM `cars`
WHERE
`name` = 'BMW R80G/S'
AND JSON_EXTRACT(`data` , '$.weight') > 180
AND JSON_EXTRACT(`data` , '$.dimensions.width') > 800
AND JSON_EXTRACT(`data` , '$.wheelbase') = '57.7 in'
AND JSON_EXTRACT(`data` , '$.is_motorcycle') = true;
את JSON_EXTRACT אפשר להחליף בחץ כדי להפוך את השאילתה לקריאה יותר.
SELECT * FROM `cars`
WHERE
`name` = 'BMW R80G/S'
AND `data` -> '$.weight' > 180
AND `data` -> '$.dimensions.width' > 800
AND `data` -> '$.wheelbase' = '57.7 in'
AND `data` -> '$.is_motorcycle' = true
AND JSON_EXTRACT(`data` , '$.class') LIKE '%sport%';
כיצד לעדכן רשומה?
נשתמש בפונקציה JSON_SET כדי לעדכן שדה מסוג JSON.
נוסיף מפתח חדש בזמן שנעדכן רשומה קיימת:
UPDATE `cars`
SET `data` = JSON_SET(
`data`,
'$.dimensions.seat_height' ,
860
)
WHERE `id` = 5
כדי לעדכן מפתח קיים:
UPDATE `cars`
SET `data` = JSON_SET(
`data`,
'$.dimensions.seat_height' ,
null
)
WHERE `id` = 5
כיצד למחוק רשומה?
נשתמש בפונקציה JSON_REMOVE כדי למחוק רשומה ע"פ מפתח:
UPDATE `cars`
SET `data` = JSON_REMOVE(
`data`,
'$.dimensions.seat_height'
)
WHERE `id` = 5
אהבתם? לא אהבתם? דרגו!
0 הצבעות, ממוצע 0 מתוך 5 כוכבים