CODEWITHSUNDEEP
node.jsmongodb
Miguel
Miguel

Reputation: 141

Aggregate mongodb by latest timestamp

I'd like to get the "population" of each city's last timestamp using the aggregate function.

In a MongoDB like this:

{
  "_id": {"$oid": "55354bc97b5dfd021f2be661"},
  "timestamp": {"$date": "2015-04-20T18:56:09.000Z"},
  "city": "Roma",
  "population": [
    {"age": 90,"count": 1000},
    {"age": 25,"count": 25}
  ]
},
{
  "_id": {"$oid": "55354c357b5dfd021f2be663"},
  "timestamp": {"$date": "2015-04-20T18:57:57.000Z"},
  "city": "Madrid",
  "population": [
    {"age": 90,"count": 10},
    {"age": 75,"count": 2343},
    {"age": 50,"count": 500},
    {"age": 70,"count": 5000}
  ]
},
{
  "_id": {"$oid": "55362da541c37aef07d4ea9a"},
  "timestamp": {"$date": "2015-04-21T10:59:49.000Z"},
  "city": "Roma",
  "population": [
    {"age": 90,"count": 5}
  ]
}

I'd like to retrieve all the cities, but for each one only the latest timestamp:

{
  "city": "Roma",
  "population": [
    {"age": 90,"count": 5}
  ]
},
{
  "city": "Madrid",
  "population": [
    {"age": 90,"count": 10},
    {"age": 75,"count": 2343},
    {"age": 50,"count": 500},
    {"age": 70,"count": 5000}
  ]
}

I have tried something like this answer, but I don't know how to "unwind" the populations after getting the latest timestamp for each city:

db.collection('population').aggregate([
  { $unwind: '$population' },
  { $group: { _id: '$city', timestamp: { $max: '$timestamp' } } },
  { $sort: { _id : -1 } }
  ], function(err, results) {
    res.send(results)
});

Upvotes: 1

Views: 1569

Answers (3)

BivorAdrito
BivorAdrito

Reputation: 84

I use this to sort any timestamp field using aggregation, I am sorting it by the latest update time of the document. If you need you can group it later. You can learn more about [aggregate sorting here.][1]

aggregate.push({ $sort: { updated_at: -1 } });

What I do is I make blocks of aggregate actions push them into an array and execute it all together. I find it easier to debug if something is not working properly. [1]: https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort/

Upvotes: 0

Moshe Simantov
Moshe Simantov

Reputation: 4503

I think that you want to use $project instead of $unwind:

db.collection('population').aggregate([{
     $group: {
       _id: '$city',
       timestamp: {$max: '$timestamp'}
     }
  }, {
     $project: {
       population: '$doc.population'
     }
  }, {
    $sort: {
      _id : -1
  }
}], function(err, results) {
  res.send(results)
});

Upvotes: 1

chridam
chridam

Reputation: 103455

The following aggregation pipeline will give you the desired result. The first step in the pipeline orders the documents by the timestamp field (descending) and then groups the ordered documents by the city field in the next $group stage. Within the $group operator, you can extract the population array field by way of the $$ROOT operator. The $first operator returns the value that results from applying the $$ROOT expression to the first document in a group of documents that share the same city key. The final pipeline stage involves projecting the fields from the previous pipeline into the desired fields:

db.population.aggregate([      
    {
        "$sort": { "timestamp": -1 }
    },
    { 
      "$group": { 
          "_id": "$city",          
          "doc": { "$first": "$$ROOT" }
       } 
    },
    {
        "$project": { 
          "_id": 0, 
          "city": "$_id",
          "population": "$doc.population"
       }
    }       
]);

Output:

/* 0 */
{
    "result" : [ 
        {
            "city" : "Madrid",
            "population" : [ 
                {
                    "age" : 90,
                    "count" : 10
                }, 
                {
                    "age" : 75,
                    "count" : 2343
                }, 
                {
                    "age" : 50,
                    "count" : 500
                }, 
                {
                    "age" : 70,
                    "count" : 5000
                }
            ]
        }, 
        {
            "city" : "Roma",
            "population" : [ 
                {
                    "age" : 90,
                    "count" : 5
                }
            ]
        }
    ],
    "ok" : 1
}

Upvotes: 2

Related Questions