Average array of arrays MongoDB

Question

I have this collection in MongoDB

{
   {"values" : [1,2,3,4,5,6]},
   {"values" : [7,8,9,10,11,12]},
   {"values" : [13,14,15,16,17,18]}
}

How I can aggregate and take a array with average by indexes?

Like this:

{ "average" : [7,8,9,9.66,10.66,12] }

Note: average[0] = (1 + 7 + 13) / 3

Regards,

Daniele Tassone · Accepted Answer · 2017-08-29 07:25:31Z

1

You can use Aggregation Framework and $avg.

$avg can be used in $project or $group.

https://docs.mongodb.com/manual/reference/operator/aggregation/avg/

With a single expression as its operand, if the expression resolves to an array, $avg traverses into the array to operate on the numerical elements of the array to return a single value. With a list of expressions as its operand, if any of the expressions resolves to an array, $avg does not traverse into the array but instead treats the array as a non-numerical value.

UPDATE #2: since the problem is now more clear, i will update my answer.

db.stackoverflow027.aggregate([
{
  $match: {
    "message.testnr":"1111"
  }
},
{
  $unwind: {
    path: "$message.content.deflection",
    includeArrayIndex: "position"    
  }
},
{
  $group: {
    _id: "$position",
    averageForIndex: {$avg: "$message.content.deflection"}/*,
    debug_totalIndexInvolvedInTheAverage: {$sum: 1},
    debug_valueInvolvedInTheAverage: {$push: "$message.content.deflection"},
    debug_documentInvolvedInTheAverage: {$push: "$$ROOT"}*/
  }
},
{
  $sort: {_id:1}
},
{
  $group: {
    _id: null,
    average: {$push: "$averageForIndex"}
  }
}
], { allowDiskUse: true });

That will give you this output:

{ 
    "_id" : null, 
    "average" : [
        6.0, 
        7.0, 
        8.0, 
        9.0, 
        10.0
    ]
}

I also added { allowDiskUse: true } in order to avoid memory limitations (check the link to have more informations). Hope now your problem is solved.

You can see some "debug_" property in order to give you the opportunity to figure out what really happen at $group iteration. But you can remove this property in product environmental.

edited Aug 29, 2017 at 7:25

answered Aug 21, 2017 at 19:31

Daniele Tassone

2,1942 gold badges18 silver badges26 bronze badges

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

11 Comments

Jorge Rosa Over a year ago

Thanks! I know about aggregation and $avg but is not direct way to calculate the average. For example, average between 1, 7 and 13.....2, 8 and 14. Finally the result is a array with averages Do you understand my problem?

Daniele Tassone Over a year ago

if is possibile to write a js function to resolve your issue, then you can use MapReduce and load your function directly into the script. Maybe you should also consider to update the original question. LINK: docs.mongodb.com/manual/core/map-reduce

Jorge Rosa Over a year ago

I would like a query that can result a array of averages. Isn't possible? I consider that MapReduce cannot solve my problem. Otherwise, isn't possible sum each element on same array position and return a array of sums? For example: [1,2,3] + [1,1,1] = [2,3,4] Note: I tried average: {"$avg" : { $arrayElemAt: [ "$values", 1 ] }}, but I don't want repeat this 6 times (LOL) in my query.

Jorge Rosa Over a year ago

this is my real query: db.test_data.find({"message.testnr" : "1111"}).pretty() and result:

{ 	"_id" : ObjectId("..."), 	"message" : { 		"testnr" : "1111", 		"content" : { 			"deflection" : [1,2,3,4,5] 		} 	} } { 	"_id" : ObjectId("..."), 	"message" : { 		"testnr" : "1111", 		"content" : { 			"deflection" : [6,7,8,9,10] 		} 	} } { 	"_id" : ObjectId("..."), 	"message" : { 		"testnr" : "1111", 		"content" : { 			"deflection" : [11,12,13,14,15] 		} 	} }

Jorge Rosa Over a year ago

Yes can happen 100 rows or more.

|

Collectives™ on Stack Overflow

Average array of arrays MongoDB

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related