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.

CodePudding user response：

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