کار با دیتاتایپ JSON در MySQL

این نوشته نخستین بار در دات‌نت تیپس منتشر شده است.

توابع ایجاد محتوای JSON

در قسمت قبل برای ذخیره‌سازی محتوای JSON از string literal استفاده کردیم؛ یعنی در واقع همانند یک مقدار رشته‌ای، فیلد JSON را مقداردهی کردیم:

INSERT INTO tableName VALUES (
'{ "name": "User1", "age": 41 }'
);

یک روش دیگر، استفاده از توابع JSON_OBJECT یا JSON_ARRAY میباشد:

INSERT INTO tableName VALUES (
 JSON_ARRAY(
 JSON_OBJECT(
 "id", 1,
 "name", "User1",
 "age", 31,
 "skills", JSON_ARRAY("JS", "DB", "Git"),
 "address", JSON_OBJECT(
"country", "Iran",
"city", "Tehran")
 ),
 JSON_OBJECT(
   "id", 2,
   "name", "User2",
   "age", 31,
   "skills", JSON_ARRAY("C#"),
   "address", JSON_OBJECT(
 "country", "Iran",
 "city", "Sanandaj"
   )
 )
 )
);

در ادامه با یکسری از توابع دیگر کار با آرایه‌ها و بطور کلی با توابعی جهت تغییر محتوای JSON آشنا خواهیم شد.

JSON_ARRAY_APPEND

فرض کنید برای کاربر User2 میخواهیم یک آیتم به پراپرتی skills اضافه کنیم. برای اینکار میتوانیم از تابع JSON_ARRAY_APPEND استفاده کنیم:

UPDATE experiments.tableName 
SET jsonData = JSON_ARRAY_APPEND(jsonData,
              '$[1].skills',
              'JS',
              '$[1].skills',
              'DB',
              '$[1].skills',
              'Kotlin'
            )

-- ["C#", "JS", "DB", "Kotlin"]

JSON_ARRAY_INSERT

این تابع نیز شبیه تابع قبلی است؛ با این تفاوت که به جای append کردن مقداری به آخر لیست، میتوانیم این مقدار جدید را در مکان مورد نظر اضافه کنیم:

UPDATE experiments.tableName 
SET 
    jsonData = JSON_ARRAY_INSERT(jsonData, '$[1].skills[4]', 'TS')
    
-- ["C#", "JS", "DB", "Kotlin", "TS"]

JSON_INSERT

از این تابع جهت درج یک مقدار جدید به محتوای JSON استفاده میشود. دقت داشته باشید که این تابع مقادیر موجود را overwrite نخواهد کرد و فقط در صورت عدم وجود آن key، مقدار را اضافه میکند:

UPDATE experiments.tableName 
SET 
    jsonData = JSON_INSERT(jsonData,
            '$[1].address.location',
            JSON_OBJECT('phone', 8989898))

JSON_REPLACE

از این تابع جهت جایگزینی مقادیر استفاده خواهد شد. به عنوان مثال میتوانیم محتوای قبلی را اینگونه به روز کنیم:

UPDATE experiments.tableName 
SET 
    jsonData = JSON_REPLACE(jsonData,
            '$[1].address.location.phone',
            12345656)

JSON_REMOVE

از این تابع میتوانیم جهت حذف یک مقدار، یا پراپرتی خاصی استفاده کنیم:

UPDATE experiments.tableName 
SET 
    jsonData = JSON_REMOVE(jsonData, '$[1].address')

JSON_SET

توسط این تابع میتوانیم دیتایی را به محتوای JSON، اضافه یا به‌روزرسانی کنیم. این تابع همانند JSON_INSERT عمل میکند؛ با این تفاوت که در صورت وجود path، مقدار را overwrite خواهد کرد، در غیراینصورت مقدار جدید را اضافه می‌کند:

UPDATE experiments.tableName 
SET 
    jsonData = JSON_SET(jsonData,
              '$[1].address',
              JSON_OBJECT('country',
                      'Iran',
                      'city',
                      '-',
                      'phone',
                      12345
              ));

/*
  { location: { "city": "-", "phone": 12345, "country": "Iran" } }
*/

UPDATE experiments.tableName 
SET 
    jsonData = JSON_SET(jsonData,
            '$[1].address.city',
            'Tehran');

/*
  { location: { "city": "-", "phone": 12345, "country": "Iran" } }
*/


UPDATE experiments.tableName 
SET jsonData = JSON_SET(jsonData, '$[1].address.postcode', '0098');

