CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Andrey Yaskulsky
Andrey Yaskulsky

Reputation: 2516

Query to count number of occurrence in array grouped by day

I have the following document structure:

(trackerEventsCollection) = 
    {
        "_id" : ObjectId("5b26c4fb7c696201040c8ed1"),
        "trackerId" : ObjectId("598fc51324h51901043d76de"),
        "trackingEvents" : [ 
            {
                "type" : "checkin",
                "eventSource" : "app",
                "timestamp" : ISODate("2017-08-25T06:34:58.964Z")
            }, 
            {
                "type" : "power",
                "eventSource" : "app",
                "timestamp" : ISODate("2017-08-25T06:51:23.795Z")
            }, 
            {
                "type" : "position",
                "eventSource" : "app",
                "timestamp" : ISODate("2017-08-25T06:51:23.985Z")        
            }
        ]
    }

I would like to write a query that would count number of trackingEvents with type "type" : "power" grouped by day. This seems to be quite tricky to me because parent document does not have date and I should rely on timestamp field that belongs to the trackingEvents array members. I'm not really experienced mongodb user and couldn't understand how can this be achieved so far. Would really appreciate any help, thanks

Upvotes: 0

Views: 319

Answers (1)

mickl
mickl

Reputation: 49975

To process your nested array as a separate documents you need to use $unwind. In the next stage you can use $match to filter out by type. Then you can group by single days counting occurences. The point is that you have to build grouping key containing year, month and day like in following code:

db.trackerEvents.aggregate([
    { $unwind: "$trackingEvents" },
    { $match: { "trackingEvents.type": "power" } },
    {
        $group: {
            _id: {
                year: { $year:"$trackingEvents.timestamp" },
                month:{ $month:"$trackingEvents.timestamp" },
                day: { $dayOfMonth:"$trackingEvents.timestamp" }
            },
            count: { $sum: 1 }
        }
    }
])

Upvotes: 1

Related Questions