MongoDb - Comparison Query Operator - $in & $nin

Description

In this page we are going to discuss about the comparison operator $in and $nin.

The $in operator is used to retrieve documents from the collection after checking whether the value of a specific column is matching within the values, specified in an array or not.

The $nin operator is used to retrieve documents from the collection except that, it matches documents for which the specified field does not have any value in the specified array.

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

Sample collection "testtable"

{
        "_id" : ObjectId("528f4e630fe5e6467e58ae7b"),
        "user_id" : "user1",
        "password" : "1a2b3c",
        "sex" : "Male",
        "age" : 17,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "CONSULTANT",
        "interest" : "MUSIC",
        "extra" : {
                "community_name" : [
                        "MODERN MUSIC",
                        "CLASSICAL MUSIC",
                        "WESTERN MUSIC"
                ],
                "community_moder_id" : [
                        "MR. Alex",
                        "MR.   Dang",
                        "MR Haris"
                ],
                "community_members" : [
                        700,
                        200,
                        1500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "kumar",
                                "harry",
                                "anand"
                        ],
                        "ban_friends_id" : [
                                "Amir",
                                "Raja",
                                "mont"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4e720fe5e6467e58ae7c"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : " MUSIC",
        "extra" : {
                "community_name" : [
                        "MODERN MUSIC",
                        "CLASSICAL MUSIC",
                        "WESTERN MUSIC"
                ],
                "co mmunity_moder_id" : [
                        "MR. Roy",
                        "MR. Das",
                        "MR Doglus"
                ],
                "community_members" : [
                        500,
                        300,
                        1400
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "pal",
                                "viki",
                                "john"
                        ],
                        "ban_friends_id" : [
                                "jalan",
                                "mono j",
                                "evan"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4e7e0fe5e6467e58ae7d"),
        "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",
        "extra" : {
                "community_name" : [
                        "MODERN ART",
                        "CLASSICAL ART",
                        "WESTERN ART"
                ],
                "community_mo der_id" : [
                        "MR. Rifel",
                        "MR. Sarma",
                        "MR Bhatia"
                ],
                "community_members" : [
                        5000,
                        2000,
                        1500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "philip",
                                "anant",
                                "alan"
                        ],
                        "ban_friends_id" : [
                                "Amir",
                                "Raja",
                                "mont"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4e910fe5e6467e58ae7e"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",

        "education" : "M.B.B.S.",
        "profession" : "DOCTOR",
        "interest" : "SPORTS",
        "extra" : {
                "community_name" : [
                        "ATHELATIC",
                        "GAMES FAN GYES",
                        "FAVOURIT GAMES"
                ],
                "community_moder_id" : [
                        "MR. Paul",
                        "MR. Das",
                        "MR Doglus"
                ],
                "community_members" : [
                        2500,
                        2200,
                        3500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "vinod",
                                "viki",
                                "john"
                        ],
                        "ban_friends_id" : [
                                "jalan",
                                "monoj",
                                "evan"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4f8a0fe5e6467e58ae7f"),
        "user_id" : "user5",
        "password" : "user5",
        "sex" : "Male",
        "age" : 21,
        "date_of_join" : "17/08/2011",
        "education" : "MCA",
        "profession" : "S.W. Engineer",
        "interest" : "SPORTS",
        "extra" : {
                "community_name" : [
                        "ATHELATIC",
                        "GAMES FAN GYES",
                        "FAVOURIT GAMES"
                ]
        }
}

Document written in command prompt

MongoDB conditional operator - $in example

If we want to fetch documents from the collection "testtable" which contains the value of "age " within the specified values [19,20,22,25 ] in the array, the following mongodb command can be used :

>db.testtable.find({"age" : { $in : [19,20,22,25]}})

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 testtable 
WHERE age IN (19,20,22,25); 

Output of the command

{
        "_id" : ObjectId("528f4e7e0fe5e6467e58ae7d"),
        "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",
        "extra" : {
                "community_name" : [
                        "MODERN ART",
                        "CLASSICAL ART",
                        "WESTERN ART"
                ],
                "community_mo der_id" : [
                        "MR. Rifel",
                        "MR. Sarma",
                        "MR Bhatia"
                ],
                "community_members" : [
                        5000,
                        2000,
                        1500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "philip",
                                "anant",
                                "alan"
                        ],
                        "ban_friends_id" : [
                                "Amir",
                                "Raja",
                                "mont"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4e910fe5e6467e58ae7e"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR",
        "interest" : "SPORTS",
        "extra" : {
                "community_name" : [
                        "ATHELATIC",
                        "GAMES FAN GYES",
                        "FAVOURIT GAMES"
                ],
                "community_moder_id" : [
                        "MR. Paul",
                        "MR. Das",
                        "MR Doglus"
                ],
                "community_members" : [
                        2500,
                        2200,
                        3500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "vinod",
                                "viki",
                                "john"
                        ],
                        "ban_friends_id" : [
                                "jalan",
                                "monoj",
                                "evan"
                        ]
                }
        }
}

Document written in command prompt

MongoDB $in operator with dot notation

If we want to fetch documents from the collection "testtable" which contain the values for "valued_friends_id" under "friends" under "extra" within the specified values ["vinod","janal","nonoj"] in the array, the following mongodb command can be used :

>db.testtable.find({"extra.friends.valued_friends_id" : {$in : ["vinod","janal","nonoj"]}})

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.

Output of the command

{
        "_id" : ObjectId("528f4e910fe5e6467e58ae7e"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR",
        "interest" : "SPORTS",
        "extra" : {
                "community_name" : [
                        "ATHELATIC",
                        "GAMES FAN GYES",
                        "FAVOURIT GAMES"
                ],
                "community_moder_id" : [
                        "MR. Paul",
                        "MR. Das",
                        "MR Doglus"
                ],
                "community_members" : [
                        2500,
                        2200,
                        3500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "vinod",
                                "viki",
                                "john"
                        ],
                        "ban_friends_id" : [
                                "jalan",
                                "monoj",
                                "evan"
                        ]
                }
        }
}

Document written in command prompt

MongoDB $nin(not in ) operator

If we want to fetch documents from the collection "testtable" which does not contain the values for "valued_friends_id" under "friends" under "extra" within the specified values ["vinod","janal","nonoj","anant"] in the array, the following mongodb command can be used :

>db.testtable.find({"extra.friends.valued_friends_id" : {$nin : ["vinod","janal","nonoj","anant"]}})

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.

Output of the command

{
        "_id" : ObjectId("528f4e630fe5e6467e58ae7b"),
        "user_id" : "user1",
        "password" : "1a2b3c",
        "sex" : "Male",
        "age" : 17,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "CONSULTANT",
        "interest" : "MUSIC",
        "extra" : {
                "community_name" : [
                        "MODERN MUSIC",
                        "CLASSICAL MUSIC",
                        "WESTERN MUSIC"
                ],
                "community_moder_id" : [
                        "MR. Alex",
                        "MR.   Dang",
                        "MR Haris"
                ],
                "community_members" : [
                        700,
                        200,
                        1500
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "kumar",
                                "harry",
                                "anand"
                        ],
                        "ban_friends_id" : [
                                "Amir",
                                "Raja",
                                "mont"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4e720fe5e6467e58ae7c"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : " MUSIC",
        "extra" : {
                "community_name" : [
                        "MODERN MUSIC",
                        "CLASSICAL MUSIC",
                        "WESTERN MUSIC"
                ],
                "co mmunity_moder_id" : [
                        "MR. Roy",
                        "MR. Das",
                        "MR Doglus"
                ],
                "community_members" : [
                        500,
                        300,
                        1400
                ],
                "friends" : {
                        "valued_friends_id" : [
                                "pal",
                                "viki",
                                "john"
                        ],
                        "ban_friends_id" : [
                                "jalan",
                                "mono j",
                                "evan"
                        ]
                }
        }
}
{
        "_id" : ObjectId("528f4f8a0fe5e6467e58ae7f"),
        "user_id" : "user5",
        "password" : "user5",
        "sex" : "Male",
        "age" : 21,
        "date_of_join" : "17/08/2011",
        "education" : "MCA",
        "profession" : "S.W. Engineer",
        "interest" : "SPORTS",
        "extra" : {
                "community_name" : [
                        "ATHELATIC",
                        "GAMES FAN GYES",
                        "FAVOURIT GAMES"
                ]
        }
}

Source: http://www.w3resource.com/mongodb/mongodb-in-operators.php