MongoDB: match non-empty doc in array

Question

I have a collection structured thusly:

{
  _id: 1,
  score: [
    {
      foo: 'a',
      bar: 0,
      user: {user1: 0, user2: 7}
    }
  ]
}

I need to find all documents that have at least one 'score' (element in score array) that has a certain value of 'bar' and a non-empty 'user' sub-document.

This is what I came up with (and it seemed like it should work):

db.col.find({score: {"$elemMatch": {bar:0, user: {"$not":{}} }}})

But, I get this error:

error: { "$err" : "$not cannot be empty", "code" : 13030 }

Any other way to do this?

Answer 1

Figured it out: { 'score.user': { "$gt": {} } } will match non-empty docs.

Answer 2

I'm not sure I quite understand your schema, but perhaps the most straight forward way would be to not have an "empty" value for score.user ?

Instead purposely not have that field in your document if it has no content?

Then your query could be something like ...

> db.test.find({ "score" : { "$elemMatch" : { bar : 0, "user" : {"$exists": true }}}}) 

i.e. looking for a value in score.bar that you want (0 in this case) checking for the mear existence ($exists, see docs) of score.user (and if it has a value, then it'll exist?)

editied: oops I missed the $elemMatch you had ...

Answer 3

You probably want to add an auxiliary array that keeps track of the users in the user document:

{
  _id: 1,
  score: [
    {
      foo: 'a',
      bar: 0,
      users: ["user1", "user2"],
      user: {user1: 0, user2: 7}
    }
  ]
}

Then you can add new users atomically:

> db.test.update({_id: 1, score: { $elemMatch: {bar: 0}}},                       
... {$set: {'score.$.user.user3': 10}, $addToSet: {'score.$.users': "user3"}})

Remove users:

> db.test.update({_id: 1, score: { $elemMatch: {bar: 0}}},
... {$unset: {'score.$.user.user3': 1}, $pop: {'score.$.users': "user3"}})      

Query scores:

> db.test.find({_id: 1, score: {$elemMatch: {bar: 0, users: {$not: {$size: 0}}}}})

If you know you'll only be adding non-existent users and removing existent users from the user document, you can simplify users to a counter instead of an array, but the above is more resilient.