Displaying $last in mongo $group aggregation

[Tibin George]

This is my record

```

{"_id" : ObjectId("2u3iuyehlej"), "x_id" : 7,"y_id" : 2886,"created_at" : ISODate("2018-03-27T11:30:17.923Z")}

{"_id" : ObjectId("wfywutdwty"), "x_id" : 7,"y_id" : 2886,"created_at" : ISODate("2018-03-38T10:30:17.923Z")}

{"_id" : ObjectId("jhgwjhghdh"), "x_id" : 7,"y_id" : 2226,"created_at" : ISODate("2018-03-29T15:30:17.923Z")}

{"_id" : ObjectId("wejhgwehw"), "x_id" : 7,"y_id" : 2856,"created_at" : ISODate("2018-03-30T19:30:17.923Z")}

``

I want to display the count of unique `y_id` with first `created_at` timestamp.

Ex. Output for the above records should be  

{ "_id" : null, "value" : 3, "timestamp" :  ISODate("2018-03-30T19:30:17.923Z") }

That is there are 2 unique y_id and date on last record is ISODate("2018-03-30T19:30:17.923Z")

I tried running this query. 

 

db.stySolutionEvaluationLog.aggregate([ { "$match" : { "x_id" :7 } },{ $sort: {created_at: 1 } },{"$group" : {"_id" : "$y_id"}},{$group: {_id:null ,value: {$sum: 1},timestamp: {$last:"$created_at"} }}]);

But it returned null for timestamp

{ "_id" : null, "value" : 3, "timestamp" : null }

[Tiago Brito]

Hi,

Your first $group does not project the timestamp further into the pipeline, so the second $group stage does not know about it. After the first $group, you're just outputting {"_id": XXX} and leaving out timestamp.

You can try:

db.stySolutionEvaluationLog.aggregate([ { "$match" : { "x_id" :7 } },{ $sort: {created_at: 1 } },{"$group" : {"_id" : "$y_id", "created_at": {"$last": "$created_at" }}},{$group: {_id:null ,value: {$sum: 1},timestamp: {$last:"$created_at"} }}]);

(there may be syntax errors as I did not test it. apologies in advance)

This will ensure the "created_at" field gets projected into the second group stage. So first you get the last timestamp for each y_id, and then you get the last timestamp overall.

I hope this helps

Tiago

[Tibin George]

Thank You Tiago Brito,