Mongoose nodejs - Grouping with arrays

Question

Mongoose nodejs - Grouping with arrays

I have got a collection with the following schema :

{OrderId : id, CustomerId : id, amount: Number, otherPayers : [{name : String, amount : Number}]}

I would like to make an average of the "amount" of all orders for a customerId, and I can do that with aggregate by grouping on customerId with avg on amount.

Now, I'd like the output "amount" field to be not only the avg of "amount", but also of the "amount" field of the "otherPayers" field.

I have had a look into mapreduce but I can't get it to work.

Any idea? In SQL it would have been quite simple with a subquery.

node.js
mongodb
mongoose

Answer 1

I'm guessing you want to average together all the amounts inside the otherPayers array plus the amount field on the top level of the document. Here is how you would do it with aggregation framework:

unwind = { $unwind : "$otherPayers" };
group =  { $ group : { _id : "$CustomerId", 
                       amt : { $first : "$amount" }, 
                       count : { $sum : 1 }, 
                       total : { $sum : "$otherPayers.amount" }
         } };
project = { $project : { _id : 0, 
                         CustomerId : "$_id", 
                         avgPaid : { $divide : [ 
                                       { $add : [ "$total", "$amt" ] },  
                                       { $add : [ "$count", 1 ] } 
                         ] } 
} };

db.collection.aggregate(unwind, group, project);