DEV Community: Javier

The One Billion Row Challenge, Autonomous Database (ADB) Edition

Javier — Tue, 06 Feb 2024 12:59:11 +0000

The internet is buzzing with the One Billion Row Challenge right now! The task is straightforward – write a Java program to fetch temperature measurements and calculate the min, max, and mean temperature per weather station. Yes, that’s dealing with a whopping 1 billion rows! You can check out the challenge details here: One Billion Row Challenge.

I've taken up this challenge myself. But, as I'm not a Java developer, I opted for SQL instead. Moreover, I approached this from a business angle, asking myself: How can I achieve peak performance with minimal effort? So, I decided to use Autonomous Database – Dedicated (ADB-D), Oracle’s state-of-the-art cloud database service, to tackle the challenge while harnessing its new In-memory (IM) capabilities.

Step 1: Generating the Data
I followed the steps outlined on this GitHub page to generate the data file, which turned out to be around 13 GB. After creating it, I uploaded this big file to Object Storage.

Step 2: Setting Up Autonomous Database with In-Memory
Creating an Autonomous Database with the In-memory option was a breeze. A few clicks to enable the feature and select the desired in-memory ratio, and I was all set.

Step 3: Running SQL with Ease
With the OCI native Database Tools, I didn’t need to install anything on my laptop to run SQL queries.

Step 4: Preparing for Data Import
First things first, I created a credential in the SQL worksheet to load data from Object Storage.

Step 5: Creating and Loading the Table
The table setup was simple – just city and temperature fields. I used the DBMS_CLOUD.COPY_DATA utility for loading. Alternatively, you could use the Database Actions’ Data Studio for a GUI-based experience, but I’m detailing everything SQL here.

Step 6: Loading Data into Oracle In-Memory
Once the table was ready (took about 1 minute to load), I executed a simple SQL statement to load the table into Oracle in-memory.

And now I will run the SQL query to generate the output:

The Results? Astounding!
The query ran in just 2.84 seconds! According to the GitHub leaderboard, that puts us at the top with minimal effort. And if we skip the plotting part, required by the challenge, it gets even faster – down to 1.3 seconds!

For this challenge, I utilized 16 ECPU with a total of 21 GB of RAM for in-memory. In comparison, all the Java tests were conducted on a 32 core AMD EPYC™ 7502P (Zen2) with 128 GB RAM.

Why Overcomplicate When You Can Simplify?
So, there you have it – my journey through the One Billion Row Challenge. It really makes you think, why spend more resources when you can achieve faster results with fewer cores and less memory? This challenge showcases the sheer power and efficiency of Autonomous Database – Dedicated (ADB-D), especially with its in-memory capabilities. It's a classic example of doing more with less, proving that sometimes the simplest approach can lead to the most impressive results.

Try this by yourself without any spending with Always Free Autonomous Database and let me know how about your experience!

Python Developer Track for Oracle JSON and Duality Views - Part 9: MongoDB API against Duality Views

Javier — Mon, 25 Dec 2023 12:02:02 +0000

In this part we are going to use all the benefits of the different data models, particularly on the Duality Views, but using MongoDB code. Oracle provides the MongoDB API which allows to reuse your same code, frameworks and tools. Let's try it!

We are going to use the same VM to install mongosh. We are going to download it directly using wget and install it via yum.

wget https://downloads.mongodb.com/compass/mongodb-mongosh-2.0.2.x86_64.rpm
yum localinstall mongodb-mongosh-2.0.2.x86_64.rpm

Before using the API, we need to make sure the ORDS is up and running. You can check the part 6 of the workshop to verify you have installed it and you have enabled the MongoDB API. Remember, to start ORDS we were using the following code:

cd /tmp
nohup ords serve >> ords_log.log 2>&1 &

If we check the log, ORDS will show which is the URL for using the MongoDB API. It will be like this:

mongodb://[{user}:{password}@]localhost:27017/{user}?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true

Using mongosh, we can use this URL to connect to our Oracle Developer Free 23c. The MongoDB API will translate the code from MongoDB into code which Oracle is able to understand.

From the URL, we have to change the user and the password. We are going to use the myapp that we have been using for the whole workshop.

It is important to notice that if we are using special characters, we will need to change it with the right one. For example, we need to change # with %23. In the following documentation link you can find the full list: documentation link

Now we can connect directly using mongosh. It is important to notice that we are using --tlsAllowInvalidCertificates because we have configured the ORDS without https and without any kind of certificate to make it simple.

mongosh --tlsAllowInvalidCertificates 'mongodb://myapp:PassworD123%23%23@localhost:27017/myapp?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

You can see we can connect!

[root@dualityviews opc]# mongosh --tlsAllowInvalidCertificates 'mongodb://myapp:PassworD123%23%23@localhost:27017/myapp?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
Current Mongosh Log ID: 65858a0a451279e22a657587
Connecting to:          mongodb://<credentials>@localhost:27017/myapp?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&tlsAllowInvalidCertificates=true&appName=mongosh+2.0.2
Using MongoDB:          4.2.14
Using Mongosh:          2.0.2
mongosh 2.1.1 is available for download: https://www.mongodb.com/try/download/shell

For mongosh info see: https://docs.mongodb.com/mongodb-shell/

myapp>

Now let's run some simple queries like checking the collections availables, count the number of documents in a collection or getting a specific document:

myapp> show collections
BILLING_COLLECTION
CarsWorkshopTour
hotel_reservations
restaurant_bookings
myapp> db.hotel_reservations.countDocuments()
5
myapp> db.hotel_reservations.find( {"reservation_id": "5"} )
[
  {
    _id: ObjectId("6582fcc50aec6a389818a1f1"),
    checkin_date: '2023-06-01',
    num_adults: 2,
    reservation_id: '5',
    hotel_id: '123',
    room_id: '101',
    payment_status: 'paid',
    num_children: 1,
    guest_contact_info: {
      email: 'john.smith@example.com',
      phone: '555-1234',
      address: { city: 'Lyon', country: 'France' }
    },
    additional_requests: [
      { type: 'extra_bed', quantity: 1 },
      {
        type: 'late_checkout',
        details: 'Please arrange for a 2pm checkout'
      }
    ],
    checkout_date: '2023-06-05',
    total_cost: 800,
    guest_name: { first_name: 'John', last_name: 'Smith' }
  }
]
myapp>

Now let's insert a new document into our SODA collection:

db.hotel_reservations.insertOne(
    {

    checkin_date: '2023-06-15',
    num_adults: 1,
    reservation_id: '16',
    hotel_id: '123',
    room_id: '315',
    payment_status: 'paid',
    num_children: 0,
    guest_contact_info: {
      email: 'jose.garcia@example.com',
      phone: '318-8206',
      address: { city: 'Madrid', country: 'Spain' }
    },
    checkout_date: '2023-06-17',
    total_cost: 350,
    guest_name: { first_name: 'Jose', last_name: 'Garcia' }
  }
)

We can validate we can insert new documents and query it back:

myapp> db.hotel_reservations.find( {"reservation_id": "16"} )
[
  {
    _id: ObjectId("65858ae0451279e22a657588"),
    checkin_date: '2023-06-15',
    num_adults: 1,
    reservation_id: '16',
    hotel_id: '123',
    room_id: '315',
    payment_status: 'paid',
    num_children: 0,
    guest_contact_info: {
      email: 'jose.garcia@example.com',
      phone: '318-8206',
      address: { city: 'Madrid', country: 'Spain' }
    },
    checkout_date: '2023-06-17',
    total_cost: 350,
    guest_name: { first_name: 'Jose', last_name: 'Garcia' }
  }
]
myapp>

