PostgreSQL یک سیستم مدیریت پایگاه داده رابطه ای منبع باز (RDBMS) قدرتمند است. در ابتدا به عنوان جانشین سیستم پایگاه داده Ingres ایجاد شد و بعداً "PostgreSQL" (مخفف "Post-Ingres SQL") نام گرفت.

PostgreSQL به دلیل استحکام، قابلیت اطمینان و مقیاس پذیری شناخته شده است و آن را به گزینه ای محبوب برای برنامه های کاربردی پایگاه داده بزرگ و پیچیده تبدیل می کند. ویژگی های پیشرفته ای مانند پشتیبانی از JSON و سایر انواع داده های غیر رابطه ای و همچنین پشتیبانی از داده های مکانی را ارائه می دهد.

پشتیبانی از فایل JSON برای اولین بار در PostgreSQL نسخه ۹.۲ معرفی شد و با هر نسخه جدید، بهبودهای ثابتی در حال انجام است.

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

JSON چیست؟

JSON مخفف JavaScript Object Notation است. این یک روش متداول برای ذخیره داده ها، به ویژه در برنامه های کاربردی وب است. این بسیار شبیه به HTML یا XML است و برای برنامه هایی ساخته شده است که به راحتی فایل های JSON را بخوانند.

جفت‌های کلید-مقدار : داده‌های JSON در جفت‌های کلید-مقدار نوشته می‌شوند که اطراف آنها را نقل قول احاطه کرده‌اند. در اینجا مثالی از یک جفت کلید-مقدار "ایمیل" آورده شده است: " [email protected] ". "ایمیل" در اینجا کلید است، در حالی که " [email protected] om" مقدار را نشان می دهد. این دو با یک کولن ":" از هم جدا می شوند.

اشیاء: یک شی یک جفت یا جفت کلید-مقدار است که در براکت های فرفری محصور شده اند. هرگاه یک جفت کلید-مقدار در براکت‌های فرفری محصور شود، به یک شی تبدیل می‌شود و می‌توان آن را به عنوان یک واحد در نظر گرفت. چندین جفت کلید-مقدار را می توان به یک شی اضافه کرد که با کاما از هم جدا می شوند.

مثالی از یک شی JSON:

 {“email” : “[email protected]”, “country” : “United Kingdom”}

آرایه ها: آرایه ها در JSON راهی برای ذخیره مجموعه ای از مقادیر در یک شی JSON واحد هستند. یک آرایه در JSON با پرانتز [ ] نشان داده می شود که حاوی فهرست ی از مقادیر جدا شده با کاما است.

در اینجا نمونه ای از یک آرایه در JSON آمده است: [ "apple", "banana", "cherry"] .

آرایه‌ها در JSON همچنین می‌توانند تودرتو باشند، به این معنی که یک آرایه می‌تواند آرایه‌ها یا اشیاء دیگری را به‌عنوان مقادیر داشته باشد. در اینجا نمونه ای از آرایه تودرتو آمده است:

 { "firstname" : "Claire", "location" : "United Kingdom", "blog" : [{ "id" : "1", "title" : "Welcome to my blog" }, { "id" : "2", "title" : "My first programming language" }]}

در این مثال از آرایه های تودرتو، می توانید ببینید، "blog" در یک آرایه قرار دارد، و آرایه همچنین شامل چندین شی است.

JSONB در PostgreSQL

نوع داده JSONB چیست؟ و چه تفاوتی با JSON دارد؟

JSONB (JSON Binary) یک نوع داده در PostgreSQL است که به شما امکان می دهد داده های JSON را به روشی مؤثرتر و کارآمدتر از نوع داده معمولی JSON ذخیره و دستکاری کنید.

JSONB داده‌های JSON را در قالب باینری ذخیره می‌کند، که ایندکس‌سازی و عملکرد پرس و جو سریع‌تر را در مقایسه با نوع داده معمولی JSON ممکن می‌سازد. این به این دلیل است که فرمت باینری امکان ذخیره سازی و بازیابی کارآمدتر داده های JSON را فراهم می کند، به ویژه هنگامی که با اشیاء بزرگ یا پیچیده JSON سروکار داریم.

