Introduction
This is a great feature of Oracle 23c. A view that is both a Relational object and a JSON object, giving you all the benefits of a relational system and a document system.
The benefits of JSON are clear. They are
- Schema Flexible
- Easier to Query
- Human Readable
- No Fixed Schema Structure
- Self Contained
- Highly Compatible with Web Technologies
The benefits of a relational model are not always so clear to developers in today's IT landscape, but they are
- Rigid Schema Structure
- Normalization for Efficiency
- Complex Query Support
- ACID Compliant
- High Levels of Data Integrity
Each model has its downsides, which I don't intend to describe here, as they tend towards the antithesis of their respective benefits. But what a JSON relational duality view gives you is the benefits of both by providing the structured JSON document which is based on underlying relational tables. This allows you to query and update documents in the ways you're used to via your JSON centric applications, whilst at the same time take advantage of all of the advanced processing, high performance, and security features offered by Oracle Database.
An Example.
This example uses a supermarket concept of categories and products.
1. Create a category table
create table category ( catID number constraint PKcatID primary key, catName varchar2(500) ) ;
2. Create a product table
create table product ( productNumber number constraint PKprdno primary key, productName varchar2(500), productDesc varchar2(2000), productIgredients varchar(2000), catID number constraint FKprdno references category );
3. Create a FK index
create index FKindex_1 on product(catID);
4. Insert the categories
insert into category values (1,'AMBIENT'); insert into category values (2,'TINNED');
5. Insert the products
insert into product values (1,'Baked Beans','baked beans is packed with fiber, low in sugar and virtually fat free','beans',2); insert into product values (2,'Vegetable Soup','vegetable soup low in salt. Contains allergens','vegetables',2); insert into product values (3,'Tomatoes','fresh vine tomatoes','tomatoes',1); insert into product values (4,'Cabbage','White Cabbage','cabbage',1);
6. Create the JSON Relational Duality View
create or replace json relational duality view v_products as select json {'category' : a.catID, 'category name' : a.catName, 'products' : [ select json {'product_number' : b.productNumber, 'product_name' : b.productName, 'product_desc' : b.productDesc, 'product_Ingredients' : b.productIgredients} from product b with insert update delete where a.catID = b.catID ]} from category a with insert update delete;
7. Select the data from our JSON Relational Duality View
select * from v_products
8. Output
{"_metadata":{"etag":"2B8F951DFAE854B62C","asof":"0000000000240A7
"},"category":1,"category name":"AMBIENT","products":[{"product_number":3,"product_name":"Tomatoes","product_desc":"fresh vine tomatoes","product_Ingredients"
:"tomatoes"},{"product_number":4,"product_name":"Cabbage","product_desc":"White Cabbage","product_Ingredients"
:"cabbage"}]}
{"_metadata":{"etag":"2B8F951DFAE854B62D","asof":"0000000000240A7
"},"catergory":2,"catergory name":"TINNED","products":[{"product_number":1,"product_name":"Baked Beans","product_desc":"baked beans is packed with fiber, low in sugar and virtually fat free","product_Ingredients"
:"beans"},{"product_number":2,"product_name":"Vegetable Soup","product_desc":"vegetable soup low in salt. Contains allergens","product_Ingredients":"vegetables"}]}Further information can be found here - JSON Relational Duality Views
Acknowledgement: Thanks to Kishy Kumar for his inputs in improving this article. He pioneered the work on JSON Relational Duality in Oracle Database 23ai and shaped the ideas covered in this article. His contributions to databases including JSON Duality have pushed the boundaries of what’s possible.
Published 21st October 2023
