[Aggregation] Total sum of a field per month for each user

[Nkansah Rexford]

Hello all. First of all, this is my first post here. I'm new to mongodb (less than two weeks old). 

Not everything makes sense at the moment, but still learning my way.

Its about Aggregation

What I want to do?:

• group collection into months
• find total sum of specific fields for the month for a user
• (optional) Project the computations

What I have (sample collection):

What I have done:

// Projects the sum of the fields where 
// I can access in template
$group: {
        '_id': this.userId,
        'hours': {
            $sum: '$hours'
        }
    }
}, {
    $project: {
        hours: '$hours',
    }

After going through the mongDB-manual a bit, I came accros this:

db.users.aggregate(
[
{ $project : { month : { $month : "$createdAt" } } } ,
{ $group : { _id : {month:"$month"} , number : { $sum : 1 } } },
{ $sort : { "_id.month" : 1 } }
]
)

With this approach in mind (using the $month operator), I tried to apply the above approach on the 'hours' field, but I couldn't get the result.

[{

        $project: {

            month_report: {

                $month: "$createdAt"

            }

        }

    }, {

        $group: {

            _id: {

                month_report: "$month_report"

            },

            number: {

                $sum: "$hours" //this part, how do I properly reference my 'hours' field?

            }

        }

    }]

The above snippet returns 'Number: 0'

Am I on the right path? 

What piece am I missing?

How do I return total sum of hours per month for each user in context?

thanks

[Dwight Merriman]

the projection will result in no hours being passed along to the next stage of the pipeilne.

so try someting like 

{ $project : { month_report: "$createdAt" , hours : 1 } }

for the first stage.

also for debugging pipelines, it's really useful to just run a portion of the pipeline and look at the output.  for example you coudl run: 

> db.foo.aggregate( [ { $project : { month_report: "$createdAt" } } ] )

and you will see there is no hours value in the output -- and know what to adjust then.

[Asya Kamsky]

You can leave out the $project stage entirely and just use the expression in the $group:

{ $group: {

            _id: { $month: "$createdAt" }, 

            number: { $sum: "$hours" }

        }

}

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/d243e682-314a-42af-aa11-87df233463a0%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
Free MongoDB Monitoring - cloud.mongodb.com
Free Online Education - university.mongodb.com
Get Involved - mongodb.org/community
We're Hiring! - https://www.mongodb.com/careers

[Nkansah Rexford]

Thank you. Your help worked.

[Nkansah Rexford]

The only part missing at the moment is getting the monthly summation specific to the user. Meteor allows accessing current logged in user as `this.userId`.

Where will getting the user context for in the grouping?

[Asya Kamsky]

If you mean that you want to only produce this aggregation for specific user id then you can add a $match stage in front which matches userId field to current user id. 

On Thursday, December 31, 2015, Nkansah Rexford <seanm...@gmail.com> wrote:

The only part missing at the moment is getting the monthly summation specific to the user. Meteor allows accessing current logged in user as `this.userId`.

Where will getting the user context for in the grouping?

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/d6f48c1a-5408-4487-a1c1-850fc7d5c322%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Nkansah Rexford]

The $match did the trick by running the aggregation on a specific user's data.  However, I store the results of the pipeline in another collection, and to be able to retrieve them and correspond the aggregation to which user, I needed to append the userId also to the output of the pipeline.

I tried to end the stages with the $project operator. I read $project operator has the power to create new fields on the fly.

        $project: {

            _id: 0,

            userId: this.userId,

            month: '$_id',

            hours: '$hours'

        }

and I got this error in server terminal:

Exception from sub reportMonthly id p5D4Qqq6kKZhcpqzc MongoError: exception: FieldPath 'n74SEjEBHjAoSDuqG' doesn't start with $

I figured it out that I was trying to cheat which wasn't accepted. The $literal operator was the right way.

I did 

        $project: {

            _id: 0,

            userId: {
                $literal: this.userId
            },

            month: '$_id',

            hours: '$hours'

        }

Solved it for me. At least its working, unless its bad practice to do so!

Just posting, might help someone out there.