Oracle Database 23ai: JSON Relational Duality Views
Oracle has just announced the release of Oracle Database 23ai on 2nd May 2024. You can find the announcement here. The new release is packed with 300+ new features. One of the features introduced is the JSON Relational Duality View. In this post we will look at the basics and try to understand this new feature.
What is JSON Relational Duality View?
The modern application development often use unstructured or semi-structured data, where data may be stored in the form of objects or documents or simply as key-value pairs. Different applications, coded in different languages and following different standards are integrated to each other. These integrated applications need a common standard to communicate and transmit data with each other. JSON is becoming the de-facto standard for storing and transmitting this data. It is a way of representing JavaScript objects in text-format. However, we can’t define relationships between the JSON documents themselves, the application must code relationships separately, as part of its logic. In particular, values that are part of one document cannot be shared by others. This leads to data duplication across different documents, which in turn can introduce inconsistencies when documents are updated.
On the other hand, when this data is stored in relational databases, the data is normalized and structured in a set of relational tables, which makes the data processing very efficient and versatile.
Jason Relational Duality View addresses this problem. It gives the best of both worlds. It exposes data stored in relational database tables as JSON documents. According to Oracle documentation: “The documents are materialized — generated on demand, not stored as such. Duality views give your data both a conceptual and an operational duality: it’s organized both relationally and hierarchically. You can base different duality views on data that’s stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data.”
In short, what it means is that you can apply CRUD operations to the data using normal SQL, also we can do the same through any language having JSON support.
Let us understand with an example. We will be using the EMPLOYEES AND DEPARTMENTS tables available in the HR schema. You can refer to my blog here, to check how you can download and install Oracle Database 23ai Free on Oracle VM or how to run it on a Docker, here.
So, let’s begin:
Query the Departments Table:
SQL> SELECT * FROM hr.departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
________________ _____________________ _____________ ______________
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
Create JSON Relational Duality View
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW j_vw_dept AS
SELECT JSON {'_id' :d.department_id,
'deptName':d.department_name,
'MgrID' IS d.manager_id,
'LocID' IS d.location_id
}
FROM hr.departments d with insert update delete;
View J_VW_DEPT created.
Here, we are mapping the relational table.column_names (department_id, department_name, manager_id, location_id) to the column names ( _id, deptName, MgrID, LocID), that would be exposed in the JSON documents.
Also notice that we have used the keywords insert, update and delete, to control the DML operations that can be performed.
We can now select directly from the Duality view.
SQL> Select * from j_vw_dept;
DATA
________________________________________________________________________________________________________
{"_id":10,"deptName":"Administration","MgrID":200,"LocID":1700,"_metadata":{"etag":"4ACDE7AD7DC4A24A5F0789B0E2EE9318","asof":"000000000079792A"}}
{"_id":20,"deptName":"Marketing","MgrID":201,"LocID":1800,"_metadata":{"etag":"DEA26E4EBD429F44DE1C75ABA3161052","asof":"000000000079792A"}}
{"_id":30,"deptName":"Purchasing","MgrID":114,"LocID":1700,"_metadata":{"etag":"0FAF1EF54C7AAFAEE882C0B352FBDC2C","asof":"000000000079792A"}}
{"_id":40,"deptName":"Human Resources","MgrID":203,"LocID":2400,"_metadata":{"etag":"343638B3BBF361CB5519C192795722DF","asof":"000000000079792A"}}
{"_id":50,"deptName":"Shipping","MgrID":121,"LocID":1500,"_metadata":{"etag":"7DF15A35E039FC5764489667CCE149FA","asof":"000000000079792A"}}
{"_id":60,"deptName":"IT","MgrID":103,"LocID":1400,"_metadata":{"etag":"875AEDF41CEF357EFAFB594825156F8E","asof":"000000000079792A"}}
{"_id":70,"deptName":"Public Relations","MgrID":204,"LocID":2700,"_metadata":{"etag":"DD5ABEA07BB0EE2BC3765BCAECE0F772","asof":"000000000079792A"}}
{"_id":80,"deptName":"Sales","MgrID":145,"LocID":2500,"_metadata":{"etag":"D685D281008A858531056FAC0E616CFA","asof":"000000000079792A"}}
{"_id":90,"deptName":"Executive","MgrID":100,"LocID":1700,"_metadata":{"etag":"12BE6D5B993E9067A07C130234376DD2","asof":"000000000079792A"}}
{"_id":100,"deptName":"Finance","MgrID":108,"LocID":1700,"_metadata":{"etag":"4F362719FC5F6BC9F260EFAF6BE1D716","asof":"000000000079792A"}}
{"_id":110,"deptName":"Accounting","MgrID":205,"LocID":1700,"_metadata":{"etag":"E143B72DB16F4165E0C6D3F526CD5243","asof":"000000000079792A"}}
{"_id":120,"deptName":"Treasury","MgrID":null,"LocID":1700,"_metadata":{"etag":"F2C9EB92CD5BCFB9290AB3E8974E6946","asof":"000000000079792A"}}
{"_id":130,"deptName":"Corporate Tax","MgrID":null,"LocID":1700,"_metadata":{"etag":"30B97A90F35550A48AAFFE60C9094F9A","asof":"000000000079792A"}}
{"_id":140,"deptName":"Control And Credit","MgrID":null,"LocID":1700,"_metadata":{"etag":"32D60823AE4EA580F031696D5964BD75","asof":"000000000079792A"}}
{"_id":150,"deptName":"Shareholder Services","MgrID":null,"LocID":1700,"_metadata":{"etag":"11744E05CC5555ADEBC7B171AE7AEEF7","asof":"000000000079792A"}}
{"_id":160,"deptName":"Benefits","MgrID":null,"LocID":1700,"_metadata":{"etag":"38B28536E84D912098476D354B1AC5DD","asof":"000000000079792A"}}
{"_id":170,"deptName":"Manufacturing","MgrID":null,"LocID":1700,"_metadata":{"etag":"B6A10B4833645557AE66DA4F47F26014","asof":"000000000079792A"}}
{"_id":180,"deptName":"Construction","MgrID":null,"LocID":1700,"_metadata":{"etag":"69B6603CD9511D8A2BCDA2D8D24E3E4A","asof":"000000000079792A"}}
{"_id":190,"deptName":"Contracting","MgrID":null,"LocID":1700,"_metadata":{"etag":"1F37FD4A4BFDF13CA8F1098B7D92B853","asof":"000000000079792A"}}
{"_id":200,"deptName":"Operations","MgrID":null,"LocID":1700,"_metadata":{"etag":"089A323C36622469246A00CB665981EF","asof":"000000000079792A"}}
{"_id":210,"deptName":"IT Support","MgrID":null,"LocID":1700,"_metadata":{"etag":"8B327BD5EB68A49AEFA1C4A2DED7F536","asof":"000000000079792A"}}
{"_id":220,"deptName":"NOC","MgrID":null,"LocID":1700,"_metadata":{"etag":"74748B6DFEC3ED0D7DB09DE6D140DF54","asof":"000000000079792A"}}
{"_id":230,"deptName":"IT Helpdesk","MgrID":null,"LocID":1700,"_metadata":{"etag":"5B2BE2CE481208D55F27737550DFF5BA","asof":"000000000079792A"}}
{"_id":240,"deptName":"Government Sales","MgrID":null,"LocID":1700,"_metadata":{"etag":"F6F51B54396D4410576BEA7A2C4A3C43","asof":"000000000079792A"}}
{"_id":250,"deptName":"Retail Sales","MgrID":null,"LocID":1700,"_metadata":{"etag":"549BEA246B2DC4BD9593886634215416","asof":"000000000079792A"}}
{"_id":260,"deptName":"Recruiting","MgrID":null,"LocID":1700,"_metadata":{"etag":"EC3BEF0F5BC18DFB07BF6206D4F1405C","asof":"000000000079792A"}}
{"_id":270,"deptName":"Payroll","MgrID":null,"LocID":1700,"_metadata":{"etag":"F062792C3837E1AA66F6DA9F0B2AF9DD","asof":"000000000079792A"}}
27 rows selected.
Notice that we have got one row for each department.
Additionally, we also get the header metadata with etag and asof. This is to enable optimistic locking for web-applications. We shall discuss optimistic locking in another blog.
You can use the json_serialize function to make the output more readable.
SQL> Select json_serialize(data pretty) from j_vw_dept;
JSON_SERIALIZE(DATAPRETTY)
_______________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 10,
"_metadata" :
{
"etag" : "4ACDE7AD7DC4A24A5F0789B0E2EE9318",
"asof" : "0000000000797CCF"
},
"deptName" : "Administration",
"MgrID" : 200,
"LocID" : 1700
}
{
"_id" : 20,
"_metadata" :
{
"etag" : "DEA26E4EBD429F44DE1C75ABA3161052",
"asof" : "0000000000797CCF"
},
"deptName" : "Marketing",
"MgrID" : 201,
"LocID" : 1800
}
{
"_id" : 30,
"_metadata" :
{
"etag" : "0FAF1EF54C7AAFAEE882C0B352FBDC2C",
"asof" : "0000000000797CCF"
},
"deptName" : "Purchasing",
"MgrID" : 114,
"LocID" : 1700
}
{
"_id" : 40,
"_metadata" :
{
"etag" : "343638B3BBF361CB5519C192795722DF",
"asof" : "0000000000797CCF"
},
"deptName" : "Human Resources",
"MgrID" : 203,
"LocID" : 2400
}
{
"_id" : 50,
"_metadata" :
{
"etag" : "7DF15A35E039FC5764489667CCE149FA",
"asof" : "0000000000797CCF"
},
"deptName" : "Shipping",
"MgrID" : 121,
"LocID" : 1500
}
{
"_id" : 60,
"_metadata" :
{
"etag" : "875AEDF41CEF357EFAFB594825156F8E",
"asof" : "0000000000797CCF"
},
"deptName" : "IT",
"MgrID" : 103,
"LocID" : 1400
}
{
"_id" : 70,
"_metadata" :
{
"etag" : "DD5ABEA07BB0EE2BC3765BCAECE0F772",
"asof" : "0000000000797CCF"
},
"deptName" : "Public Relations",
"MgrID" : 204,
"LocID" : 2700
}
{
"_id" : 80,
"_metadata" :
{
"etag" : "D685D281008A858531056FAC0E616CFA",
"asof" : "0000000000797CCF"
},
"deptName" : "Sales",
"MgrID" : 145,
"LocID" : 2500
}
{
"_id" : 90,
"_metadata" :
{
"etag" : "12BE6D5B993E9067A07C130234376DD2",
"asof" : "0000000000797CCF"
},
"deptName" : "Executive",
"MgrID" : 100,
"LocID" : 1700
}
{
"_id" : 100,
"_metadata" :
{
"etag" : "4F362719FC5F6BC9F260EFAF6BE1D716",
"asof" : "0000000000797CCF"
},
"deptName" : "Finance",
"MgrID" : 108,
"LocID" : 1700
}
{
"_id" : 110,
"_metadata" :
{
"etag" : "E143B72DB16F4165E0C6D3F526CD5243",
"asof" : "0000000000797CCF"
},
"deptName" : "Accounting",
"MgrID" : 205,
"LocID" : 1700
}
{
"_id" : 120,
"_metadata" :
{
"etag" : "F2C9EB92CD5BCFB9290AB3E8974E6946",
"asof" : "0000000000797CCF"
},
"deptName" : "Treasury",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 130,
"_metadata" :
{
"etag" : "30B97A90F35550A48AAFFE60C9094F9A",
"asof" : "0000000000797CCF"
},
"deptName" : "Corporate Tax",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 140,
"_metadata" :
{
"etag" : "32D60823AE4EA580F031696D5964BD75",
"asof" : "0000000000797CCF"
},
"deptName" : "Control And Credit",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 150,
"_metadata" :
{
"etag" : "11744E05CC5555ADEBC7B171AE7AEEF7",
"asof" : "0000000000797CCF"
},
"deptName" : "Shareholder Services",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 160,
"_metadata" :
{
"etag" : "38B28536E84D912098476D354B1AC5DD",
"asof" : "0000000000797CCF"
},
"deptName" : "Benefits",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 170,
"_metadata" :
{
"etag" : "B6A10B4833645557AE66DA4F47F26014",
"asof" : "0000000000797CCF"
},
"deptName" : "Manufacturing",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 180,
"_metadata" :
{
"etag" : "69B6603CD9511D8A2BCDA2D8D24E3E4A",
"asof" : "0000000000797CCF"
},
"deptName" : "Construction",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 190,
"_metadata" :
{
"etag" : "1F37FD4A4BFDF13CA8F1098B7D92B853",
"asof" : "0000000000797CCF"
},
"deptName" : "Contracting",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 200,
"_metadata" :
{
"etag" : "089A323C36622469246A00CB665981EF",
"asof" : "0000000000797CCF"
},
"deptName" : "Operations",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 210,
"_metadata" :
{
"etag" : "8B327BD5EB68A49AEFA1C4A2DED7F536",
"asof" : "0000000000797CCF"
},
"deptName" : "IT Support",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 220,
"_metadata" :
{
"etag" : "74748B6DFEC3ED0D7DB09DE6D140DF54",
"asof" : "0000000000797CCF"
},
"deptName" : "NOC",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 230,
"_metadata" :
{
"etag" : "5B2BE2CE481208D55F27737550DFF5BA",
"asof" : "0000000000797CCF"
},
"deptName" : "IT Helpdesk",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 240,
"_metadata" :
{
"etag" : "F6F51B54396D4410576BEA7A2C4A3C43",
"asof" : "0000000000797CCF"
},
"deptName" : "Government Sales",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 250,
"_metadata" :
{
"etag" : "549BEA246B2DC4BD9593886634215416",
"asof" : "0000000000797CCF"
},
"deptName" : "Retail Sales",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 260,
"_metadata" :
{
"etag" : "EC3BEF0F5BC18DFB07BF6206D4F1405C",
"asof" : "0000000000797CCF"
},
"deptName" : "Recruiting",
"MgrID" : null,
"LocID" : 1700
}
{
"_id" : 270,
"_metadata" :
{
"etag" : "F062792C3837E1AA66F6DA9F0B2AF9DD",
"asof" : "0000000000797CCF"
},
"deptName" : "Payroll",
"MgrID" : null,
"LocID" : 1700
}
27 rows selected.
Now let us take a step further and create a JSON Relational Duality View which de-normalizes the tables DEPARTMENTS and EMPLOYEES.
SQL> CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW j_vw_deptemp AS
SELECT JSON {'_id' IS d.department_id,
'deptName' IS d.department_name,
'MgrID' IS d.manager_id,
'LocID' IS d.location_id,
'employees' IS [ select json { 'empId' IS e.employee_id,
'empFirstName' IS e.first_name,
'empLastName' IS e.last_name,
'email' IS e.email,
'phone' IS e.phone_number,
'hireDate' IS e.hire_date,
'job' IS e.job_id,
'salary' IS e.salary,
'commission' IS e.commission_pct,
'managerId' IS e.manager_id}
from employees e with insert update delete
where d.department_id = e.department_id ] }
FROM hr.departments d with insert update delete;
View J_VW_DEPTEMP created.
We query the data. (**Showing only a part of the output below)
SQL> Select * from j_vw_deptemp;
{"_id":10,"deptName":"Administration","MgrID":200,"LocID":1700,"employees":[{"empId":200,"empFirstName":"Jennifer","empLastName":"Whalen","email":"JWHALEN","phone":"515.123.4444","hireDate":"2003-09-17T00:00:00","job":"AD_ASST","salary":4400,"commission":null,"managerId":101}],"_metadata":{"etag":"F114D57DC751F07705E084D389567441","asof":"00000000007983D6"}}{"_id":20,"deptName":"Marketing","MgrID":201,"LocID":1800,"employees":[{"empId":201,"empFirstName":"Michael","empLastName":"Hartstein","email":"MHARTSTE","phone":"515.123.5555","hireDate":"2004-02-17T00:00:00","job":"MK_MAN","salary":13000,"commission":null,"managerId":100},{"empId":202,"empFirstName":"Pat","empLastName":"Fay","email":"PFAY","phone":"603.123.6666","hireDate":"2005-08-17T00:00:00","job":"MK_REP","salary":6000,"commission":null,"managerId":201}],"_metadata":{"etag":"970A6BE884005790BC5388FC5A4EB012","asof":"00000000007983D6"}}
~~~~~~~~
{"_id":220,"deptName":"NOC","MgrID":null,"LocID":1700,"employees":[],"_metadata":{"etag":"E360D9CDB41EF0CCAB8D9982FB6661A4","asof":"00000000007983D6"}}
{"_id":230,"deptName":"IT Helpdesk","MgrID":null,"LocID":1700,"employees":[],"_metadata":{"etag":"426AD90EB6C85CCB4475090FC0089D6C","asof":"00000000007983D6"}}
{"_id":240,"deptName":"Government Sales","MgrID":null,"LocID":1700,"employees":[],"_metadata":{"etag":"4D784400653F14F96FB9DEAB0941349E","asof":"00000000007983D6"}}
{"_id":250,"deptName":"Retail Sales","MgrID":null,"LocID":1700,"employees":[],"_metadata":{"etag":"402D5702E8EEAFAB18323BD101ACA622","asof":"00000000007983D6"}}
{"_id":260,"deptName":"Recruiting","MgrID":null,"LocID":1700,"employees":[],"_metadata":{"etag":"D721FC0F101E49052DB3106F13BB431D","asof":"00000000007983D6"}}
{"_id":270,"deptName":"Payroll","MgrID":null,"LocID":1700,"employees":[],"_metadata":{"etag":"438802948433A88DF64BB2FB13FAFC33","asof":"00000000007983D6"}}
~~~~~~~~
** Note: There is a limitation of 4000 character for json_serialize function. I got the following error:
You can also select the JSON document for a specific department. Here, I am select the records from the duality view for DEPARTMENT_ID = 210. Notice that the array for employees is NULL, as DEPARTMENT_ID = 210 does not have any employees.
SQL> SELECT json_serialize(data pretty) from j_vw_deptemp j WHERE j.data."_id" = 210;
JSON_SERIALIZE(DATAPRETTY)
________________________________________________________________________________________________________
{
"_id" : 210,
"_metadata" :
{
"etag" : "BC5EB481B74D81A4BB100EC8AD371F92",
"asof" : "000000000079644C"
},
"deptName" : "IT Support",
"MgrID" : null,
"LocID" : 1700,
"employees" :
[
]
}
You can check the metadata of the viewusing DESC. I’m using SQLcl, so am using INFO. It contains only one column of data-type JSON.
SQL> INFO j_vw_deptemp
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
DATA JSON Yes
Details about the JSON-Relational Duality Views can be found in the following views:
- USER_JSON_DUALITY_VIEWS
- USER_JSON_DUALITY_VIEW_TABS
- USER_JSON_DUALITY_VIEW_TAB_COLS
- USER_JSON_DUALITY_VIEW_LINKS
Now let us perform some DML operations.
Update using JSON Relational Duality View
I will add an employee for DEPARTMENT_ID = 210 using the JSON Relational View.
SQL> UPDATE j_vw_deptemp jde
SET data = ('
{ "_id" : "210",
"deptName" :"IT Support",
"MgrID" : NULL,
"LocID" : "1700",
"employees" : [ { "empId" : 90000,
"empFirstName" : "Rishin",
"empLastName" : "Mitra",
"email" : "RMitra",
"phone" : "12345",
"hireDate" : "2002-06-07",
"job" : "IT_PROG",
"salary" : "12008",
"commission" : NULL,
"managerId" : NULL} ] }
')
WHERE jde.data."_id" = 210;
1 row updated.
If I now query the duality view, I shall see the employee.
I select the records from the duality view for DEPARTMENT_ID = 210. Notice that the array for employees is NULL, as DEPARTMENT_ID = 210 does not have any employees.
SQL> SELECT json_serialize(data pretty) from j_vw_deptemp j WHERE j.data."_id" = 210;
JSON_SERIALIZE(DATAPRETTY)
________________________________________________________________________________________________________
{
"_id" : 210,
"_metadata" :
{
"etag" : "BC5EB481B74D81A4BB100EC8AD371F92",
"asof" : "000000000079644C"
},
"deptName" : "IT Support",
"MgrID" : null,
"LocID" : 1700,
"employees" :
[
]
}
I can directly query the employees table.
SQL> SELECT * FROM employees where department_id = 210;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ _________ _______________ ____________ __________ _________ _________________ _____________ ________________
90000 Rishin Mitra RMitra 12345 07-JUN-02 IT_PROG 12008 210
Insert using JSON Relational Duality View
Using this duality view, we can insert a new department and an employee for the department, as shown below:
SQL> INSERT INTO j_vw_deptemp jde (data)
VALUES('
{ "_id" : "300",
"deptName" :"JSON-INSERT",
"MgrID" : NULL,
"LocID" : "1700",
"employees" : [ { "empId" : 99999,
"empFirstName" : "Darryl",
"empLastName" : "G",
"email" : "DG",
"phone" : "67890",
"hireDate" : "2002-06-07",
"job" : "IT_PROG",
"salary" : "12008",
"commission" : NULL,
"managerId" : NULL} ] }
');
1 row inserted.
We can query the view.
SQL> SELECT json_serialize(data pretty) FROM j_vw_deptemp j WHERE j.data."_id" = 300;
JSON_SERIALIZE(DATAPRETTY)
________________________________________________________________________________________________________
{
"_id" : 300,
"_metadata" :
{
"etag" : "58BC105CBA0A642F552DCAD54A0BCC40",
"asof" : "0000000000796EC2"
},
"deptName" : "JSON-INSERT",
"MgrID" : null,
"LocID" : 1700,
"employees" :
[
{
"empId" : 99999,
"empFirstName" : "Darryl",
"empLastName" : "G",
"email" : "DG",
"phone" : "67890",
"hireDate" : "2002-06-07T00:00:00",
"job" : "IT_PROG",
"salary" : 12008,
"commission" : null,
"managerId" : null
}
]
}
We can also query the relational tables to confirm that the records have been inserted successfully.
SQL> SELECT * FROM departments WHERE department_id = 300 ;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
________________ __________________ _____________ ______________
300 JSON-INSERT 1700
SQL> SELECT * FROM employees WHERE department_id = 300 ;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ ________ _______________ ____________ __________ _________ _________________ _____________ ________________
99999 Darryl G DG 67890 07-JUN-02 IT_PROG 12008 300
Delete using JSON Relational Duality View
We can delete the data from the underlying relational tables if we delete a department from the duality view.
SQL> Delete From j_vw_deptemp j WHERE j.data."_id" = 300 ;
1 row deleted.
We query the data first from the view.
SQL> Select json_serialize(data pretty) from j_vw_deptemp j WHERE j.data."_id" = 300;
no rows selected
We query the relational tables to confirm.
SQL> Select * from departments where department_id = 300 ;
no rows selected
SQL> Select * from employees Where DEPARTMENT_ID = 300 ;
no rows selected
And there you go, we have learnt how we can create and JSON Relational Duality View and how we can perform DML operations on the underlying relational tables using a JSON.
In the next post, we will see some use-cases using JSON Relational Duality View.
References
RishOraDev's Oracle Blogs 