I have two collections:

{
"Sku" : "A",
"Stock" : [
    {
        "StoreCode" : "1",
        "Qty" : 3
    }
 ]
},
{
"Sku" : "B",
"Stock" : [
    {
        "StoreCode" : "1",
        "Qty" : 3
    },
    {
        "StoreCode" : "2",
        "Qty" : 5
    },
    {
        "StoreCode" : "10",
        "Qty" : 7
    }
 ]
}

And

{
"Sku" : "A",
"MinQty" : 2
}
{
"Sku" : "B",
"MinQty" : 5
}

How can I aggregate this two collections to get the below expected output:

{
"Sku" : "A",
"Availability" : "in stock"
}
{
"Sku" : "B",
"Availability" : "out of stock"
}

Basically, If one store of a specific SKU doesn't have the MinQty in stock, the availability of the product is "out of stock". All stores need to have the minimum quantity (MinQty) in stock.

I did this using lookup, unwind and joining again the unwinded data.

Is there a simple way to do this using $cond iterating through Stock in the first collection:

CodePudding user response：

If I've understood correctly you can try this query:

• First $lookup to "join" the fields by the sku value.
• Then a $project stage to output values you want.
• Using $cond where you can add the stages $gte, $max and $min.

And if I'm not wrong the logic is: "if ($cond) the minimum ($min) number in stock is greater or equal ($gte) than the maximum ($max) number inMinQty then is "in stock", otherwise "out of stock".

db.collA.aggregate([
  {
    "$lookup": {
      "from": "collB",
      "localField": "Sku",
      "foreignField": "Sku",
      "as": "skus"
    }
  },
  {
    "$project": {
      "_id": 0,
      "Sku": 1,
      "Availability": {
        "$cond": {
          "if": {
            "$gte": [
              {
                "$min": "$Stock.Qty"
              },
              {
                "$max": "$skus.MinQty"
              }
            ]
          },
          "then": "in stock",
          "else": "out of stock"
        }
      }
    }
  }
])

Example here