علاوه بر این، JSONB از گزینه‌های نمایه‌سازی اضافی مانند توانایی فهرست‌بندی کلیدهای خاص در یک شی JSON پشتیبانی می‌کند که امکان درخواست‌های سریع‌تر را نیز فراهم می‌کند.

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

نحوه ایجاد جدول با نوع داده JSONB

شما می توانید یک جدول ایجاد کنید و به یک ستون یک نوع داده از JSON یا JSONB بدهید، درست همانطور که به ستونی از نوع داده Int، VARCHAR یا Double می دهید. شما فقط می توانید به ستون یک نوع داده JSON یا JSONB بدهید.

در اینجا نمونه ای از ایجاد یک Table Journal و دادن نوع داده JSONB به ستون "diary_information" آورده شده است.

 CREATE TABLE journal ( id Int NOT NULL PRIMARY KEY, day VARCHAR, diary_information JSONB );

از آنجایی که ما نوع داده را از نوع JSONB تعیین کردیم، هر داده ای که در آن ستون نگهداری می شود باید JSON معتبر باشد.

نحوه درج داده های JSON در جداول

پس از ایجاد جدول و دادن نوع داده JSONB به ستون خود، چگونه مقادیر را در ستون وارد کنیم؟ به یاد داشته باشید که داده ها باید در قالب JSON معتبر باشند.

برای درج داده ها در جدول خود، از این دستور استفاده می کنیم:

 INSERT INTO journal (id, day, diary_information) VALUES ( 1, “Tuesday”, '{"title": "My first day at work", "Feeling": "Mixed feeling"}' );

اگر بخواهیم اطلاعات را با استفاده از عبارت Select SELECT * FROM journal بازیابی کنیم، خروجی زیر را دریافت می کنیم، به این معنی که رکوردها درج شده اند.

در بخش بعدی، نگاهی به برخی از توابع و اپراتورها خواهیم داشت.

مروری بر توابع و اپراتورهای JSON

توابع و عملگرها به شما امکان می دهند داده ها را در قالب JSON در PostgreSQL ذخیره، دستکاری و پرس و جو کنید.

در اینجا برخی از توابع و عملگرهای رایج PostgreSQL که در کار با فایل های JSON استفاده می شوند آورده شده است:

-> : این عملگر به شما امکان می دهد یک مقدار خاص را از یک شی JSON استخراج کنید، شما کلید را به عنوان "فرزند" به "والد" مشخص می کنید.

مثلا:

برای بازیابی یک مقدار خاص از یک شی JSON با استفاده از عملگر -> ، از آن در دستور SELECT همانطور که در زیر مشاهده می کنید استفاده کنید:

 SELECT Id, day, diary_information -> 'Feeling' AS Feeling FROM journal;

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

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

مثلا:

 SELECT id, day, dairy_information ->> 'Feeling' as Feeling FROM products;

این مقدار کلید "ماده" را به عنوان متن از ستون "ویژگی ها" در جدول "محصولات" استخراج می کند.

json_agg : این تابع مقادیر JSON را در یک آرایه JSON جمع می کند.

برای مثال، SELECT json_agg(my_column) FROM my_table; یک آرایه JSON حاوی مقادیر ستون "my_column" جدول "my_table" را برمی گرداند.

jsonb_set : این تابع یک فیلد شی JSON را با یک مقدار جدید به روز می کند. مثلا:

 UPDATE my_table SET json_column = jsonb_set( json_column, '{field_name}', '"new_value"' ) WHERE id = 1;

برای به‌روزرسانی یک رکورد JSON موجود، از تابع jsonb_set() () در یک دستور به‌روزرسانی استفاده می‌کنیم.

به عنوان مثال، برای به روز رسانی رکورد در جدولی که قبلا ایجاد کردیم، می توانید کد زیر را اجرا کنید:

 UPDATE journal SET diary_information = jsonb_set( diary_information, '{Feeling}', '"Excited"' ) WHERE id = 1;

با این کار کلید "Feeling" در ستون "diary_information" جدول "journal" با مقدار جدید "Excited" به روز می شود.

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

می توانید از یک تابع JSONB_BUILD_OBJECT برای درج یک رکورد متن ساده استفاده کنید و با این کار آن را به داده JSON تبدیل کنید. برای مثال اگر کد را اجرا کنید:

 JSONB_BUILD_OBJECT('Morning', 'Everybody is annoying today', 'Evening', 'Cannot wait to go home')

