How to perform AND operation in Mongodb for a query on nested array of objects

Question

I have the following records:

Record 1:  
{
    "status": "active",
    "users": [
        {
            "name": "foo",
            "status": "a"
        },
        {
            "name": "foo",
            "status": "b"
        }        
    ]
}

Record 2:  
{
    "status": "active",
    "users": [
        {
            "name": "bar",
            "status": "a"
        },
        {
            "name": "foo",
            "status": "b"
        }        
    ]
}

Now, I wish to return only those records in which the user.name is "foo" and the user.status is "a" - That is I need to get Record 1 back since it satisfies my requirements.

How do I do the AND operation on the array within the record to achieve this? I tried the following query:

{
    "$and": [
        {
            "users.name": "foo"
        },
        {
            "users.status": "a"
        }
    ]
}

but I get both the records - I need only the first record (since in the users array, it has an entry where the name is "foo" AND the status is "a").

Any clues?

callmekatootie · Accepted Answer · 2013-06-19 15:30:51Z

3

Ok, I found the answer out here

The solution would be to have a query as follows:

{
    "users": {
        "$elemMatch": {
            "name": "foo",
            "status": "a"
        }
    }
}

That did the trick!

answered Jun 19, 2013 at 15:30

callmekatootie

11.2k16 gold badges74 silver badges109 bronze badges

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

Comments

Philipp · Accepted Answer · 2013-06-19 15:12:39Z

0

Instead of individual field values you can also use whole objects in find queries. Try:

db.collection.find( { 
    users: {
         "name":"foo",
         "status":"a"
    }
});

This should return all documents where the users array includes an object (or is an object) which looks as described. But note that this doesn't work when the object has additional fields.

answered Jun 19, 2013 at 15:12

Philipp

70.1k10 gold badges121 silver badges159 bronze badges

2 Comments

callmekatootie Over a year ago

So you mean to say that if users array contains objects and these objects contain fields other than name and status, then the above query will not work? In my scenario, yes - the object contains fields other than the two mentioned, but I do not wish to include them in the query - how do I go about it then?

Asya Kamsky Over a year ago

@Philipp please note that your answer won't work correctly - it will only match the exact object {"name":"foo","status":"a"} - it won't even match users that has those two fields in the opposite order!

Collectives™ on Stack Overflow

How to perform AND operation in Mongodb for a query on nested array of objects

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related