Group by subdocument field using aggregation framework

aggregation-framework, mongodb

Solution

The following query:

db.book.aggregate([
    { $unwind: '$bookings' },
    { $project: { bookings: 1, rating: 1, month: { $month: '$bookings.startDate' } } },
    { $group: { _id: { rating: '$rating', month: '$month' }, count: { $sum: 1 } } }
]);

Will give you the result per rating/month, but it does not make a subdocument for months. In general, you can not convert a value (such as the month nr) to a key (such as month1)—this is something you can probably quite easily handle in your application though.

The above aggregation results in:

"result" : [
    {
        "_id" : {
            "rating" : 2,
            "month" : 1
        },
        "count" : 1
    },
    {
        "_id" : {
            "rating" : 2,
            "month" : 12
        },
        "count" : 1
    }
],
"ok" : 1

Problem

The structure is the following: ``` { "_id" : "79f00e2f-5ff6-42e9-a341-3d50410168de", "bookings" : [ { "name" : "name1", "email" : "george_bush@gov.us", "startDate" : ISODate("2013-12-31T22:00:00Z"), "endDate" : ISODate("2014-01-09T22:00:00Z") }, { "name" : "name2", "email" : "george_bush@gov.us", "startDate" : ISODate("2014-01-19T22:00:00Z"), "endDate" : ISODate("2014-01-24T22:00:00Z") } ], "name" : "Hotel0", "price" : 0, "rating" : 2 } ``` Now, I want to generate a report telling me how many bookings were made, grouped by booking month (assume that only booking start date matters) and also grouped by hotels rating. I expect the answer to be like that: ``` { { rating: 0, counts: { month1: 10, month2: 20, ... month12: 7 } } { rating: 1, counts: { month1: 5, month2: 8, ... month12: 9 } } ... { rating: 6, counts: { month1: 22, month2: 23, ... month12: 24 } } } ``` I tried this with aggregation framework but I'm a little bit stuck.

Original source