mysql: استفاده از داده های json و متنفر نبودن از آن

این یک حقیقت جهانی است که یکی از کارهای اصلی توسعه دهندگان بک اند نوشیدن از firehose json است که فرانت اند مدام به سمت ما اسپری می کنند. به طور معمول، ما همه آن جفت‌های کلید/مقدار را از هم جدا می‌کنیم و آنها را در ستون‌های مرتب‌شده db خود قرار می‌دهیم، و ساختار پایگاه داده‌مان را خوب و عادی نگه می‌داریم. اما گاهی اوقات دلایل خوبی برای ذخیره یک شی یا آرایه کامل json به عنوان یک حباب تمایز نیافته وجود دارد.

در تاریکی، روزهای قدیم، این json به یک text ستون، که اگر تنها کاری که ما نیاز داشتیم این بود که آن را ذخیره کرده و برگردانیم خوب بود. اما اگر لازم بود کار پیچیده‌تری انجام دهیم، مثلاً مقادیر خاصی را از آن json استخراج کنیم یا خدای ناکرده از یکی در یک استفاده کنیم. WHERE بند، همه چیز می تواند خیلی سریع از کنترل خارج شود.

خوشبختانه، mysql مدرن پشتیبانی می کند JSON به عنوان یک نوع داده بومی، به ما امکان می دهد با داده های json به روشی تقریبا لذت بخش کار کنیم. در این پست می خواهیم به استخراج مقادیر از json و استفاده از داده های json در آن بپردازیم WHERE بندها

ایجاد یک JSON ستون

بیایید با ایجاد یک جدول با یک ستون از نوع شروع کنیم JSON.

CREATE TABLE `some_data` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `some_json` json NULL,
  `some_text` text NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