/*
  { location: {"city": "Tehran", "phone": 12345, "country": "Iran", "postcode": '0098' } }
*/

JSON_UNQUOTE

توسط این تابع میتوانیم خروجی را به صورت unquote شده ببینیم. بدون استفاده از این تابع، خروجی داخل quotation میباشد:

SELECT 
    JSON_EXTRACT(jsonData, '$[1].address.city')
FROM
    experiments.tableName;
    
-- "Tehran"

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(jsonData, '$[1].address.city'))
FROM
    experiments.tableName;
    
-- Tehran

همانطور که مشاهده میکنید از تابع JSON_EXTRACT برای کوئری گرفتن از پراپرتی city استفاده کرده‌ایم. خروجی تابع را نیز به JSON_UNQUOTE جهت حذف quotation ارسال کرده‌ایم. یک سینتکس دیگر نیز برای خلاصه‌سازی JSON_EXTRACT وجود دارد:

SELECT 
    jsonData -> '$[1].address.city'
FROM
    experiments.tableName;

-- "Tehran"

همچنین برای حذف quoteها میتوانیم اپراتور فوق را اینگونه بنویسیم که همان کار تابع JSON_UNQUOTE را انجام میدهد:

SELECT 
    jsonData ->> '$[1].address.city'
FROM
    experiments.tableName;

-- Tehran

نکته: هر دو حالت را میتوانیم در قسمت WHERE نیز استفاده کنیم:

SELECT 
    jsonData ->> '$[1].address.city'
FROM
    experiments.tableName
WHERE jsonData ->> '$[1].address.city' = 'Tehran';

ادغام محتوای JSON با یکدیگر

در MySQL دو تابع با نامهای JSON_MERGE_PATCH و JSON_MERGE_PRESERVE برای ادغام دو یا چند محتوای JSON وجود دارد. تابع JSON_MERGE_PRESERVE همانطور که از نامش پیداست، مقادیر را نگه میدارد؛ یعنی کلیدهای یکسان را با هم ادغام میکند و مقادیر را به صورت آرایه به عنوان valueی شیء در نظر میگیرد:

SELECT 
    JSON_MERGE_PRESERVE('{
                "id": "1",
                "name": "Product One",
                "price": 12.45,
                "discount": 10,
                "rating": 4,
                "category": ["fashion", "men"],
                "tags": ["fashion", "men", "jacket", "full sleeve"]
            }',
            '{
                "id": "2",
                "name": "Product Two",
                "price": 30,
                "discount": 0,
                "rating": 3,
                "category": ["fashion", "men"],
                "tags": ["fashion", "men", "jacket", "full sleeve"]
            }');

خروجی کوئری فوق به اینصورت خواهد بود:

{
  "id": ["1", "2"],
  "name": ["Product One", "Product Two"],
  "tags": [
    "fashion",
    "men",
    "jacket",
    "full sleeve",
    "fashion",
    "men",
    "jacket",
    "full sleeve"
  ],
  "price": [12.45, 30],
  "rating": [4, 3],
  "category": ["fashion", "men", "fashion", "men"],
  "discount": [10, 0]
}

اما تابع JSON_MERGE_PATCH در نهایت یک خروجی را خواهد داشت؛ کاری که انجام میدهد به‌روزرسانی (patch) مقدار جدید، با مقدار قبلی است. یعنی کلیدهای آبجکت اول را که در آبجکت دوم قرار دارند، حذف میکند. همچنین کلیدهای جدید را در شیء یکی شده‌ی نهایی نیز اضافه خواهد کرد. به عنوان مثال برای آبجکت اول، یک پراپرتی جدید را با نام sku اضافه کرده‌ایم:

SELECT JSON_MERGE_PATCH('{
                "id": "1",
                "name": "Product One",
                "price": 12.45,
                "discount": 10,
                "rating": 4,
                "category": ["fashion", "men"],
                "tags": ["fashion", "men", "jacket", "full sleeve"],
                "sku": "asdf123"
            }',
            '{
                "id": "2",
                "name": "Product Two",
                "price": 30,
                "discount": 0,
                "rating": 3,
                "category": ["fashion", "men"],
                "tags": ["fashion", "men", "jacket", "full sleeve"]
            }');

خروجی کوئری فوق این چنین خواهد بود:

{
  "id": "2",
  "sku": "asdf123",
  "name": "Product Two",
  "tags": ["fashion", "men", "jacket", "full sleeve"],
  "price": 30,
  "rating": 3,
  "category": ["fashion", "men"],
  "discount": 0
}