MongoDB advanced query: get elements in an array matching a second condition

Question

We have a collection of elements that has the following structure:

elements:

{
id : 123,
items : [ { color: "blue", "groups" : [3, 5] }, { color: "red", "groups" : [6, 8] } ]
}

{
id : 124,
items : [ { color: "blue", "groups" : [1, 2] }, { color: "green", "groups" : [5, 9] } ]
}

We want an efficient way to get Elements that have an item with color blue accessible to groups 5, 9, 27, 123 or 56. This should return Element with id 123 but not element with id 124 because item must meet both conditions. We want the query to be as efficient as possible.

This query is efficient but does not meet the requirement:

{$and : { "items.groups" : { $in : [5, 9, 27, 123, 56] }, "items.color" : "blue" }} 

as it will match id = 124 because it has an item that matches "blue" and another one that matches group 9.

Answer 1

You need to use $elemMatch because you're looking to match multiple attributes of a single array element:

db.test.find({ items: { $elemMatch: { 
    color: "blue", 
    groups: { $in: [5, 9, 27, 123, 56] } 
}}});