MongoDB $in and $or operation in query

Description

In this page, we are going to discuss how $in, and $or can be used with mongodb queries.

The $in operator checks a value within the specified values within the arguments.

The $or operator checks whether any of the specified value is matching in the documents.

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

Sample collection "userinfo"

{
        "_id" : ObjectId("528f0491bf24dafb4dbb4af5"),
        "user_id" : "user1",
        "password" : "1a2b3c",
        "sex" : "Male",
        "age" : 17,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "CONSULTANT",
        "interest" : "MUSIC",
        "community_name" : [
                "MODERN MUSIC",
                "CLASSICAL MUSIC",
                "WESTERN MUSIC"
        ],
        "community_moder_id" : [
                "MR. Alex",
                "MR. Dang",
                "MR Haris"
        ],
        "community_members" : [
                700,
                200,
                1500
        ],
        "friends_id" : [
                "kumar",
                "harry",
                "anand"
        ],
        "ban_friends_id" : [
                "Amir",
                "Raja",
                "mont"
        ]
}
{
        "_id" : ObjectId("528f0532bf24dafb4dbb4af6"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : " MUSIC",
        "community_name" : [
                "MODERN MUSIC",
                "CLASSICAL MUSIC",
                "WESTERN MUSIC"
        ],
        "co mmunity_moder_id" : [
                "MR. Roy",
                "MR. Das",
                "MR Doglus"
        ],
        "community_members" : [
                500,
                300,
                1400
        ],
        "friends_id" : [
                "pal",
                "viki",
                "john"
        ],
        "ban_friends_id" : [
                "jalan",
                "mono j",
                "evan"
        ]
}
{
        "_id" : ObjectId("528f05eabf24dafb4dbb4af7"),
        "user_id" : "user3",
        "password" : "b1c1d1",
        "sex" : "Female",
        "age" : 19,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "IT COR.",
        "interest" : "AR T",
        "community_name" : [
                "MODERN ART",
                "CLASSICAL ART",
                "WESTERN ART"
        ],
        "community_mo der_id" : [
                "MR. Rifel",
                "MR. Sarma",
                "MR Bhatia"
        ],
        "community_members" : [
                5000,
                2000,
                1500
        ],
        "friends_id" : [
                "philip",
                "anant",
                "alan"
        ],
        "ban_friends_id" : [
                "Amir",
                "Raja",
                "mont"
        ]
}
{
        "_id" : ObjectId("528f08c0bf24dafb4dbb4af8"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR",
        "interest" : "SPORTS",
        "community_name" : [
                "ATHELATIC",
                "GAMES FAN GYES",
                "FAVOURIT GAMES"
        ],
        "community_moder_id" : [
                "MR. Paul",
                "MR. Das",
                "MR Doglus"
        ],
        "community_members" : [
                2500,
                2200,
                3500
        ],
        "friends_id" : [
                "vinod",
                "viki",
                "john"
        ],
        "ban_friends_id" : [
                "jalan",
                "monoj",
                "evan"
        ]
}

Document written in command prompt.

MongoDB query language with $in separator

If we want to fetch documents from the collection "userinfo" which contains the value of "age" is either 19 or 22, the following mongodb command can be used :

>db.userinfo.find({"age" :{$in :[19,22]}}).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.

The SQL equivalent code is

SELECT * 
FROM userinfo 
WHERE age IN(19,22);

Output:

{
        "_id" : ObjectId("528f05eabf24dafb4dbb4af7"),
        "user_id" : "user3",
        "password" : "b1c1d1",
        "sex" : "Female",
        "age" : 19,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "IT COR.",
        "interest" : "AR T",
        "community_name" : [
                "MODERN ART",
                "CLASSICAL ART",
                "WESTERN ART"
        ],
        "community_mo der_id" : [
                "MR. Rifel",
                "MR. Sarma",
                "MR Bhatia"
        ],
        "community_members" : [
                5000,
                2000,
                1500
        ],
        "friends_id" : [
                "philip",
                "anant",
                "alan"
        ],
        "ban_friends_id" : [
                "Amir",
                "Raja",
                "mont"
        ]
}
{
        "_id" : ObjectId("528f08c0bf24dafb4dbb4af8"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR",
        "interest" : "SPORTS",
        "community_name" : [
                "ATHELATIC",
                "GAMES FAN GYES",
                "FAVOURIT GAMES"
        ],
        "community_moder_id" : [
                "MR. Paul",
                "MR. Das",
                "MR Doglus"
        ],
        "community_members" : [
                2500,
                2200,
                3500
        ],
        "friends_id" : [
                "vinod",
                "viki",
                "john"
        ],
        "ban_friends_id" : [
                "jalan",
                "monoj",
                "evan"
        ]
}

Document written in command prompt.

The above output shows that, only those documents have fetched from the collection "userinfo" which contain the value of "age" is either 19 or 22.

MongoDB query language with $or separator

If we want to fetch documents from the collection "userinfo" which must contain the value of "sex" is "Male" and either "age" should be "17" or "date_of_join" should be "17/10/2009", the following mongodb command can be used :

> db.userinfo.find({ "sex" : "Male" , $or : [ { "age" : 17 } , { "date_of_join" : "17/10/2009" } ] } ).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.

The SQL equivalent code is

SELECT * 
FROM userinfo 
WHERE sex="Male" 
AND (age=17 or date_of_join="17/10/2009");

Output:

{
        "_id" : ObjectId("528f0491bf24dafb4dbb4af5"),
        "user_id" : "user1",
        "password" : "1a2b3c",
        "sex" : "Male",
        "age" : 17,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "CONSULTANT",
        "interest" : "MUSIC",
        "community_name" : [
                "MODERN MUSIC",
                "CLASSICAL MUSIC",
                "WESTERN MUSIC"
        ],
        "community_moder_id" : [
                "MR. Alex",
                "MR. Dang",
                "MR Haris"
        ],
        "community_members" : [
                700,
                200,
                1500
        ],
        "friends_id" : [
                "kumar",
                "harry",
                "anand"
        ],
        "ban_friends_id" : [
                "Amir",
                "Raja",
                "mont"
        ]
}
{
        "_id" : ObjectId("528f0532bf24dafb4dbb4af6"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : " MUSIC",
        "community_name" : [
                "MODERN MUSIC",
                "CLASSICAL MUSIC",
                "WESTERN MUSIC"
        ],
        "co mmunity_moder_id" : [
                "MR. Roy",
                "MR. Das",
                "MR Doglus"
        ],
        "community_members" : [
                500,
                300,
                1400
        ],
        "friends_id" : [
                "pal",
                "viki",
                "john"
        ],
        "ban_friends_id" : [
                "jalan",
                "mono j",
                "evan"
        ]
}

Document written in command prompt.

Previous: MongoDB query language
Next: MongoDB query with null