1

What is the difference among MongoDB's aggregation $min versus query modifier $min versus find().sort() that returns the minimum of the_field with: db.the_collection.find().sort({the_field:1}).limit(1)?

Which one should I use if there are about a few hundred calls per minute to retrieve the minimum element in a collection and work with it independently each time?

Side question: Can someone show me the correct syntax using either $min to give me the minimum of a field in a collection? db.the_collection.find().min({the_field:10}) doesn't work.

Melissa
  • 1,236
  • 5
  • 12
  • 29

2 Answers2

1

To get the document for lowest value of 'the_field' you should use db.the_collection.find().sort({the_field:1}).limit(1) So over here we are Sorting the document first and then taking the first one out of it as you can see from the query.

Aggregation $min :
It is used when we are grouping the document into a single document and we want to keep the value of the this single document as minimum of all the documents from where it was grouped.

$min operator :
It is used for inclusive lower bound for a specific index in order to constrain the results of find(). In simple words, if the_field is indexed and we want to keep some constraint on the find() then we can use it. It is generally used for improving the performance.
The syntax which you were entering was correct but it requires an Indexed field and the result will be different from what you actually want.

Ajay Gupta
  • 3,192
  • 1
  • 22
  • 30
  • Hi imagin, I have indexed my collection by the_field and used `db.the_collection.find().min({the_field:10})` but got the error: `... Sort: {}\nProj: {}\n planner returned error: unable to find relevant index for max/min query","code" : 17007` – Melissa Oct 08 '15 at 03:04
  • @Melissa : This is the query for getting the lowest value rwo for the column the_field `db.the_collection.find().sort({the_field:1}).limit(1)`. $min operator is used for performance improvement of find query when it is indexed and does not relate to getting the min row for the_field. – Ajay Gupta Oct 08 '15 at 03:07
  • Wow thanks imagin for such a fast reply! I have applied an index by the_field, so is it okay if I call the `db.the_collection.find().sort({the_field:1}).limit(1)` hundreds of times per minute? I hope the database doesn't repeatedly do the sort. – Melissa Oct 08 '15 at 03:10
  • 1
    Yes it wont create any issue. – Ajay Gupta Oct 08 '15 at 03:15
  • 1
    To increase the performance you can refer this link : http://docs.mongodb.org/manual/tutorial/optimize-query-performance-with-indexes-and-projections/ – Ajay Gupta Oct 08 '15 at 03:18
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/91686/discussion-between-imagin-and-melissa). – Ajay Gupta Oct 08 '15 at 03:21
1

Because min() requires an index on a field, and forces the query to use this index even though a better index is available to be picked up, So you must prefer the $gte or the sort operations for the query if possible. 

db.the_collection.find().sort({the_field:1}).limit(1)

So the above query is better is to use as compare the $min operation.

Refer the below mentioned link. http://docs.mongodb.org/manual/reference/operator/meta/min/#interaction-with-index-selection

While running the min operator on a no index key, It will give the following error

Error: error: {
    "$err" : "Unable to execute query: error processing query: ns=db.dbName limit=0 skip=0\nTree: status == \"approved\"\nSort: {}\nProj: {}\n planner returned error: unable to find relevant index for max/min query",
    "code" : 17007
}
Nishant
  • 3,614
  • 1
  • 20
  • 26