how to get all the documents having minimum values of a particular field using aggregation in Mongodb with corresponding java code

Question

Suppose each of my document is in the below format

{"_id" : ObjectId(""),"WordName":"foo","numberOfOccurances" :1}
{"_id" : ObjectId(""),"WordName":"bar","numberOfOccurances" :5}
{"_id" : ObjectId(""),"WordName":"abc","numberOfOccurances" :1}
{"_id" : ObjectId(""),"WordName":"pen","numberOfOccurances" :1}
{"_id" : ObjectId(""),"WordName":"box","numberOfOccurances" :5}

I need to get all documents having minimum value in the "numberofoccurrences" field.My expected output as below

{"_id" : ObjectId(""),"WordName":"foo","numberOfOccurances" :1}
{"_id" : ObjectId(""),"WordName":"abc","numberOfOccurances" :1}
{"_id" : ObjectId(""),"WordName":"pen","numberOfOccurances" :1}

I have tried several ways.Below is my code which will sort and it gives only one document with minimum value in "numberOfOccurences" field.

 Bson sort = sort(new Document("numberOfOccurances"
                + "", 1));
        Bson limit=new Document("$limit",1);
        AggregateIterable<Document> output 
    =collection.aggregate(asList(sort,limit)).allowDiskUse(true);

How can I get all the documents having minimum value in "numberOfOccurences" field using java?

Thanks in Advance

Define "minimum". If the `numberOfOccurrences` values were `{6,9,10,4,6,6,8,14}` what would you want? The question is unclear. I _think_ what youj mean is: _Find the minimum value of `numberOfOccurrences` across all documents, then return all documents where `numberOfOccurrences` matches that value._ — Jim Garrison, Aug 24 '17 at 00:28
yes Jim,I need all the documents with minimum value in **numberOfOccurences** field. — Anonymous, Aug 24 '17 at 06:45
This is "two" queries, and no other way around it. You basically do one query to determine the minimum value, which is really a simple usage of [`$min`](https://docs.mongodb.com/manual/reference/operator/aggregation/min/). Then another regular query simply matches where the value matches the returned result. The next release of MongoDB allows a "non-correlated" `$lookup` which would enable this type of matching in a single aggregation pipeline. But it's not available yet, and not even very practical without a latest build from source. — Neil Lunn, Aug 24 '17 at 07:49
Can you tell me what you mean by "two" queries?I tried several ways.But still I am stuck here. — Anonymous, Aug 24 '17 at 08:00
Thanks for the input.Neil can u please show me how to use "min" and "match" aggregation operations in java.I am not able to find any samples. — Anonymous, Aug 24 '17 at 08:16
I tried like below ` Bson groupfields=new Document("_id","$numberOfOccurances"); Bson group1=new Document("$group",groupfields); Bson min=new Document("$min","$numberOfOccurances"); AggregateIterable output =collection.aggregate(asList(group1,min)).allowDiskUse(true);` . But i m getting **Command failed with error 40324: 'Unrecognized pipeline stage name: '$min''**.How to use the $min using java? — Anonymous, Aug 24 '17 at 11:23

score 1 · Answer 1 · edited Jun 09 '22 at 03:42

I had the same requirement and I solved it.

Here is a demo Collection

[
  {
    "_id": 1,
    "item": "abc",
    "price": 10,
    "quantity": 2,
    "date": ISODate("2014-01-01T08:00:00Z")
  },
  {
    "_id": 2,
    "item": "jkl",
    "price": 20,
    "quantity": 1,
    "date": ISODate("2014-02-03T09:00:00Z")
  },
  {
    "_id": 3,
    "item": "xyz",
    "price": 5,
    "quantity": 5,
    "date": ISODate("2014-02-03T09:05:00Z")
  },
  {
    "_id": 4,
    "item": "abc",
    "price": 10,
    "quantity": 10,
    "date": ISODate("2014-02-15T08:00:00Z")
  },
  {
    "_id": 5,
    "item": "xyz",
    "price": 5,
    "quantity": 10,
    "date": ISODate("2014-02-15T09:05:00Z")
  }
]

Here is the aggregation

db.collection.aggregate([
  {
    $group: {
      _id: {},
      minPrice: {
        $min: "$price"
      },
      maxPrice: {
        $max: "$price"
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    "$addFields": {
      "data.minPrice": "$minPrice",
      "data.maxPrice": "$maxPrice"
    }
  },
  {
    "$unwind": "$data"
  },
  {
    "$replaceRoot": {
      "newRoot": "$data"
    }
  },
  {
    $match: {
      "$or": [
        {
          "$expr": {
            $eq: [
              "$price",
              "$minPrice"
            ]
          }
        },
        {
          "$expr": {
            $eq: [
              "$price",
              "$maxPrice"
            ]
          }
        }
      ]
    }
  }
])

score 0 · Answer 2 · answered Sep 01 '17 at 22:46

You can try something like below. It is not efficient as everything in the collection will be grouped.


    db.yourcollection.aggregate([{"$group":{"_id":"$numberOfOccurances","words":{"$push":"$WordName"}}},{"$sort":{"_id":1}},{"$limit":1}])

Output:



    { "_id" : 1, "words" : [ "foo", "abc", "pen" ] }

how to get all the documents having minimum values of a particular field using aggregation in Mongodb with corresponding java code

2 Answers2