Improving Query Performance for Conditional Summation in groupby

[steevej (Steeve Juneau)]

What works sometimes is to $group with _id only and do any computation in a $lookup pipeline.

Something like:

{ $group : {
    _id : "$userId"
} } ,
{ $lookup : {
    from : "collection_name" ,
    localField : "_id" ,
    foreignField : "userId" ,
    as : "result" ,
    pipeline : [
        { $group : {
            _id : null ,
            totalMonthly : ... ,
            ... ,
            subscriptionId : ...
        } }
    ] 
} }

The theory, is that the first empty $group uses a lot less memory. While the computations are the same in the inner $group it is performed on a smaller set of documents and can be sent to the next stage faster.

But sorting on a computed value like totalMonthly will always be kind of slow since no index can be used. Sometimes the computed pattern is your best friend.