I want to find the most up-to-date data by user in mongodb.

Assuming the data exists as follows:

{
 _id: "1",
 user_id: "userA",
 date: "2022-10-20 11:00:00.000000000"
},
{
 _id: "2",
 user_id: "userA",
 date: "2022-10-25 18:00:00.000000000"
},
{
 _id: "3",
 user_id: "userB",
 date: "2022-10-25 18:00:00.000000000"
},
{
 _id: "4",
 user_id: "userC",
 date: "2022-10-25 18:00:00.000000000"
}

So I want find result like this:

{
 _id: "2",
 user_id: "userA",
 date: "2022-10-25 18:00:00.000000000"
},
{
 _id: "3",
 user_id: "userB",
 date: "2022-10-25 18:00:00.000000000"
},
{
 _id: "4",
 user_id: "userC",
 date: "2022-10-25 18:00:00.000000000"
}

What's the best way to this?

CodePudding user response：

If you're using Mongo version 5.2 then you can use $group with the new $bottom operator, like so:

db.collection.aggregate([
  {
    $group: {
      _id: "$user_id",
      root: {
        "$bottom": {
          "sortBy": {
            "date": 1
          },
          "output": "$$ROOT"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: "$root"
    }
  }
])

MongoPlayground

Otherwise there is no real good way of doing this, you'll have to sort the entire collection by date (assuming all dates are saved in a proper format) then group by user id and get the "latest" instance.

db.collection.aggregate([
  {
    $sort: {
      date: -1
    }
  },
  {
    $group: {
      _id: "$user_id",
      root: {
        $first: "$$ROOT"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: "$root"
    }
  }
])

Mongo Playground