Mongo average aggregation query with no group

Question

I am trying to get the average of a whole field using the aggregation framework in Mongo. However i can't seem to find any example that uses it without a group parameter.

I have the following document structure:

 {
      "_id" : ObjectId("5352703b61d2739b2ea44e4d"),
      "Semana" : "2014-02-23 - 2014-03-01",
      "bolsaDeValores" : "7",
      "bvc" : "8",
      "dollar" : "76",
      "ecopetrol" : "51",
      "dollarPrice" : "18"
 }

Basically what i want to do is get the average value of the bvc field, and any other numeric one, for the whole collection in the fastest possible way (without using MapReduce as it is less efficient than the Aggregation Framework).

I have tried to group on a greater than zero basis as well but to no avail:

db.EvaluatedSentiments.aggregate([
    { "$group": { 
        "bvc" : {"$gt:0"}
        }, 
        {
            "bvc" : { "$avg" : "$bvc"}
        }
    }
])

I appreciate any help you could provide.

References: Mongo aggregation manual

You do actually realize that none of the values you present are actually numeric, don't you? They are in fact all strings. — Neil Lunn, Apr 27 '14 at 12:48
That is very true, i hadn't realized about it, an omission i made while storing the vales. Thanks — NicolasZ, Apr 27 '14 at 13:14

score 45 · Accepted Answer · edited Jun 14 '22 at 09:53

45

First of all store numerical values as numbers. Afterwards you can use a simple statement to calculate the average:

db.collection.aggregate([{ 
  "$group": {
    "_id": null, 
    "avg_bvc": { "$avg": "$bvc" } 
  } 
}])

You can simply use more $avg aggregation operators to get averages for your other numeric fields:

db.collection.aggregate([{ 
  "$group": {
    "_id": null, 
    "avg_bvc": { "$avg": "$bvc" }, 
    "avg_dollar": { "$avg": "$dollar" } 
  } 
}])

edited Jun 14 '22 at 09:53

nimrod serok

14,151
2
11
33

answered Apr 27 '14 at 12:48

Sebastian

16,813
4
49
56

5

almost missed the "_id": null. this will group it all in one! genius ! – Karan Jun 12 '17 at 13:23
I had to put brackets [ ] around query to make it work. `db.collection.aggregate([{ "$group": { "_id": null, "avg_bvc": { "$avg": "$bvc" } } }])` – Milan Feb 01 '21 at 13:25

score 7 · Answer 2 · answered Apr 27 '14 at 12:59

7

So if your data actually was numeric which is it not and your intention is to exclude the documents that have a "greater than zero" value then you include a $match statement in your aggregation pipeline in order to "filter" out these documents:

db.EvaluatedSentiments.aggregate([
    { "$match": {
        "bvc": { "$gt": 0 }
    }},
    { "$group": {
        "_id": null,
        "bvc": { "$avg": "$bvc" }
    }}
])

answered Apr 27 '14 at 12:59

Neil Lunn

148,042
36
346
317

I want 0 value to display in list but in avg i need to exclude zero.. In this situation the above didnt work.. is there any possiiblities to get it – user3211705 Dec 30 '15 at 06:58

score -1 · Answer 3 · edited Jan 17 '21 at 14:01

-1

For more details please visit the documentation.

db.EvaluatedSentiments.aggregate([
{
    $group:{_id:null,avgbvc: {$avg:"$bvc"}}
}
]).forEach(printjson)

edited Jan 17 '21 at 14:01

Glorfindel

21,988
13
81
109

answered Apr 07 '19 at 10:41

ABHISHEK GUPTA

41
1
6

2

Please, can you extend your answer with more detailed explanation? This will be very useful for understanding. Thank you! – vezunchik Apr 07 '19 at 11:10

Mongo average aggregation query with no group

3 Answers3

Linked

Related