از نسخه 12cR1، دیتابیس اوراکل از JSON پشتیبانی می کند به این معنی که با ارائه چندین تابع، امکان انجام عملیاتی چون اجرای query، ایجاد index و ایجاد view بر روی JSON Document را فراهم می سازد به طور مثال می توان با کمک این قابلیت، برای هر یک از فیلدهای موجود در JSON Document ایندکس گذاری کرد.

البته در این نسخه Data Type جدیدی برای JSON معرفی نشده و JSON Documentها باید در نوع داده VARCHAR2، CLOB و یا BLOB ذخیره شوند(نوع داده NCLOB و NVARCHAR2 را هم باید به این مجموعه اضافه کرد) که معمولا نوع داده BLOB برای این کار توصیه می شود.

توجه 1: برای کار با JSON در نسخه های قبلی اوراکل(مثل 11g) هم راهکارهای وجود دارد که یکی از این راهکارها نصب کامپوننت APEX و استفاده از APEX_JSON می باشد.

توجه 2: اوراکل در نسخه 20c نوع داده JSON را برای ذخیره JSON Documentها ارائه کرده است.

در قسمت زیر، نمونه ای از اطلاعات با فرمت JSON را مشاهده می کنید:

{

“First_Name“:”Vahid”,

“Last_Name” :”Usefzadeh“,

“Contact”   :

{

“Email”     :”vahidusefzadeh@gmail.com“,

“Phone”     :09128110897

}

}

قصد داریم این اطلاعات را در جدولی به نام tbl1 درج کنیم:

SQL> create table tbl1 (id number,ettelaat varchar2(4000));

Table created

SQL> insert into tbl1 values(1,

  2  ‘{

  3  “First_Name” :”Vahid”,

  4  “Last_Name”  :”Usefzadeh”,

  5  “Contact”    :

  6  {

  7  “Email”      :”vahidusefzadeh@gmail.com”,

  8  “Phone”      :09128110897

  9  }

 10  }‘);

1 row inserted

بدیهی است که با توجه به نوع داده ستون ettelaat(که varchar2 می باشد) می توان اطلاعات non-JSON را هم در این ستون ذخیره کرد:

SQL> insert into tbl1 values(2,’teeeeest1′);

1 row inserted

برای جلوگیری از این اختلاط می توان constraintای را برای ستون ettelaat  تعریف کرده و مانع از ثبت اطلاعات non-JSON در این ستون شد هر چند که این کار سربار پرفورمنسی به همراه خواهد داشت:

SQL> create table tbl2 (id number,ettelaat varchar2(4000)  constraint jc1 check (ettelaat is json) );

Table created

SQL> insert into tbl2 values(1,’teeeeest1′);

ORA-02290: check constraint (USEF.JC1) violated