Also we can update the document. For example, let's change the number of adults from 1 to 2:

myapp> db.hotel_reservations.updateOne(
...    { "reservation_id": "16" },
...    {
...      $set: { "num_adults": 2 }
...    }
... )
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
myapp> db.hotel_reservations.find( {"reservation_id": "16"} )
[
  {
    _id: ObjectId("65858ae0451279e22a657588"),
    checkin_date: '2023-06-15',
    num_adults: 2,
    reservation_id: '16',
    hotel_id: '123',
    room_id: '315',
    payment_status: 'paid',
    num_children: 0,
    guest_contact_info: {
      email: 'jose.garcia@example.com',
      phone: '318-8206',
      address: { city: 'Madrid', country: 'Spain' }
    },
    checkout_date: '2023-06-17',
    total_cost: 350,
    guest_name: { first_name: 'Jose', last_name: 'Garcia' }
  }
]
myapp>

Even if we updated the document via MongoDB, we can validate this change via SQL:

SQL> select h.data.reservation_id,h.data.num_adults from hotel_reservations h where h.data.reservation_id='16';

RESERVATION_ID    NUM_ADULTS
_________________ _____________
"16"              2

SQL>

Now is time to work with the Duality Views. In part 7 we created 2 Duality Views and we mapped one of them into a SODA collection. We need to do the same with the second one:

SQL> DECLARE col soda_collection_t;
  2  BEGIN
  3    col := DBMS_SODA.create_dualv_collection('GUEST_COLLECTION', 'GUEST_DV');
  4  END;
  5* /

PL/SQL procedure successfully completed.

SQL>

Now let's insert a new booking using the GUEST_COLLECTION, which is using Duality Views underneath through the MongoDB API. Then we are going to run a simple query to retrieve it:

myapp> db.GUEST_COLLECTION.insertOne(
...
... {
...   "_id" : 9,
...   "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-04T12:00:00",
...       "checkout_date" : "2023-09-20T12:00:00",
...       "num_adults" : 2,
...       "num_children" : 1
...     }
...   ]
... }
...
... )
{ acknowledged: true, insertedId: 9 }


myapp> db.GUEST_COLLECTION.find({"email":"mike.smith@example.com"})
[
  {
    _id: 9,
    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: ISODate("2023-09-04T00:00:00.000Z"),
        checkout_date: ISODate("2023-09-20T00:00:00.000Z"),
        num_adults: 2,
        num_children: 1
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64("z2nvHEzQ0LfSL0Z8HyY/oA==", 0),
      asof: Binary.createFromBase64("AAAAAADC748=", 0)
    }
  }
]
myapp>

If you run it via SQL, this row is also there!

SQL> select * from guest_dv;

