Mongodb WeekofMonth?

Question

I am stuck with achieving weekofMonth instead of WeekofYear. Can somebody guide me on how to get this right?

db.activity.aggregate([
    {
        $group:{
            _id: {
                week: { $week: "$createdAt" }, 
                month: { $month: "$createdAt" },      
                year: { $year: "$createdAt" } 
            },
            count: { $sum: 1 }
        }
     },
     { $match : { "_id.year" : 2016, "_id.month" : 5 } }
])

Output

/* 1 */
{
    "_id" : {
        "week" : 19,
        "month" : 5,
        "year" : 2016
    },
    "count" : 133.0
}

/* 2 */
{
    "_id" : {
        "week" : 18,
        "month" : 5,
        "year" : 2016
    },
    "count" : 1.0
}

In the above shown data, it is actually not displaying weekofMonth. How can I get this given week 18 is the first week of Month?

joao · Answer 1 · 2016-05-11T17:56:53.903

5

The $week operator gives you the week of year as described in the docs.

The week of month can be calculated by getting the day of month and dividing by 7.

db.activity.aggregate([
    {$project: {
        "year": {$year: "$createdAt"},
        "month": {$month: "$createdAt"},
        "weekOfMonth": {$floor: {$divide: [{$dayOfMonth: "$createdAt"}, 7]}}
    }},
    {$group: {
        "_id": {"year": "$year", "month": "$month", "weekOfMonth": "$weekOfMonth"},
        count: { $sum: 1 }
    }},
    {$match : { "_id.year" : 2016, "_id.month" : 5}}
])

Note that the week of month here is 0 based. If you want it to start at 1 just $add 1. Also, the $floor operator is new in version 3.2.

Edit

You can simulate the floor using $mod (which exists in version 3.0)

"weekOfMonth": {$subtract: [{$divide: [{$dayOfMonth: "$createdAt"}, 7]}, {$mod: [{$divide: [{$dayOfMonth: "$createdAt"}, 7]}, 1]}]},

edited May 11 '16 at 17:56

answered May 11 '16 at 13:42

joao

4,067
3
33
37

Answer is true, but my server is deployed with version 3.0.2, doing at application Level, thanks for quick response another question inside $match i need to pass few more query params which is like this. childid: req.params.childid, word:{$in:["BAT","CAT"]} after passing this i am not getting any output and error even. @joao – Ayyappa A May 11 '16 at 14:13
@user1099855 after the $project stage those fields disappear. You either also add them to the $project or do a $match at the very beginning (which is recommended because of index usage). – joao May 11 '16 at 14:18
@user1099855 check my edit, you can use $mod (works with version 3.0) instead of $floor to achieve the same result. – joao May 11 '16 at 17:58

Mongodb WeekofMonth?

1 Answers1