Search in Array in mongodb

Question

I have a collection "room_user" with these document -

/* 0 */
{
    "_id" : ObjectId("524d1ae07847c05671a2965c"),
    "room_name" : "room1",
    "members" : [ 
        "user1", 
        "user2"
    ]
}

/* 1 */
{
    "_id" : ObjectId("524d1aec7847c05671a2965d"),
    "room_name" : "room2",
    "members" : [ 
        "user1", 
        "user2", 
        "user3"
    ]
}

/* 2 */
{
    "_id" : ObjectId("524d1af27847c05671a2965e"),
    "room_name" : "room3",
    "members" : [ 
        "user1", 
        "user2", 
        "user4"
    ]
}

/* 3 */
{
    "_id" : ObjectId("524d1b387847c05671a2965f"),
    "room_name" : "room4",
    "members" : [ 
        "user1", 
        "user5"
    ]
}

Now I want to search/get the name of room ("room_name") where ONLY user1 and user2 are.

Lix · Accepted Answer · 2013-10-03 08:27:34Z

2

This should be the query you are looking for:

db.room_user.find(
  { "$and": 
    [ 
      { "members": { "$all": [ "user2", "user1" ] } } ,
      { "members": { "$size": 2 } } 
    ]
  },
  { "room_name": 1 }
)

The first part of the query matches the members array that contains all of the desired elements. In addition it will only match an array with a size of 2.
The second part is the projection where we are only returning the room_name attribute. Note that you'll also get the _id attribute unless you specifically state that you don't want it.

Index considerations:

The documentation of the $all operator has this to say regarding the use of indexes when using this operator (emphasis added):

Note In most cases, MongoDB does not treat arrays as sets. This operator provides a notable exception to this approach. In the current release queries that use the $all operator must scan all the documents that match the first element in the query array. As a result, even with an index to support the query, the operation may be long running, particularly when the first element in the array is not very selective.

edited Oct 3, 2013 at 8:27

answered Oct 3, 2013 at 7:44

Lix

48.2k12 gold badges107 silver badges134 bronze badges

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

10 Comments

Saurabh Over a year ago

I have to search/get "room_name" so can't provide that in query. I tried this query without room_name and its not working :(

Lix Over a year ago

The room name is not in the query. It is in the projection. There is a difference. The protection defines what fields you want Mongo to return

Sammaye Over a year ago

The query is most likely not working because object comparison is order sensitive.

Oran Over a year ago

@Lix I don't recommend to use $all operator since it's a slow op that doesn't use indexes. Please check out the note in this link: [docs.mongodb.org/manual/reference/operator/all/#op._S_all]

Lix Over a year ago

@ora - good observation! Indeed with regard to performance it would be wise to take this into consideration. That being said - the query will still work.

|

Collectives™ on Stack Overflow

Search in Array in mongodb

1 Answer 1

Index considerations:

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Index considerations:

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related