Which is the best way to find min and max in same Mongodb query

Question

sorry to bother you, but I am not able to find a working solution to my question. I want to make a MongoDB query that give me the same results from an SQL query: SELECT MIN(ZIP) AS MIN, MAX(ZIP) AS MAX FROM table WHERE BUSINESS_STATE_TERRITORY='Arizona' First of all I'm querying the database from java using the mongodb java driver 3.6version. I tried this solution from How to find min value in mongodb

db.sales.aggregate(
[
  {
    $group:
    {
      _id: "$item",
      minQuantity: { $min: "$quantity" }
    }
  }
]
)

In java:

Document min = new Document("$min","$ZIP");
Document max = new Document("$max","$ZIP");
Document group = new Document("$group", new Document("_id","$Business_State_Territory").append("minimum",min).append("maximum",max));
Document match = new Document("$match", new Document("Business_State_Territory","Arizona"));
AggregateIterable output = coll.aggregate(Arrays.asList(match,group));
MongoCursor<Document> cur = output.iterator();
    while(cur.hasNext()){
        Document next = cur.next();
        System.out.println(next.toString());
    }

the result is:

Document{{_id=Arizona, minimum=1608, maximum=null}}

But it isn't evaluating the max value, that is 99701.

All the documents in the collections follow this structure:

_id:5ad0aea8471aa1aa7f425885
NPI:1003000142
CCN:""
Provider_Type:"EP"
Business_State_Territory:"Ohio"
ZIP:43623
Specialty:"Pain Medicine"
Hospital_Type:""
Program_Type:"Medicare"
Program_Year:2016
Provider_Stage_Number:"Stage 2"
Payment_Year:3
Attestation_Month:2
Attestation_Year:2017
MU_Definition_2014:""
Stage_2_Scheduled_2014:0
EHR_Certification_Number:"1314E01QPVPIEAN"
EHR_Product_CHP_Id:"CHP-027887"
Vendor_Name:"Epic Systems Corporation"
EHR_Product_Name:"EpicCare Ambulatory 2014 Certified EHR Suite"
EHR_Product_Version:"Epic 2015"
Product_Classification:"Complete EHR"
Product_Setting:"Ambulatory"
Product_Certification_Edition_Yr:2014

I hope you can help me out. Start editing: I tried to use $project instead of $group with only the max clause. Now the result documents are in a structure of:

Document{{_id=5ad0aea8471aa1aa7f425955, Business_State_Territory=Arizona, maximum=85255}}

As you can see now the maximum value is not empty but the values are not representing the maximum value. The odd thing is also that some documents are:

Document{{_id=5ad0aea9471aa1aa7f426881, Business_State_Territory=Arizona, maximum=}}

Can it be that mongodb interprets this empty value as tha maximum value? From docs.mongodb.com it says: If some, but not all, documents for the $max operation have either a null value for the field or are missing the field, the $max operator only considers the non-null and the non-missing values for the field. So the answer must be no, the empty value is not taken for the result of the query. Is this a bug?

Answer 1

You're doing it the right way, using an aggregation. You don't need to do mapReduce. You need to make a correction though:

When you use the $group operator, you need to refer to any document fields with a leading $, like in the example:

{ $group: {_id: "$item", minQuantity: { $min: "$quantity" } }

In your example, try adding the leading $ to the field name Business_State_Territory, like this:

Document group = new Document("$group", new Document("_id","$Business_State_Territory").append("minimum",min).append("maximum",max));