10

I have Game collection in my DB:

var game = {
  players: [{username:"user1", status:"played"},
            {username:"user2", status:"accepted"}]
}

As far as I understand query like this: db.games.find({"players.status":"played"}) will give me all games where at least one player has status "played". How can I find games with ALL players having status "played"?

andr111
  • 2,982
  • 11
  • 35
  • 45
  • Here a discussion with a more flexible solution: http://stackoverflow.com/questions/11823296/mongodb-find-subdocument-in-array-matching-parameters/11823340#11823340 – Marcello Verona Mar 30 '14 at 22:19

3 Answers3

13

If you only have one other status than "played" use the query:

db.games.find({ "players.status": { $ne:"accepted" } })

You can adjust the query to handle more status values, as long as they are all known at the time of the query.

karaxuna
  • 26,752
  • 13
  • 82
  • 117
Asya Kamsky
  • 41,784
  • 5
  • 109
  • 133
  • 1
    This is how I did it. Still wondering if there is a better way, since now I have to change the query each time I add a new game status. – andr111 May 08 '12 at 19:45
  • 1
    You can make your query dynamic - if you you create a set of all status values as an array, remove "played" from it and use it with $nin (not in) operation it should work. – Asya Kamsky May 08 '12 at 21:34
  • Thanks, I guess this is the only way I can do this. – andr111 May 08 '12 at 21:48
0

I ran into this problem too. I was trying to use $all and making little to no progress. It helped to flip my perspective on the problem and use $not instead. I wrote about what I found here.

In your situation, you should be able to do something like this:

db.games.find({
  players: {
    $not: {
      $elemMatch: {
        status: { $ne: "played" }
      }
    }
  }
})

Find all documents with a player status not equal to "played" and return the inverse of that set. The nice thing about this is that you never have to worry about adding more status values.

pcorey
  • 850
  • 2
  • 9
  • 14
-1

Use the $unwind feature of the aggregation framework:

db.collection.aggregate([{$unwind:"$players.status"},{$match:{"players.status":"played"},{$project:{"_id":0,"players.status":1}}])

(check out this answer: https://stackoverflow.com/a/15082321/1214167)

Community
  • 1
  • 1
yaara4
  • 304
  • 3
  • 8
  • 1
    that does not match what the OP wants - this will return just the players who have status "played" - the problem was to return teams where all players have played. You can do it with aggregation by extending the query to count the number of players who played and comparing it to the size of full array, but that's more complex and slower than simple find. – Asya Kamsky Jul 16 '14 at 17:25