Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB: multiple $elemMatch

I have MongoDB documents structured like this:

{_id: ObjectId("53d760721423030c7e14266f"),
fruit: 'apple',
vitamins: [
    {
     _id: 1,
     name: 'B7',
     usefulness: 'useful',
     state: 'free',
    }
    {
     _id: 2,
     name: 'A1',
     usefulness: 'useful',
     state: 'free',
    }
    {
     _id: 3,
     name: 'A1',
     usefulness: 'useful',
     state: 'non_free',
    }
  ]
}
{_id: ObjectId("53d760721423030c7e142670"),
fruit: 'grape',
vitamins: [
    {
     _id: 4,
     name: 'B6',
     usefulness: 'useful',
     state: 'free',
    }
    {
     _id: 5,
     name: 'A1',
     usefulness: 'useful',
     state: 'non_free',
    }
    {
     _id: 6,
     name: 'Q5',
     usefulness: 'non_useful',
     state: 'non_free',
    }
  ]
}

I want to query and get all the fruits which have both {name: 'A1', state: 'non_free'} and {name: 'B7', state: 'free'}. In the worst case I want at least to count these entries if getting them is not possible and if the equivalent code exists for pymongo, to know how to write it.

For the given example I want to retrieve only the apple (first) document.

If I use $elemMatch it works only for one condition, but not for both. E.g. if I query find({'vitamins': {'$elemMatch': {'name': 'A1', 'state': 'non_free'}, '$elemMatch': {'name': 'B7', 'state': 'free'}}}) it will retrieve all the fruits with {'name': 'B7', 'state': 'free'}.

like image 979
gevra Avatar asked Jul 29 '14 11:07

gevra


People also ask

How do I use multiple elemMatch in MongoDB?

In this case you can use the $and -operator . Try this query: find({ $and: [ {'vitamins': {'$elemMatch': {'name': 'A1', 'state': 'non_free'} } }, {'vitamins': {'$elemMatch': {'name': 'B7', 'state': 'free'} } } ] });

What does $elemMatch do in MongoDB?

Definition. The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

How do I test multiple values in MongoDB?

MongoDB provides the find() that is used to find multiple values or documents from the collection. The find() method returns a cursor of the result set and prints all the documents. To find the multiple values, we can use the aggregation operations that are provided by MongoDB itself.

What is $all in MongoDB?

$all. The $all operator selects the documents where the value of a field is an array that contains all the specified elements.


2 Answers

In this case you can use the $and-operator .

Try this query:

find({
    $and: [
         {'vitamins': {'$elemMatch': {'name': 'A1', 'state': 'non_free'} } },
         {'vitamins': {'$elemMatch': {'name': 'B7', 'state': 'free'} } }
    ]
});

To explain why you received only the result matching the second criteria: The objects inside each {} you pass to MongoDB are key/value pairs. Each key can only exist once per object. When you try to assign a value to the same key twice, the second assignment will override the first. In your case you assigned two different values to the key $elemMatch in the same object, so the first one was ignored. The query which actually arrived in MongoDB was just find({'vitamins': {'$elemMatch': {'name': 'B7', 'state': 'free'}}}).

Whenever you need to apply the same operator to the same key twice, you need to use $or or $and.

like image 171
Philipp Avatar answered Oct 07 '22 22:10

Philipp


var fruits = db.fruits.find({
    "vitamins": {
        $all: [{
            $elemMatch: {
                "name": "A1",
                "state": "non_free"
            }
        }, {
            $elemMatch: {
                "name": "B7",
                "state": "free"
            }
        }]
    }
})
like image 37
Karthik Bashyam Avatar answered Oct 07 '22 20:10

Karthik Bashyam