Querying documents by fields in an array

Question

In MongoDB I have a collection of documents called 'clients', where each document is structured roughly as follows. Note that TimeStamp is a DateTime:

{
  "Sessions": [
    {
      "SessionId": "pojiu5iprr2xw5ucsudyrkpv",
      "LocationId": {
        "$oid": "4de4590bfcee1a1b44165e2b"
      },
      "Timestamp": "Wed, 01 Jun 2011 09:39:26 GMT -04:00"
    }
  ],
  "_id": {
    "$oid": "4de6410efcee1a1204a3326c"
  }
}

I'd like to be able to query for "all clients which have at least one session with a timestamp in the last 24 hours". Is this possible without resorting to a full M/R scan? I will also accept answers with a statically defined Date, e.g. "all clients which have at least one session with a timestamp later than 1/5/2012"

Eve Freeman · Accepted Answer · 2012-01-07 22:50:13Z

1

How about adding a field to the main document for "latest session time".

Updated: Ok, here's how you can do it without adding a new field/index:

db.clients.find(
   { "Sessions" : 
      { $elemMatch : 
         { Timestamp : { $gt : new Date(new Date() - 86400000) } } 
      } 
   } 
);

same code on a single line:

db.clients.find({ "Sessions" : { $elemMatch : { Timestamp : { $gt : new Date(new Date() - 86400000) } } } } );

86400000 is the number of milliseconds in a day.

edited Jan 7, 2012 at 22:50

answered Jan 7, 2012 at 19:47

Eve Freeman

33.3k5 gold badges89 silver badges104 bronze badges

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

4 Comments

Chris Shain Over a year ago

That's my backup plan. I'd rather not have to duplicate data.

Eve Freeman Over a year ago

Think of it as a way of indexing. That's all indexes are, in reality--duplicate data.

Chris Shain Over a year ago

Sure. I just want to stay out of the index writing business if I can.

Eve Freeman Over a year ago

Ok, updated my answer. Looks like it's possible with $elemMatch. You might want to add an index on "Sessions.Timestamp"...

Collectives™ on Stack Overflow

Querying documents by fields in an array

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related