A collection called bookstore is as below (sample 2 documents):

[{
"bookname": "hi",
"orders" : [
    {"rate" : 1},
    {"rate" : 2}
 ]
},

{
"bookname":"hello",
"orders" : [
    {"rate" : 3},
    {"rate" : 2},
    {"rate" : 5}
  ]
}]

I want to return documents where the minimum rate value from orders array is equal to value number 2.

Output: Entire document with all fields where bookname:hello as the minimum rate value in orders array is 2 and not the document with bookname:hi as the minimum rate is 1 there.

How to I achieve this with mongo db query?

I tried this:

db.collection.aggregate([
      {
        $unwind: "$orders"
      },
      {
        $group: {
          _id: "$_id",
          minRateDoc: {
            $min: "$orders.rate"
          }
        }
      },
      {
        "$match": {
          "minRateDoc": 2
        }
      }
    ])

It returns output as:

[{
_id: ObjectId of 2nd document,
"minRateDoc": 2
}
]

But to return all fields I don't want to use projection as in reality there are many fields I want to return. Also is there other way to return documents where minimum rate value is number 2.

CodePudding user response：

You can directly apply $min to the array orders.rate and compare to 2 to get the documents you want.

db.collection.find({
  $expr: {
    $eq: [
      2,
      {
        $min: "$orders.rate"
      }
    ]
  }
})

Mongo Playground

CodePudding user response：

You could query for documents which contain an order whose rate is 2, and which do NOT contain an order whose rate is less than 2. This can be achieved as follows:

db.collection.find(
  {
    $and: [
      { "orders.rate": 2 },
      { "orders.rate": { $not: { $lt: 2 } } }
    ]
  }
)