MongoDB Query to combine AND & OR?

To combine AND & OR operators in MongoDB, use the $and and $or operators together to create complex query conditions. This allows you to match documents that satisfy multiple logical combinations.

Syntax

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

Sample Data

db.combinedAndOrDemo.insertMany([
    {
        "StudentFirstName": "John",
        "StudentAge": 23,
        "StudentSkill": "MongoDB"
    },
    {
        "StudentFirstName": "Larry",
        "StudentAge": 21,
        "StudentSkill": "MySQL"
    },
    {
        "StudentFirstName": "Sam",
        "StudentAge": 24,
        "StudentSkill": "SQL Server"
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5cd306dcb64f4b851c3a13e2"),
        ObjectId("5cd306f3b64f4b851c3a13e3"),
        ObjectId("5cd30701b64f4b851c3a13e4")
    ]
}

Display All Documents

db.combinedAndOrDemo.find().pretty();

{
    "_id": ObjectId("5cd306dcb64f4b851c3a13e2"),
    "StudentFirstName": "John",
    "StudentAge": 23,
    "StudentSkill": "MongoDB"
}
{
    "_id": ObjectId("5cd306f3b64f4b851c3a13e3"),
    "StudentFirstName": "Larry",
    "StudentAge": 21,
    "StudentSkill": "MySQL"
}
{
    "_id": ObjectId("5cd30701b64f4b851c3a13e4"),
    "StudentFirstName": "Sam",
    "StudentAge": 24,
    "StudentSkill": "SQL Server"
}

Combined AND & OR Query

Find documents where either (StudentFirstName is "John" AND _id matches) OR StudentSkill is "MongoDB" ?

db.combinedAndOrDemo.find({
    "$or": [
        {
            "$and": [
                { "StudentFirstName": "John" },
                { "_id": ObjectId("5cd306dcb64f4b851c3a13e2") }
            ]
        },
        { "StudentSkill": "MongoDB" }
    ]
});

{
    "_id": ObjectId("5cd306dcb64f4b851c3a13e2"),
    "StudentFirstName": "John",
    "StudentAge": 23,
    "StudentSkill": "MongoDB"
}

Key Points

• Use $or for documents matching any of the specified conditions.
• Use $and for documents matching all specified conditions within a group.
• Combine both operators to create complex logical expressions with multiple condition groups.

Conclusion

MongoDB's $and and $or operators can be combined to create sophisticated queries with multiple logical conditions. This enables precise document filtering based on complex business logic requirements.