Why does query document erroneously match field in array of subdocuments using value `null`?

Question

I am trying to match a field without specifying an array index with the following query:

db.games.find( { 'players.userId': userId } )

where userId is either a String or is null if no user is logged in. I expect the count() of the above cursor to be 0 when userId === null, but I find that this is not the case -- all documents for which players is an empty array [] are counted. I would expect that players.userId would be undefined perhaps, but not null. What is going on here? Does mongo use == and not ===, coercing undefined and null to be the same value?

Apparently, you have documents with that field set to null. — Sergio Tulentsev
– Sergio Tulentsev, Commented Feb 24, 2014 at 18:01
when count() returns unexpected number, leave off the count() and see what find returns - those are the documents you didn't expect. — Asya Kamsky
– Asya Kamsky, Commented Feb 25, 2014 at 1:36

Neil Lunn · Accepted Answer · 2014-02-25 01:38:59Z

2

To explain consider the following documents:

{ 
    name: "PacMan",
    players: [ { userId: "Bill" }, { userId: "Ted" }, { userId: "Death" } ]
}
{ 
    name: "Frogger", 
    players: [ { userId: null }, { userId: "Bill" }, { userId: null } ] 
}
{
    name: "Defender",
    players: [ ]
}

If you issue a query like:

>db.games.find({ "players.userId" : "Bill" })

{ 
    name: "PacMan",
    players: [ { userId: "Bill" }, { userId: "Ted" }, { userId: "Death" } ]
}
{ 
    name: "Frogger", 
    players: [ { userId: null }, { userId: "Bill" }, { userId: null } ] 
}

You will get the two documents you expect that has the player with that userId present in the array. But if we change that to null

>db.games.find({ "players.userId" : null })

{ 
    name: "Frogger", 
    players: [ { userId: null }, { userId: "Bill" }, { userId: null } ] 
}
{
    name: "Defender",
    players: [ ]
}

Wait a minute, you get a document you didn't expect. There are no elements in the players array. So why is it matching? Let's look at this form:

>db.games.find({ "players.userId" : {$exists: true } })
{ 
    name: "PacMan",
    players: [ { userId: "Bill" }, { userId: "Ted" }, { userId: "Death" } ]
}
{ 
    name: "Frogger", 
    players: [ { userId: null }, { userId: "Bill" }, { userId: null } ] 
}

Now that gives us results only where the players.userId field is actually there, and the third document will not be included as it has no items that match this condition. So finally consider the last form:

>db.games.find({ $and: [
    {"players.userId" : {$exists: true }}, 
    {"players.userId": null }
] })

{ 
    name: "Frogger", 
    players: [ { userId: null }, { userId: "Bill" }, { userId: null } ] 
}

This will find the result whsere there is a field present and it has a null value.

So to sum up. Even if an array does not contain any elements that would match a null value test it may still return as a result as the null condition is considered to be true as the empty items evaluates to null. If you want to exclude this case then you use $exists in order to test for the presence of the field in the first place.

answered Feb 25, 2014 at 1:38

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

2 Comments

Donny Winston Over a year ago

Thank. However, it's confusing to me that the query would match on empty arrays because these arrays do not contain elements on which to match.

Neil Lunn Over a year ago

@DonnyWinston Re-read the answer. Search the documentation given the clauses I used. It's not confusing, there is a really good reason why this is so. It's best you understand that for your own development.

Collectives™ on Stack Overflow

Why does query document erroneously match field in array of subdocuments using value `null`?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related