داده‌های آزمایشی که قرار است استفاده کنیم، نمایش json تک‌آهنگ «بوس و سوار» bratmobile است.[https://www.discogs.com/master/409458-Bratmobile-Kiss-And-Ride].

{
  "artist": "Bratmobile",
  "title": "Kiss and Ride",
  "format": "single",
  "year": 1992,
  "label": "Kill Rock Stars",
  "tracks": {
    "A": [
      {
        "title": "Kiss and Ride",
        "duration_seconds": 88
      }
    ],
    "B": [
      {
        "title": "No You Don't",
        "duration_seconds": 105
      },
      {
        "title": "Queenie",
        "duration_seconds": 79
      }
    ]
  }
}

ما آن json را در هر دو یک Native قرار می دهیم JSON ستون و همچنین در a تایپ کنید TEXT ستون بعداً به آن col متن باز خواهیم گشت.

INSERT INTO some_data VALUES(
  null,
  'some name',
  -- as json
  '{ "artist": "Bratmobile", "title": "Kiss and Ride", "format": "single", "year": 1992, "label": "Kill Rock Stars", "tracks": { "A": [ { "title": "Kiss and Ride", "duration_seconds": 88 } ], "B": [ { "title": "No You Don\'t", "duration_seconds": 105 }, { "title": "Queenie", "duration_seconds": 79 } ] } }',
  -- as string
  '{ "artist": "Bratmobile", "title": "Kiss and Ride", "format": "single", "year": 1992, "label": "Kill Rock Stars", "tracks": { "A": [ { "title": "Kiss and Ride", "duration_seconds": 88 } ], "B": [ { "title": "No You Don\'t", "duration_seconds": 105 }, { "title": "Queenie", "duration_seconds": 79 } ] } }'
);

استخراج برخی از داده ها از آن json

اکنون که مقداری json در خود داریم JSON ستون، ما می توانیم روی انتخاب مقادیر فردی کار کنیم. ما این کار را با mysql انجام می دهیم JSON_EXTRACT تابع.

JSON_EXTRACT دو آرگومان می گیرد: ستون و مسیر عنصری که می خواهید انتخاب کنید. مسیرها با یک نقطه از هم جدا می شوند، به همان روشی که مسیرهای دایرکتوری با a جدا می شوند /، و سطح بالای مسیر با نشان داده می شود $. اگر تا به حال با آن مبارزه کرده اید jq، باید احساس راحتی کنید که با این دست و پنجه نرم کنید.

در این مثال، ما را انتخاب می کنیم artist عنصر از سطح بالای شی json ما.

SELECT  JSON_EXTRACT(some_json, '$.artist') as artist
FROM    some_data
WHERE   id = 1;
+--------------+
| artist       |
+--------------+
| "Bratmobile" |
+--------------+

mysql نیز فراهم می کند -> عملگر به عنوان مخفف JSON_EXTRACT کمک کند تا سوالات ما کمی تمیزتر شود.

SELECT  some_json -> '$.artist' as artist
FROM    some_data
WHERE   id = 1;
+--------------+
| artist       |
+--------------+
| "Bratmobile" |
+--------------+

مزاحمتی که بلافاصله با این نتایج متوجه می‌شویم این است که آنها به صورت نقل قول هستند. ما این را نمی خواهیم هیچ کس این را نمی خواهد اجازه دهید آنها را با تابع حذف کنیم JSON_UNQUOTE

SELECT  JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.artist')) as artist
FROM    some_data
WHERE   id = 1;
+------------+
| artist     |
+------------+
| Bratmobile |
+------------+

کوتاه نویسی -> عملگری که قبلا استفاده کردیم را می توان به آن تغییر داد ->> به طور خودکار شامل شود JSON_UNQUOTE. استفاده از این اپراتور مانند محصور کردن یک تماس است JSON_EXTRACT که در JSON_UNQUOTE:

SELECT  some_json ->> '$.artist' as artist
FROM    some_data
WHERE   id = 1;
+------------+
| artist     |
+------------+
| Bratmobile |
+------------+

برخورد با آرایه ها

گرفتن عناصر منفرد از سطح بالا عالی است، اما ما همچنین می خواهیم با آرایه ها سر و کار داشته باشیم.

می توانیم در مسیری که به آن پاس می دهیم به آرایه ها ارجاع دهیم JSON_EXTRACT با استفاده از یک شاخص در داخل پرانتز. این چیزهای بسیار آشنا است به عنوان مثال، در داده های نمونه خود، یک شی داریم که به آن نامیده می شود tracks که شامل دو آرایه است که به عنوان کلید شده است A و B; این ها آهنگ های طرف “a” و “b” هستند. آن ها A و B عناصر آرایه هایی از اشیاء آهنگ هستند. اگر می خواستیم اولین آهنگ ساید را بگیریم B، ما خود را می سازیم JSON_EXTRACT اینطوری زنگ بزن:

SELECT  JSON_EXTRACT(some_json, '$.tracks.B[0].title') as side_b_song_one
FROM    some_data
WHERE   id = 1;
+-----------------+
| side_b_song_one |
+-----------------+
| "No You Don't"  |
+-----------------+

با نگاه کردن به آرگومان مسیر، می‌توانیم ببینیم که از بالای سلسله مراتب شروع می‌کنیم $، سپس به tracks هدف – شی. بعدی آرایه است B. نشان می دهیم که اولین عنصر این آرایه را با آن می خواهیم [0]، سپس با دادن کلید مسیر خود را ادامه دهید title. نتیجه عنوان اولین آهنگ سمت b است.

اگر ما بخواهیم همه آهنگ های سمت b، می توانیم آن شاخص را با علامت عام جایگزین کنیم *. این یک آرایه json از عناوین را برمی گرداند.

SELECT  JSON_EXTRACT(some_json, '$.tracks.B[*].title') as side_b
FROM    some_data
WHERE   id = 1;
+-----------------------------+
| side_b                      |
+-----------------------------+
| ["No You Don't", "Queenie"] |
+-----------------------------+

ما همچنین می توانیم استفاده کنیم ** wildcard برای انجام این کار.

SELECT  JSON_EXTRACT(some_json, '$.tracks.B**.title') as side_b
FROM    some_data
WHERE   id = 1;
+-----------------------------+
| side_b                      |
+-----------------------------+
| ["No You Don't", "Queenie"] |
+-----------------------------+

مهم است که توجه داشته باشید ** در واقع یعنی همه مسیرهای بین پیشوند و پسوند. این بدان معنی است که ما می توانیم از آن برای دریافت همه موارد استفاده کنیم title عناصر زیر tracks، صرف نظر از اینکه آیا در آرایه کلید خورده است یا خیر A یا B مانند:

SELECT  JSON_EXTRACT(some_json, '$.tracks**.title') as side_a_and_side_be
FROM    some_data
WHERE   id = 1;
+----------------------------------------------+
| side_a_and_side_be                           |
+----------------------------------------------+
| ["Kiss and Ride", "No You Don't", "Queenie"] |
+----------------------------------------------+

در آن مثال، ** با هر دو مطابقت دارد A و B. چیزهای بسیار قدرتمند

ما همچنین می توانیم در هنگام تعریف شاخص های آرایه خود از محدوده استفاده کنیم. به عنوان مثال، اگر ما می خواستیم همه را بدست آوریم titleدر کنار B بین موقعیت صفر و یک، می‌توانیم از شاخص استفاده کنیم [0 to 1]:

SELECT  JSON_EXTRACT(some_json, '$.tracks.B[0 to 1].title') as side_b_first_two
FROM    some_data
WHERE   id = 1;
+-----------------------------+
| side_b_first_two            |
+-----------------------------+
| ["No You Don't", "Queenie"] |
+-----------------------------+

و اگر تنها چیزی که می خواهیم آخرین عنصر یک آرایه باشد، mysql به ما اجازه می دهد از کلمه تحت اللفظی استفاده کنیم last:

SELECT  JSON_EXTRACT(some_json, '$.tracks.B[last].title') as side_b_last_track
FROM    some_data
WHERE   id = 1;
+-------------------+
| side_b_last_track |
+-------------------+
| "Queenie"         |
+-------------------+

با استفاده از این در WHERE بندها

استخراج داده‌ها از json مفید است، اما در واقع، می‌توانیم کل ستون را انتخاب کرده و داده‌ها را اجرا کنیم json_decode در کنترل کننده ما را واقعی پول از این استفاده می کند WHERE بندها بیایید نگاه بیندازیم:

SELECT  JSON_EXTRACT(some_json, '$.title') as title
FROM    some_data
WHERE   JSON_EXTRACT(some_json, '$.year') = 1992;
+-----------------+
| title           |
+-----------------+
| "Kiss and Ride" |
+-----------------+

در اینجا ما فقط استخراج کردیم year از ستون json ما و در a استفاده می شود WHERE عبارت.

یک کلاژ برای شبیه سازی WHERE IN بندها

اگر بخواهیم الف بنویسیم WHERE IN بند در برابر یک آرایه در شی json ما، همه چیز کمی پیچیده تر می شود. ایده آل اولیه ما ممکن است این باشد که به سادگی استفاده کنیم IN در یک آرایه استخراج شده از json ما:

-- this absolutely DOES NOT WORK
SELECT  JSON_EXTRACT(some_json, '$.title') as title
FROM    some_data
WHERE   "No You Don't" IN JSON_EXTRACT(some_json, '$.tracks**.title');

بدون تاس. البته مشکل اینجاست که WHERE IN لیستی از مقادیر را انتظار دارد، نه یک آرایه json. یک عدم تطابق نوع وجود دارد.

راه حل استفاده است MEMBER OF.

این سازه به نوعی مانند یک تابع و به نوعی مانند یک عملگر رفتار می کند. مقدار سمت چپ آن را می گیرد و آزمایش می کند که آیا در آرایه json که به عنوان آرگومان به آن ارسال می شود قرار دارد یا خیر.

SELECT  JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.artist')) as artist,
        JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.title')) as title
FROM    some_data
WHERE   "Queenie" MEMBER OF(CAST(JSON_EXTRACT(some_json, '$.tracks**.title') AS json));
+------------+---------------+
| artist     | title         |
+------------+---------------+
| Bratmobile | Kiss and Ride |
+------------+---------------+

چیزی که در اینجا باید به آن توجه کرد، استدلال به است MEMBER OF. ما همه را استخراج کرده ایم title مقادیر از ستون های json ما و آنها را با هم در یک آرایه له می کنیم. هر چند که «آرایه» واقعاً یک آرایه نیست. این یک رشته است و نه حتی یک رشته json، فقط یک رشته.

از آنجا که MEMBER OF به داده های سبک json به عنوان آرگومان نیاز دارد، باید فراخوانی کنیم CAST تا رشته آرایه خود را به یک آرایه json واقعی تبدیل کنیم.

این راه حل کمی آشفته است، و اگر من درخت اشتباهی را در اینجا پنهان می کنم و راه بهتر و ظریف تری برای انجام این کار وجود دارد، من بسیار آماده ورود هستم. با این حال، این ساختار کار می کند.

صبر کن. این کار می کند TEXT ستون ها هم؟

راز این است که همه چیزهایی که در اینجا پوشش داده ایم روی آنها نیز کار می کنند TEXT ستون ها. اگر آن را اجرا کنیم WHERE IN در مقابل ما انتخاب کنید some_text ستون، خوب کار می کند:

SELECT  JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.artist')) as artist,
        JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.title')) as title
FROM    some_data
WHERE   "Queenie" MEMBER OF(CAST(JSON_EXTRACT(some_text, '$.tracks**.title') AS json));
+------------+---------------+
| artist     | title         |
+------------+---------------+
| Bratmobile | Kiss and Ride |
+------------+---------------+

پس چرا با JSON اصلا نوع ستون؟ خوب، مهمتر از همه، زمانی که ما از JSON تایپ کنید که در درج یا به روز رسانی اعتبار سنجی خودکار دریافت می کنیم. می توانیم invliad json را در آن قرار دهیم TEXT ستون ها و mysql هرگز شکایت نمی کنند. فقط منتظر خواهد ماند تا در زمان معینی ناامید شویم.

دوم، بومی JSON نوع بسیار کارآمدتر است. آیا می‌توانیم از مقادیر json استخراج‌شده در پیوند پنج جدولی استفاده کنیم؟ آره. اگر از آن استفاده کنیم به طرز دردناکی کند می شود؟ TEXT? همچنین بله بومی JSON بسیار سریعتر خواهد بود.

در نهایت، تقریباً به طور قطع توابع جدید json در نسخه‌های آینده mysql وجود خواهد داشت (شامل، امیدوارم، روشی منطقی‌تر برای انجام WHERE INs) و هیچ تضمینی وجود ندارد که آنها رفتار خوبی با آنها داشته باشند TEXT. خط پایانی: اگر می دانید که json را در یک ستون قرار می دهید، آن را a JSON ستون

🔎 این پست در اصل در وبلاگ فنی گرنت هوروود نوشته شده است