MongoDB Logical Query Operator - $and & $not

Logical Operator - $and

The MongoDB $and operator performs a logical AND operation on an array of two or more expressions and retrieves the documents which satisfy all the expressions in the array. The $and operator uses short-circuit evaluation. If the first expression (e.g. <expression1>) evaluates to false, MongoDB will not evaluate the remaining expressions.

Syntax:

{ $and: [ { <exp1> }, { <exp2> } , ... , { <expN> } ] }

Our database name is 'myinfo' and our collection name is 'student'. Here, is the collection bellow.

Sample collection "student"

[
        {
                "f_name" : "Zenny",
                "sex" : "Female",
                "class" : "VI",
                "age" : 12,
                "grd_point" : 32.6342
        },
        {
                "f_name" : "Paul",
                "sex" : "Male",
                "class" : "VII",
                "age" : 13,
                "grd_point" : 29.5904
        },
        {
                "f_name" : "Tom",
                "sex" : "Male",
                "class" : "VI",
                "age" : 11,
                "grd_point" : 30.1257
        },
        {
                "f_name" : "Lassy",
                "sex" : "Female",
                "class" : "VIII",
                "age" : 13,
                "grd_point" : 28.2514
        },
        {
                "f_name" : "Peter",
                "sex" : "Male",
                "class" : "VI",
                "age" : 11,
                "grd_point" : 31.5201
        }
]			

Example of MongoDB Logical Operator - $and

If we want to select all documents from the collection "student" which satisfying the condition -

1. sex of student is Female and

2. class of the student is VI and

3. grd_point of the student is greater than equal to 31

the following mongodb command can be used :

>db.student.find({$and:[{"sex":"Male"},{"grd_point":{ $gte: 31 }},{"class":"VI"}]}).pretty();

N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.

SQL equivalent command is

SELECT * 
FROM student 
WHERE sex="Male" AND grd_point>=31 AND class="VI";

Output:

{
        "_id" : ObjectId("527b3cc65ceafed9b2254a98"),
        "f_name" : "Peter",
        "sex" : "Male",
        "class" : "VI",
        "age" : 11,
        "grd_point" : 31.5201
}

The above AND operation can be written inplicitly in such a manner, by specifying a comma separated list of expressions. Here is the example below:

db.student.find({"sex":"Male","grd_point":{ $gte: 31},"class":"VI"}).pretty();

Logical Operator - $not

The MongoDB $not operator performs a logical NOT operation on the given expression and fetches selected documents that do not match the expression and the document that do not contain the field as well, specified in the expression.

Syntax:

{ field: { $not: { <expression> } } }

Example of MongoDB Logical Operator - $not

If we want to select all documents from the collection "student" which satisfying the condition -

age of the student is at least 12

the following mongodb command can be used :

>db.student.find( {"age": { $not: {$lt : 12}}}).pretty();

SQL equivalent command is

SELECT * 
FROM student 
WHERE age>=12;

Output:

{
        "_id" : ObjectId("527b3cc65ceafed9b2254a94"),
        "f_name" : "Zenny",
        "sex" : "Female",
        "class" : "VI",
        "age" : 12,
        "grd_point" : 32.6342
}
{
        "_id" : ObjectId("527b3cc65ceafed9b2254a95"),
        "f_name" : "Paul",
        "sex" : "Male",
        "class" : "VII",
        "age" : 13,
        "grd_point" : 29.5904
}
{
        "_id" : ObjectId("527b3cc65ceafed9b2254a97"),
        "f_name" : "Lassy",
        "sex" : "Female",
        "class" : "VIII",
        "age" : 13,
        "grd_point" : 28.2514
}

Example of MongoDB Logical Operator - $not with pattern matching

If we want to select all documents from the collection "student" which satisfying the condition -

sex of student must not Male.

the following mongodb command can be used :

>db.student.find( {"sex": { $not: /^M.*/}}).pretty();

SQL equivalent command is

SELECT * 
FROM student 
WHERE sex NOT LIKE 'M%';

Output:

{
        "_id" : ObjectId("527b3cc65ceafed9b2254a94"),
        "f_name" : "Zenny",
        "sex" : "Female",
        "class" : "VI",
        "age" : 12,
        "grd_point" : 32.6342
}
{
        "_id" : ObjectId("527b3cc65ceafed9b2254a97"),
        "f_name" : "Lassy",
        "sex" : "Female",
        "class" : "VIII",
        "age" : 13,
        "grd_point" : 28.2514
}

Previous: $in $nin
Next: $or $nor