Reputation: 189
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
Reputation: 36154
$unwind
deconstruct activities
array$sort
by dateOfActivity
in descending order$group
by _id
and get first activity required fieldsdb.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"
}
}
}
}
}
])
Upvotes: 1