DATA                                                                                                                                                                                                                                                                                          
_______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{"_id":1,"email":"mrodriguez@example.com","first_name":"Maria","last_name":"Rodriguez","phone":"777-4231","city":"Paris","country":"France","reservation_info":[{"reservation_id":1,"room_id":105,"checkin_date":"2023-06-03T12:00:00","checkout_date":"2023-06-07T12:00:00","num_adults":2,"num_children":0}],"_metadata":{"etag":"BB2CA7510104E3DAB370E37A949E9E9C","asof":"0000000000C2EF9F"}}
{"_id":2,"email":"ethan.lee@example.com","first_name":"Ethan","last_name":"Lee","phone":"123-8106","city":"Madrid","country":"Spain","reservation_info":[{"reservation_id":2,"room_id":315,"checkin_date":"2023-06-15T12:00:00","checkout_date":"2023-06-17T12:00:00","num_adults":1,"num_children":0}],"_metadata":{"etag":"6141ADC8B7D40FA88BC4AE7CE3D14AF7","asof":"0000000000C2EF9F"}}
{"_id":3,"email":"olivia.johnson@example.com","first_name":"Olivia","last_name":"Johnson","phone":"987-1890","city":"Barcelona","country":"Spain","reservation_info":[{"reservation_id":3,"room_id":207,"checkin_date":"2023-06-25T12:00:00","checkout_date":"2023-06-30T12:00:00","num_adults":2,"num_children":0}],"_metadata":{"etag":"836777ECE15724CCBDB8EA574A70B65F","asof":"0000000000C2EF9F"}}
{"_id":4,"email":"liam.patel@example.com","first_name":"Liam","last_name":"Patel","phone":"123-8106","city":"Malaga","country":"Spain","reservation_info":[{"reservation_id":4,"room_id":222,"checkin_date":"2023-06-07T12:00:00","checkout_date":"2023-06-17T12:00:00","num_adults":2,"num_children":0}],"_metadata":{"etag":"FF76B77C33D706332546D97BDCB814F3","asof":"0000000000C2EF9F"}}
{"_id":5,"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"}]}],"_metadata":{"etag":"1CE4912465045AD9F24459E0E2474360","asof":"0000000000C2EF9F"}}
{"_id":6,"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}],"_metadata":{"etag":"794D358786842954BB52C645D0602660","asof":"0000000000C2EF9F"}}
{"_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-04T00:00:00","checkout_date":"2023-09-20T00:00:00","num_adults":2,"num_children":1}],"_metadata":{"etag":"FF991A0473A2933B5A4019E728824562","asof":"0000000000C2EF9F"}}
{"_id":9,"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}],"_metadata":{"etag":"CF69EF1C4CD0D0B7D22F467C1F263FA0","asof":"0000000000C2EF9F"}}

8 rows selected.

SQL>

We had added a new booking, now we can add the billing for that specific customer. Let's insert a new document:


myapp> db.BILLING_COLLECTION.insertOne(
... {
...   "_id" : 9,
...   "email" : "mike.smith@example.com",
...   "total_cost" : 1350,
...   "payment_status" : "paid",
...   "reservation_id":9
... }
... )
{ acknowledged: true, insertedId: 9 }
myapp>

And query it:

myapp> db.BILLING_COLLECTION.find({"email":"mike.smith@example.com"})
[
  {
    _id: 9,
    email: 'mike.smith@example.com',
    total_cost: 1350,
    payment_status: 'paid',
    reservation_id: 9,
    guest: [
      {
        guest_id: 9,
        first_name: 'Mike',
        last_name: 'Smith',
        phone: '999-1234'
      }
    ],
    _metadata: {
      etag: Binary.createFromBase64("usx+aLzOATKs1iIZLVh5mA==", 0),
      asof: Binary.createFromBase64("AAAAAADC7/4=", 0)
    }
  }
]
myapp>

This is the end of the workshop! We have learn how to take advantage of the flexibility of the data modeling using Oracle Database, even through the MongoBD API. I hope you have enjoyed it!

Python Developer Track for Oracle JSON and Duality Views - Part 8: Run SQL over JSON

Javier — Thu, 21 Dec 2023 16:48:53 +0000

In this post, we are going to cover how to run SQL on top of your JSON. It doesn't matter which data model you choose (SODA, hybrid or Duality Views), you can run queries on top of them.

To run SQL on top of JSON, we are going to use the dot notation. We need to explicitly tell which is the field we want to query. Also is mandatory to have an alias for the table we plan to use.

Let's run an example. Let's query the payment_status field and the reservation_id. We want to see if we have a customer who didn't pay. If you recall from the last chapter, the HOTEL_RESERVATIONS is a SODA collection. The JSON is stored inside of a column called data. Therefore we have to use the alias (h), the column (data) and finally the field to query:

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"

If you are wondering if there is any limitation with SQL and JSON, there is not! Let's run another example where we want to sum the aggregated income per country:

SQL> select h.data.guest_contact_info.address.country "Country",sum(h.data.total_cost) "Total Income"
  2  from HOTEL_RESERVATIONS h
  3  group by  h.data.guest_contact_info.address.country
  4* order by sum(h.data.total_cost) desc;

Country        Total Income
___________ _______________
"Spain"                1632
"France"               1450

It is possible to run any SQL over JSON data but, what happens if the JSON is complex? We can run queries too! Let's use the collection restaurant_bookings. This JSON has an array called allergens. We can use the nested clause to see this data as relational:

SQL> select customers.*
  2  from RESTAURANT_BOOKINGS nested data columns
  3      (
  4      guest_email varchar2(50),
  5      board varchar2(10),
  6      included_in_reservation varchar2(10) ,
  7*     nested allergens[*] columns (allergen path '$')) customers;

GUEST_EMAIL                   BOARD    INCLUDED_IN_RESERVATION    ALLERGEN
_____________________________ ________ __________________________ ___________
olivia.johnson@example.com    full     true                       gluten
olivia.johnson@example.com    full     true                       nuts
liam.patel@example.com        full     true                       milk
liam.patel@example.com        full     true                       eggs

When we normalize the JSON data, we are going to have an individual row per element in the array. In the same way, we can use the JSON_TABLE clause to have JSON data printed as relational. In this example we are querying the fields inside the guest_contact_info document.

SQL> select jt.*
  2  from hotel_reservations, JSON_TABLE(data, '$' columns(
  3      reservation_id varchar(5),
  4      room_id varchar2(5),
  5      checkin_date date,
  6      checkout_date varchar2(15),
  7      email varchar2(50) path '$.guest_contact_info.email',
  8      phone varchar2(50) path '$.guest_contact_info.phone'
  9* )) jt;

RESERVATION_ID    ROOM_ID    CHECKIN_DATE    CHECKOUT_DATE    EMAIL                         PHONE
_________________ __________ _______________ ________________ _____________________________ ___________
1                 105        03-JUN-23       2023-06-07       mrodriguez@example.com        777-4231
2                 315        15-JUN-23       2023-06-17       ethan.lee@example.com         123-8106
3                 207        25-JUN-23       2023-06-30       olivia.johnson@example.com    987-1890
4                 222        07-JUN-23       2023-06-17       liam.patel@example.com        123-8106
5                 101        01-JUN-23       2023-06-05       john.smith@example.com        555-1234

Not all the JSON are always the same, that's one of the great benefits of using JSON. In our example, some customers had introduced some information which was optional. What happens if the data is not the same? There is no problem! This data will appear like null and the SQL query will run without problems. Check the columns type, quantity and details:

SQL> select jt.*
  2  from hotel_reservations, JSON_TABLE(data, '$' columns(
  3      reservation_id varchar(5),
  4      room_id varchar2(5),
  5      checkin_date date,
  6      checkout_date varchar2(15),
  7      email varchar2(50) path '$.guest_contact_info.email',
  8      phone varchar2(50) path '$.guest_contact_info.phone',
  9          nested path '$.additional_requests[*]' columns(
 10              type varchar2(20),
 11              quantity number,
 12              details varchar2(50)
 13          )
 14* )) jt;

RESERVATION_ID    ROOM_ID    CHECKIN_DATE    CHECKOUT_DATE    EMAIL                         PHONE       TYPE                QUANTITY DETAILS
_________________ __________ _______________ ________________ _____________________________ ___________ ________________ ___________ ____________________________________
1                 105        03-JUN-23       2023-06-07       mrodriguez@example.com        777-4231
2                 315        15-JUN-23       2023-06-17       ethan.lee@example.com         123-8106
3                 207        25-JUN-23       2023-06-30       olivia.johnson@example.com    987-1890
4                 222        07-JUN-23       2023-06-17       liam.patel@example.com        123-8106
5                 101        01-JUN-23       2023-06-05       john.smith@example.com        555-1234    extra_bed                  1
5                 101        01-JUN-23       2023-06-05       john.smith@example.com        555-1234    late_checkout                Please arrange for a 2pm checkout

Another great capability of using SQL, is that we can change a specific value of the JSON document. For example, a customer wants to change a room. We can update that specific value with a simple update. For that we are going to use JSON_TRANSFORM:

SQL> SELECT h.data.reservation_id, h.data.room_id, h.data.guest_contact_info.email
  2  from hotel_reservations h
  3* where h.data.reservation_id='4';

RESERVATION_ID    ROOM_ID    GUEST_CONTACT_INFO
_________________ __________ ___________________________
"4"               "222"      "liam.patel@example.com"

SQL>
SQL> update hotel_reservations h
  2  set h.data = JSON_TRANSFORM(data,
  3  set '$.room_id'='105'
  4  )
  5* where h.data.reservation_id='4';

1 row updated.

SQL>
SQL> commit;
SQL> SELECT h.data.reservation_id, h.data.room_id, h.data.guest_contact_info.email
  2  from hotel_reservations h
  3* where h.data.reservation_id='4';

RESERVATION_ID    ROOM_ID    GUEST_CONTACT_INFO
_________________ __________ ___________________________
"4"               "105"      "liam.patel@example.com"

If we want to return a scalar SQL value from a JSON scalar value, we can use JSON_VALUE. In this example we want to be sure that the reservation_id we receive is a number:

SQL> select JSON_Value (data, '$.reservation_id' returning number) from HOTEL_RESERVATIONS;

   JSON_VALUE(DATA,'$.RESERVATION_ID'RETURNINGNUMBER)
_____________________________________________________
                                                    1
                                                    2
                                                    3
                                                    4
                                                    5

Another query we could run, is to know how many customers have an additional request. For that we can use JSON_Exists, which will allow us to filter rows based on the content of a JSON document:

SQL> select count(1)
  2  from HOTEL_RESERVATIONS
  3* where JSON_Exists(data, '$.additional_requests');

   COUNT(1)
___________
          1

And we can go further! We can use JSON_Exists to query inside the array! In the next query, I want to get the customers in JSON format using JSON_SERIALIZE who have asked for an extra bed:

SQL> select JSON_SERIALIZE(h.data)
  2  from HOTEL_RESERVATIONS h
  3* where JSON_Exists(h.data, '$.additional_requests.type?(@ == "extra_bed")');

JSON_SERIALIZE(H.DATA)                                                                                                                                                                                                                                                                        
_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{"_id":"6582fcc50aec6a389818a1f1","checkin_date":"2023-06-01","num_adults":2,"reservation_id":"5","hotel_id":"123","room_id":"101","payment_status":"paid","num_children":1,"guest_contact_info":{"email":"john.smith@example.com","phone":"555-1234","address":{"city":"Lyon","country":"France"}},"additional_requests":[{"type":"extra_bed","quantity":1},{"type":"late_checkout","details":"Please arrange for a 2pm checkout"}],"checkout_date":"2023-06-05","total_cost":800,"guest_name":{"first_name":"John","last_name":"Smith"}}

SQL>

And finally if we want to simplify the queries to any application or Business Intelligence user, we can create a view on top of that JSON data. Using this view, I can query the JSON format and they will think it is a standard table!

SQL> create view reservation_info as
  2  select jt.*
  3  from hotel_reservations, JSON_TABLE(data, '$' columns(
  4      reservation_id varchar(5),
  5      room_id varchar2(5),
  6      checkin_date date,
  7      checkout_date varchar2(15),
  8      email varchar2(50) path '$.guest_contact_info.email',
  9      phone varchar2(50) path '$.guest_contact_info.phone',
 10          nested path '$.additional_requests[*]' columns(
 11              type varchar2(20),
 12              quantity number,
 13              details varchar2(50)
 14          )
 15* )) jt;

SQL> select * from reservation_info;

RESERVATION_ID    ROOM_ID    CHECKIN_DATE    CHECKOUT_DATE    EMAIL                         PHONE       TYPE                QUANTITY DETAILS
_________________ __________ _______________ ________________ _____________________________ ___________ ________________ ___________ ____________________________________
1                 105        03-JUN-23       2023-06-07       mrodriguez@example.com        777-4231
2                 315        15-JUN-23       2023-06-17       ethan.lee@example.com         123-8106
3                 207        25-JUN-23       2023-06-30       olivia.johnson@example.com    987-1890
4                 105        07-JUN-23       2023-06-17       liam.patel@example.com        123-8106
5                 101        01-JUN-23       2023-06-05       john.smith@example.com        555-1234    extra_bed                  1
5                 101        01-JUN-23       2023-06-05       john.smith@example.com        555-1234    late_checkout                Please arrange for a 2pm checkout

But if you are not sure of all the fields available on the JSON, or you want to avoid to write the query before, you can use the Data Guide we learn before. With the Data Guide, we can automatically create this view!

SQL> DECLARE
  2    dg CLOB;
  3    BEGIN
  4      SELECT JSON_DATAGUIDE(data,dbms_json.FORMAT_HIERARCHICAL,dbms_json.pretty) "DATA GUIDE"
  5        INTO dg
  6        FROM HOTEL_RESERVATIONS;
  7      DBMS_JSON.create_view('HOTEL_VIEW','HOTEL_RESERVATIONS','DATA',dg);
  8    END;
  9* /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from hotel_view;

CREATED_ON                         ID                            LAST_MODIFIED                      VERSION                             _id    room_id    hotel_id    last_name    first_name       num_adults    total_cost checkin_date       num_children checkout_date    payment_status    reservation_id    email                         phone       city         country    type             details                                 quantity
__________________________________ _____________________________ __________________________________ ___________________________________ ______ __________ ___________ ____________ _____________ _____________ _____________ _______________ _______________ ________________ _________________ _________________ _____________________________ ___________ ____________ __________ ________________ ____________________________________ ___________
20-DEC-23 02.39.15.505917000 PM    086582FC930AEC6A389818A1D9    20-DEC-23 02.39.15.505917000 PM    7CDDA8ADC0F54FE3BFA2EA9E503A080F           105        123         Rodriguez    Maria                     2           650 2023-06-03                    0 2023-06-07       paid              1                 mrodriguez@example.com        777-4231    Paris        France
20-DEC-23 02.39.49.390467000 PM    086582FCB50AEC6A389818A1DF    20-DEC-23 02.39.49.390467000 PM    5BBD60B5C24C4F2CBFB5AC7A51C18AA7           315        123         Lee          Ethan                     1           350 2023-06-15                    0 2023-06-17       paid              2                 ethan.lee@example.com         123-8106    Madrid       Spain
20-DEC-23 02.40.05.271264000 PM    086582FCC50AEC6A389818A1E5    20-DEC-23 02.40.27.885089000 PM    6F2FA1C41E604FB0BFF87D4613282CD3           207        123         Johnson      Olivia                    2           932 2023-06-25                    0 2023-06-30       paid              3                 olivia.johnson@example.com    987-1890    Barcelona    Spain
20-DEC-23 02.40.05.271264000 PM    086582FCC50AEC6A389818A1EB    20-DEC-23 02.40.05.271264000 PM    AC9EDF1CC0694F0ABF6BCB522966DE33           105        123         Patel        Liam                      2           350 2023-06-07                    0 2023-06-17       paid              4                 liam.patel@example.com        123-8106    Malaga       Spain
20-DEC-23 02.40.05.271264000 PM    086582FCC50AEC6A389818A1F1    20-DEC-23 02.40.05.271264000 PM    D138C6FD1D804F52BF425094E3687DFE           101        123         Smith        John                      2           800 2023-06-01                    1 2023-06-05       paid              5                 john.smith@example.com        555-1234    Lyon         France     extra_bed                                                       1
20-DEC-23 02.40.05.271264000 PM    086582FCC50AEC6A389818A1F1    20-DEC-23 02.40.05.271264000 PM    D138C6FD1D804F52BF425094E3687DFE           101        123         Smith        John                      2           800 2023-06-01                    1 2023-06-05       paid              5                 john.smith@example.com        555-1234    Lyon         France     late_checkout    Please arrange for a 2pm checkout

6 rows selected.

SQL>

In the last part of the workshop, we are going to learn how to use the MongoDB API against SODA and Duality Views: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-9-mongodb-api-against-duality-views-9a2

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

Javier — Fri, 15 Dec 2023 16:28:50 +0000

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

Python Developer Track for Oracle JSON and Duality Views - Part 6: Database Actions, GUI for JSON

Javier — Thu, 07 Dec 2023 11:29:22 +0000

In this part we are going to install Oracle Rest Data Services (ORDS). ORDS is the HTTPS Web Gateway for your Oracle Database, which includes features such as Oracle Database Actions, Oracle APEX access, REST APIs for your data and databases, Oracle Database API for MongoDB, and much more.

After the installation, we are going to use Database Actions, the GUI that ORDS offers to create and work with JSON documents. Let's install it! If you want to know more about ORDS, you can find it here: https://www.oracle.com/database/technologies/appdev/rest.html

Installing ORDS

As I'm using an Oracle Cloud VM with Oracle Linux, this is very stright forward. If you are not using Oracle Linux, you can go to the ORDS webpage I pointed before and download it. It is very simple to install.

ORDS is a Java application, so we are going to install the GraalVM jdk. If you want to know more about GraalVM, you can find the info here: https://www.graalvm.org/

yum install graalvm-17-jdk.x86_64
yum install ords

In the installation, ORDS is going to create an oracle user. We need to configure, start and stop ORDS via this user. Let's change user to start the configuration:

su - oracle

We need to run the following command to configure it. During the configuration it will ask for some configurations. I'm going to put my output here so it can help you as a reference. We are going to run the installation as SYS user which has the following password: PassworD123##

[oracle@dualityviews opc]$ ords --config /etc/ords/config install

ORDS: Release 23.3 Production on Tue Nov 28 11:15:38 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /etc/ords/config/

The configuration folder /etc/ords/config does not contain any configuration files.

Oracle REST Data Services - Interactive Install

  Enter a number to select the type of installation
    [1] Install or upgrade ORDS in the database only
    [2] Create or update a database pool and install/upgrade ORDS in the database
    [3] Create or update a database pool only
  Choose [2]:
  Enter a number to select the database connection type to use
    [1] Basic (host name, port, service name)
    [2] TNS (TNS alias, TNS directory)
    [3] Custom database URL
  Choose [1]:
  Enter the database host name [localhost]:
  Enter the database listen port [1521]:
  Enter the database service name [orcl]: FREEPDB1
  Provide database user name with administrator privileges.
    Enter the administrator username: SYS
  Enter the database password for SYS AS SYSDBA:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/FREEPDB1

Retrieving information.
  Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]:
  Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]:
  Enter a number to select additional feature(s) to enable:
    [1] Database Actions  (Enables all features)
    [2] REST Enabled SQL and Database API
    [3] REST Enabled SQL
    [4] Database API
    [5] None
  Choose [1]:
  Enter a number to configure and start ORDS in standalone mode
    [1] Configure and start ORDS in standalone mode
    [2] Skip
  Choose [1]:
  Enter a number to select the protocol
    [1] HTTP
    [2] HTTPS
  Choose [1]:
  Enter the HTTP port [8080]:

