mongodb aggregate average of array elements

Question

I have collection of documents in mongodb 2.6.11 where 'cpu' is array showing [cpu0,cpu1], see example below

{ cpu: [ '100','20' ], hostname: 'host1',_id: 1 },
{ cpu: [ '40','30' ], hostname: 'host1',_id: 2 }, etc

I'm looking for average cpu on 'host1' which is ['70','25'] ( because '100'+'40'/2='70' and '20'+'30'='25' ). I am trying aggregate for cpu1 but it is not giving me right result

db.collection.aggregate(
    [
        {
            $group:
            {
                _id:"hostname",
                avgCPU: {$avg: "$cpu.1"}
            }
        }
    ]
);

Orejano · Accepted Answer · 2016-02-05 16:47:52Z

2

In MongoDB 2.6 positional notation in aggregation is unsupported, you will need to $unwind your document, and on the next stage perform the $avg.

In MondoDB 3.2 however, you have the $arrayElemAt operator which return the element at specified index { $arrayElemAt: [ <array>, <idx> ] }

Based on the document structure you posted, this query will do the work:

db.cpu.aggregate([
{$unwind: "$cpu"},
{$group: {
   _id: "$_id",
   first: {$first: "$cpu"},
   last: {$last: "$cpu"}
}},
{$group: {
   _id: 0,
   firstAvg: {$avg: "$first"},
   lastAvg: {$avg: "$last"}
}}
]);

edited Feb 5, 2016 at 16:47

answered Feb 5, 2016 at 14:23

Orejano

1,8242 gold badges16 silver badges16 bronze badges

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

4 Comments

irom Over a year ago

sorry, _id are different , I just wanted to simplify

irom Over a year ago

So like below ? db.collection.aggregate( [ { $unwind: { path: "$cpu", includeArrayIndex: "VS" } }, {$match: {"VS":{"$eq":1}}}, { $group: { _id: "hostname", avgCPU: {$avg: "$cpu"} } } ] );

Orejano Over a year ago

I have update my answer with a query that will get the result that you need

irom Over a year ago

Appreciate, print(JSON.stringify(db.cpu.aggregate(..)) gives me {"_firstBatch":[{"_id":0,"firstAvg":0,"lastAvg":0}],"_cursor":{}} ?

Collectives™ on Stack Overflow

mongodb aggregate average of array elements

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related