DEV Community

Cover image for Python Developer Track for Oracle JSON and Duality Views - Part 7: Data Modeling, including Duality Views
Javier
Javier

Posted on • Edited on

Python Developer Track for Oracle JSON and Duality Views - Part 7: Data Modeling, including Duality Views

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:

  1. SODA collections
  2. Hybrid tables
  3. 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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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.

Image description

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:

relational_model

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);

Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
/
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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'}]}
Enter fullscreen mode Exit fullscreen mode

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)