MongoDB Aggregate multiple count and latest date

Viewed 492

I'm trying get a Mongo 3.0 query that is beyond my depth. Was hoping for a bit of help. Basically, my database has transcription records whereby there is a given username, project_id, expedition_id and finished_date. Those are the fields I'm interested in. A project will have multiple expeditions, each expedition multiple transcriptions.

I would like to display information for a given user in a stats page for a given project. The display would be User Name, Total Project Transcriptions that user submitted for the whole project, Total Participated Expeditions the number of expeditions the user participated in across the project, and the last date the user actually performed a transcription.

So far, it's easy enough to get the Total Project Transcriptions by using the count on the user_name and matching the project_id

db.transcriptions.aggregate([
{ "$match" : {"projectId" => 13}},
{ "$group": {
        "_id": "$user_name",
        "transcriptionCount" : {"$sum" : 1 } 
    }
}
])

Each transcription document has an expeditionId field (4, 7, 9, 10, etc.) and the finished_date. So if a user performed 100 transcriptions, only participating in expedition 7 and 10, the Total Participated Expeditions would = 2 The last finished_date being a date showing the last time a user performed a transcription. Example of returned record:

user_name: john smith
transcriptionCount: 100
expeditionCount: 2
last_date: 2017-08-15

Hope I explained that well enough. Would appreciate any help.

1 Answers
Related