Using $and & $or operators in MongoDB (with examples)

Introduction

MongoDB, as a NoSQL database, offers flexible querying capabilities through its rich set of operators. Among these, the $and and $or operators play a fundamental role in combining queries. This tutorial provides a comprehensive understanding of using $and and $or operators, with examples spanning from basic usage to more advanced scenarios.

Working wtih $and

The $and operator allows you to specify multiple conditions that documents must satisfy to match the query. Here’s the basic syntax:

{
    $and: [{condition1}, {condition2}, ...]
}

Let’s start with a simple collection named products which we’ll be using throughout our examples:

db.products.insertMany([
    { name: "Laptop", price: 1000, ratings: 4.5, quantity: 10 },
    { name: "Keyboard", price: 100, ratings: 4.7, quantity: 50 },
    { name: "Mouse", price: 25, ratings: 4.3, quantity: 150 }
]);

Basic $and Usage

To find products priced above $50 with a rating of at least 4.5, use the following query:

db.products.find({
    $and: [
        {price: {$gt: 50}},
        {ratings: {$gte: 4.5}}
    ]
});

The output should look like this:

[
    { name: "Laptop", price: 1000, ratings: 4.5, quantity: 10 },
    { name: "Keyboard", price: 100, ratings: 4.7, quantity: 50 }
]

Working with $or

The $or operator is used to specify a compound query with multiple conditions, where at least one condition must be satisfied for a document to match. Its basic syntax is as follows:

{
    $or: [{condition1}, {condition2}, ...]
}

Basic $or Usage

Using our products collection, we can find items that are either priced below $30 or have a rating above 4.6 like so:

db.products.find({
    $or: [
        {price: {$lt: 30}},
        {ratings: {$gt: 4.6}}
    ]
});

The expected results should be:

[
    { name: "Keyboard", price: 100, ratings: 4.7, quantity: 50 },
    { name: "Mouse", price: 25, ratings: 4.3, quantity: 150 }
]

Combining $and and $or

It’s possible to combine $and and $or in a single query to create complex logical conditions. Consider a scenario where we want to find products that are either heavily stocked with low ratings or expensive products with high ratings:

db.products.find({
    $or: [
        { $and: [{quantity: {$gt: 100}}, {ratings: {$lt: 4.5}}] },
        { $and: [{price: {$gt: 500}}, {ratings: {$gt: 4.5}}] }
    ]
});

The output should be:

[
    { name: "Laptop", price: 1000, ratings: 4.5, quantity: 10 },
    { name: "Mouse", price: 25, ratings: 4.3, quantity: 150 }
]

Advanced Examples

Now, let’s explore more advanced use cases of the $and and $or operators in MongoDB.

Nested $and and $or Conditions

Nesting allows for the combination of multiple logical conditions at different levels. Suppose we have the following requirements for our query:

• Find products that either have a quantity greater than 100 or have a high rating.
• From these, filter out only the ones that are either named ‘Keyboard’ or are not very expensive.

The corresponding query would look like this:

db.products.find({
    $and: [
        {
            $or: [
                {quantity: {$gt: 100}},
                {ratings: {$gt: 4.5}}
            ]
        },
        {
            $or: [
                {name: "Keyboard"},
                {price: {$lt: 500}}
            ]
        }
    ]
});

This query yields:

[
    { name: "Keyboard", price: 100, ratings: 4.7, quantity: 50 },
    { name: "Mouse", price: 25, ratings: 4.3, quantity: 150 }
]

Using $and and $or with Other Operators

The following example demonstrates the use of $and and $or combined with the $in and $nin operators. We want to retrieve products based on multiple possible values for the ‘name’ attribute, as well as a specific price range:

db.products.find({
    $and: [
        {
            name: {
                $in: ["Laptop", "Keyboard"]
            }
        },
        {
            price: {
                $nin: [50, 75],
                $gt: 25
            }
        }
    ]
});

The result of this query should look something like:

[
    { name: "Laptop", price: 1000, ratings: 4.5, quantity: 10 },
    { name: "Keyboard", price: 100, ratings: 4.7, quantity: 50 }
]

Conclusion

This tutorial has covered the use of $and and $or operators in MongoDB queries, providing examples to help you create both basic and advanced queries. By leveraging these operators, you can construct intricate conditions tailored to your data retrieval needs, enabling robust and flexible database operations.