CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
user1144446
user1144446

Reputation: 571

Aggregate to get average from document and of array elements

I've got this array of these objects:

item1 = {
  name:'item',
  val:1,
  list:[
    {type:'a',value:1},
    {type:'b',value:1},
    {type:'c',value:1}
  ]
};

item2 = {
  name:'item',
  val:5,
  list:[
    {type:'a',value:3},
    {type:'b',value:99},
    {type:'c',value:1}
  ]
};

They all have the same array, same types 'a', 'b' & 'c', but different values.

How can I get the average value of type 'a', 'b' & 'c'?

How can I get the average value of all items?

I'm expecting

itemAvg = {
  name:'item',
  val:3,
  list:[
    {type:'a',value:2},
    {type:'b',value:50},
    {type:'c',value:1}
  ]
};

I thought grouping first the val by name and pushing list. Then unwinding list. Then grouping the list by types.

But this doesn't work

model.aggregate([
{ $match : <condition> },
{ $group : {
  _id:{name:'$name'},
  ValAvg:{$avg:'$val'}
  List:{$push:'list'}
}},
{ $unwind:'$List'},
{ $group:{                
  _id:{type:'$List.type',
  ValueAvg:{$avg:'$List.value'}
}}
])

I was hoping the last group after the unwind would group by tune type and calculate the average of value for each distinct type... but no... I get ValueAvg=0

Thanks

Upvotes: 2

Views: 2307

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151092

You need two $unwind stages since you pushed arrays inside an array and then follow up with two $group stages:

model.aggregate([
    { "$match": { <condition> }},
    { "$group": {
        "_id": "name",
        "val": { "$avg": "$val" },
        "list": { "$push": "$list" }
    }},
    { "$unwind": "$list" },
    { "$unwind": "$list" },
    { "$group": {
        "_id": { "name": "$_id", "type": "$list.type" },
        "val": { "$avg": "$val" },
        "valAvg": { "$avg": "$list.value" }
    }},
    { "$sort": { "_id": 1 } },
    { "$group": {
        "_id": "$_id.name",
        "val": { "$avg": "$val" },
        "list": { "$push": {
           "type": "$_id.type",
           "value": "$valAvg"
        }}
    }}
])

So by grouping at the "type" level first the obtained results can get the averages across the elements, then the original form is reconstructed. Note the $sort to retain the order of elements, otherwise they will be reversed:

{
    "_id" : "name",
    "val" : 3,
    "list" : [
            {
                    "type" : "a",
                    "value" : 2
            },
            {
                    "type" : "b",
                    "value" : 50
            },
            {
                    "type" : "c",
                    "value" : 1
            }
    ]
}

If you are tempted to $unwind first to avoid putting arrays inside arrays then don't do that. The averages you seek outside the array will be affected by the number of elements in the array when unwound. So arrays with more elements in one document to another would "weight" their value more highly in determining the average there.

Upvotes: 1

Related Questions