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!
Top comments (0)