SQL> insert into tbl2 values(2,'{“First_Name”:”Vahid”,”Last_Name”:”Usefzadeh”,”Contact”:{“Email”:”vahidusefzadeh@gmail.com”,”Phone”:”09128110897″}}’);

1 row inserted

SQL> commit;

Commit complete

پس از درج JSON document در این جدول، می توان با اجرای یک پرس و جوی ساده به محتویات آن دسترسی پیدا کرد:

SQL> select * from tbl2;

برای نمایش مستقل هر کدام از فیلدهای استفاده شده در JSON documentها می توان از روش زیر که اصطلاحا Dot Notation هم نامیده می شود، استفاده کرد:

SQL> select t.ETTELAAT.Last_Name,t.ETTELAAT.Contact.Email from tbl2 t;

LAST_NAME         CONTACT

————–  —————————-

Usefzadeh        vahidusefzadeh@gmail.com

البته اگر محدودیت IS JSON برای ستونی تعیین نشده باشد، نمی توان به اطلاعات JSON موجود در آن به روش Dot Notation دسترسی پیدا کرد:

SQL> select t.ettelaat    from tbl1 t;

ETTELAAT

———————————————

{

    “First_Name”  :”Vahid”,

    “Last_Name”  :”Usefzadeh”,

    “Contact”  :

    {

    “Email”  :”vahidusefzadeh@gmail.com”,

    “Phone”  :”09128110897″

    }

   }

SQL> select t.ettelaat.First_Name from tbl1 t;

ORA-00904: “T”.”ETTELAAT”.”FIRST_NAME”: invalid identifier

SQL> alter table TBL1 add constraint jjj check(ettelaat is json);

Table altered

SQL> select t.ettelaat.First_Name from tbl1 t;

FIRST_NAME

———–

Vahid

با غیرفعال کردن این constraint، کماکان می توان به اطلاعات JSON به روش dot notation دسترسی پیدا کرد:

SQL> alter table TBL1 disable constraint JJJ;

Table altered

SQL> select t.ettelaat.First_Name from tbl1 t;

FIRST_NAME

———-

Vahid

همانطور که مشاهده شد، روش Dot Notation زمانی کاربرد خواهد داشت که به ستون حاوی JSON document، محدودیت IS JSON اضافه شده باشد در کنار روش Dot Notation، اوراکل توابعی را هم ارائه کرده است که برای کار با JSON نیازی به constraint نخواهند داشت.

یکی از این توابع، JSON_VALUE می باشد که یک scalar value را از اطلاعات JSON انتخاب و به صورت SQL value برمی گرداند. مقدار برگشتی این تابع به صورت پیش فرض از نوع (VARCHAR2(4000 می باشد.

برای مثال، در پرس و جوی زیر، با کمک تابع JSON_VALUE، مقادیر فیلدهای First_Name، Last_Name و Contact.Phone را مشخص می کنیم:

SQL>SELECT json_value(ETTELAAT, ‘$.First_Name’) first_name,

       json_value(ETTELAAT, ‘$.Last_Name’) last_name,

       json_value(ETTELAAT, ‘$.Contact.Phone’) Phone

  FROM tbl2;

FIRST_NAME LAST_NAME  PHONE

———-        ———-    ————

Vahid      Usefzadeh  09128110897

این تابع قابلیتهای بیشتری را هم به ما می دهد به عنوان نمونه از طریق این تابع می توان مقادیر برگشتی را با عبارتهایی چون RETURNING، ON ERROR و DEFAULT X ON EMPTY کنترل کرد.

مثلا به کمک عبارت RETURNING NUMBER ERROR ON ERROR در کنار ستون Phone، محدودیتی را اعمال می کنیم که اگر شماره همراه فرد از نوع number نبود، دستور با خطا متوقف شود:

SQL> insert into tbl2 values(2,'{“First_Name”:”Ali”,”Last_Name”:”Usefzadeh”,”Contact”:{“Email”:”Aliusefzadeh@gmail.com”,”Phone”:”test”}}’);

1 row inserted

SQL>SELECT json_value(ETTELAAT, ‘$.First_Name’) first_name,

       json_value(ETTELAAT, ‘$.Last_Name’) last_name,

       json_value(ETTELAAT, ‘$.Contact.Phone’  RETURNING NUMBER ERROR ON ERROR) Phone

  FROM tbl2;

ORA-01722: invalid number

برای handle کردن این خطا هم می توان از عبارت RETURNING NUMBER NULL ON ERROR استفاده کرد:

SELECT json_value(ETTELAAT, ‘$.First_Name’) first_name,

       json_value(ETTELAAT, ‘$.Last_Name’) last_name,

       json_value(ETTELAAT, ‘$.Contact.Phone’  RETURNING NUMBER NULL ON ERROR) Phone

  FROM tbl2;

FIRST_NAME LAST_NAME       PHONE

———- ———- ————

Ali      Usefzadeh

Vahid    Usefzadeh    09128110897

علاوه بر قابلیتهای شمرده شده برای تابع JSON_VALUE، از این تابع می توان در قسمت where clause و یا order by هم استفاده کرد.

در کنار این تابع، تابع دیگری هم توسط اوراکل ارائه شده است که برای مشروط کردن خروجی JSON document در دستور select از آن استفاده می شود این تابع json_exists می باشد که در ادامه با آن آشنا خواهیم شد.

قصد داریم با اجرای دستورات زیر، اطلاعات دیگری را هم به جدول tbl2 اضافه کنیم:

SQL> insert into tbl2 values(3,'{“First_Name”:”sara”}’);

1 row inserted

SQL> insert into tbl2 values(4,'{“First_Name”:”mahdi”,”Last_Name”:”Usefzadeh”}’);

1 row inserted

SQL> select ETTELAAT from tbl2;

در این شرایط برای تعیین تعداد رکوردهایی که فیلد Last_Name در آن موجود است، می توان از دستور زیر استفاده کرد:

SQL> select count(*) from tbl2 where JSON_EXISTS (ETTELAAT, ‘$.Last_Name‘);

  COUNT(*)

———-

         2

به عنوان مثال دوم، با کمک تابع JSON_EXISTS در دستور select زیر، رکوردهایی که در آنها فیلد First_Name برابر با mahdi می باشد نمایش داده می شوند:

SQL> select * from tbl2 where JSON_EXISTS (ETTELAAT, ‘$.First_Name?(@ == “mahdi”)’);

        ID ETTELAAT

———- ————————————————

  4         {“First_Name”:”mahdi”,”Last_Name”:”Usefzadeh”}