CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkaggregate
Shrabanee
Shrabanee

Reputation: 2766

Mongodb - aggregate - Get records from a set of records for each date from a set of dates

==Edit==

Initial records from DB:

[{ _id: 100,
  ca : 12,
  st : 0,
 tranhisRev: [
 { 
   uid : 1,
   et: 2018-02-08T11:28:53.745Z },
 { 
   uid : 2,
   et: 2018-02-08T11:28:55.745Z },
 { 
   uid : 3,
   et: 2018-02-07T10:56:05.993Z },
 {
   uid : 4,
   et: 2018-02-07T10:56:08.927Z },
 { 
   uid : 1,
   et: 2018-02-06T10:55:57.094Z },
 { 
   uid : 2,
   et: 2018-02-06T10:59:57.094Z }
]},
{ _id: 101,
  ca : 19,
  st : 1,
 tranhisRev: [
 { 
   uid : 1,
   et: 2018-02-08T11:28:53.745Z },
 { 
   uid : 2,
   et: 2018-02-08T11:28:55.745Z },
 { 
   uid : 3,
   et: 2018-02-07T10:56:05.993Z },
 {
   uid : 4,
   et: 2018-02-07T10:56:08.927Z },
 { 
   uid : 1,
   et: 2018-02-06T10:55:57.094Z },
 { 
   uid : 2,
   et: 2018-02-06T10:59:57.094Z }
]}
]

I want to get the last records for each date i.e. 6th feb, 7th feb and 8th feb and for each '_id'.

Desired result is :

[
{ _id: 100,
  ca : 12,
  st : 0,
 tranhisRev: 
 { 
   uid : 1,
   et: 2018-02-08T11:28:55.745Z } },

{ _id: 100,
  ca : 12,
  st : 0,
tranhisRev: 
 {
   uid : 4,
   et: 2018-02-07T10:56:08.927Z } },

{ _id: 100,
  ca : 12,
  st : 0,
tranhisRev: 
 { 
   uid : 2,
   et: 2018-02-06T10:59:57.094Z} },

{ _id: 101,
  ca : 19,
  st : 1,
 tranhisRev: 
 { 
   uid : 2,
   et: 2018-02-08T11:28:55.745Z } },

{ _id: 101,
  ca : 19,
  st : 1,
tranhisRev: 
 {
   et: 2018-02-07T10:56:05.927Z } },

{ _id: 101,
  ca : 19,
  st : 1,
tranhisRev: 
 { 
   uid : 2,
   et: 2018-02-06T10:59:57.094Z } }]

I have tried to use 

$group:
{
    _id: "$_id",
    tranhisRev: {$first: "$tranhisRev"}
}

But did not help me and I got one record for each _id.

$redact I have tried using but that also did not help me.

$redact will help if for only one day I want to get data. But the problem is I have to get records for multiple dates as well.

Can someone guide me on how I can achieve this?

Any existing question link also will help.

Upvotes: 0

Views: 59

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

"For each day for each id" means you need to group by both values, i.e. include both day and _id in the grouping _id:

db.collection.aggregate([
    { $unwind: "$tranhisRev" },
    { $group: { 
        _id: { 
            id: "$_id", 
            day: { $dateToString: { format:"%Y-%m-%d", date:"$tranhisRev.et" } }
        }, 
        et:{ $max:"$tranhisRev.et" } 
    } }
])

Upvotes: 1

Related Questions