Apply accumulator operations over a numeric value field across all documents

What are different ways to find avg/sum/min/max etc for a non array mumeric field present in documents? for example these are our documents in our collection; and we want to find avg/min/max etc for year field; What are ways to find that simplest :wink:
One I know is using $group but here you can’t group and make a array of year as there is only one field other then _id; Also there can be many instances when we really can’t apply group even some other field is present…
image
Thanks

You could $group with _id:null to get avg/min/max of the field over all documents.

no other way then using group?

Sure there is.

You download all documents in your application and code what ever computation your wish.

Another solution is to $group by year counting how many documents in each year, then sort by year, the first document is the min, the last document is the max, then you have to compute the weighted average of all documents to get the average.

None of the 2 solutions above are acceptable.

The only acceptable solution is $group with _id:null.

Is it possible to see the query you used to produce the list of documents you shared. Rejecting the $group makes me thing that may be there is something you are hiding that makes $group not working. Hence your insistence of not using $group. Another thing I notice in your output is the presence of the square brackets. They usually represents array but your specifying that these are documents with

So just to be clear do you want to work across documents or within an array of objects within documents?

Please read Formatting code and log snippets in posts before posting more documents or code. Having well formatted code and documents allows everyone to help better since we can cut-n-paste. We cannot do that with a screenshot of documents or code.

So the above screenshot I had posted to explain my problem… the actual problem was in Lab1 chapter 3; where I was trying this:

PIPELINE 1

> db.movies.aggregate([{ 
$group: { "_id": {awards: /Won \d{1,2} Oscars?/ }, "average": {$avg: "$imdb.rating"}, "standard_daviation": {$stdDevSamp: "$imdb.rating"}, "Min_rating": {$min: "$imdb.rating"}, "Max_rating":{$max: "$imdb.rating"}, "total": {$sum:1} }}])

and was expecting this should return 2 groups one which matches regex and another which dosen’t. and for both will have 4 accumulator fields as mentioned above.
BUT THIS AGGREGATION was returning only 1 GROUP and that was for all the records in the collection; Thats why was asking is there any other way to calculate min/max/avg/sum etc across documents for a numeric field.

Also tried
PIPELINE 2

    db.movies.aggregate([ 
        { 
            $addFields: {"won_oscar": { 
                "$cond": { 
                    if: {"awards": /Won \d{1,2} Oscars?/}, 
                    then: "true", 
                    else: "false"}}}}, 
        {
            $group: {"_id": "$won_oscar",
                "average": {$avg: "$imdb.rating"}, 
                "standard_daviation": {$stdDevSamp: "$imdb.rating"}, 
                "Min_rating": {$min: "$imdb.rating"}, 
                "Max_rating":{$max: "$imdb.rating"}, 
                "total": {$sum:1}}}])

Pipeline2 also grouping all docs in 1 group why? I’m not sure…

But finally got the solution for this problem i.e filter out non Oscar then apply $group with id:null.

I think it is the use of the /…/ short-cut syntax that does not work in this context.

In cases like this you would need to use the complete syntax of https://www.mongodb.com/docs/manual/reference/operator/aggregation/regexMatch/.

1 Like