CODEWITHSUNDEEP
mongodbmongodb-query
Slow Snail
Slow Snail

Reputation: 189

Mongo query how to retrieve the latest inserted array value?

I have a mongodb collection which contains some array values such as ActivityType, Note and ActivityDate. The array name is called activities. I need to rename some fields so I used aggregate and $project to rename some columns for the output. But I only need to return the latest inserted ActivityDate for the array value.

My current query returns all the array value in the Activity array:

db.test.aggregate([
   {$match: {}
},   {$unwind: "$activities"},
        {$match: {}},



{ "$project": {
         "_id" : 0,
      "Project Number": "$ProjectNumber" ,
      "Activity Type": "$activities.activityTypeDesc" ,
      "Date of Activity": { 
                "$dateToString": { "format": "%Y-%m-%d", "date": "$activities.dateOfActivity" } 
            }
    }}
   ])

It is sort of like getting the top 1 order by in sql server. How do I do that in Mongodb? After some reading seems like I need to use $sort and $group, but I don't know how to fit in here.

I have some sample data below:

{
    "_id" : ObjectId("5fd289a93f7cf02c36837ca7"),
    "ProjectNumber" : "ABC1234567",
    "activities" : [ 
        {
            "activityTypeDesc" : "Type1",
            "dateOfActivity" : ISODate("2021-02-20T06:00:00.000Z"),
            "activityNote" : ""
        }, 
        {
            "activityTypeDesc" : "Type2",
            "dateOfActivity" : ISODate("2021-03-04T06:00:00.000Z"),
            "activityNote" : ""
        }, 
        {
            "activityTypeDesc" : "Type3",
            "dateOfActivity" : ISODate("2021-01-04T06:00:00.000Z"),
            "activityNote" : ""
        }, 
        {
            "activityTypeDesc" : "Type4",
            "dateOfActivity" : ISODate("2021-04-15T05:00:00.000Z"),
            "activityNote" : ""
        }
    ]
}

{
    "_id" : ObjectId("5fd2ca65d1a01d157c0179be"),
    "ProjectNumber" : "12345",
    "activities" : []
}

The result of the query should return two rows, one with the lastest activitydate , one with no activitydate (as no array value) Any help will be appreciated!

Upvotes: 1

Views: 146

Answers (1)

turivishal
turivishal

Reputation: 36154

  • $unwind deconstruct activities array
  • $sort by dateOfActivity in descending order
  • $group by _id and get first activity required fields
db.collection.aggregate([
  {
    $unwind: {
      path: "$activities",
      preserveNullAndEmptyArrays: true
    }
  },
  { $sort: { "activities.dateOfActivity": -1 } },
  {
    $group: {
      _id: "$_id",
      "Project Number": { $first: "$ProjectNumber" },
      "Activity Type": { $first: "$activities.activityTypeDesc" },
      "Date Of Activity": {
        $first: {
          "$dateToString": {
            "format": "%Y-%m-%d",
            "date": "$activities.dateOfActivity"
          }
        }
      }
    }  
  }
])

Playground

Upvotes: 1

Related Questions