db={"orders":[{"order_id":"123","user_id":"u1","history":["sh1","sh2"]},{"order_id":"111","user_id":"u1","history":["sh3"]}],"status_history":[{_id:"sh1",event:"order_placed",amount:100,timestamp:".."},{_id:"sh2",event:"do_refund",amount:50,timestamp:".."},{_id:"sh3",event:"placed",amount:300,timestamp:".."},]}

db.orders.aggregate([{$match:{"user_id":"u1"}},{$project:{order_id:1,history:1,user_id:1}},{$lookup:{from:"status_history",localField:"history",foreignField:"_id",as:"status_history_object"}},{$unwind:"$status_history_object"},{$group:{"_id":null,"total_placed":{"$sum":{"$cond":{"if":{"$eq":["$status_history_object.event","placed"]},"then":"$status_history_object.amount","else":0}}},"total_refund":{"$sum":{"$cond":{"if":{"$eq":["$status_history_object.event","do_refund"]},"then":"$status_history_object.amount","else":0}}}}},{$set:{total:{$subtract:["$total_placed","$total_refund"]}}}])