MongoDB: Match multiple array elements

Question

I have a collection with next data:

db.MyCollection.insert({ 
        id: 1, 
        Location: [ 1, 1 ],
        Properties: [ { Type: 1, Value: "a" }, { Type: 2, Value: "b" }, { Type: 3, Value: "c" } ]
    });

db.MyCollection.insert({ 
        id: 2, 
        Location: [ 1, 2 ],
        Properties: [ { Type: 1, Value: "a" }, { Type: 2, Value: "a" }, { Type: 3, Value: "c" } ]
    });

db.MyCollection.insert({ 
        id: 3, 
        Location: [ 2, 1 ],
        Properties: [ { Type: 1, Value: "a" }, { Type: 3, Value: "b" }, { Type: 3, Value: "a" } ]
    });

db.MyCollection.insert({ 
        id: 4, 
        Location: [ 2, 2 ],
        Properties: [ { Type: 2, Value: "b" }, { Type: 2, Value: "a" }, { Type: 3, Value: "c" } ]
    });

db.MyCollection.ensureIndex({ Location: "2d"});
db.MyCollection.ensureIndex({ "Properties.Type": 1, "Properties.Value": 1});
db.MyCollection.ensureIndex({ Location: "2d", "Properties.Type": 1, "Properties.Value": 1});

What I want is to find all items (using any of above indexes) that:

match the Location
contain properties with (Type=1 and Value="a") and (Type=2 and Value="b")

Here is my query (it doesn't work, but looks close to the right one):

db.MyCollection.find(
{ 
    Location: { "$within": { "$center": [ [1, 1], 5 ] } },
    Properties: {
        $elemMatch: {
            $and: [
                { Type: 1, Value: "a" },
                { Type: 2, Value: "b" }
            ]
        }
    }
})

Update:

The $all query works better as there is a problem with the $and one (see my comment in the JohnnyHK answer). Thanks for help.

JohnnyHK · Accepted Answer · 2013-03-13T13:52:15.047

15

In a case like this where you want the docs that include a specific set of array elements, you can use the $all operator:

db.MyCollection.find(
{ 
    Location: { "$within": { "$center": [ [1, 1], 5 ] } },
    Properties: {
        $all: [
            {$elemMatch: { Type: 1, Value: "a" }},
            {$elemMatch: { Type: 2, Value: "b" }}
        ]
    }
})

To do it without the $all operator you could use:

db.MyCollection.find(
{ 
    Location: { "$within": { "$center": [ [1, 1], 5 ] } },
    $and: [
        { Properties: {
            $elemMatch: { Type: 1, Value: "a" }
        }},
        { Properties: {
            $elemMatch: { Type: 2, Value: "b" }
        }}
    ]
})

edited Mar 13 '13 at 13:52

answered Mar 12 '13 at 15:28

JohnnyHK

305,182
66
621
471

1

Are there alternatives to $all operator (https://jira.mongodb.org/browse/SERVER-1748)? There is an open bug for this operator causes it performance penalty and looks like it will not be fixed in next 2 years – Kamarey Mar 12 '13 at 15:48
@Kamarey You could also do it with `$and` but I don't know if it would be any faster. See updated answer. – JohnnyHK Mar 12 '13 at 16:31
that's not right. You want $all *and* $elemMatch. See the indexing second for examples: http://www.chemeo.com/doc/technology – Asya Kamsky Mar 12 '13 at 19:31
@AsyaKamsky Both of these did work when I tested them. The `$all` elements behave as if you've specified `$elemMatch` for each one. – JohnnyHK Mar 12 '13 at 19:41
That's because this is a poor test data set to find that bug. – Asya Kamsky Mar 13 '13 at 00:06
@AsyaKamsky I know what you're getting at, but I did try it with an extra doc where the `Type:2` and `Value:b` parts were split between two elements and that wasn't included in the results. Let me know if you do find a doc where the `$all` query doesn't work. – JohnnyHK Mar 13 '13 at 00:24
Did you try one where subdocument has one other field in addition to type and value? – Asya Kamsky Mar 13 '13 at 09:07
@AsyaKamsky I don't know if that case is in scope of the OP's problem or not. But you're right, if you're not making an exact match then you'd need `$elemMatch`. – JohnnyHK Mar 13 '13 at 12:37
In my real app I do have additional fields within the Property object. I don't catch your above talks. I understand that the query with $and will not work with all cases (depends on data), and there is a problem with $elemMatch in a case there are other properties except Type and Value. Do I right? Can you explain more about it? – Kamarey Mar 13 '13 at 13:47
@Kamarey I updated the answer that uses `$all` to include `$elemMatch` so I _think_ they both should work in all cases now. – JohnnyHK Mar 13 '13 at 13:54
Is it true to say that the both queries are identical? – Kamarey Mar 13 '13 at 14:00
@Kamarey I believe so, yes. – JohnnyHK Mar 13 '13 at 14:03
And the last question: do you know if there are any limitations for above queries in sharded environment? Specifically the $and query don't use index when I use more than 1 element within the $and array (even without location part). With $all everything is ok – Kamarey Mar 14 '13 at 13:09
@Kamarey These queries will work fine in a sharded environment. – JohnnyHK Mar 14 '13 at 13:15
Why does `$all` not work for empty arrays? – Displee Jan 28 '22 at 20:45

MongoDB: Match multiple array elements

1 Answers1

Linked