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

Answer 1

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" ] }

Answer 2

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"
            ]
          }
        }
      ]
    }
  }
])