Count value in a document lookup and return 0 if not exist mongodb aggregate

Question

Hello I have the following collections

const TransactionSchema = mongoose.Schema({
schedule: {
    type: mongoose.Schema.ObjectId,
    required: true,
    ref: "Schedule"
},
uniqueCode: {
    type: String,
    required: true
},
created: {
    type: Date,
    default: Date.now
},

status: {
    type: String,
    required: false
},

})

const ScheduleSchema = mongoose.Schema({
start: {
    type: Date,
    required: true,
},
end: {
    type: Date,
    required: false,
},
questions: {
    type: Array,
    default: [],
},
items: [{
    item: {
        type: mongoose.Schema.ObjectId,
        require: true,
        ref: "Item"
    },
    stok: {
        type: Number,
        required: true
    }
}],

status: {
    type: String,
    required: false
},

})

and I want to return how many times the schedule appear in transaction and reduce it with the number of total item I have in array of objects items in schedule collection. For example I have the following data.

transaction

  [
   {
      "_id":"identifier",
      "schedule":identifier1,
      "uniqueCode":"312312312312",
      "created":"Date"
   },
   {
      "_id":"identifier",
      "schedule":identifier1,
      "uniqueCode":"1213123123",
      "created":"Date"
   }
]

schedule

[
   {
      "_id":identifier1,
      "start":"date",
      "end":"date",
      "questions":[
         12,
         32,
         122
      ],
      "items":[
         {
            "item":itemIdentifier1,
            "stock":120
         },
         {
            "item":itemIndentifier2,
            "stock":1000
         }
      ],
      "status":"Active"
   },
   {
      "_id":identifier2,
      "start":"date",
      "end":"date",
      "questions":[
         12,
         32,
         122
      ],
      "items":[
         {
            "item":itemIdentifier1,
            "stock":120
         }
      ],
      "status":"Active"
   }
]

and I want to get the following result:

[
   {
      "schedule":identifier1,
      "total":1118
   },
   {
      "schedule":identifier2,
      "total":120
   }
]

note: the first row shows 1118 from total stock of item 1120 - 2 which is how many times the schedule appeared in transaction. The second row shows 120 because the schedule hasn't appeared in transaction.

thank you. Sorry for my bad english.

Answer 1

1. $lookup - Join schedule collection (_id) with transaction collection (schedule) to get transactions array.

2. $project - Decorate output documents. For total field, $subtract for $sum of items.stock and $size of transactions array.

db.schedule.aggregate([
  {
    "$lookup": {
      "from": "transaction",
      "localField": "_id",
      "foreignField": "schedule",
      "as": "transactions"
    }
  },
  {
    $project: {
      schedule: "$_id",
      total: {
        $subtract: [
          {
            $sum: "$items.stock"
          },
          {
            $size: "$transactions"
          }
        ]
      }
    }
  }
])

Sample Mongo Playground