اوراکل در نسخه 23c قابلیتی به نام JSON Relational Duality View را ارائه کرده است که می توان از طریق آن به طور همزمان از(بسیاری از) مزیتهای Relational data model و JSON data model بهرمند شد.

می دانیم که انتخاب هر کدام از این دیتامدلها می تواند بر حسب شرایط، مزایا و معایبی را به همراه داشته باشد. به طور مثال در مدل Relational می توان از مزیتهایی نظیر «جلوگیری از Data duplication، نرمالسازی، consistency» و … بهره گرفت و از طرف دیگر «خوانایی، سادگی، خود توصیفی، hierarchical document» نمونه هایی از مزیتهای JSON هستند.

بر اساس قابلیت JSON Relational Duality View، دیتا در جداول به صورت relational ذخیره می شوند و با ایجاد یک Duality View بر روی جداول رابطه ای، امکان دسترسی و دستکاری(Delete، Insert، Update) دیتا به صورت JSON هم به وجود خواهد آمد و Duality Viewها بر خلاف Viewهای معمولی، امکان write را بر روی جداول رابطه ای فراهم می کنند(حتی در حالت Complex View).

در زمان استفاده از این قابلیت، با توجه به ذخیره شدن اطلاعات در جداول رابطه ای، دستورات SQLای متعارف برای اجرای عملیات DML کاملا معتبر هستند و در کنار آن Developer این انتخاب را دارد تا با استفاده از Duality Viewها عملیات DML را با فرمت JSON انجام دهد که این مسئله mapping راحت تر بین Application Object و Relational Table را به همراه دارد و در بعضی از موارد می تواند نیاز به convert دیتا به فرمت JSON و یا برعکس را از بین ببرد.

در ادامه با ایجاد دو جدول book_tbl و author_tbl سعی خواهیم کرد نحوه کار با JSON Relational Duality View را شرح دهیم:

SQL> create table book_tbl(
ID number primary key,
title varchar2(100),
total_pages number,
pblished_date date,
AUTHID number
);
Table created

SQL> create table author_tbl(
ID number primary key,
name varchar2(100),
gender varchar2(100),
current_job varchar2(100),
country varchar2(40)
);		  
Table created

SQL> alter table BOOK_TBL add constraint FK_AUTH foreign key (AUTHID) references AUTHOR_TBL (ID);
Table altered

SQL> insert into author_tbl values(1,'Rahmat Rabbani','m','Oracle DBA','IRAN');
1 row inserted

SQL> insert into author_tbl values(2,'Armin Bahamin','m','Developer','IRAN');
1 row inserted

SQL> insert into book_tbl values(1,'expert oracle goldengate',100,sysdate - 100,1);
1 row inserted

SQL> insert into book_tbl values(2,'database administrators guide',451,sysdate - 300,1);
1 row inserted

SQL> insert into book_tbl values(3,'multitenant administrators guide',365,sysdate - 9000,2);
1 row inserted

SQL> select * from author_tbl;

ID NAME             GENDER     CURRENT_JOB    COUNTRY   
--- ---------------- ---------- -------------  --------- 
  1 Rahmat Rabbani   m          Oracle DBA     IRAN      
  2 Armin Bahamin    m          Developer      IRAN      

SQL> select * from book_tbl;
ID TITLE                             TOTAL_PAGES PBLISHED_     AUTHID
-- --------------------------------- ----------- --------- ----------
 3 multitenant administrators guide          365 22-JAN-99          2
 2 database administrators guide             451 17-NOV-22          1
 1 expert oracle goldengate                  100 05-JUN-23          1

با استفاده از دو جدول فوق، قصد داریم Duality Viewای به نام DVW_AUTHOR را ایجاد کنیم.

توجه: در زمان ایجاد Duality View می توان تعیین کرد که کدام یک از عملیاتهای DMLای{delete، insert، update} از طریق این view بر روی جداول امکان پذیر است با استفاده از عبارت with می توان برای هر کدام از جداول موجود در Duality View سیاست جداگانه ای را وضع کرد.

create or replace json relational duality view DVW_AUTHOR as
select json { 'authorID' :a.ID,
       'NAME' :a.NAME,
       'GENDER' :a.GENDER,
       'CURRENT_JOB' :a.CURRENT_JOB,
       'COUNTRY' :a.COUNTRY,
       'BOOKS:' :[
        select json { 'BOOKID' :b.ID,
               'BOOK_TITLE' :b.TITLE,
               'PBLISHED_DATE' :b.PBLISHED_DATE}
          from book_tbl b with insert update delete
         where b.AUTHID = a.ID ] } from author_tbl a with insert update delete;

این ویو صرفا یک ستون با نام DATA(از نوع JSON) خواهد داشت:

SQL> desc DVW_AUTHOR
 Name    Null    Type
 ------- -------- -----
 DATA             JSON

و با کوئری گرفتن از این ویو، اطلاعات به فرمت JSON نمایش داده می شوند البته اوراکل متناسب با محتویات رکوردها، برای هر رکورد metadata ای را هم اضافه می کند:

