In this post we are going to cover the different ways of data modeling. Oracle Database 23c with JSON offers a great variety of flexibility when designing a data model. Although we are going to have a specific chapter for each of them, the great thing is that you can combine them! Let's explore them!
When working with JSON and Oracle Database 23c, we can identify three main categories:
- SODA collections
- Hybrid tables
- Duality Views
1. SODA Collections
Until now, we have been working with SODA collections. A SODA collection contains documents. These collections are stored in an Oracle Database schema (in this tutorial, myapp). A SODA collection is analogous to an Oracle Database table or view. Wait a minute? a table? Let's have a look! Let's find our collection. Let's connect and describe that collection:
[root@dualityviews opc]# sqlplus myapp/PassworD123##@localhost:1521/FREEPDB1
SQL*Plus: Release 23.0.0.0.0 - Production on Tue Dec 5 16:59:51 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Thu Nov 30 2023 15:50:38 +00:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> desc HOTEL_RESERVATIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL RAW(4000)
CREATED_ON NOT NULL TIMESTAMP(6)
LAST_MODIFIED NOT NULL TIMESTAMP(6)
VERSION NOT NULL VARCHAR2(255)
DATA JSON
SQL>
The collection we created via SODA, is creating a table underneath! What we can see here, is the metadata information. All the JSON documents are stored under the column DATA, as JSON data type. The metadata is automatically populated and there is nothing to do!
The next question is... if data is stored in a table, can I run SQL queries? And the answer is YES! Developers can use SODA APIs to create an application meanwhile business can run analytics on top of the data!
The next part will cover how to run SQL over JSON data, but here you have an example:
SQL> select h.data.payment_status, h.data.reservation_id
2* from HOTEL_RESERVATIONS h;
PAYMENT_STATUS RESERVATION_ID
_________________ _________________
"paid" "1"
"paid" "2"
"paid" "3"
"paid" "4"
"paid" "5"
SQL>
If you want to have a look into the metadata info, you can run a select all query:
SQL> select * from hotel_reservations where rownum <2;
ID CREATED_ON LAST_MODIFIED VERSION DATA
_____________________________ __________________________________ __________________________________ ___________________________________ __________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
08655F63E40AE7791964F8F859 23-NOV-23 02.38.28.210730000 PM 23-NOV-23 02.38.28.210730000 PM CC51DD2489C04F76BFAA85CC32F15747 {"checkin_date":"2023-06-03","num_adults":2,"reservation_id":"1","hotel_id":"123","room_id":"105","payment_status":"paid","num_children":0,"guest_contact_info":{"email":"mrodriguez@example.com","phone":"777-4231","address":{"city":"Paris","country":"France"}},"_id":"655f63e40ae7791964f8f859","checkout_date":"2023-06-07","total_cost":650,"guest_name":{"first_name":"Maria","last_name":"Rodriguez"}}
SQL>
2. Hybrid tables
As we saw at the example before, there was a table with a column with data type JSON. You can create your own structures, combining relational and JSON data. This is a very common scenario, we have some data that is fixed, but some other data is variable. We can create this combination without any problem.
The only thing to consider, is that we will be using SQL to create this data model. Therefore we can work as a normal table but we can't use SODA APIs. SODA collections has a static metadata although the content is variable. So if you chose this model, we can query it only via SQL. Let's have a look.
In this example, we are going to create the table souvenir. Here we have some fixed fields, like the email of the customer and the room number. However, customer can buy different souvenirs with different characteristics. We will store that data in JSON:
SQL> create table souvenir(
2 email varchar2(50),
3 room_number number,
4 products json
5* );
Table SOUVENIR created.
SQL>
SQL> insert into souvenir values(
2 'mrodriguez@example.com',
3 105,
4 '[{
5 "id": 1,
6 "type": "t-shirt",
7 "price": 15,
8 },
9 {
10 "id": 2,
11 "type": "ball",
12 "price": 3,
13 }
14 ]
15 '
16* );
1 row inserted.
SQL>
SQL> insert into souvenir values(
2 'ethan.lee@example.com',
3 315,
4 '[{
5 "id": 1,
6 "type": "shoes",
7 "price": 75,
8 },
9 {
10 "id": 2,
11 "type": "glasses",
12 "price": 100,
13 }
14 ]
15 '
16* );
1 row inserted.
SQL>
SQL> commit;
This is great! Very easy to combine data! Now let's run some queries:
SQL> select s.email,s.room_number,s.products.type
2* from souvenir s;
EMAIL ROOM_NUMBER TYPE
_________________________ ______________ ______________________
mrodriguez@example.com 105 ["t-shirt","ball"]
ethan.lee@example.com 315 ["shoes","glasses"]
SQL>
As I mention before, we can mix data models. Let's try to combine our hybrid model, our souvenir table, with the hotel_reservation collection to get more meaningful information using a join:
SQL> select h.data.guest_name.first_name "Name",h.data.guest_name.last_name "Last Name",s.email "Email",s.room_number "Room",s.products.type "Products"
2 from souvenir s,hotel_reservations h
3* where h.data.guest_contact_info.email=s.email;
Name Last Name Email Room Products
__________ ______________ _________________________ _______ ______________________
"Maria" "Rodriguez" mrodriguez@example.com 105 ["t-shirt","ball"]
"Ethan" "Lee" ethan.lee@example.com 315 ["shoes","glasses"]
SQL>
3. Duality Views
We have seen a great flexibility before, but can we go further? Yes we can!
Oracle Database 23c Duality Views unifies the benefits of the Relational and Document worlds. Using Duality Views, data is still stored in relational tables in a highly efficient normalized format but is accessed by apps in the form of JSON documents.
Developers can thus think in terms of JSON documents for data access while using the highly efficient relational model for data storage, without having to compromise simplicity or efficiency.
How do we get started? As stated before, first we need to design the relational model. We are going to take a single JSON document, check which information we have and we are going to design the relational model. I have done this work for you, and the relational model looks like the following:
Once the relational model is defined, we will create the Duality Views. These views will define how the JSON will look like. We can use a combination of different tables, and we will be able to operate as JSON documents. Let's do it. First, let's generate all the relational model:
create table hotel(
hotel_id int primary key
);
insert into hotel values (123);
commit;
create table room(
room_id int primary key,
hotel_id int,
constraint fk_room_hotel foreign key (hotel_id) references hotel(hotel_id)
);
insert into room values (105,123);
insert into room values (315,123);
insert into room values (207,123);
insert into room values (222,123);
insert into room values (101,123);
insert into room values (305,123);
commit;
create table reservation(
reservation_id int primary key,
guest_id number,
room_id int,
checkin_date date,
checkout_date date,
num_adults int,
num_children int,
guest_email varchar2(100),
additional_requests json,
constraint fk_room_reservation foreign key (room_id) REFERENCES ROOM(room_id)
);
insert into reservation values(1,1,105,TO_DATE('2023-06-03 12:00:00', 'yyyy-MM-dd hh:mi:ss'),TO_DATE('2023-06-07 12:00:00', 'yyyy-MM-dd hh:mi:ss'),2,0,'mrodriguez@example.com','');
insert into reservation values(2,2,315,TO_DATE('2023-06-15 12:00:00', 'yyyy-MM-dd hh:mi:ss'),TO_DATE('2023-06-17 12:00:00', 'yyyy-MM-dd hh:mi:ss'),1,0,'ethan.lee@example.com','');
insert into reservation values(3,3,207,TO_DATE('2023-06-25 12:00:00', 'yyyy-MM-dd hh:mi:ss'),TO_DATE('2023-06-30 12:00:00', 'yyyy-MM-dd hh:mi:ss'),2,0,'olivia.johnson@example.com','');
insert into reservation values(4,4,222,TO_DATE('2023-06-07 12:00:00', 'yyyy-MM-dd hh:mi:ss'),TO_DATE('2023-06-17 12:00:00', 'yyyy-MM-dd hh:mi:ss'),2,0,'liam.patel@example.com','');
insert into reservation values(5,5,101,TO_DATE('2023-06-01 12:00:00', 'yyyy-MM-dd hh:mi:ss'),TO_DATE('2023-06-05 12:00:00', 'yyyy-MM-dd hh:mi:ss'),2,1,'john.smith@example.com','[{"type": "extra_bed","quantity": 1},{"type": "late_checkout","details": "Please arrange for a 2pm checkout"}]');
insert into reservation values(6,6,305,TO_DATE('2023-06-04 12:00:00', 'yyyy-MM-dd hh:mi:ss'),TO_DATE('2023-06-20 12:00:00', 'yyyy-MM-dd hh:mi:ss'),2,0,'marcus.wong@example.com','');
commit;
create table guest(
guest_id number primary key,
email varchar2(100) unique,
first_name varchar2(100),
last_name varchar2(100),
phone varchar2(20),
city varchar2(50),
country varchar2(50)
);
insert into guest values(1,'mrodriguez@example.com','Maria','Rodriguez','777-4231','Paris','France');
insert into guest values(2,'ethan.lee@example.com','Ethan','Lee','123-8106','Madrid','Spain');
insert into guest values(3,'olivia.johnson@example.com','Olivia','Johnson','987-1890','Barcelona','Spain');
insert into guest values(4,'liam.patel@example.com','Liam','Patel','123-8106','Malaga','Spain');
insert into guest values(5,'john.smith@example.com','John','Smith','555-1234','Lyon','France');
insert into guest values(6,'marcus.wong@example.com','Marcus','Wong','123-1234','Nice','France');
commit;
alter table reservation add constraint fk_reservation_guest2 foreign key (guest_id) REFERENCES guest(guest_id);
create table bill(
bill_id int primary key,
guest_id int,
reservation_id int,
email varchar2(100),
total_cost number,
payment_status varchar2(20)
);
insert into bill values(1,1,1,'mrodriguez@example.com',650,'paid');
insert into bill values(2,2,2,'ethan.lee@example.com',350,'paid');
insert into bill values(3,3,3,'olivia.johnson@example.com',932,'pending');
insert into bill values(4,4,4,'liam.patel@example.com',350,'paid');
insert into bill values(5,5,5,'john.smith@example.com',800,'paid');
insert into bill values(6,6,6,'marcus.wong@example.com',1350,'cancelled');
commit;
alter table bill add constraint fk_guest_bill foreign key (guest_id) REFERENCES guest(guest_id);
alter table bill add constraint fk_reservation_bill foreign key (reservation_id) REFERENCES reservation(reservation_id);
Now let's create our first Duality View. This view will store information about our customers, combining information from the guest table and the reservation table:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW guest_dv AS
SELECT JSON {
'_id' : g.guest_id,
'email' is g.email,
'first_name' is g.first_name,
'last_name' is g.last_name,
'phone' is g.phone,
'city' is g.city,
'country' is g.country,
'reservation_info' is[
select json {r.reservation_id,r.room_id,r.checkin_date,r.checkout_date,r.num_adults,r.num_children,r.additional_requests}
from reservation r WITH INSERT NODELETE
where g.guest_id=r.guest_id ]}
FROM guest g WITH INSERT UPDATE NODELETE;
It is important to mention, that the primary key (guest_id) of our root table (guest) is defined as "_id". This is mandatory to make it work with MongoDB API later on.
Now let's run a simple select all, but using PRETTY to be able to read the JSON easier:
SQL> SELECT json_serialize(data PRETTY) FROM guest_dv where rownum<2;
JSON_SERIALIZE(DATAPRETTY)
_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 9,
"_metadata" :
{
"etag" : "CF69EF1C4CD0D0B7D22F467C1F263FA0",
"asof" : "0000000000A2C719"
},
"email" : "mike.smith@example.com",
"first_name" : "Mike",
"last_name" : "Smith",
"phone" : "999-1234",
"city" : "Toronto",
"country" : "Canada",
"reservation_info" :
[
{
"reservation_id" : 9,
"room_id" : 105,
"checkin_date" : "2023-09-04T00:00:00",
"checkout_date" : "2023-09-20T00:00:00",
"num_adults" : 2,
"num_children" : 1
}
]
}
SQL>
And of course we can run some SQL queries on top of it. For example, let's look for an email which contains Marcus:
SQL> SELECT json_serialize(data PRETTY) FROM guest_dv g where g.data.email like '%marcus%';
JSON_SERIALIZE(DATAPRETTY)
______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 6,
"_metadata" :
{
"etag" : "794D358786842954BB52C645D0602660",
"asof" : "0000000000A2C723"
},
"email" : "marcus.wong@example.com",
"first_name" : "Marcus",
"last_name" : "Wong",
"phone" : "123-1234",
"city" : "Nice",
"country" : "France",
"reservation_info" :
[
{
"reservation_id" : 6,
"room_id" : 305,
"checkin_date" : "2023-06-04T12:00:00",
"checkout_date" : "2023-06-20T12:00:00",
"num_adults" : 2,
"num_children" : 0
}
]
}
SQL>
And let's run a query to check how many reservations we have asking for an extra bed:
SQL> SELECT json_serialize(data PRETTY) FROM guest_dv b where json_exists(b.data.reservation_info.additional_requests[*].type, '$[*]?(@ == "extra_bed")');
JSON_SERIALIZE(DATAPRETTY)
___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 5,
"_metadata" :
{
"etag" : "1CE4912465045AD9F24459E0E2474360",
"asof" : "0000000000A2BD9A"
},
"email" : "john.smith@example.com",
"first_name" : "John",
"last_name" : "Smith",
"phone" : "555-1234",
"city" : "Lyon",
"country" : "France",
"reservation_info" :
[
{
"reservation_id" : 5,
"room_id" : 101,
"checkin_date" : "2023-06-01T12:00:00",
"checkout_date" : "2023-06-05T12:00:00",
"num_adults" : 2,
"num_children" : 1,
"additional_requests" :
[
{
"type" : "extra_bed",
"quantity" : 1
},
{
"type" : "late_checkout",
"details" : "Please arrange for a 2pm checkout"
}
]
}
]
}
SQL>
Even if we want to insert new documents, we can do it:
insert into guest_dv values('
{
"_id" : 7,
"email" : "ana.smith@example.com",
"first_name" : "Ana",
"last_name" : "Smith",
"phone" : "999-1234",
"city" : "Paris",
"country" : "France",
"reservation_info" :
[
{
"reservation_id" : 7,
"room_id" : 305,
"checkin_date" : "2023-09-04T12:00:00",
"checkout_date" : "2023-09-20T12:00:00",
"num_adults" : 2,
"num_children" : 1
}
]
}
');
commit;
This insert will introduce the information in the two tables at the same time. Is not cool?
Let's check this last document inserted:
SQL> SELECT json_serialize(data PRETTY) FROM guest_dv dv where dv.data."_id"=7;
JSON_SERIALIZE(DATAPRETTY)
___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 7,
"_metadata" :
{
"etag" : "FF991A0473A2933B5A4019E728824562",
"asof" : "0000000000A2C74F"
},
"email" : "ana.smith@example.com",
"first_name" : "Ana",
"last_name" : "Smith",
"phone" : "999-1234",
"city" : "Paris",
"country" : "France",
"reservation_info" :
[
{
"reservation_id" : 7,
"room_id" : 305,
"checkin_date" : "2023-09-04T00:00:00",
"checkout_date" : "2023-09-20T00:00:00",
"num_adults" : 2,
"num_children" : 1
}
]
}
SQL>
We can create as many duality views as we want. Let's create a new one for the billing, where we have information from our guest table and bill table:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW billing_dv AS
SELECT JSON {
'_id' IS b.bill_id,
'email' is b.email,
'total_cost' is b.total_cost,
'payment_status' is b.payment_status,
'reservation_id' is b.reservation_id,
'guest' is [
select json{g.guest_id,g.first_name,g.last_name,g.phone}
from guest g WITH INSERT NODELETE
where g.guest_id=b.bill_id
]
}
FROM bill b with UPDATE INSERT DELETE;
Let's have a look into our new duality view. Let's look for the id 6:
SQL> SELECT json_serialize(data PRETTY) FROM billing_dv bdv where bdv.data."_id"=6;
JSON_SERIALIZE(DATAPRETTY)
_________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 6,
"_metadata" :
{
"etag" : "9B75F6BC91C8B51712245AD13F32FFF8",
"asof" : "0000000000A2C9F4"
},
"email" : "marcus.wong@example.com",
"total_cost" : 1350,
"payment_status" : "cancelled",
"reservation_id" : 6,
"guest" :
[
{
"guest_id" : 6,
"first_name" : "Marcus",
"last_name" : "Wong",
"phone" : "123-1234"
}
]
}
SQL>
We can run SQL to update that duality view. Let's change it to paid:
UPDATE billing_dv dv
SET data = json_transform(data, SET '$.payment_status' = 'paid')
WHERE dv.data."_id"=6;
COMMIT;
We can check we had changed it to paid:
SQL> SELECT json_serialize(data PRETTY) FROM billing_dv bdv where bdv.data."_id"=6;
JSON_SERIALIZE(DATAPRETTY)
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{
"_id" : 6,
"_metadata" :
{
"etag" : "365D3894D95AF79F2ABB0BE007FA795C",
"asof" : "0000000000A2CA20"
},
"email" : "marcus.wong@example.com",
"total_cost" : 1350,
"payment_status" : "paid",
"reservation_id" : 6,
"guest" :
[
{
"guest_id" : 6,
"first_name" : "Marcus",
"last_name" : "Wong",
"phone" : "123-1234"
}
]
}
SQL>
Now that we have this duality views, how do we make them available to the developers? How SODA will know that this collections exists? For that, we need to execute the following code:
DECLARE col soda_collection_t;
BEGIN
col := DBMS_SODA.create_dualv_collection('BILLING_COLLECTION', 'BILLING_DV');
END;
/
This code maps the existing duality view (billing_dv) and maps it to a new collection (billing_collection). Now we can run any SODA python code. This will run transparently. Let's for example run a query to get all the customers bills, and get the one bigger than 1000. Remember that you have to run this code with the user you set the environment variables (in this tutorial root):
import oracledb
#thick mode for SODA
oracledb.init_oracle_client()
#connection details
connection = oracledb.connect(user="myapp",password="PassworD123##",dsn="localhost:1521/FREEPDB1")
#connect, create and list collections
soda = connection.getSodaDatabase()
list=soda.getCollectionNames()
print(list)
collection = soda.openCollection("BILLING_COLLECTION")
documents = collection.find().filter({'total_cost': {"$gt" : 1000}}).getDocuments()
for d in documents:
content = d.getContent()
print(content)
This will return only one document:
{'_metadata': {'etag': 'C68E0623A3B1CADB6979E3A6362518CA', 'asof': '0000000000827250'}, 'bill_id': 6, 'email': 'marcus.wong@example.com', 'total_cost': 1350, 'payment_status': 'cancelled', 'guest': [{'guest_id': 6, 'first_name': 'Marcus', 'last_name': 'Wong', 'phone': '123-1234'}]}
In the next chapter we will have a deeper look about how to run SQL over the different JSON documents:https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-8-run-sql-over-json-5eib
Top comments (0)