Question 118201 in Mongodb Question posted in
The official documentation can be found here.

Fetch last record based on field in mongoDB

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"
}]
Tags:

2

Answers


  1. 0
    1. $sort – Sort by eventTime DESC.
    2. $group – Group by deviceId and take first document into data field via $first.
    3. $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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search