Fetch last record based on field in mongoDB-CodePudding

I'm a learner and new at mongo. I'm trying to fetch the last records according to particular field. In SQL select * from powerandlevel group by deviceId order by desc limit 1. So in this SQL query based on deviceId I can get the last record of every deviceId. Same I want do in mongodb

[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:35:30.615Z"
deviceId: "48701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]

And I'm using this query db.imageEvent.aggregate([{ "$group" : {"_id" : { "deviceId" : "$deviceId"}}}, {$sort: {eventTime: 1}}, { $limit : 1 }])

Result

[{ "_id" : { "deviceId" : "58701ED21819" } },
{ "_id" : { "deviceId" : "48701ED21819" } }]

And expecting a result like this

[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]

CodePudding user response：

If you don't need the ObjectId of each result, just the common values and the latest date, you can simplify it by:

db.imageEvent.aggregate([
  {
    $group: {
      _id: "$deviceId",
      imageName: {$first: "$imageName"},
      eventTime: {$max: "$eventTime"},
      deviceId: {$first: "$deviceId"},
    }
  }
])

As you can see on the playground The $first is convenient for values that are the same for the group. The $max will give you the latest date.

This will prevent you from sorting the entire collection and from creating large documents during the query, since there is a cap size for a document.

CodePudding user response：

$sort - Sort by eventTime DESC.
$group - Group by deviceId and take first document into data field via $first.
$replaceRoot - Replace the input document with data.

db.imageEvent.aggregate([
  {
    $sort: {
      eventTime: -1
    }
  },
  {
    "$group": {
      "_id": {
        "deviceId": "$deviceId"
      },
      data: {
        $first: "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$data"
    }
  }
])

Sample Mongo Playground