select * from DVW_AUTHOR;
DATA
------
{"_metadata":{"etag":"348F443F59A4EF647B945A64BF4E91A4","asof":"000000000DDDBC2A"},"authorID":1,"NAME":"Rahmat Rabbani","GENDER":"m","CURRENT_JOB":"Oracle DBA","COUNTRY":"IRAN","BOOKS:":[{"BOOKID":1,"BOOK_TITLE":"expert oracle goldengate","PBLISHED_DATE":"2023-06-05T12:53:02"},{"BOOKID":2,"BOOK_TITLE":"database administrators guide","PBLISHED_DATE":"2022-11-17T12:52:54"}]}

{“_metadata”:{“etag”:”6022DCA6D9299826A27A96AF48AEAFAF”,”asof”:”000000000DDDBC2A”},”authorID”:2,”NAME”:”Armin Bahamin”,”GENDER”:”m”,”CURRENT_JOB”:”Developer”,”COUNTRY”:”IRAN”,”BOOKS:”:[{“BOOKID”:3,”BOOK_TITLE”:”multitenant administrators guide”,”PBLISHED_DATE”:”1999-01-22T12:53:21”}]}

برای نمایش خواناتر اطلاعات می توانیم از تابع json_serialize استفاده کنیم:

select json_serialize(data pretty) from DVW_AUTHOR;
{
  "_metadata" :
  {
    "etag" : "348F443F59A4EF647B945A64BF4E91A4",
    "asof" : "000000000DDDC967"
  },
  "authorID" : 1,
  "NAME" : "Rahmat Rabbani",
  "GENDER" : "m",
  "CURRENT_JOB" : "Oracle DBA",
  "COUNTRY" : "IRAN",
  "BOOKS:" :
  [
    {
      "BOOKID" : 1,
      "BOOK_TITLE" : "expert oracle goldengate",
      "PBLISHED_DATE" : "2023-06-05T12:53:02"
    },
    {
      "BOOKID" : 2,
      "BOOK_TITLE" : "database administrators guide",
      "PBLISHED_DATE" : "2022-11-17T12:52:54"
    }
  ]
}


{
  "_metadata" :
  {
    "etag" : "6022DCA6D9299826A27A96AF48AEAFAF",
    "asof" : "000000000DDDC967"
  },
  "authorID" : 2,
  "NAME" : "Armin Bahamin",
  "GENDER" : "m",
  "CURRENT_JOB" : "Developer",
  "COUNTRY" : "IRAN",
  "BOOKS:" :
  [
    {
      "BOOKID" : 3,
      "BOOK_TITLE" : "multitenant administrators guide",
      "PBLISHED_DATE" : "1999-01-22T12:53:21"
    }
  ]
}											 		   

نمایش فیلدهای به خصوصی از این خروجی هم امکان پذیر است:

SQL> select a.data.NAME author_name,a.data.COUNTRY from DVW_AUTHOR a;
AUTHOR_NAME          COUNTRY
-------------------- -------- 
"Rahmat Rabbani"     "IRAN"
"Armin Bahamin"      "IRAN"

همانطور که در ابتدای متن اشاره شد، این نوع از viewها امکان write را هم فراهم می کنند. برای مثال، از طریق دستور زیر، مشخصات نویسنده جدید به همراه مشخصات کتابی که تالیف کرده است را اضافه می کنیم:

SQL>insert into DVW_AUTHOR(data)
values
  ('
{
  "authorID" : 3,
  "NAME" : "Khaje Dizaji",
  "GENDER" : "m",
  "CURRENT_JOB" : "Oracle DBA",
  "COUNTRY" : "IRAN",
  "BOOKS:" :
  [
    {
      "BOOKID" : 4,
      "BOOK_TITLE" : "oracle-database utilities",
      "PBLISHED_DATE" : "2020-01-05T12:53:02"
    }
    ]
}'
)
1 row inserted

با اجرای دستور فوق، در پس زمینه دستورات زیر اجرا شده اند:

PARSING IN CURSOR #139653041346472 len=191 dep=1 uid=152 oct=2 lid=152 tim=16258905355234 hv=4055157581 ad='69948ea0' sqlid='ahwvrqzsv9kud'

insert into “USEF”.”AUTHOR_TBL” (“ID”, “NAME”, “GENDER”, “COUNTRY”, “CURRENT_JOB”) values (:1, :2, :3, :4, :5) returning sys_row_etag(“COUNTRY”, “CURRENT_JOB”, “GENDER”, “ID”, “NAME”) into :6

insert into “USEF”.”BOOK_TBL” (“ID”, “TITLE”, “AUTHID”, “PBLISHED_DATE”) values (:1, :2, :3, :4) returning sys_row_etag(“ID”, “PBLISHED_DATE”, “TITLE”) into :5