با این کار مقداری به شکل زیر ایجاد می شود:

 {“Morning”: “Everybody is annoying today”, “Evening”: “Cannot wait to go home”}

استفاده از این تابع در دستور insert:

 INSERT INTO journal (id, day, feeling) VALUES ( 2, 'Wednesday', JSONB_BUILD_OBJECT( 'Tired', 'Everybody is annoying today', 'Hungry', 'Cannot wait to go home'));

رکورد جدید به جدول اضافه خواهد شد و چون از تابع JSONB_BUILD__OBJECT استفاده کردیم، مقادیر زیر در قالب JSON خواهند بود.

اینها چند توابع و عملگرهایی هستند که می توانیم در این مقاله به آنها بپردازیم. می توانید اطلاعات بیشتری در مورد توابع و اپراتورهای JSON در PostgreSQL در اسناد رسمی اینجا بخوانید.

نحوه کار با آرایه های JSON در PostgreSQL

در PostgreSQL، می‌توانید داده‌های JSON را به‌عنوان یک مقدار ستون در جدول ذخیره کنید، و می‌توانید از آرایه‌های JSON برای ذخیره مجموعه‌ای از اشیاء JSON در یک ستون استفاده کنید.

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

نحوه درج آرایه های JSON در جداول

برای درج آرایه‌های JSON در جدول در PostgreSQL، می‌توانید از عبارت INSERT INTO همراه با عبارت VALUES برای تعیین آرایه JSON به عنوان یک مقدار رشته استفاده کنید.

در اینجا یک مثال است:

فرض کنید جدولی به نام کارکنان با شناسه ستون، نام و مهارت دارید. ستون skills آرایه ای از اشیاء JSON را ذخیره می کند که نشان دهنده مهارت های هر کارمند است.

برای درج سابقه کارمند جدید با جزئیات زیر:

شناسه: ۱

نام: جان

مهارت‌ها: [{"نام": "پایتون"، "سطح": "متوسط"}، {"نام": "جاوا اسکریپت"، "سطح": "متخصص"}]

می توانید از دستور SQL زیر استفاده کنید:

 INSERT INTO employees (id, name, skills) VALUES ( 1, 'John', '[{"name": "Python", "level": "Intermediate"}, {"name": "JavaScript", "level": "Expert"}]' );

نحوه پرس و جو کردن آرایه های JSON با استفاده از عملگرهای JSON

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

فرض کنید جدولی به نام کارکنان دارید که دارای ستون مهارت است که آرایه ای از اشیاء JSON نشان دهنده مهارت های هر کارمند را ذخیره می کند.

برای استخراج نام همه کارمندانی که یکی از مهارت‌هایشان «پایتون» است، می‌توانید از عملگر ->> برای استخراج ویژگی «name» هر شی skill و از عملگر @> برای تحلیل اینکه آیا آرایه به دست آمده حاوی آرایه است استفاده کنید. مقدار "Python":

 SELECT name FROM employees WHERE skills @ > '[{"name": "Python"}]' :: jsonb

این تنها نمونه‌ای از روش‌های متعددی است که از طریق آنها می‌توانید آرایه‌های JSON را با استفاده از عملگرهای JSON ارائه‌شده توسط PostgreSQL پرس‌وجو و دستکاری کنید.

نتیجه

در نتیجه، پشتیبانی PostgreSQL برای JSON به توسعه دهندگان این امکان را می دهد که مدل های داده را ساده کنند، عملکرد برنامه را بهبود بخشند و موارد دیگر. این همچنین یک رابطه یکپارچه بین ساختارهای داده رابطه ای و غیر رابطه ای ایجاد می کند.

شما در مورد انواع داده‌های JSON و JSONB و اینکه چه جفت‌ها، اشیاء و آرایه‌های کلید-مقدار در JSON هستند، یاد گرفته‌اید. همچنین در مورد برخی از عملگرها و توابع در PostgreSQL برای پرس و جو از داده ها در فرمت JSON یاد گرفتید.

اگر یکی دو مورد از این مقاله آموختید، لطفاً آن را با دیگران به اشتراک بگذارید.