پرس و جو آرایه های تعبیه شده در JSON (اسناد Postgresql JSONB و MongoDB)

هنگام کار با ساختار داده های مبتنی بر اسناد ، زمینه های موجود در ریشه را می توان با استفاده از شاخص های بیان ساده فهرست بندی کرد. با این حال ، هنگامی که یک آرایه در مسیر وجود دارد ، که نمایانگر یک رابطه یک به یک است ، PostgreSQL به یک شاخص GIN و استفاده از اپراتورهای مسیر JSON برای نمایه سازی ، کارآمدتر از نمایش داده های SQL/JSON نیاز دارد.

نمونه

من جدول زیر را برای ذخیره کتاب ایجاد می کنم. من تصمیم گرفتم که اطلاعات بیشتری را با یک طرح انعطاف پذیر جاسازی کنم و یک ستون “داده” برای ذخیره داده های JSON اضافه کردم:

create table books (  
   primary key(book_id),  
   book_id bigint,  
   title text,  
   data jsonb  
);

من یک کتاب را وارد می کنم و برخی از نظرات را در سند طرح انعطاف پذیر خود اضافه می کنم:

insert into books values (  
   8675309,  
   'Brave New World',  
   '{ "reviews":[  
      { "name": "John", "text": "Amazing!" },  
      { "name": "Jane", "text": "Incredible book!" }  
   ] }'
);

نیازی به جدول دیگری نیست ، زیرا بررسی ها ذاتاً با کتابهایی که در مورد آنها بحث می کنند مرتبط است. یک کتاب بدون نمایش در کنار بررسی آن قابل بررسی نیست ، و هر جدول جداگانه ای را غیر ضروری می کند. من می دانم که به نظر می رسد نقض اولین فرم عادی است ، اما هیچ ناهنجاری به روزرسانی در اینجا امکان پذیر نیست زیرا هیچ تکثیر وجود ندارد. از نظر عادی سازی ، این با ذخیره متن ، که مجموعه ای از کاراکتر یا تعبیه شده است ، که آرایه هایی از اعداد هستند ، تفاوت چندانی ندارد.

پرس و جو ناکارآمد با SQL پیوستن

اگر با SQL راحت هستید ، ممکن است بخواهید این ساختار را با استفاده از SQL پرس و جو کنید. به سادگی آرایه سند JSON را غیرقانونی کنید و از آن مانند یک جدول رابطه ای استفاده کنید:

SELECT DISTINCT title FROM books    
JOIN LATERAL jsonb_array_elements(books.data->'reviews') AS review 
ON review->>'name' = 'John'
;  

jsonb_array_elements یک آرایه JSON را برای نمایش داده های SQL در ردیف ها گسترش می دهد. The Lateral Join اطلاعات کتاب ، فیلترهای بند OR یا Where Where را با نام Reverseer اضافه می کند و عناوین تکراری را حذف می کند. این نحو استاندارد SQL است اما نمی تواند از یک فهرست برای فیلتر کردن نام بازرسان قبل از عدم استفاده استفاده کند ، و نیاز به خواندن همه ردیف ها و اسناد دارد:

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Unique
   ->  Sort
         Sort Key: books.title
         ->  Nested Loop
               ->  Seq Scan on books
               ->  Function Scan on jsonb_array_elements review
                     Filter: ((value ->> 'name'::text) = 'John'::text)

در حالی که این یک نحو معتبر SQL است ، و JSON یک داده SQL معتبر است ، اما آنها چندان دوستانه نیستند زیرا یک پایگاه داده رابطه ای یک پایگاه داده اسناد نیست. هنگام استفاده از اسناد در PostgreSQL ، باید یاد بگیرید که چگونه از آنها پرس و جو کنید و از آنها فهرست بندی کنید.

توجه داشته باشید که jsonb_array_elements استاندارد SQL نیست ، اما PostgreSQL 17 JSON_TABLE را معرفی کرد که APRT استاندارد است. پرس و جو را می توان دوباره نوشت:

SELECT books.title  
FROM books  
JOIN JSON_TABLE(  
  books.data->'reviews',  
  '$[*]' COLUMNS (  
    name TEXT PATH '$.name' 
  )  
) AS review  
ON review.name = 'John'
;  

این روش استاندارد SQL/JSON برای جستجوی اسناد است. متأسفانه ، کارآمد نیست زیرا اسکن شاخص امکان پذیر نیست. فراموش نکنید که شاخص های SQL بخشی از استاندارد SQL نیستند.

پرس و جو کارآمد با اپراتورهای JSON

برای پرس و جو کارآمد از داده های JSONB برای بررسی توسط یک شخص خاص ، ما باید از اپراتور مهار PostgreSQL استفاده کنیم @> به جای تکیه بر SQL استاندارد:

SELECT title FROM books  
WHERE data->'reviews' @> '[{"name": "John"}]'
;

اکنون که مستقیماً بدون تغییر سند روی جدول فیلتر می کنم ، می توانم یک فهرست ایجاد کنم. از آنجا که می توان چندین کلید در هر ردیف جدول وجود داشت ، یک شاخص معکوس لازم است:

CREATE INDEX ON books USING gin ((data->'reviews') jsonb_path_ops)
;

