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-13 13:52:15Z

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, 2013 at 13:52

answered Mar 12, 2013 at 15:28

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

15 Comments

Kamarey Over a year ago

Are there alternatives to $all operator (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

JohnnyHK Over a year ago

@Kamarey You could also do it with $and but I don't know if it would be any faster. See updated answer.

Asya Kamsky Over a year ago

that's not right. You want $all and $elemMatch. See the indexing second for examples: chemeo.com/doc/technology

JohnnyHK Over a year ago

@AsyaKamsky Both of these did work when I tested them. The $all elements behave as if you've specified $elemMatch for each one.

Asya Kamsky Over a year ago

That's because this is a poor test data set to find that bug.

|

Collectives™ on Stack Overflow

MongoDB: Match multiple array elements

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related