Azure Cosmos DB for NoSQL: A Deep Dive into Operators with Practical Examples

Azure Cosmos DB, a globally distributed, multi-model database service, is a powerful tool for developers aiming to build high-performance applications. One of its key features is the support for a variety of operators in SQL queries. In this blog post, we will explore the different types of operators supported by Azure Cosmos DB, including equality and comparison operators, logical operators, ternary and coalesce operators, and bitwise operators. Let’s dive in!

Equality and Comparison Operators

Equality and comparison operators are fundamental to any SQL query, and Azure Cosmos DB is no exception. These operators allow you to compare two JSON values. However, it’s important to note that comparing across different types or between two objects or arrays may yield an undefined result.

For instance, consider a collection of students with a ‘grade’ property. If you want to find all students with a grade of 5, you could use the equality operator (=) as follows:

SELECT *
FROM Students s
WHERE s.grade = 5

SELECT *

FROM Students s

WHERE s.grade = 5

Logical Operators

Logical operators are used to combine or invert Boolean values. Azure Cosmos DB supports the AND, OR, and NOT logical operators.

For example, if you want to find all students in grade 5 who are also in section A, you could use the AND operator:

SELECT *
FROM Students s
WHERE s.grade = 5 AND s.section = 'A'

SELECT *

FROM Students s

WHERE s.grade = 5 AND s.section = 'A'

If you want to find students who are either in grade 5 or in section A, you could use the OR operator:

SELECT *
FROM Students s
WHERE s.grade = 5 OR s.section = 'A'

SELECT *

FROM Students s

WHERE s.grade = 5 OR s.section = 'A'

Ternary and Coalesce Operators

Ternary and coalesce operators allow you to build conditional expressions and efficiently check for a property in an item.

For instance, consider a collection of students with an ‘age’ property. If you want to classify students as ‘minor’ or ‘adult’ based on their age, you could use the ternary operator:

SELECT (s.age < 18)? "minor": "adult" AS ageGroup
FROM Students s

1 2	SELECT (s.age < 18)? "minor": "adult" AS ageGroup FROM Students s

The coalesce operator (??) can be used to check for a property in an item. For instance, if you have a collection of students with either a ‘lastName’ or a ‘surname’ property, you could use the coalesce operator to return the appropriate property:

SELECT s.lastName ?? s.surname AS familyName
FROM Students s

1 2	SELECT s.lastName ?? s.surname AS familyName FROM Students s

Bitwise Operators

Bitwise operators are useful for performing operations on binary representations of numbers. Azure Cosmos DB supports a variety of bitwise operators, including left shift (<<), right shift (>>), zero-fill right shift (>>>), AND (&), OR (|), and XOR (^).

For instance, if you want to shift the binary representation of a number to the left by a certain number of bits, you could use the left shift operator:

SELECT (5 << 2) AS leftShift

1	SELECT (5 << 2) AS leftShift

This will shift the binary representation of 5 (101) to the left by 2 bits, resulting in 20 (10100).

Conclusion

In conclusion, Azure Cosmos DB provides a powerful and flexible way to use various operators in SQL queries. These operators, combined with the schema-free and denormalized nature of NoSQL databases, allow for highly efficient and scalable data operations. Whether you’re working with equality and comparison operators, logical operators, ternary and coalesce operators, or bitwise operators, Azure Cosmos DB can help you create more flexible and efficient queries.