با داشتن یک شاخص برای اپراتورهای مسیر JSON ، هر کلید با یک مورد در آرایه مطابقت دارد. این می تواند هنگام پرس و جو با فیلتر برابری در قسمت آرایه تعبیه شده استفاده شود:

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Bitmap Heap Scan on books
   Recheck Cond: ((data -> 'reviews'::text) @> '[{"name": "John"}]'::jsonb)
   ->  Bitmap Index Scan on books_expr_idx
         Index Cond: ((data -> 'reviews'::text) @> '[{"name": "John"}]'::jsonb)

جین (شاخص معکوس تعمیم یافته) برای داده های داده ای طراحی شده است که شامل چندین کلید مانند موارد آرایه یا کلمات ، ساقه ها یا TRIGRAM در متن است. در حالی که قدرتمند است ، جین محدودیت هایی دارد: نمی تواند از پرس و جوهای دامنه پشتیبانی کند ، ترتیب را با بندها بهینه کند ، یا پیش بینی های پوشش را انجام دهد (بدون اسکن فقط شاخص).

مقایسه با یک پایگاه داده سند

در حالی که PostgreSQL انعطاف پذیری را در ذخیره و نمایه سازی اسناد JSON ارائه می دهد ، این یک پایگاه داده سند را جایگزین نمی کند که اسناد انواع بومی هستند. به عنوان مثال ، در MongoDB زمینه های موجود در یک آرایه مانند هر زمینه دیگر استفاده می شود. من سند مشابه را در MongoDB وارد می کنم:

db.books.insertOne({  
  book_id: 8675309,  
  title: "Brave New World",  
  reviews: [  
    { name: "John", text: "Amazing!" },  
    { name: "Jane", text: "Incredible book!" }  
  ]  
}); 

نیازی به اپراتورهای ویژه نیست و من می توانم مانند هر زمینه دیگری از زمینه تعبیه شده پرس و جو کنم:

db.books.find(
  { "reviews.name": "John" } // filter
, { title: 1, _id: 0 }      // projection
);  

[ { title: 'Brave New World' } ]

نیازی به نوع شاخص خاص نیست و من می توانم مانند هر زمینه دیگری قسمت تعبیه شده را فهرست بندی کنم:

db.books.createIndex({ "reviews.name": 1 })
;  

برنامه اجرای تأیید می کند که از این فهرست برای فیلتر کردن در “بررسی ها” استفاده می شود:

db.books.find(
  { "reviews.name": "John" } // filter
, { title: 1, _id: 0 }      // projection
).explain().queryPlanner.winningPlan
; 

{
  isCached: false,
  stage: 'PROJECTION_SIMPLE',
  transformBy: { title: 1, _id: 0 },
  inputStage: {
    stage: 'FETCH',
    inputStage: {
      stage: 'IXSCAN',
      keyPattern: { 'reviews.name': 1 },
      indexName: 'reviews.name_1',
      isMultiKey: true,
      multiKeyPaths: { 'reviews.name': [ 'reviews' ] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { 'reviews.name': [ '["John", "John"]' ] }
    }
  }
}

این یک شاخص معمولی است ، تنها خاصیت این است که اجازه می دهد تا ورودی های چند کلید را انجام دهد.

بر خلاف شاخص GIN PostgreSQL ، که به اسکن Bitmap نیاز دارد که سفارش ورود را حفظ نمی کند ، MongoDB از یک شاخص معمولی استفاده می کند که از نمایش داده های دامنه پشتیبانی می کند. به عنوان مثال ، اگر من فقط از آغاز یک نام می دانم ، می توانم از یک عبارت منظم برای فیلتر کردن نتایج به طور مؤثر استفاده کنم:

db.books.find(  
  { "reviews.name": { $regex: "^Joh" } }, // filter using regex  
  { title: 1, _id: 0 }                    // projection  
).explain().queryPlanner.winningPlan
;  

{
  isCached: false,
  stage: 'PROJECTION_SIMPLE',
  transformBy: { title: 1, _id: 0 },
  inputStage: {
    stage: 'FETCH',
    inputStage: {
      stage: 'IXSCAN',
      keyPattern: { 'reviews.name': 1 },
      indexName: 'reviews.name_1',
      isMultiKey: true,
      multiKeyPaths: { 'reviews.name': [ 'reviews' ] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { 'reviews.name': [ '["Joh", "Joi")', '[/^Joh/, /^Joh/]' ] }
    }
  }
}

MongoDB از شاخص به طور کارآمد استفاده کرد ، زیرا برنامه ریز پرس و جو بیان منظم را تغییر داد /^Joh/ به طور خاص به یک اسکن دامنه ["Joh", "Joi")بشر

پایان

هنگام مقایسه PostgreSQL و MongoDB ، درک مکانیسم های پرس و جو و نمایه سازی آنها ضروری است و فقط به توانایی آنها در ذخیره JSON تکیه نمی کنند.
مانند سایر RDBMS ، PostgreSQL به عنوان یک بانک اطلاعاتی متمرکز و یکپارچه که توسط چندین برنامه استفاده می شود ، برتری می یابد. با توابع تخصصی JSONB و شاخص های جین ، انعطاف پذیری را به جداول عادی اضافه می کند.
MongoDB برای توسعه چابکی به ویژه در میکروسرویس و طراحی دامنه محور ایده آل است ، جایی که الگوهای دسترسی به خوبی تعریف شده است ، اما برنامه با سرعت بالا تکامل می یابد. مدل سند آن به خوبی با اشیاء تجاری هماهنگ است.

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