MongoDB: combining AND and OR?

In MongoDB, you can combine AND and OR operators to create complex query conditions. Use $and to ensure all conditions are met, and $or to match any one of multiple conditions within the same query.

Syntax

db.collection.find({
    $and: [
        {
            $or: [
                { "field1": "value1" },
                { "field2": "value2" }
            ]
        },
        { "field3": "value3" }
    ]
});

Sample Data

Let us first create a collection with documents ?

db.demo293.insertMany([
    { "FirstName": "Chris", "LastName": "Brown", "Age": 24 },
    { "FirstName": "David", "LastName": "Miller", "Age": 23 },
    { "FirstName": "John", "LastName": "Smith", "Age": 24 },
    { "FirstName": "Adam", "LastName": "Doe", "Age": 21 }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e4d45075d93261e4bc9ea32"),
        ObjectId("5e4d45265d93261e4bc9ea33"),
        ObjectId("5e4d45385d93261e4bc9ea34"),
        ObjectId("5e4d46cf5d93261e4bc9ea35")
    ]
}

Display all documents from the collection ?

db.demo293.find();

{ "_id": ObjectId("5e4d45075d93261e4bc9ea32"), "FirstName": "Chris", "LastName": "Brown", "Age": 24 }
{ "_id": ObjectId("5e4d45265d93261e4bc9ea33"), "FirstName": "David", "LastName": "Miller", "Age": 23 }
{ "_id": ObjectId("5e4d45385d93261e4bc9ea34"), "FirstName": "John", "LastName": "Smith", "Age": 24 }
{ "_id": ObjectId("5e4d46cf5d93261e4bc9ea35"), "FirstName": "Adam", "LastName": "Doe", "Age": 21 }

Example: Combining AND and OR

Find documents where (FirstName is "John" OR LastName is "Brown") AND Age is 24 ?

db.demo293.find({
    $and: [
        {
            $or: [
                { "FirstName": "John" },
                { "LastName": "Brown" }
            ]
        },
        { "Age": 24 }
    ]
});

{ "_id": ObjectId("5e4d45075d93261e4bc9ea32"), "FirstName": "Chris", "LastName": "Brown", "Age": 24 }
{ "_id": ObjectId("5e4d45385d93261e4bc9ea34"), "FirstName": "John", "LastName": "Smith", "Age": 24 }

How It Works

The query returns documents that satisfy both conditions:

• Chris Brown: Matches because LastName is "Brown" (OR condition) AND Age is 24
• John Smith: Matches because FirstName is "John" (OR condition) AND Age is 24
• David Miller: Excluded because Age is 23, not 24

Conclusion

Use $and and $or operators together to create sophisticated queries. The $and ensures all conditions are met, while $or provides flexibility within specific condition groups.