'MongoDB get only the last documents per grouping based on field

I have a collection "TokenBalance" like this holding documents of this structure

{
_id:"SvVV1qdUcxNwSnSgxw6EG125"
 balance:Array
 address:"0x6262998ced04146fa42253a5c0af90ca02dfd2a3"
 timestamp:1648156174658
 _created_at:2022-03-24T21:09:34.737+00:00
 _updated_at:2022-03-24T21:09:34.737+00:00
}

Each address has multiple documents like of structure above based on timestamps.

So address X can have 1000 objects with different timestamps.

What I want is to only get the last created documents per address but also pass all the document fields into the next stage which is where I am stuck. I don't even know if the way I am grouping is correctly done with the $last operator. I would appreciate some guidance on how to achieve this task.

What I have is this

$group stage (1st stage)

{
_id: '$address',
timestamp: {$last: '$timestamp'}
}

This gives me a result of

_id:"0x6262998ced04146fa42253a5c0af90ca02dfd2a3"
 timestamp:1648193827320

But I want the other fields of each document as well so I can further process them.

Questions

1) Is it the correct way to get the last created document per "address" field?

2) How can I get the other fields into the result of that group stage?

mongodb


Solution 1:[1]

Use $denseRank

db.collection.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$address",
      sortBy: { timestamp: -1 },
      output: { rank: { $denseRank: {} } }
    }
  },
  {
    $match: { rank: 1 }
  }
])

mongoplayground

Solution 2:[2]

I guess you mean this:

{ $group: {
    _id: '$address',
    timestamp: {$last: '$timestamp'},
    data: { $push: "$$ROOT" }
} }

Solution 3:[3]

If the latest timestamp is also the last sorted by _id you can use something like this:

[{$group: {
 _id: '$_id',
 latest: {
  $last: '$$ROOT'
 }
}}, {$replaceRoot: {
 newRoot: '$latest'
}}]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 YuTing
Solution 2 Wernfried Domscheit
Solution 3 Ilan Toren

Related Questions