[SERVER-10337] MIN, MAX queries (non-aggregation) Created: 25/Jul/13  Updated: 25/Jun/15  Resolved: 28/May/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Ken Williams Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

If I have a field string like "_id.foo.bar", currently I think there are three ways to find its min/max (Java driver shown here):

Object min = ((BasicDBObject) ((BasicDBObject) myColl.find()
  .sort(new BasicDBObject("_id.foo.bar", 1))
  .limit(1).next().get("_id")).get("foo")).get("bar");
Object min = ((BasicDBObject) ((BasicDBObject) myColl.find()
  .sort(new BasicDBObject("_id.foo.bar",-1))
  .limit(1).next().get("_id")).get("foo")).get("bar");

or

Object min = ((BasicDBObject) ((BasicDBObject) myColl.aggregate(
  new BasicDBObject("$sort", new BasicDBObject("_id.foo.bar", 1)),
  new BasicDBObject("$limit", 1))
  .results().iterator().next().get("_id")).get("foo")).get("bar");
Object max = ((BasicDBObject) ((BasicDBObject) myColl.aggregate(
  new BasicDBObject("$sort", new BasicDBObject("_id.foo.bar",-1)),
  new BasicDBObject("$limit", 1))
  .results().iterator().next().get("_id")).get("foo")).get("bar");

or

Object min = myColl.aggregate(new BasicDBObject("$group", 
  new BasicDBObject("_id", null)
  .append("minVal", new BasicDBObject("$min", "$_id.foo.bar"))))
  .results().iterator().next().get("minVal");
Object max = myColl.aggregate(new BasicDBObject("$group",
  new BasicDBObject("_id", null)
  .append("maxVal", new BasicDBObject("$max", "$_id.foo.bar"))))
  .results().iterator().next().get("maxVal");

That's a lot of syntactical machinery for a simple concept. And the latter aggregation approach doesn't take advantage of any indexes.

What relief is there for this, even if only syntactic sugar for the above happening behind the scenes?

The distinct() method would seem to provide a template for this:

Object min = myColl.min("_id.foo.bar");
Object max = myColl.max("_id.foo.bar");
// or with a query:
Object min = myColl.min("_id.foo.bar", new BasicDBObject("otherField", 6));
Object max = myColl.max("_id.foo.bar", new BasicDBObject("otherField", 6));



 Comments   
Comment by Ken Williams [ 29/May/15 ]

Bummer.

Comment by David Storch [ 28/May/15 ]

The correct way to get the highest/lowest value of a field in a collection is to deliver a query with a ascending/descending sort on that field with a limit of 1. We do not plan to add syntactic sugar for such a query.

Comment by Stennie Steneker (Inactive) [ 13/Aug/13 ]

Hi Ken,

Thanks for clarifying; does seem like this wants new operators and/or driver helpers to simplify your example usage.

Regards,
Stephen

Comment by Ken Williams [ 13/Aug/13 ]

No, I don't think that applies here - the use case is to find out what the minimum value is (possibly subject to some other constraint), whereas the $min and $max operators constrain the search to a given min or max boundary.

Comment by Stennie Steneker (Inactive) [ 13/Aug/13 ]

Hi Ken,

There are $min and $max operators you can use to specific lower and upper bounds of an indexed find().

Would those work for your use case?

Regards,
Stephen

Generated at Thu Feb 08 03:22:53 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.