CODEWITHSUNDEEP
mongodbdategroup-by
atandon
atandon

Reputation: 557

mongoDB get the latest date for an array

I like to get the latest enter and exit timestamp for a given user and for a given location. The collection is like this 

{ "ActivityList" : [ 
{ "type" : "exit",
      "timestamp" : Date( 1348862537170 ),
      "user" : { "$ref" : "userProfile",
        "$id" : ObjectId( "4fdeaeeede26fd298262bb80" ) } }, 
    { "type" : "entry",
      "timestamp" : Date( 1348862546966 ),
      "user" : { "$ref" : "userProfile",
        "$id" : ObjectId( "4fdeaeeede26fd298262bb80" ) } }, 
       { "type" : "entry",
      "timestamp" : Date( 1348870744386 ),
      "user" : { "$ref" : "userProfile",
        "$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } }, 
    { "type" : "exit",
      "timestamp" : Date( 1348878233785 ),
      "user" : { "$ref" : "userProfile",
        "$id" : ObjectId( "4fdeaf6fde26fd298262bb81" ) } } ],
  "Location" : { "$ref" : "loc",
    "$id" : ObjectId( "4fd410f0e7e994b59054b824" ) },
  "_id" : ObjectId( "4fe8f3c6e7e9ebe3697ee836" ) }

I tried something like this but does not work 

db.collection.group(
{
    keyf: function(doc) {
        return {
            location    :doc.Location._id,
             userid     : doc.ActivityList.user._id,           
             actiontype : doc. ActivityList.type
        };
    },
    reduce: function(obj,prev) {
        if (prev.maxdate < obj. ActivityList.timestamp) { 
            prev.maxdate = obj. ActivityList.timestamp; 
        } 
    },
    initial: {maxdate:0}
});

Thanks for your help.

Upvotes: 2

Views: 4642

Answers (1)

Stennie
Stennie

Reputation: 65303

A simple $group won't work for your data structure and finding/filtering the maximum values within an array. You would have to iterate the array to find the maximum values, which would be more efficiently done by retrieving the document and iterating in your application code.

A possible server query approach in MongoDB 2.2 would be to use the new Aggregation Framework:

db.activity.aggregate(

    // Find matching location documents first (can take advantage of index)
    { $match : {
        Location: {
            "$ref" : "loc", 
            "$id" : ObjectId("4fd410f0e7e994b59054b824")
        }
    }},

    // Unwind the ActivityList arrays as a document stream
    { $unwind : "$ActivityList" },

    // Filter activities to the user reference of interest
    { $match : {
       'ActivityList.user': {
            "$ref" : "userProfile",
            "$id" : ObjectId("4fdeaeeede26fd298262bb80")
        } 
    }},

    // Group the stream by activity types, and get the timestamp for the latest of each
    { $group : {
        _id : "$ActivityList.type",
        latest: { $max: '$ActivityList.timestamp' }
    }}
)

Sample result:

{
    "result" : [
        {
            "_id" : "entry",
            "latest" : ISODate("2012-09-28T20:02:26.966Z")
        },
        {
            "_id" : "exit",
            "latest" : ISODate("2012-09-28T20:02:17.170Z")
        }
    ],
    "ok" : 1
}

Upvotes: 2

Related Questions