CODEWITHSUNDEEP
node.jsmongodbaggregation-framework
RayKahn
RayKahn

Reputation: 125

mongoDB Aggregate By Date and another field

I am trying to run an aggregation by a date field and offer id field. I have the following code which produces the sum of all offers viewed for each month.

db.useroffers.aggregate(

    {$project: {date: {
        month: {$month: "$viewdate"},
        year: {$year: "$viewdate"},
    }}},
    {$limit: {offer_id: {$in: [101,102,103,104,105,106]}}},
    {$group: {_id: {offer_id: "$offer_id", date: "$date"}, views: {$sum: 1}}}
)

However, I need to group by $month-$year and show the sum of each offer viewed to the limited offer_ids.

Upvotes: 0

Views: 1680

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151172

The question would be better with a sample document, however it mostly seems you have some of your terms mixed around.

Firstly $project is for reshaping of the document in the pipeline. While you have changed the structure of the date to what you want, you have omitted the other fields that you want to use later in other pipeline phases. The only other field that is implicitly included is _id. So you need to explicitly declare the fields that you want.

Secondly $limit takes a single numeric argument only, representing the number of documents in the collection to keep. It generally only makes sense to use this if you are testing a very large source and want a subset until your whole pipeline is complete, or after sorting a final result where you just want to return a certain number of entries.

For the sort of selection you are trying to do here, you need the $match operator, which you can use in the way you are attempting to match a set of offer_id values.

db.useroffers.aggregate(
    // Match first
    {$match: {offer_id: {$in: [101,102,103,104,105,106]}}},

    // Change the document. You could also do this in group
    {$project: {
        date: {
            month: {$month: "$viewdate"},
            year: {$year: "$viewdate"},
        },
        offer_id: 1,
        views: 1
    }},

    // Group by "date" and "offer_id"
    {$group: {
        _id: { date: "$date", offer_id: "$offer_id"},
        views: {$sum: 1}
    }},

    // Nicer output
    {$project: {
        _id: 0,
        date: {$concat: [
            {$substr: ["$_id.date.month", 0, 2]},
            "-",
            {$substr: ["$_id.date.year", 0, 4]}
        ]},
        offer_id: "$_id.offer_id,
        views: 1
    }}
)

Upvotes: 2

Related Questions