After the configuration is done, it will automatically start ORDS. You should see something like this at the screen:

Mapped local pools from /etc/ords/config/databases:
  /ords/                              => default                        => VALID


2023-11-28T11:17:10.737Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 23.3.0.r2891830
Oracle REST Data Services server info: jetty/10.0.17
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 17.0.9+11-LTS-jvmci-23.0-b21

Right now we are serving ORDS via web, but the port is blocked. Let's do some extra configuration before we start it again. For that, run ctrl + c to stop serving the ORDS.

ctrl + c

First, let's open the port 8080 where ORDS is listening. Let's do this operation as root user, so we need to exit from oracle user first:

exit
firewall-cmd --permanent --zone=public --add-port=8080/tcp
firewall-cmd --reload

Now we need to enable our "myapp" user to access via web. All users are blocked by default for security reasons. We need to enable the schema running the following command as SYS user:

sqlplus sys/PassworD123##@localhost:1521/FREEPDB1 as sysdba

grant inherit privileges on user SYS to ORDS_METADATA;
BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'MYAPP',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'myapp',
    p_auto_rest_auth      => FALSE
  );
  COMMIT;
END;
/

exit;

We will take advantage and we are going to configure ORDS for a later exercise. We are going to enable the MongoDB API so we can use MongoDB code against our JSON data. We connect as oracle user again and we enable this feature:

su - oracle
ords config set mongo.enabled true

We are done! Now let's tart ORDS. We are going to start it as a background process and we are going to save the output in a custom log so we can have a look if something goes wrong:

cd /tmp
nohup ords serve >> ords_log.log 2>&1 &

Using Database Actions

To connect to ORDS, this is the url you have to use: http://YOUR_PUBLIC_IP:8080/ords/sql-developer You will see the following:

Now you can login with the user:myapp and password: PassworD123##

Let's use Database Actions to create collections and insert JSON data. Click on the JSON menu:

Click on the button to create a new collection:

Let's call it restaurant_bookings and click create:

Now let's insert some documents. Click on the icon to insert documents:

Let's insert our first JSON document. Copy and paste this document and click create:

{
    "guest_email": "olivia.johnson@example.com",
    "board":"full",
    "included_in_reservation": true ,
    "allergens": ["gluten", "nuts"]
}

Follow the same process for a second JSON document:

{
    "guest_email": "liam.patel@example.com",
    "board":"half",
    "included_in_reservation": true ,
    "allergens": ["milk", "eggs"]
}

Now let's change the half board to full board. Click on the edit button to our document with half board:

Once it is changed to full, click save:

Now, select hotel_reservation collection. Then click on the diagram button:

We can see the JSON structure for our hotel_reservation collection.

Also you can run queries following the Query By Example (QBE):

In the next chapter we are going to talk about Data Modeling and Duality Views: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-7-data-modeling-including-duality-views-10l3

Python Developer Track for Oracle JSON and Duality Views - Part 5: Indexing

Javier — Tue, 05 Dec 2023 08:43:25 +0000

In this tutorial we are going to learn about indexing. We can create different indexes with different purposes:

Create an index to improve query performance
Create a search index to search for keywords
Create an index to get a Data Guide, or get the JSON structure

Let's go with the first index. As usual, first we are going to connect to the Oracle Database 23c and open our collection "hotel_reservations":

import oracledb
import json

#connect with thick-client
oracledb.init_oracle_client()
connection = oracledb.connect(user="myapp",
                              password="PassworD123##",
                              dsn="localhost:1521/FREEPDB1")
#auto commit
connection.autocommit = True

#Open collection for hotel reservations
soda = connection.getSodaDatabase()
collection = soda.openCollection("hotel_reservations")

Now we have to define the index. In this case I want to create a composite index, as I would like to search by the reservation_id and the room_id. We are going to call the index "reservation_index". Then we will call the function "createIndex":

index_def = {
    'name': 'RESERVATION_INDEX',
    'fields': [
        {
            'path': 'reservation_id',
            'datatype': 'string',
            'order': 'asc'
        },
        {
            'path': 'room_id',
            'datatype': 'string',
            'order': 'asc'
        },

    ]
}
collection.createIndex(index_def)

Easy, isn't it? Now let's create our second index. Let me show you the code first:

index_search_def ={ 
    "name"      : "SEARCH_AND_DATA_GUIDE_IDX",
    "dataguide" : "on",
    "search_on" : "text" }

collection.createIndex(index_search_def)

In this case, the index is called "search_and_data_guide_idx". As the name describes, it has double purpose. There is a first parameter which is called "dataguide". Data guide provides a summary of the structural and type information about our JSON documents. Later on we will have a look into it. This parameter can be on or off.

The second parameter is "search_on". This parameter can have two values: text or text_value. If your search index involve only text search or string-equality search you can use text. If you are interested in numeric ranges as well as text, you have to use text_value.

Now that we have defined the index, let's do a query where we are looking for bookings that are talking about checking, because the customer had added a comment into the booking:

documents = collection.find().filter({"$textContains" : "check%"}).getDocuments()
print('\n Found the following documents talking about check:')
for d in documents:
    content = d.getContent()
    print(content)

We can see we have a reservation with the following comment: Please arrange for a 2pm checkout.

As promised before, let's have a look at the Data Guide. One of the great benefits of JSON is the flexibility, and could be also one of the problems! If we want to know if there are any document which is not following the expected structure or if I want to know how it is, we can get this data guide. Let's have a look:

data_guide= collection.getDataGuide().getContent()
print(json.dumps(data_guide, indent=1))

You should get the following JSON document. We can get all the info about number of fields, length and preferred column name.

