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

לכל מדריכי ה- mySQL

אהבתם? לא אהבתם? דרגו!

0 הצבעות, ממוצע 0 מתוך 5 כוכבים