The best of Relational and JSON — at the same time
Oracle Database 23ai JSON-Relational Duality Views allow data to be stored as rows in tables to provide the benefits of SQL access in a relational model, while also allowing read and write access as JSON documents to the exact same data. This blog shows how to use the new views in Python.
Photo by Pietro Jeng on Unsplash
The relational model is great: you can avoid data duplication; data consistency is guaranteed; and you have access via a very powerful, very efficient language — SQL. But, for developers, the requirement to define a relational schema — to decide on tables, columns and data types — before beginning to code is a chore. It’s not easy to
predict future uses for the system, some of which may be difficult with the chosen schema.
This is why you love JSON. A JSON object can contain all the information for one use case without the need to use SQL to join tables. Access is via a simple query, or a single call to a database API. JSON is schema-flexible so, as your use cases evolve over the lifetime of a system, you can easily modify applications. But there are
drawbacks: a single hierarchy may only allow a few use cases. Data can end up being duplicated, which affects not just space but makes it very hard to keep consistent. Optimization is harder. So what appears to be a simple model can end up causing long term complexity.
This is where Oracle Database 23ai JSON-Relational Duality Views are an outright game changer — not a phrase I use lightly. These duality views build on Oracle Database’s long history of JSON support and longer history with the relational model.
Let’s start by creating two relational tables:
drop table if exists AuthorTab;
drop table if exists BookTab;
create table AuthorTab (
AuthorId number generated by default on null as identity primary key,
AuthorName varchar2(100)
);
create table BookTab (
BookId number generated by default on null as identity primary key,
BookTitle varchar2(100),
AuthorId number references AuthorTab (AuthorId)
);
insert into AuthorTab values (1, 'Isabel M. Rich');
insert into AuthorTab values (2, 'Bobbie Cool');
insert into AuthorTab values (3, 'Charlie Shore');
insert into BookTab values (1, 'The Mysterious Dog', 1);
insert into BookTab values (2, 'The Mysterious Pony', 1);
insert into BookTab values (3, 'The Mysterious Tiger', 1);
insert into BookTab values (4, 'Self Help for Programmers', 2);
insert into BookTab values (5, 'More Self Help for Programmers', 2);
insert into BookTab values (6, 'Travel Guide Volume I', 3);
insert into BookTab values (7, 'Travel Guide Volume II', 3);
commit;
These can obviously be accessed via relational SELECT, INSERT etc statements which are trivial to execute in Python using python-oracledb, for example:
import oracledb
connection = oracledb.connect(user="cj", password="MySecret",
dsn="localhost/orclpdb1")
with connection.cursor() as cursor:
sql = """select AuthorName, BookTitle
from AuthorTab, BookTab
where AuthorTab.AuthorId = BookTab.AuthorId"""
for r in cursor.execute(sql):
print(r)
The output lists the authors and their books:
('Isabel M. Rich', 'The Mysterious Dog')
('Isabel M. Rich', 'The Mysterious Pony')
('Isabel M. Rich', 'The Mysterious Tiger')
('Bobbie Cool', 'Self Help for Programmers')
('Bobbie Cool', 'More Self Help for Programmers')
('Charlie Shore', 'Travel Guide Volume I')
('Charlie Shore', 'Travel Guide Volume II')
The fun is to create a duality view. In your SQL editor run:
create or replace json relational duality view BookDV as
BookTab @insert @update @delete
{
_id: BookId,
book_title: BookTitle,
author: AuthorTab @insert @update
{
author_id: AuthorId,
author_name: AuthorName
}
};
This creates a view that is accessible using JSON. The syntax shown here is GraphQL, le
Truncated by Planet PHP, read more at the original (another 6376 bytes)
