Simple MongoDB query on HASH not working as expected (only in some conditions)

Question

Ok, it's a bit complex so i'll try to be clear.

I have a structure I use to build some sort of ticket system.

Parent Collection: Thread

Embedded Collection: Message (a Thread embeds 0..N Messages)

In a message I have an attribute "read_time" of type HASH where the keys are the OID of a user and the values a datetime. A sample set of data for a Thread would look like

_id                   "4e9806c223349f0001000044" 
author_id            {"$oid": "4e8b281429e167765d00001a"} 
created_at           2011-10-14 09:54:10 UTC 
ref                  252 
status       "open" 
... 
messages 
                    [ 
                      0 
                          { 
                          _id                  {"$oid":     "4e9806c223349f0001000045"} 
                          author_id            {"$oid":     "4e8b281429e167765d00001a"} 
                          content              "Hello" 
                          created_at    2011-10-14 09:54:11 UTC 
                          read_time 
                                               { 
                                                     4e8b281429e167765d00001a           2011-10-14 09:54:11 UTC
                                                     4d5a7dfe29e1674958000013           2011-10-14 11:48:18 UTC
                                                     4d5a62ac29e1676226000050     2011-10-15 06:44:21 UTC 
                                               } 
                          }, 
                    1 
                          { 
                          _id                  {"$oid":   "4e9806c223349f0001000046"} 
                          author_id            {"$oid":   "4e8b281429e167765d00001a"} 
                          content              "Hello 2" 
                          created_at    2011-10-14 09:54:11 UTC 
                          read_time 
                                               { 
                                                     4e8b281429e167765d00001a           2011-10-15 09:54:11 UTC 
                                                     4d5a7dfe29e1674958000013           2011-10-16 11:48:18 UTC 
                                               } 
                          } 
                    ] 

The idea here is to build a query to only the threads that are UNREAD for a given author. With the example given above, the user with OID 4d5a62ac29e1676226000050 has read the first message of the thread but not the second (as the read_time hash does not contain an entry for the key "4d5a62ac29e1676226000050").

My query looks likes this, which is pretty straight forward in my opinion and should work flawlessly but the results are quite unexpected....

{ "support_messages.read_time.4d5a62ac29e1676226000050" : { "$exists" : false} } 

Simply put, I query all the Threads that contains at least one message that does not have a key of "4d5a62ac29e1676226000050" in it's read_time attribute.

The weird part now... is that this query works, but not all the time ! It only returns a subset of the threads that I am expecting to see. I haven't been able to determine an exact pattern yet for the cases that don't work, but it seems that when there are more than one Messages in a thread and that "many" other users have read them, but not the user I am querying for, then the Thread in question does not appear in the results... I have no idea why. If I am querying the documents manually I see all the data I am expecting (just like the example above), but the Thread is simply ignored...

Please help !

Alex

Answer 1

If I understand correctly, you want 4e8b281429e167765d00001a, 4d5a7dfe29e1674958000013, 4d5a62ac29e1676226000050 to be the keys of the inner hash under the first read_time. But I can't see any colon between (let say) 4e8b281429e167765d00001a and 2011-10-14 09:54:11 UTC.

I would be expect it to be:

read_time 
{
   4e8b281429e167765d00001a : 2011-10-14 09:54:11 UTC
   4d5a7dfe29e1674958000013 : 2011-10-14 11:48:18 UTC
   4d5a62ac29e1676226000050 : 2011-10-15 06:44:21 UTC 
}