I have this following query in MongoDB:
var subquery = {"shipdate" : {
"$gte" : 19960101,
"$lt" : 19960401 }
};
var eachsupp = db.lineitems.aggregate([
{ $match : subquery },
{ $project : {
"_id" : 0,
"revenue" : {$multiply : ["$extendedprice", {$subtract : [1, "$discount"] }] },
"supplier_no" : "$partsupp.supplier.suppkey",
"name" : "$partsupp.supplier.name",
"address" : "$partsupp.supplier.address",
"phone" : "$partsupp.supplier.phone" }},
{ $group : {
_id : "$supplier_no",
total_revenue : { $sum : "$revenue" },
name : {$first : "$name"},
address : {$first : "$address"},
phone : {$first : "$phone"} }}
]);
eachsupp.forEach(function(document){db.tmp.insert(document)});
db.tmp.find().sort({total_revenue : -1}).limit(1);
But for academical purposes I need this query using .group()
. Something like this
db.lineitems.group(...);
I'm starting like this but I don't know how to go with the next step
var red = function(doc, out){
out.supplier_no
out.revenue_calc = doc.extendedprice*(1-doc.discount);
};
db.lineitems.group({
key : "revenue",
cond : subquery,
initial : {revenue_calc : 0},
reduce : red,
finalize : fin
});