>>> print(json.dumps(data_guide, indent=1))
{
 "type": "object",
 "o:length": 1,
 "properties": {
  "_id": {
   "type": "string",
   "o:length": 32,
   "o:preferred_column_name": "DATA$_id"
  },
  "room_id": {
   "type": "string",
   "o:length": 4,
   "o:preferred_column_name": "DATA$room_id"
  },
  "hotel_id": {
   "type": "string",
   "o:length": 4,
   "o:preferred_column_name": "DATA$hotel_id"
  },
  "guest_name": {
   "type": "object",
   "o:length": 1,
   "o:preferred_column_name": "DATA$guest_name",
   "properties": {
    "last_name": {
     "type": "string",
     "o:length": 16,
     "o:preferred_column_name": "DATA$last_name"
    },
    "first_name": {
     "type": "string",
     "o:length": 8,
     "o:preferred_column_name": "DATA$first_name"
    }
   }
  },
  "num_adults": {
   "type": "number",
   "o:length": 1,
   "o:preferred_column_name": "DATA$num_adults"
  },
  "total_cost": {
   "type": "number",
   "o:length": 4,
   "o:preferred_column_name": "DATA$total_cost"
  },
  "checkin_date": {
   "type": "string",
   "o:length": 16,
   "o:preferred_column_name": "DATA$checkin_date"
  },
  "num_children": {
   "type": "number",
   "o:length": 1,
   "o:preferred_column_name": "DATA$num_children"
  },
  "checkout_date": {
   "type": "string",
   "o:length": 16,
   "o:preferred_column_name": "DATA$checkout_date"
  },
  "payment_status": {
   "type": "string",
   "o:length": 4,
   "o:preferred_column_name": "DATA$payment_status"
  },
  "reservation_id": {
   "type": "string",
   "o:length": 1,
   "o:preferred_column_name": "DATA$reservation_id"
  },
  "guest_contact_info": {
   "type": "object",
   "o:length": 1,
   "o:preferred_column_name": "DATA$guest_contact_info",
   "properties": {
    "email": {
     "type": "string",
     "o:length": 32,
     "o:preferred_column_name": "DATA$email"
    },
    "phone": {
     "type": "string",
     "o:length": 8,
     "o:preferred_column_name": "DATA$phone"
    },
    "address": {
     "type": "object",
     "o:length": 1,
     "o:preferred_column_name": "DATA$address",
     "properties": {
      "city": {
       "type": "string",
       "o:length": 16,
       "o:preferred_column_name": "DATA$city"
      },
      "country": {
       "type": "string",
       "o:length": 8,
       "o:preferred_column_name": "DATA$country"
      }
     }
    }
   }
  },
  "additional_requests": {
   "type": "array",
   "o:length": 1,
   "o:preferred_column_name": "DATA$additional_requests",
   "items": {
    "properties": {
     "type": {
      "type": "string",
      "o:length": 16,
      "o:preferred_column_name": "DATA$type"
     },
     "details": {
      "type": "string",
      "o:length": 64,
      "o:preferred_column_name": "DATA$details"
     },
     "quantity": {
      "type": "number",
      "o:length": 1,
      "o:preferred_column_name": "DATA$quantity"
     }
    }
   }
  }
 }
}
>>>

We can use this Data Guide for creating a table to make it very easy to run SQL over it, but this will be in a different chapter ;)

Python Developer Track for Oracle JSON and Duality Views - Part 4: Queries by Example

Javier — Wed, 29 Nov 2023 08:24:21 +0000

In this part we are going to learn about Queries by Example (QBE). What is QBE? Is a SODA query that uses a pattern expressed in JSON. SODA query operations use a QBE to select all JSON documents in a collection that satisfy it, meaning that the filter evaluates to true for only those documents. Let's run some of them.

As usual, first we are going to connect and open our collection "hotel_reservations" which contains all our JSON documents from our hotel:

import oracledb

#connect with thick-client
oracledb.init_oracle_client()
connection = oracledb.connect(user="myapp",
                              password="PassworD123##",
                              dsn="localhost:1521/FREEPDB1")
#auto commit
connection.autocommit = True

#Open collection for hotel reservations
soda = connection.getSodaDatabase()
collection = soda.openCollection("hotel_reservations")

Now we are ready to run some queries. First, I would like to know if there are reservations with children. In our JSON collections, there is a field called "num_children". We are going to run a query to find all the reservations which are greater than 0. We are going to use the operator "gt":

print("\n Reservations with children:")
documents = collection.find().filter({'num_children': {"$gt" : 0}}).getDocuments()
for d in documents:
    content = d.getContent()
    print("Number of children:",content["num_children"],". Contact:",content["guest_contact_info"])

We can check we have only 1. Now I'm interested in looking a reservation based on an email, but I don't remember exactly how it was. I remember that it starts with john. Let's use like to find a pattern:

print("\n Reservations found using email:")
documents = collection.find().filter({'guest_contact_info.email': {'$like': 'john%'}}).getDocuments()
for d in documents:
    content = d.getContent()
    print("Reservation number:",content["reservation_id"],". Contact:",content["guest_contact_info"])

We got it! Now I'm interested to know how many reservations I have starting on one specific date:

print("\n Reservations found for check in at specific date:")
documents = collection.find().filter({ "checkin_date" : { "$date" : {"$gt":"2023-06-15"} } }).getDocuments()
for d in documents:
    content = d.getContent()
    print("Reservation number:",content["reservation_id"],". Contact:",content["guest_contact_info"])

Finally, I want to know more about my customers. I'm interested to know how many customers are coming from Malaga or Paris:

print("\n Give all the reservations with customers from Malaga and Paris:")
documents = collection.find().filter({'guest_contact_info.address.city': {"$in" : ["Malaga", "Paris"]}}).getDocuments()
for d in documents:
    content = d.getContent()
    print("Reservation number:",content["reservation_id"],". Contact:",content["guest_contact_info"])

Easy right? If you want to know more about the different operators and more examples, you can refer to the following documentation link: https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/adsdi/overview-soda-filter-specifications-qbes.html#GUID-CB09C4E3-BBB1-40DC-88A8-8417821B0FBE

You can go to the next part, indexing: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-5-indexing-51fe

Python Developer Track for Oracle JSON and Duality Views - Part 3: CRUD operations

Javier — Mon, 27 Nov 2023 11:23:07 +0000

In the 2 we learned what is SODA, how to connect to an Oracle Database and how to create some collections. In this tutorial we are going to learn the different CRUD operations we can perform. In the part 4 we will have a deeper look at the queries.

As mentioned in the last part, we are going to build a simple hotel booking application as an example of all the exercises we are going to see.

We are going to use the flexibility of JSON to store data about our customers, bookings and payments. Let's start!

As we already saw, the first thing we are going to do is to connect to the Oracle Database using the oracledb driver:

import oracledb
#connect with thick-client
oracledb.init_oracle_client()
connection = oracledb.connect(user="myapp",
                              password="PassworD123##",
                              dsn="localhost:1521/FREEPDB1")

As we are going to perform some inserts and deletes, I want to be sure that no data is lost. We are going to configure the autocommit, so all data will be committed after each transaction automatically:

connection.autocommit = True

As we also know, let's create a new collection to store all the information regarding our hotel, let's call it "hotel_reservations". After that, we are going to open it to start doing some operations:

soda = connection.getSodaDatabase()
collection = soda.createCollection("hotel_reservations")
collection = soda.openCollection("hotel_reservations")

Let's insert our first reservation. We are going to use the method "insertOne" for this purpose:

collection.insertOne(
{
    "reservation_id": "1",
    "hotel_id": "123",
    "room_id": "105",
    "checkin_date": "2023-06-03",
    "checkout_date": "2023-06-07",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Maria",
      "last_name": "Rodriguez"
    },
    "guest_contact_info": {
      "email": "mrodriguez@example.com",
      "phone": "777-4231",
      "address": {
        "city": "Paris",
        "country": "France"
      }
    },
    "total_cost": 650.00,
    "payment_status": "paid"
  })

