Since we will sometimes require removing documents in Azure Cosmos DB, we’ll want to be able to specify the documents for removal. In some cases, this will be as simple as specifying a field for removal, such as removing one type of workout in our temporary database we’ve created. In other delete situations, we’ll want to remove if the value of the field isn’t what we expect – such as greater than what we want. This applies to updates as well – we may want to drill into a specific value range for an update. In this tip, we’ll look at using operators with strings, numeric types and dates.

Throughout this tip, we’ll be using the Shell in Cosmos DB and outputting fewer fields than our documents store so that we can see consolidate views. In addition, some of the queries won’t have corresponding images, but will have the document count that we expect along with the query to execute.

Using Operators to Specify Documents

We’ve seen that we can remove or update records by using the unique key. We can also remove or update a group of records by using groups or by using operators. In the below query, we return all of the documents in our Azure Cosmos DB that are Treadmill Runs, displaying only the distance and time to show a consolidation of these documents. What if we wanted to remove all records that were over 3 miles? We could remove all the Treadmill Run types and add the two values that are exactly 3 back – but this would not be efficient if we had more documents.

Here we’ll look at using operators. For this case, we’ll use a not equals operator ($ne) because our values for distance are strings. In Azure Cosmos DB, we should the appropriate string operators like we would use with SQL Server, .NET or other programming languages and because our distance values are strings (as opposed to numbers), so we’ll use the not equals. We’ll see that we follow a similar structure to our query so far where we specify the field and then open brackets with our operators. This is the SQL Server equivalent of WHERE Distance <> “3”. Since our numerical values for these documents are strings, let’s apply a math operator to our numerical values.

In the below query, we use the math operator greater than ($gt) to return our Jump Rope/Pushup Circuit exercises in our Azure Cosmos DB where we did more than 250 pushups. We’ll notice that our query for this follows the same syntax: we specify our field (Pushups) and use our greater than operator with the number we want returned above the value.

Using Comparative Operators

With querying values in Azure Cosmos DB, we can use the below comparative operators that can be used similar to operators in SQL Server. The list covers the useful operators that we may require in common CRUD operations, especially in removals or updates.

• $eq: returns values that are equal to what’s provided
• $gt: returns values that are greater to what’s provided
• $gte: returns values that are greater than or equal to what’s provided
• $in: returns values that are in what’s provided
• $lt: returns values that are less than what’s provided
• $lte: returns values that are less than or equal to what’s provided
• $ne: returns values that are not equal to what’s provided
• $nin: returns values that are not in what’s provided

Without showing images of the results, let’s run the below queries in our Azure Cosmos DB and see what we get using each of these operators minus the ones we’ve already seen in the above examples with images. I highlight the number of results we should get each time.

We use the equals operator ($eq) to find all the Treadmill Runs where the distance is equal to three miles and we get back 2 documents.

We use the greater than or equals to operator ($gte) to find all the Jump Rope/Pushup Circuit exercises where there were 290 pushups or more and get back 2 documents.

We use the in operators ($in) to find all the documents that are exercise types of Treadmill Runs or Endurance and we get back 6 documents. With Azure Cosmos DB, using the in or not in operators works like an array (for those familiar with object-oriented languages), where we’re looking for values in our array or not in our array. In the above query, we specify the values we’re seeking within the square brackets [] and because these values are a string, we wrap these values in quotation marks. We’ll use this same operator again, but this time apply the operator to numbers and we’ll notice that we remove the quotation marks because in Cosmos DB, these are not required for numerical values.

This numerical in query returns 2 documents.

We use the less than operator ($lt) to find all Jump Rope/Pushup Circuit exercises where the pushups are less than 290 and we get back 2 documents.

We use the less than or equals to operator ($lt) to find all Jump Rope/Pushup Circuit exercises where the pushups are less than or equal to 290 and we get back 3 documents.

Similar to the in operator ($in) which uses an array in Azure Cosmos DB, we use the not in operator ($nin) to get all the Treadmill Runs where the distance is not in 3 or 3.1 miles. We get back 1 document. Identical to the in operator, we must pass in the appropriate type for our array – string values for strings and numerical values for numbers. For dates, we format our in or not in like strings, as we see in the below query which returns 7 documents.

Removing Documents By Sets and Adding Objects

For our Cosmos DB, suppose that our client wanted to set Treadmill runs at 3 miles only, regardless of whether it exceeded that value. We could run an update statement, but for this example we will remove these documents in a set – removing two documents using our operator not equal ($ne). However, we want to keep these values in our database, so we’ll re-add them back with the same details except the distance is 3 miles instead of the more detailed values. Rather than add them as we’ve done in the previous tip, we’ll first store the documents in objects that we’ll add later.

From here, we remove the documents in our Azure Cosmos DB that have distances not equal to 3 miles and validate the removals with a query to return the 2 documents remaining.

Now, we’ll insert our two documents that we stored in two different objects – doc1 and doc2. Notice that we simply have to pass in the object with the insert statement and the record is added.

When we work with documents in Azure Cosmos DB, just like with MongoDB, we will be working with objects that have documents or arrays of documents. As we see, we won’t have always specify the fields – though there may be CRUD operations where we do because of a change or removal. We will generally be getting an object made of a document or set of documents or adding a document or a set of documents. For instance, suppose that we wanted to store details of a workout in an object from a query – we can apply the above logic and save a document to and object with a query:

We can apply this further to multiple documents – a collection of documents within one object:

This becomes useful in many contexts with Cosmos DB, as we can re-use the object we’ve created if we need the documents for multiple purposes. In addition, we may want to transform the data for reports or for feeding to other back-ends and these objects allow us to do further manipulation without affecting the underlying data in our database.

Conclusion

Using operators can save us significant time when we remove documents or run updates on documents. Some updates may be applied relative to a field filter, but we may need to drill further into a field, like we saw with removing and re-adding our Treadmill runs. Azure Cosmos DB provides us with many standard operators we use with back-ends like SQL Server, which allow us to execute our CRUD operations against multiple documents at a time. In addition, we can create documents and save them to objects, or save query results in objects, from one document to multiple documents in the same object.

Table of contents

• Author
• Recent Posts

Timothy Smith

Tim manages hundreds of SQL Server and MongoDB instances, and focuses primarily on designing the appropriate architecture for the business model.

He has spent a decade working in FinTech, along with a few years in BioTech and Energy Tech.He hosts the West Texas SQL Server Users' Group, as well as teaches courses and writes articles on SQL Server, ETL, and PowerShell.

In his free time, he is a contributor to the decentralized financial industry.

View all posts by Timothy Smith

Latest posts by Timothy Smith (see all)

• Data Masking or Altering Behavioral Information - June 26, 2020
• Security Testing with extreme data volume ranges - June 19, 2020
• SQL Server performance tuning – RESOURCE_SEMAPHORE waits - June 16, 2020