MongoDB: multiple $elemMatch

Question

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'}.

Philipp · Accepted Answer · 2014-07-29T13:15:39.620

42

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.

edited Jul 29 '14 at 13:15

answered Jul 29 '14 at 13:10

Philipp

67,764
9
118
153

1

but I need the fruit to have **both** the vitamins and not either of them – gevra Jul 29 '14 at 13:13
1

@gevra My bad, In that case use `$and` instead of `$or`. Updated answer. – Philipp Jul 29 '14 at 13:15
use `$and` instead `$or` – Barno Jul 29 '14 at 13:15
@Philipp you are so quickly :) – Barno Jul 29 '14 at 13:16
1

Is it possible to do something like this dynamically for an unknown number of $elemMatch's ? – Puma Apr 22 '15 at 21:18
1

@chillinpuma You could build the array you pass to `$and` dynamically on the application. – Philipp Apr 23 '15 at 07:45

score 8 · Answer 2 · edited Jul 29 '14 at 13:34

8

var fruits = db.fruits.find({
    "vitamins": {
        $all: [{
            $elemMatch: {
                "name": "A1",
                "state": "non_free"
            }
        }, {
            $elemMatch: {
                "name": "B7",
                "state": "free"
            }
        }]
    }
})

edited Jul 29 '14 at 13:34

Mo.

26,306
36
159
225

answered Jul 29 '14 at 13:30

Karthik Bashyam

81
1

please explaint your answer – Mo. Jul 29 '14 at 13:34
The Query finds the documents which satisfy the below conditions 1) Exact sub document match for name :B7 and state : free 2) Exact sub document match for name :A1 and state : non_free and the array should satisy both the conditions for that i used $all operator – Karthik Bashyam Jul 29 '14 at 13:39
As per stackoverflow, you should explain the answer – Mo. Jul 29 '14 at 13:46
Note: "The $all is equivalent to an $and operation of the specified values" from [$all operator docs](https://docs.mongodb.com/manual/reference/operator/query/all/#equivalent-to-and-operation) – ppython Dec 14 '17 at 20:22

score -2 · Answer 3 · answered Apr 27 '22 at 10:14

-2

let query = [];
  query.push({
    id: product.id,
  });
  query.push({ date });
  for (const slot of slots) {
    query.push({
      slots: {
        $elemMatch: {
          id: slot.id,
          spots: { $gte: slot.spots },
        },
      },
    });
  }
  const cal = await Product.findOne({ $and: query });

answered Apr 27 '22 at 10:14

Rafiq

8,987
4
35
35

This does not seems like native mongo query or python code as tagged by the question. – ray Apr 27 '22 at 10:20

MongoDB: multiple $elemMatch

3 Answers3

Linked