Conditional aggregation stage

Question

We have 3 collections to track a machine shop.
PARTS gives total items against against a shopID
We need to confirm if the sum of items in INPROCESS and REJECT equal to the count in PARTS for the shop.

However,we will go to look into REJECT - ONLY if INPROCESS does not equal count ,thereby economizing lookups.

How to achieve this.

PARTS

{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "count": 200,
}


INPROCESS

{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "itemID": "5647",
},
{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "itemID": "2314",
},


REJECTED

{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "itemID": "xxxx",
},
{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "itemID": "yyyy",
},

score 0 · Accepted Answer · answered Feb 03 '20 at 20:40

Try this:

db.PARTS.aggregate([
  {
    $lookup: {
      from: "INPROCESS",
      localField: "shop",
      foreignField: "shop",
      as: "inprocess"
    }
  },
  {
    $match: {
      $expr: {
        $ne: [
          "$count",
          {
            $size: "$inprocess"
          }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "REJECTED",
      localField: "shop",
      foreignField: "shop",
      as: "rejected"
    }
  },
  {
    $match: {
      $expr: {
        $ne: [
          "$count",
          {
            $add: [
              {
                $size: "$inprocess"
              },
              {
                $size: "$rejected"
              }
            ]
          }
        ]
      }
    }
  }
])

MongoPlayground

Conditional aggregation stage

1 Answers1