Congratulations! You have inserted our first JSON document using SODA API against the Oracle Database. As you have seen, no need for SQL knowledge or structure created in advance!

For each document we store, the Oracle Database generates a key. Let's insert a new document and retrieve the key for this document. We are going to use the method "insertOneAndGet" for this purpose:

content = {
    "reservation_id": "2",
    "hotel_id": "123",
    "room_id": "315",
    "checkin_date": "2023-06-15",
    "checkout_date": "2023-06-17",
    "num_adults": 1,
    "num_children": 0,
    "guest_name": {
      "first_name": "Ethan",
      "last_name": "Lee"
    },
    "guest_contact_info": {
      "email": "ethan.lee@example.com",
      "phone": "123-8106",
      "address": {
        "city": "Madrid",
        "country": "Spain"
      }
    },
    "total_cost": 350.00,
    "payment_status": "paid"
  }

document = collection.insertOneAndGet(content)
key = document.key
print('\n The key of the document is: ', key)

You should get a result like the following:

The key of the document is:  08655F63EA0AE7791964F8F85F
>>>

Now that we have the key, let's use it to get the document. As we know that this key will be unique, we are going to use "getOne". In case you want to retrieve more documents, you should use "getDocuments()":

document = collection.find().key(key).getOne() 
content = document.getContent()                
print('\n The document is:')
print(content)

You will see that we are getting the same JSON document. We need to insert more JSON documents to have a more representative workload. Instead of inserting one by one, we are going to use "insertManyandGet()". We could use also "insertMany". The only difference is the first one returns a list of SODA Document objects:

all_documents=[
    {
    "reservation_id": "3",
    "hotel_id": "123",
    "room_id": "207",
    "checkin_date": "2023-06-25",
    "checkout_date": "2023-06-30",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Olivia",
      "last_name": "Johnson"
    },
    "guest_contact_info": {
      "email": "olivia.johnson@example.com",
      "phone": "987-1890",
      "address": {
        "city": "Barcelona",
        "country": "Spain"
      }
    },
    "total_cost": 932.00,
    "payment_status": "pending"
  }
,

{
    "reservation_id": "4",
    "hotel_id": "123",
    "room_id": "222",
    "checkin_date": "2023-06-07",
    "checkout_date": "2023-06-17",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Liam",
      "last_name": "Patel"
    },
    "guest_contact_info": {
      "email": "liam.patel@example.com",
      "phone": "123-8106",
      "address": {
        "city": "Malaga",
        "country": "Spain"
      }
    },
    "total_cost": 350.00,
    "payment_status": "paid"
  }

,

{
    "reservation_id": "5",
    "hotel_id": "123",
    "room_id": "101",
    "checkin_date": "2023-06-01",
    "checkout_date": "2023-06-05",
    "num_adults": 2,
    "num_children": 1,
    "guest_name": {
      "first_name": "John",
      "last_name": "Smith"
    },
    "guest_contact_info": {
      "email": "john.smith@example.com",
      "phone": "555-1234",
      "address": {
        "city": "Lyon",
        "country": "France"
      }
    },
    "additional_requests": [
      {
        "type": "extra_bed",
        "quantity": 1
      },
      {
        "type": "late_checkout",
        "details": "Please arrange for a 2pm checkout"
      }
    ],
    "total_cost": 800.00,
    "payment_status": "paid"
  }
  ,
    {
    "reservation_id": "6",
    "hotel_id": "123",
    "room_id": "305",
    "checkin_date": "2023-06-04",
    "checkout_date": "2023-06-20",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Marcus",
      "last_name": "Wong"
    },
    "guest_contact_info": {
      "email": "marcus.wong@example.com",
      "phone": "123-1234",
      "address": {
        "city": "Nice",
        "country": "France"
      }
    },
    "total_cost": 1350.00,
    "payment_status": "cancelled"
  }

]
result_docs = collection.insertManyAndGet(all_documents)

Now we have a lot of documents but, how many? Let's run our first simple query by running a "count()":

total = collection.find().count()
print('\n My hotel has', total, 'reservations')

We have 6 reservations! As our hotel is getting more reservations, we need to know if there is any customer who didn't pay! Let's check the payment status. For that, we are going to run a query with a filter. We want to get the customers who are on pending status:

documents = collection.find().filter({'payment_status': "pending"}).getDocuments()
print('\n Customers who didn\'t pay:')
for d in documents:
    content = d.getContent()
    print(content["guest_contact_info"])

We are going to print only the contact info from the JSON Document. In this case we have 1 customer with status pending:

{'email': 'olivia.johnson@example.com', 'phone': '987-1890', 'address': {'city': 'Barcelona', 'country': 'Spain'}}

It seems there was a mistake! Customer solved the issue and we have to change the status from pending to paid. To do that, we are going to get the key of the document with status pending and replace the document using "replaceOne()":

new_content = {
    "reservation_id": "3",
    "hotel_id": "123",
    "room_id": "207",
    "checkin_date": "2023-06-25",
    "checkout_date": "2023-06-30",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Olivia",
      "last_name": "Johnson"
    },
    "guest_contact_info": {
      "email": "olivia.johnson@example.com",
      "phone": "987-1890",
      "address": {
        "city": "Barcelona",
        "country": "Spain"
      }
    },
    "total_cost": 932.00,
    "payment_status": "paid"

  } 

to_modify_doc = collection.find().filter({'payment_status': "pending"}).getOne()
key=to_modify_doc.key
collection.find().key(key).replaceOne(new_content)

Finally, we have seen that a customer cancelled. Let's delete that reservation:

deleted = collection.find().filter({'payment_status': "cancelled"}).remove()
print('\n Deleted', deleted, 'documents')

We have done some CRUD operations, easy right? In the next part we are going to run some more queries! You can find the next part here: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-4-queries-by-example-57ho

Python Developer Track for Oracle JSON and Duality Views - Part 2: First steps with Oracle and Python

Javier — Thu, 23 Nov 2023 08:41:32 +0000

In the previous blog, we had installed and configured the Oracle Database 23c Developer free and Python 3.11. In this blog, we are going to start learning how to use Python with JSON documents at the Oracle Database 23c.

During this workshop, we mainly are going to use SODA API. Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database. SODA makes it extremely easy to work with JSON and speed up our development.

In this part, we are going to connect to the Oracle Database 23c, create a few collections to store data, delete some of them and also list the collections available.

First, we need to import the Oracle Database driver for Python: oracledb

import oracledb

Now we are going to learn a new concept for the oracledb driver. There are two ways of using it: thin driver or thick driver. Depending on which features you want to use of the Oracle Database, you should use one or the other. If you are using the thin driver, there is nothing else to configure. In our case, for using the SODA APIs, we need the thick driver.

In order to use the thick driver, we need to call the following function which will load the Oracle Client libraries. With the Oracle 23c Developer Free, the client is automatically installed and we had configured the environment variables in the first part. With this configuration already done, we can now call the function:

oracledb.init_oracle_client()

If you want to know which features are available for thin and thick driver, you can find them at the following link:https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html#featuresummary

The next step is to define and establish the connection. We are going to use the user we created at the first blog and we are going to connect to the PDB created automatically during the installation called FREEPDB1:

connection = oracledb.connect(user="myapp",password="PassworD123##",dsn="localhost:1521/FREEPDB1")
soda = connection.getSodaDatabase()

