Why is Oracle’s new JSON Duality a big thing? Understand the basics and decide for yourself!
Let’s get to the bottom of what is really possible with the JSON Duality feature of the Oracle Database 23ai, and see if this is something you should use in your next project.
Oracle announced the JSON Duality View as part of the launch of it’s new Database version 23ai, and put quite a focus on this new feature in the various presentations as a key element for developers to have the best of both worlds: the strong typing and clear relations of Database tables, together with the flexibility of storing JSON documents straight from an application context with no need to worry about normalisation.
So let’s investigate step by step what a JSON Duality view really is and how you can use it from both perspectives: the traditional DB view as well as the JSON developer perspective.
Creating the basic tables
Let’s start with the “hello world” database equivalent to get started … a department and an employee. I’ll keep it really simple on purpose as to not distract your attention. So first we create a Department table, with an ID, a name and a location, and we’ll insert a few rows into the table:
CREATE TABLE department
(deptno NUMBER(2,0),
dname VARCHAR2(14),
loc VARCHAR2(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno));
insert into department values (1, 'Sales', 'Brussels');
insert into department values (2, 'Marketing', 'Antwerp');
insert into department values (3, 'Production', 'Ghent');
commit;Of course you can query this table with SQL, but this is where we want to bring in the JSON view:
CREATE or replace JSON RELATIONAL DUALITY VIEW department_dv AS
SELECT JSON {'_id' : d.deptno,
'departmentName' : d.dname,
'location' : d.loc}
FROM department d WITH UPDATE INSERT DELETE;
select * from department;
select * from department_dv;sSo now we already have a basic double view of our data: as a classic SQL query, or as a JSON Document:
{"_id":1,"departmentName":"Sales","location":"Brussels","_metadata":{"etag":"B4CA8D53031BDA8EF42AF522DBFC90AB","asof":"00000000005062E5"}}
{"_id":2,"departmentName":"Marketing","location":"Antwerp","_metadata":{"etag":"06E9B4C923C0AB98E8AA6E79A668FB54","asof":"00000000005062E5"}}
{"_id":3,"departmentName":"Production","location":"Ghent","_metadata":{"etag":"965F09A0F56C99FAD2B531593D1321FC","asof":"00000000005062E5"}}But as you might have noticed, we used the”UPDATE and INSERT” keywords when we defined our JSON Duality table : it is more than just a view … we can also update, insert or delete the JSON data, which is directly reflected in the tables. Let’s change the location of the Sales department:
update department_dv set data = ('{"_id":1,"departmentName":"Sales","location":"Bruges"}') where json_value(DATA, '$._id.numberOnly()') = 1;Looking at the content of the table shows that the update was done in the table:
DEPTNO DNAME LOC
------- ------------ ----------
1 Sales Bruges
2 Marketing Antwerp
3 Production GhentAnd of course it’s also possible to insert data using the JSON view of the world:
insert into department_dv values ('{"_id":4,"departmentName":"IT","location":"Brussels"}');Conclusion: So the first elementary use of JSON Duality is already quite neat: I can insert, query and update my data either as normal table-based data, or alternatively do these same operations on JSON documents.
But wait a second … where is my flexibility?
So far this whole exercise is quite interesting, but it looks very “database table” oriented! I need to start from a Database schema, then build the JSON views onto these tables, and then I need to strictly comply with that schema ?
No you don’t ! Oracle introduced the concept of “Flex JSON Columns”, which allow you to store any extra fields that might be of interest as pure JSON, but at the same time impose the “database” side of the data structure.
Let’s amend our Department table with an extra JSON type column first, and then define a second JSON Duality view to offer this flexibility:
alter table department add flex_json JSON(object);
CREATE or REPLACE JSON RELATIONAL DUALITY VIEW department_dv2 AS
SELECT JSON {'_id' : d.deptno,
'departmentName' : d.dname,
'location' : d.loc,
d.flex_json as flex column}
FROM department d WITH UPDATE INSERT DELETE;We can now insert a new record with some extra data “Address” that has not been specified upfront, and is not present in the Database Tables
insert into department_dv2
values ('{"_id":5,"departmentName":"HR",
"location":"Leuven","Address":"Lippenslaan 34"}');And again, we can retrieve that info through both views, either as JSON:
{"_id":1,"departmentName":"Sales","location":"Bruges","_metadata":{"etag":"452774F489EACD42699B2A33FB0EDAA2","asof":"0000000000507A2E"}}
{"_id":2,"departmentName":"Marketing","location":"Antwerp","_metadata":{"etag":"A8DC54E2B505E85A5C3C683914CE7EB0","asof":"0000000000507A2E"}}
{"_id":3,"departmentName":"Production","location":"Ghent","_metadata":{"etag":"E89F06EF081610D83BD9E43D7A44ACC1","asof":"0000000000507A2E"}}
{"_id":4,"departmentName":"IT","location":"Brussels","_metadata":{"etag":"00FF670F630FD4F8741D206CCD68C5C7","asof":"0000000000507A2E"}}
{"_id":5,"departmentName":"HR","location":"Leuven","Address":"Lippenslaan 34","_metadata":{"etag":"C33EA9580E426623DACE3FD2A754FCD3","asof":"0000000000507A2E"}}Or via a classic query, whereby we look for available fields “Address” in the generic JSON column:
select deptno, dname, loc, json_value(flex_json,'$.Address') as detail
from department;
DEPTNO DNAME LOC DETAIL
------ -------------- ------------- -------------
1 Regional Sales Bruges
2 Marketing Antwerp
3 Production Ghent
4 IT Brussels
5 HR Leuven Lippenslaan 34Conclusion: We keep the flexibility of JSON documents, allowing applications to manage really instructured data, while at the same time reflecting this in the Database tables, either as a defined column or in the Flex JSON column!
Adding relational tables to the mix
So far we’ve worked with a single table, just to clarify the core principles of the JSON Duality. But covering the relational nature of data stored in database tables is at the core of JSON Duality, offering even more power once we start working with data that is normalized in tables, but captured in a single JSON document in the application.
To illustrate this, let’s add the Employee table to the database, add a few employees and build JSON Duality view of adepartment, including the employees of that department:
CREATE TABLE employee
(empno NUMBER(4,0),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4,0),
sal NUMBER(7,2),
deptno NUMBER(2,0),
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES department(deptno));
INSERT INTO employee (empno, ename, job, mgr, sal, deptno) VALUES (1001, 'John', 'Manager', 1000, 7500.00, 1);
INSERT INTO employee (empno, ename, job, mgr, sal, deptno) VALUES (1002, 'Peter', 'BusDev', 1000, 6500.00, 1);
INSERT INTO employee (empno, ename, job, mgr, sal, deptno) VALUES (1003, 'Eric', 'Engineer', 1000, 7000.00, 3);
INSERT INTO employee (empno, ename, job, mgr, sal, deptno) VALUES (1004, 'Marc', 'DBA', 1000, 5500.00, 4);
CREATE or replace JSON RELATIONAL DUALITY VIEW dept_employees_dv AS
SELECT JSON {'_id' : d.deptno,
'departmentName' : d.dname,
'location' : d.loc,
'employees' :
[ SELECT JSON {'employeeNumber' :e.empno,
'name' : e.ename}
FROM employee e WITH UPDATE INSERT DELETE
WHERE e.deptno = d.deptno ]}
FROM department d WITH UPDATE INSERT DELETE;We can now simply query the department information in JSON format by accessing the dept_employees_dv:
{"_id":1,"departmentName":"Sales","location":"Bruges",
"employees":[
{"employeeNumber":1001,"name":"John"},
{"employeeNumber":1002,"name":"Peter"}],
"_metadata":{"etag":"5D5696EF136CABE08EE6AD67DC98ABAD","asof":"00000000005FDF5E"}}
{"_id":2,"departmentName":"Marketing","location":"Antwerp",
"employees":[],
"_metadata":{"etag":"8CA21B5E91C9FEC5790941C4659664A4","asof":"00000000005FDF5E"}}
{"_id":3,"departmentName":"Production","location":"Ghent",
"employees":[
{"employeeNumber":1003,"name":"Eric"}],
"_metadata":{"etag":"A574EE8CEE6E6941B215FCE649EF1218","asof":"00000000005FDF5E"}}
{"_id":4,"departmentName":"IT","location":"Brussels",
"employees":[
{"employeeNumber":1004,"name":"Marc"}],
"_metadata":{"etag":"0E11F2C026A4D99AFA90BE419C0B9C55","asof":"00000000005FDF5E"}}And again, the magic is in the capability to insert/update/delete directly via this view :
- Update the info of an employee (employee “John” becomes “Yann”, and the employee table entry gets updated
update dept_employees_dv set data = (
'{"_id":1,"departmentName":"Sales",
"location":"Bruges",
"employees":[
{"employeeNumber":1001,"name":"Yann"},
{"employeeNumber":1002,"name":"Peter"}]}')
where json_value(DATA, '$._id.numberOnly()') = 1;- Use the same update, this time changing the name of the department:
update dept_employees_dv set data = (
'{"_id":1,"departmentName":"Regional Sales",
"location":"Bruges",
"employees":[
{"employeeNumber":1001,"name":"Yann"},
{"employeeNumber":1002,"name":"Peter"}]}')
where json_value(DATA, '$._id.numberOnly()') = 1;So writing back a JSON document into the Duality View will automatically update all the underlying, related tables as required. And you have full control over what you want to allow and what not, theough the definition of the JSON view itself.
And of course you can create multiple JSON views, adapting the views definition to the specific requirements of each application working with JSON Data.
Conclusion
In this article I showed you the core principles of JSON Duality, using the most basic data schema to illustrate clearly the principles of JSON Duality:
- CRUD access to your data both using classic SQL Table queries, as well as directly using JSON Document operations
- Control of the Structure and the realtions of your database schema, but flexibility to insert any type of extra data throught the Flex JSON type
- Capture Database table relations and the equivalent nested JSON document structures by defining your JSON view accordingly
You can now have a single Database model with JSON Duality views defined on these tables: this data can be accessed at the same time by applications that use classic SQL Table queries as well as by applications that are exclusively managing their data using JSON.
