MongoDB query with multiple conditions

Question

I have data with multiple documents :

{
 "_id" : ObjectId("57b68dbbc19c0bd86d62e486"),
 "empId" : "1"
 "type" : "WebUser",
 "city" : "Pune"
}
{
 "_id" : ObjectId("57b68dbbc19c0bd86d62e487"),
 "empId" : "2"
 "type" : "Admin",
 "city" : "Mumbai"
}
{
 "_id" : ObjectId("57b68dbbc19c0bd86d62e488"),
 "empId" : "3"
 "type" : "Admin",
 "city" : "Pune"
}
{
 "_id" : ObjectId("57b68dbbc19c0bd86d62e489"),
 "empId" : "4"
 "type" : "User",
 "city" : "Mumbai"
}

I want to get data according to my multiple conditions :

condition 1:- {"type" : "WebUser", "city" : "Pune"}

condition 2:- {"type" : "WebUser", "city" : "Pune"} & {"type" : "User", "city" : "Mumbai"}

I want below result when run condition 1 :

    {
     "_id" : ObjectId("57b68dbbc19c0bd86d62e486"),
     "empId" : "1"
     "type" : "WebUser",
     "city" : "Pune"
    }

When I run second condition :

{
  "_id" : ObjectId("57b68dbbc19c0bd86d62e486"),
  "empId" : "1"
  "type" : "WebUser",
  "city" : "Pune"
}
{
  "_id" : ObjectId("57b68dbbc19c0bd86d62e489"),
  "empId" : "4"
  "type" : "User",
  "city" : "Mumbai"
}

I want above result by one query,

Currently I am using below aggregate query,

 db.emp.aggregate([
     { $match: { '$and': [
         {"type" : "WebUser", "city" : "Pune"}, 
         {"type" : "User", "city" : "Mumbai"}
     ] } },
     { $group: { _id: 1, ids: { $push: "$empId" } } }
 ])

Above query work for first condition & fails for other. Please help me.

chridam · Accepted Answer · 2016-09-08 11:49:45Z

34

For the second condition, you can use the $in operator in your query as:

db.emp.find({
    "type" : { "$in": ["WebUser", "User"] },
    "city" : { "$in": ["Pune", "Mumbai"] }
})

If you want to use in aggregation:

 db.emp.aggregate([
     { 
        "$match": {
            "type" : { "$in": ["WebUser", "User"] },
            "city" : { "$in": ["Pune", "Mumbai"] }
        }
     },
     { "$group": { "_id": null, "ids": { "$push": "$empId" } } }
 ])

or simply use the distinct() method to return an array of distinct empIds that match the above query as:

var employeeIds = db.emp.distinct("empId", {
    "type" : { "$in": ["WebUser", "User"] },
    "city" : { "$in": ["Pune", "Mumbai"] }
});

edited Sep 8, 2016 at 11:49

answered Sep 8, 2016 at 11:43

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amrut Gaikwad Over a year ago

Above query work as "OR" condition. I need "AND". i.e. Result only return on exact match of both condition.

Anurag Over a year ago

The comma operator behaves as an $and implicity.

Julesezaar · Accepted Answer · 2021-04-27 18:59:04Z

29

If you are looking for the AND operator

This example checks if a field exists AND is null

db.getCollection('TheCollection').find({
    $and: [
        {'the_key': { $exists: true }},
        {'the_key': null}
    ]
})

This example checks if a field has 'value1' OR 'value2'

db.getCollection('TheCollection').find({
    $or: [
        {'the_key': 'value1'},
        {`the_key': 'value2'}
    ]
})

When just checking for null, the return contains non-existing fields plus fields with value null

db.getCollection('TheCollection').find({'the_key': null})

edited Apr 27, 2021 at 18:59

answered Oct 23, 2020 at 13:30

Julesezaar

3,5041 gold badge28 silver badges26 bronze badges

2 Comments

Steven de Salas Over a year ago

-1 db.getCollection('TheCollection').find({ 'the_key': 'value1', 'the_key': 'value2' }) will evaluate to db.getCollection('TheCollection').find({ 'the_key': 'value2' }) because javascript will not allow an object with two keys using same name. Also, mongoDB will evaluate multiple parameters as AND logic, not OR.

Julesezaar Over a year ago

I thought I tested and it worked but anyway. I edited it. Thanks for pointing it out

turivishal · Accepted Answer · 2021-05-10 04:53:39Z

13

You can use mongo db $or operator.

    db.emp.find({ $or: [
      { "type": "WebUser", "city": "Pune" },
      { "type": "user", "city": "Mumbai"}
    ]})

You can pass conditions in the array.

For more reference see mongo docs

edited May 10, 2021 at 4:53

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

answered Apr 7, 2021 at 9:13

Ram

1531 silver badge7 bronze badges

Comments

vvMINOvv · Accepted Answer · 2022-11-22 12:48:10Z

0

Display the document where in the “StudName” has value “Ajay Rathod”.

db.Student.find({name:"ajay rathod"})
```
{ "_id" : ObjectId("5fdd895cd2d5a20ee8cea0de"), "
```

Retrieve only Student Name and Grade.

db.Student.find({},{name:1,grade:1,_id:0})

{ "name" : "dhruv", "grade" : "A" }
{ "name" : "jay", "grade" : "B" }
{ "name" : "abhi", "grade" : "C" }
{ "name" : "aayush", "grade" : "A" }
{ "name" : "sukhdev", "grade" : "B" }
{ "name" : "dhruval", "grade" : "B" }
{ "name" : "ajay rathod", "grade" : "D" }

edited Nov 22, 2022 at 12:48

vvMINOvv

1,8784 gold badges23 silver badges49 bronze badges

answered Dec 23, 2020 at 8:17

Mr. Sukhdev

214 bronze badges

Collectives™ on Stack Overflow

MongoDB query with multiple conditions

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related