Now let's create your two first collections. Once created, we are going to call getCollectionNames() to see all the available collections:

collection1 =soda.createCollection("my_first_collection")
collection2= soda.createCollection("my_second_collection")
list=soda.getCollectionNames()
print(list)

The output should be the following:

['my_first_collection', 'my_second_collection']
>>>

Now let's drop the second collection and list all the collections again:

collection2.drop()
list=soda.getCollectionNames()
print(list)

Now you should be able to see the first collection only. In this part of the workshop, we have learned what is SODA, the possible configurations for the Oracle driver for Python and how to connect. In the next part we are going to start running CRUD operations based on an example: creating a hotel booking system. The next part is the following: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-3-crud-operations-2oji

Python Developer Track for Oracle JSON and Duality Views - Part 1: Create and configure your Oracle 23c

Javier — Wed, 22 Nov 2023 08:51:51 +0000

On this tutorial we are going to install and configure Oracle 23c Free Developer Release. For that, we are going to use Oracle Cloud Infrastructure were I have a VM just created. You can run all the tutorial at your laptop or a custom VM.

Oracle Database 23c Free includes all the features and capabilities, and as it's name says, it is fully free! If you want to know all the details, you can go to the webpage here: https://www.oracle.com/uk/database/free/.

Let's install it. There are different ways of installing it, on this tutorial we are going to use the rpm provided at the webpage. Let's install it as root user. We have to install the prerequisites package and the Oracle Database 23c free:

sudo su
wget https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
yum -y localinstall oracle-database*

Now let's configure it. For that, we will have to provide a password for the SYS, SYSTEM and ADMIN user. We are going to use this password for the whole workshop: PassworD123##

Once the rpm has been installed, you have to configure it with the following command:

/etc/init.d/oracle-free-23c configure

You will have to introduce your password or the one used as example in this tutorial. Wait until the configuration reaches 100%.

Once configured, let's set the environment variables for sqlplus. We will define them at .bashrc so it will be configured every time you log in again. We will define them for root user:

cd /root
echo 'export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree' >> .bashrc
echo 'export PATH=$ORACLE_HOME/bin:$PATH' >> .bashrc
. .bashrc

Also we are going to configure the Oracle 23c Free to automatically start with the VM, and also we are going to open the firewall port in case you want to connect from outside:

systemctl enable oracle-free-23c
firewall-cmd --permanent --add-port=1521/tcp
firewall-cmd --reload

We are ready to go! Before starting, let's create a new user. We will use this user for the whole workshop:

sqlplus sys/PassworD123##@localhost:1521/FREEPDB1 as sysdba
create user myapp identified by PassworD123##;
grant db_developer_role to myapp;
alter user myapp quota unlimited on users;
exit;

Granting the db_developer_role, we will have all the privileges required.

As last step, we are going to configure Python. In order to use Python and Oracle 23c I need a version 3.7 or higher. Let's check it:

python --version

In this case I have version 3.6. I'm going to proceed to install Python 3.11 and pip.

yum install python3.11
yum install python3.11-pip

Now let's select Python 3.11 as default version for Python:

update-alternatives --config python3

There are 2 programs which provide 'python3'.

  Selection    Command
-----------------------------------------------
*+ 1           /usr/bin/python3.6
   2           /usr/bin/python3.11

Enter to keep the current selection[+], or type selection number: 2

Finally, install the Python driver for Oracle, which is called oracledb:

python -m pip install oracledb --upgrade

We are done! We have Oracle 23c Free up and running and our Python environment set. On the next tutorial, I will be explaining the first steps on Python and Oracle 23c: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-2-first-steps-with-oracle-and-python-2c0m

Python Developer Track for Oracle JSON and Duality Views

Javier — Mon, 20 Nov 2023 11:26:37 +0000

Do you want to take advantage of JSON and Relational combined, while avoiding the limitations of each? This workshop is for you!

In this new workshop, you are going to learn how to develop against an Oracle 23c and how to create advanced data models using Duality Views. The full content of the workshop is the following:

Create and configure your Oracle 23c
First steps with Oracle and Python
CRUD operations
Queries by example
Indexing
Database Actions, GUI for JSON
Data Modeling, including Duality Views
Run SQL over JSON
MongoDB API against Duality Views

I will be creating a blog post for each of the labs and I will share all the code at GitHub. Stay tuned!

Here you have all the direct links to all the chapters:

Autonomous Database - Tip of the Day collection

Javier — Mon, 20 Nov 2023 08:43:01 +0000

Do you want to know all the secrets about Autonomous Database? In this post you can find the collection of all the Tips of the Day!

Tip of the Day 1

// Detect dark theme var iframe = document.getElementById('tweet-1698631712526278980-172'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1698631712526278980&theme=dark" }

Tip of the Day 2

// Detect dark theme var iframe = document.getElementById('tweet-1699001866456019123-500'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1699001866456019123&theme=dark" }

Tip of the Day 3

// Detect dark theme var iframe = document.getElementById('tweet-1699342259915747569-348'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1699342259915747569&theme=dark" }

Tip of the Day 4

// Detect dark theme var iframe = document.getElementById('tweet-1699703066944036946-673'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1699703066944036946&theme=dark" }

Tip of the Day 5

// Detect dark theme var iframe = document.getElementById('tweet-1700093706626277418-575'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1700093706626277418&theme=dark" }

Tip of the Day 6

// Detect dark theme var iframe = document.getElementById('tweet-1701166858089472215-85'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1701166858089472215&theme=dark" }

Tip of the Day 7

// Detect dark theme var iframe = document.getElementById('tweet-1701506971180916962-587'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1701506971180916962&theme=dark" }

Tip of the Day 8

// Detect dark theme var iframe = document.getElementById('tweet-1701896726112522318-548'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1701896726112522318&theme=dark" }

Tip of the Day 9

// Detect dark theme var iframe = document.getElementById('tweet-1702249833677881463-95'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1702249833677881463&theme=dark" }

Tip of the Day 10

// Detect dark theme var iframe = document.getElementById('tweet-1702616119264047384-516'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1702616119264047384&theme=dark" }

Tip of the Day 11

// Detect dark theme var iframe = document.getElementById('tweet-1703705403220000978-145'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1703705403220000978&theme=dark" }

Tip of the Day 12

// Detect dark theme var iframe = document.getElementById('tweet-1704087827065999712-815'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1704087827065999712&theme=dark" }

Tip of the Day 13

// Detect dark theme var iframe = document.getElementById('tweet-1704451783425221026-203'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1704451783425221026&theme=dark" }

Tip of the Day 14

// Detect dark theme var iframe = document.getElementById('tweet-1704761275937362278-876'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1704761275937362278&theme=dark" }

Tip of the Day 15

// Detect dark theme var iframe = document.getElementById('tweet-1705127211655660005-587'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1705127211655660005&theme=dark" }

Tip of the Day 16

// Detect dark theme var iframe = document.getElementById('tweet-1706235681641476225-826'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1706235681641476225&theme=dark" }

Tip of the Day 17

// Detect dark theme var iframe = document.getElementById('tweet-1706588164960268668-811'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1706588164960268668&theme=dark" }

Tip of the Day 18

// Detect dark theme var iframe = document.getElementById('tweet-1706978352114942416-475'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1706978352114942416&theme=dark" }