CODEWITHSUNDEEP
mongodbmongodb-query
roschach
roschach

Reputation: 9336

MongoDB query documents having a latest value of a field and keeping other fields

Suppose that I have this schema document in mongo:

{
    "_id" : ObjectId("asd9024380s"),
    "timestamp" : 1581407619976,
    "value" : 5,
    "typeId" : ObjectId("lkjdsa08934"),
    "unitId" : ObjectId("ce234890")
}

First of all I want to consider only the documents having, for example, abc as unitId. Among them, for each typeId I want retrieve only the latest one (the one having the highest timestamp).

Suppose the collection is like:

_id timestamp value typeId unitId
1 20 3 "ff" "abc"
2 17 2 "ff" "fff"
3 18 3 "gg" "abc"
4 21 5 "gg" "fff"
5 15 4 "ff" "abc"
6 24 2 "gg" "abc"

I would like to get:

_id timestamp value typeId unitId
1 20 3 "ff" "abc"
6 24 2 "gg" "abc"

I was using this query:

db.observations.aggregate(
   [
     { $match: {"unitId": "abc",
     { $sort: { "typeId": 1, "timestamp": 1 } },
     {
       $group:
         {
           _id: "$typeId",
           lastSalesDate: { $last: "$timestamp" }
         }
     }
])

But this retrieves only the fields of timestamp and typeId whereas I am also interested in the others, especially value and being an aggregation I am not sure I modify it to include value as well.

Upvotes: 1

Views: 393

Answers (2)

MauriRamone
MauriRamone

Reputation: 503

Try using "projection". For example:

db.observations.aggregate(
   [
     { $project: { "typeId": 1, "timestamp": 1, "value": 1 } },
     { $match: {"unitId": "abc"}},
     { $sort: { "typeId": 1, "timestamp": 1 } },
     {
       $group:
         {
           _id: "$typeId",
           lastSalesDate: { $last: "$timestamp" }
         }
     }
])

Upvotes: 0

turivishal
turivishal

Reputation: 36104

You can get full object using $$ROOT,

{
  latestRecord: { $last: "$$ROOT" }
}

Playground

You can get required fields specifying the name of the field,

{
  latestRecord: { 
    $last: {
       _id: "$_id",
       timestamp: "$timestamp",
       alue: "$value"
    } 
  }
}

Playground

Upvotes: 1

Related Questions