توابعی را که برای تغییر دیتای JSON مورد استفاده قرار میگیرند، بررسی کردیم. در ادامه به بررسی روش‌های تبدیل یک دیتای غیر JSON به خروجی JSON و بلعکس خواهیم پرداخت.
کوئری‌هایی که تا اینجا نوشته‌ایم، بر روی یک فیلد از نوع JSON بودند؛ اما این امکان را نیز داریم که از توابع JSON بر روی فیلدهای غیر JSON نیز استفاده کنیم. به عنوان مثال جدول کاربران و بلاگ‌پست را در نظر بگیرید که به این صورت تعریف شده‌اند:
جداول فوق یا یکسری دیتای fake پر شده‌اند. اکنون فرض کنید میخواهیم لیست کاربران را به همراه بلاگ‌پست‌هایشان، کوئری بگیریم:
همانطور که میدانید خروجی موردانتظار در یک دیتابیس رابطه‌ایی به صورت سطر و ستون است:
تبدیل یک ساختار Relational به JSON
میتوانیم خروجی موردنظر را در قالب JSON نیز کوئری بگیریم:
SELECT
JSON_OBJECT('id',
U.id,
'user',
CONCAT_WS(' ', U.first_name, U.last_name),
'title',
BP.title)
FROM
Users AS U
JOIN
BlogPosts AS BP ON U.id = BP.user_id;
به عنوان مثال میتوانیم لیست کاربران را به همراه بلاگ پست‌هایشان، اینگونه کوئری بگیریم:
SELECT
JSON_OBJECT('user',
CONCAT_WS(' ', U.first_name, U.last_name),
'blog_posts',
JSON_ARRAYAGG(JSON_OBJECT('id', BP.id, 'title', BP.title))) AS JSON
FROM
Users AS U
JOIN
BlogPosts AS BP ON U.id = BP.user_id
GROUP BY U.id; خروجی کوئری فوق اینچنین خواهد بود:
تبدیل یک ساختار JSON به Relational
همچنین میتوانیم یک ساختار JSON را به صورت Relational تبدیل کنیم. اینکار توسط تابع JSON_TABLE قابل انجام است. کاری که این تابع انجام میدهد، ایجاد یک جدول موقت و کپی کردن دیتای موردنظر درون آن است. فرض کنید ساختار JSON زیر را به اینصورت درون دیتابیس ذخیره کرده‌ایم:
{
"id": "1",
"new": false,
"sku": "asdf123",
"tag": ["fashion", "men", "jacket", "full sleeve"],
"name": "Lorem ipsum jacket",
"image": [
"/assets/img/product/fashion/1.jpg",
"/assets/img/product/fashion/3.jpg",
"/assets/img/product/fashion/6.jpg",
"/assets/img/product/fashion/8.jpg",
"/assets/img/product/fashion/9.jpg"
],
"price": 12.45,
"rating": 4,
"category": ["fashion", "men"],
"discount": 10,
"offerEnd": "October 5, 2020 12:11:00",
"saleCount": 54,
"description": {
"fullDescription": "Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem. Ut enim ad minima veniam, quis nostrum exercitationem ullam corporis suscipit laboriosam, nisi ut aliquid ex ea commodi consequatur? Quis autem vel eum iure reprehenderit qui in ea voluptate velit esse quam nihil molestiae consequatur, vel illum qui dolorem eum fugiat quo voluptas nulla pariatur? Nor again is there anyone who loves or pursues or desires to obtain pain of itself, because it is pain, but because occasionally circumstances occur in which toil and pain can procure him some great pleasure. To take a trivial example, which of us ever undertakes laborious physical exercise, except to obtain some advantage from it? But who has any right to find fault with a man who chooses to enjoy a pleasure that has no annoying consequences, or one who avoids a pain that produces no resultant pleasure?",
"shortDescription": "Ut enim ad minima veniam, quis nostrum exercitationem ullam corporis suscipit laboriosam, nisi ut aliquid ex ea commodi consequatur? Quis autem vel eum iure reprehenderit qui in ea voluptate velit esse quam nihil molestiae consequatur."
}
} اکنون میخواهیم چنین خروجی‌ای داشته باشیم:
کوئری موردنیاز برای تهیه خروجی فوق اینچنین خواهد بود:
SELECT newTable.*
FROM experiments.productMetadata,
JSON_TABLE(
data,
"$" COLUMNS (
id INT PATH "$.id",
new CHAR(5) PATH "$.new",
sku CHAR(20) PATH "$.sku",
price FLOAT PATH "$.price",
rating FLOAT PATH "$.rating",
name CHAR(255) PATH "$.name",
discount FLOAT PATH "$.discount",
offerEnd TEXT PATH "$.offerEnd",
saleCount INT PATH "$.saleCount",
description TEXT PATH "$.description.shortDescription"
)
) AS newTable;
تابع JSON_TABLE دو ورودی نیاز خواهد داشت؛ ورودی اول ستون JSONی است که میخوایم از آن کوئری بگیریم. ورودی دوم با تعیین path شروع خواهد شد. از آنجائیکه محتوای داخل ستون data به صورت آبجکت ذخیره شده‌است، از $ استفاده کرده‌ایم که به معنای داکیومنت جاری است. سپس توسط کلمه کلیدی COLUMNS ساختار جدول موقت‌مان را تعریف خواهیم کرد. این ساختار به صورت یک آرگومان به COLUMNS ارسال خواهد شد و شبیه به ساختار CREATE TABLE است؛ با این تفاوت که بعد از تعریف نوع داده‌ای هر ستون باید مسیر رسیدن به مقدار موردنظر را نیز تعیین کنیم که در واقع همان سینتکس pathی است که در مثالهای قبل نیز بررسی کردیم. به عنوان مثال برای رسیدن به مقدار پراپرتی name، مسیر را به صورت name.$ نوشته‌ایم. این path از آرایه نیز پشتیبانی میکند؛ مثلاً برای دسترسی به عنصر اول آرایه tag کافی است اینگونه عمل کنیم:
tag CHAR(20) PATH "$.tag[0]"
همچنین تابع JSON_TABLE، از ساختارهای تودرتو نیز پشتیبانی میکند. به عنوان مثال برای داشتن مقادیر tag, category, image در خروجی میتوانیم از کلمه کلیدی NESTED استفاده کنیم:
SELECT newTable.*
FROM experiments.productMetadata,
JSON_TABLE(
data,
"$" COLUMNS (
id INT PATH "$.id",
new CHAR(5) PATH "$.new",
sku CHAR(20) PATH "$.sku",
price FLOAT PATH "$.price",
rating FLOAT PATH "$.rating",
NESTED PATH '$.tag[*]' COLUMNS (tag TEXT PATH '$'),
name CHAR(255) PATH "$.name",
discount FLOAT PATH "$.discount",
offerEnd TEXT PATH "$.offerEnd",
saleCount INT PATH "$.saleCount",
description TEXT PATH "$.description.shortDescription",
NESTED PATH '$.image[*]' COLUMNS (image TEXT PATH '$'),
NESTED PATH '$.category[*]' COLUMNS (category TEXT PATH '$')
)
) AS newTable;
کوئری فوق به ازای هر آیتم از آرایه‌های فوق، یک ردیف جدید اضافه خواهد کرد:
درون COLUMNS میتوانیم یک name FOR ORDINALITY نیز تعیین کنیم. این فیلد دقیقاً مشابه AUTO INCREMENT در ساختار CREATE TABLE میباشد. به این معنا که به ازای هر آیتم فیلد nested، یک واحد اضافه خواهد شد. از آن میتوانیم به عنوان rowId برای آیتم آرایه استفاده کنیم:
NESTED PATH '$.category[*]' COLUMNS (categoryRowId FOR ORDINALITY, category TEXT PATH '$')
همچنین از ON EMPTY برای پراپرتی‌هایی که در ساختار JSON وجود ندارند نیز میتوانیم استفاده کنیم. به عنوان مثال در کوئری زیر گفته‌ایم در صورت عدم وجود price، یک مقدار پیش‌فرض باید نمایش داده شود و همچنین در صورت عدم وجود name، یک خطا در خروجی نمایش داده شود:
name CHAR(255) PATH "$.name" ERROR ON EMPTY,
price FLOAT PATH "$.price" DEFAULT "0" ON EMPTY,
همچنین میتوانیم مقدار NULL را در صورت عدم وجود name ست کنیم:
name CHAR(255) PATH "$.name" NULL ON EMPTY,