پس از درج سطر فوق از طریق ویو DVW_AUTHOR، محتویات دو جدول را بازبینی می کنیم:

select * from book_tbl;

SQL> select * from author_tbl;

با کوئری گرفتن از ویو DVW_AUTHOR خروجی برای رکورد فوق به صورت JSON نمایش داده خواهد شد:

SQL>select json_serialize(data pretty) from DVW_AUTHOR a where a.data.authorID=3;

JSON_SERIALIZE(DATAPRETTY)
{
  “_metadata” :
  {
    “etag” : “9EFE9F9AA864D4BDDBA0F53728755968”,
    “asof” : “000000000DDE67F9”
  },
  “authorID” : 3,
  “NAME” : “Khaje Dizaji”,
  “GENDER” : “m”,
  “CURRENT_JOB” : “Oracle DBA”,
  “COUNTRY” : “IRAN”,
  “BOOKS:” :
  [
    {
      “BOOKID” : 4,
      “BOOK_TITLE” : “oracle-database utilities”,
      “PBLISHED_DATE” : “2020-01-05T00:00:00”
    }
  ]
}

به روزرسانی رکوردهای DVW_AUTHOR هم به صورت زیر امکان پذیر است:

SQL> update DVW_AUTHOR a
set a.data = 
  (‘
{
  “authorID” : 3,
  “NAME” : “Mohammd Khaje Dizaji”,
  “GENDER” : “m”,
  “CURRENT_JOB” : “DB2 DBA”,
  “COUNTRY” : “IRAN”,
  “BOOKS:” :
  [
    {
      “BOOKID” : 4,
      “BOOK_TITLE” : “oracle-database utilities”,
      “PBLISHED_DATE” : “2023-01-05T12:53:02”
    }
    ]
}’
)
where a.data.authorID=3
1 row updated.

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

PARSING IN CURSOR #139653040354168 len=233 dep=1 uid=152 oct=6 lid=152 tim=16259505322568 hv=3682468397 ad=’6eb36d60’ sqlid=’2ht0ms3drw0jd’
update “USEF”.”AUTHOR_TBL” set “NAME”=:1, “CURRENT_JOB”=:2 where “ID”=:3 and sys_row_etag(“COUNTRY”, “CURRENT_JOB”, “GENDER”, “ID”, “NAME”)=RAWTOHEX(:4) returning sys_row_etag(“COUNTRY”, “CURRENT_JOB”, “GENDER”, “ID”, “NAME”) into :5
update “USEF”.”BOOK_TBL” set “PBLISHED_DATE”=:1 where “ID”=:2 and sys_row_etag(“ID”, “PBLISHED_DATE”, “TITLE”)=RAWTOHEX(:3) returning sys_row_etag(“ID”, “PBLISHED_DATE”, “TITLE”) into :4

با این بروزرسانی، اطلاعات مربوط به رکوردی که اخیرا ثبت شده بود، به صورت زیر تغییر خواهد کرد:

SQL> select json_serialize(data pretty) from DVW_AUTHOR a where a.data.authorID=3;

JSON_SERIALIZE(DATAPRETTY)
----------------------------------
{
  "_metadata" :
  {
    "etag" : "2CC293A2C3FBEBC62435F5AC6FD0C7F2",
    "asof" : "000000000DDE73BD"
  },
  "authorID" : 3,
  "NAME" : "Mohammd Khaje Dizaji",
  "GENDER" : "m",
  "CURRENT_JOB" : "DB2 DBA",
  "COUNTRY" : "IRAN",
  "BOOKS:" :
  [
    {
      "BOOKID" : 4,
      "BOOK_TITLE" : "oracle-database utilities",
      "PBLISHED_DATE" : "2023-01-05T00:00:00"
    }
  ]
}

البته بروزرسانی صرفا به روش فوق محدود نمی شود و می توانیم از تابع JSON_TRANSFORM هم برای این کار استفاده کنیم(رجوع شود به مطلب “بروزرسانی اطلاعات JSON با کمک تابع JSON_TRANSFORM“).

حذف رکورد هم به صورت زیر قابل انجام است:

SQL> delete DVW_AUTHOR a where a.data.authorID=3;
1 row deleted

پ.ن: برای مشاهده لیست JSON Relational Duality Viewهای موجود در دیتابیس می توانیم از ویوی USER_JSON_DUALITY_VIEWS استفاده کنیم:

SQL> desc USER_JSON_DUALITY_VIEWS;
 Name               Type
 ------------------ ----------------
 VIEW_NAME          VARCHAR2(128)
 JSON_COLUMN_NAME   VARCHAR2(128)
 ROOT_TABLE_NAME    VARCHAR2(128)
 ROOT_TABLE_OWNER   VARCHAR2(128)
 ALLOW_INSERT       BOOLEAN
 ALLOW_UPDATE       BOOLEAN
 ALLOW_DELETE       BOOLEAN
 READ_ONLY          BOOLEAN
 JSON_SCHEMA        JSON
 STATUS             VARCHAR2(7)