CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-frameworkaggregate
Eduardo G.R.
Eduardo G.R.

Reputation: 737

MongoDB aggregation: Average of acumulated values possible?

I'd like to know if I can do an $avg of the sum of different document values. My document structure is the following:

    time: { type: Number, required: true },
    date: { type: Date, required: true },
    reg: {
      hi: { type: Number, default: 0 },
      mid: { type: Number, default: 0 },
      low: { type: Number, default: 0 },
      null: { type: Number, default: 0 }
    },
    area: { type: mongoose.Schema.Types.ObjectId, ref: 'Area', required: true }

And this are three example documents in the collection:

{
    "_id" : ObjectId("5a2c64237a651d3cc2dc2da1"),
    "time" : 1510193369104,
    "date" : ISODate("2017-11-09T02:09:29.104Z"),
    "area" : ObjectId("5a236b2309391a0febfac0c9"),
    "reg" : {
        "hi" : 59,
        "mid" : 35,
        "low" : 1573,
        "null" : 4733
    }
}
{
    "_id" : ObjectId("5a2c64237a651d3cc2dc2da5"),
    "time" : 1510193369104,
    "date" : ISODate("2017-11-09T02:09:29.104Z"),
    "area" : ObjectId("5a236b2309391a0febfac0a9"),
    "reg" : {
        "hi" : 0,
        "mid" : 0,
        "low" : 2018,
        "null" : 4382
    }
}
{
    "_id" : ObjectId("5a2c64247a651d3cc2dc2df4"),
    "time" : 1510193369104,
    "date" : ISODate("2017-11-09T02:09:29.104Z"),
    "area" : ObjectId("5a236b2309391a0febfac11b"),
    "reg" : {
        "hi" : 57,
        "mid" : 235,
        "low" : 1909,
        "null" : 4199
    }
}

For each document I need to multiply: "reg.hi" by 3, "reg.mid" by 2 and "reg.low" by 1. After that, sum up the three results and get the average.

I don't know if this can be done in an aggregation query, I have tryed in many ways with no success.

This is the one that, at least, threw out a valid number as a result (note I'm filtering documents with $match, but that shouldn't bother).

Log.aggregate([{
          $group: {
            _id: { area: "$area" },
            avg: {
              $avg: {
                $sum: { 
                   $multiply: ["$reg.low", 1], 
                   $multiply: ["$reg.mid", 2], 
                   $multiply: ["$reg.hi", 3] 
                }
              }
            }
          }
        }
    ])

Thanks in advance!

Upvotes: 0

Views: 359

Answers (2)

s7vr
s7vr

Reputation: 75964

You have operators paired incorrectly. Use $sum variant which takes list of operand expressions. Each operand is a document.

Try this

Log.aggregate([{"$group":{
  "_id":{"area":"$area"},
  "avg":{
    "$avg":{
      "$sum":[
        {"$multiply":["$reg.low",1]},
        {"$multiply":["$reg.mid",2]},
        {"$multiply":["$reg.hi",3]}
       ]
    }
  }
}}])

Upvotes: 1

Buzz Moschetti
Buzz Moschetti

Reputation: 7588

Well, if for EACH document you want to do the multiply and average, then simply use $addFields :

db.foo.aggregate([
  {$addFields: { theAverage: {$avg: [
      {$multiply: ["$reg.low", 1]}
     ,{$multiply: ["$reg.mid", 2]}
     ,{$multiply: ["$reg.hi", 3]}
                        ]}
  }}
]);

Upvotes: 1

Related Questions