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

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