Max and min in mongodb

Question

I have the a collection of entries as follows:

{
  company:"C3",
  model:"M3",
  price:55600,
  discount:9,
  ...
}

and there are more than 2000 entries.

I am trying to find out max and min prices in the whole collection.

Maybe with something along the lines of:

db.collection.find({ max: { $max: "$price" }, min: { $min: "$price" } });

I'd like the output as { max: 2000, min: 5000 }.

what did u mean by *it didn't work* did you get any error message? — styvane, Oct 21 '15 at 08:26
oh i see , i was not using _id as null , it is showing the expected result thanks .. — Mohit H, Oct 21 '15 at 08:33

styvane · Accepted Answer · 2015-10-19T05:11:03.673

29

You need to use the .aggregate() method for it to work.

db.collection.aggregate([ 
    { "$group": { 
        "_id": null,
        "max": { "$max": "$price" }, 
        "min": { "$min": "$price" } 
    }}
])

The _id field is mandatory in the $group stage and to find the max/min values for price for the hole collection not for special group it needs to be set to null otherwise the query will only return max/min for each group

edited Oct 19 '15 at 05:11

answered Oct 19 '15 at 04:54

styvane

59,869
19
150
156

2

For very large collections aggregating pipeline can be very slow because it scans each document. The solution mentioned here can be better if you need to find min/max of the indexed field: https://stackoverflow.com/a/6360583/3438640 Use find with sort and limit: `db.collection.find().sort({price: 1}).limit(1)`. – gelin Sep 29 '20 at 13:26

score 2 · Answer 2 · answered Dec 04 '21 at 11:01

You can make use of a $facet stage, combined with $sort/$limit stages:

// { "price": 123 }
// { "price": 165 }
// { "price": 98  }
// { "price": 34  }
db.collection.aggregate([
  { $facet: {
    min: [{ $sort: { price:  1 } }, { $limit: 1 }],
    max: [{ $sort: { price: -1 } }, { $limit: 1 }]
  }},
  { $project: { min: { $first: "$min.price" }, max: { $first: "$max.price" } } }
])
// { "min" : 34, "max" : 165 }

The $facet stage allows us to run multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its result is stored as an array of documents.

Each field is thus produced by its own aggregation pipeline whose first stage $sorts prices in a specified order, followed by a $limit stage that'll only keep the first item (the smallest as defined by the chosen ordering).

The second part of the pipeline (the $set stage) is just there to clean-up the $facet output to the format you wished for.

Note that a $sort followed by a $limit stage is optimised by Mongo (see here). This allows the sort operation to only maintain the top n results (in our case only 1 element) as it progresses.

Also note that our $sort stages will benefit from having an index on price.

Max and min in mongodb

2 Answers2

Linked

Related