Aggregate to get average from document and of array elements

Question

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

Neil Lunn · Accepted Answer · 2015-03-11 01:17:56Z

1

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.

answered Mar 11, 2015 at 1:17

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1144446 Over a year ago

thanks a lot, that's exactly right. I'm still not 100% sure how this works but thanks for the solution, I'll study it more closely

Neil Lunn Over a year ago

@user1144446 Take some more time to study .aggregate() and read all the documentation. What works best for me (still) is to execute the pipeline while adding "one stage at a time" and inspecting the results at every stage. It's the best way to visualize what is happening and the best way to learn.

Collectives™ on Stack Overflow

Aggregate to get average from document and of array elements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related