db={transactions:[{"postingDate":"1/1/2023","instrumentId":"Type_1","Amount":" $2,000,000.00 "},{"postingDate":"1/4/2023","instrumentId":"Type_2","Amount":" $700,000.00 "},{"postingDate":"1/4/2023","instrumentId":"Type_3","Amount":" $400,000.00 "},{"postingDate":"2/1/2023","instrumentId":"Type_1","Amount":" $10,000.00 "},{"postingDate":"2/4/2023","instrumentId":"Type_2","Amount":" $4,000.00 "},{"postingDate":"2/4/2023","instrumentId":"Type_3","Amount":" $2,000.00 "},{"postingDate":"3/2/2023","instrumentId":"Type_2","Amount":" $3,900.00 "},{"postingDate":"3/4/2023","instrumentId":"Type_3","Amount":" $1,950.00 "},{"postingDate":"4/1/2023","instrumentId":"Type_2","Amount":" $3,900.00 "},{"Accounting Period":"Apr-23","postingDate":"4/6/2023","instrumentId":"Type_3","Amount":" $1,950.00 "},{"postingDate":"5/1/2023","instrumentId":"Type_2","Amount":" $3,900.00 "},{"postingDate":"5/6/2023","instrumentId":"Type_3","Amount":" $1,950.00 "},{"postingDate":"5/15/2023","instrumentId":"Type_1","Amount":" $500,000.00 "},{"postingDate":"6/15/2023","instrumentId":"Type_2","Amount":" $3,500.00 "},{"postingDate":"6/15/2023","instrumentId":"Type_3","Amount":" $1,700.00 "},{"postingDate":"6/15/2023","instrumentId":"Type_1","Amount":" $100,000.00 "}],attributes:[{"postingDate":"1/1/2023","instrumentId":"Type_1","status":"A"},{"postingDate":"1/4/2023","instrumentId":"Type_2","status":"A"},{"postingDate":"1/4/2023","instrumentId":"Type_3","status":"A"},{"postingDate":"2/1/2023","instrumentId":"Type_1","status":"A"},{"postingDate":"2/4/2023","instrumentId":"Type_2","status":"A"},{"postingDate":"2/4/2023","instrumentId":"Type_3","status":"A"},{"postingDate":"3/2/2023","instrumentId":"Type_2","status":"A"},{"postingDate":"3/4/2023","instrumentId":"Type_3","status":"N"},{"postingDate":"4/1/2023","instrumentId":"Type_2","status":"A"},{"Accounting Period":"Apr-23","postingDate":"4/6/2023","instrumentId":"Type_3","status":"N"},{"postingDate":"5/1/2023","instrumentId":"Type_2","status":"A"},{"postingDate":"5/6/2023","instrumentId":"Type_3","status":"N"},{"postingDate":"5/15/2023","instrumentId":"Type_1","status":"N"},{"postingDate":"6/15/2023","instrumentId":"Type_2","status":"A"},{"postingDate":"6/15/2023","instrumentId":"Type_3","status":"A"},{"postingDate":"6/15/2023","instrumentId":"Type_1","status":"A"}]}

db.transactions.aggregate([{$match:{$expr:{$lte:[{$dateFromString:{dateString:"$postingDate",format:"%m/%d/%Y"}},ISODate("2023-04-30")]}}},{$setWindowFields:{partitionBy:"$instrumentId",output:{allAmounts:{$push:{amount:"$Amount",postingDate:"$postingDate"},window:{documents:["unbounded","unbounded"]}}}}},{$match:{$expr:{$gte:[{$dateFromString:{dateString:"$postingDate",format:"%m/%d/%Y"}},ISODate("2023-04-01")]}}},{$lookup:{from:"attributes",let:{postingDate:"$postingDate",instrumentId:"$instrumentId"},pipeline:[{$match:{$expr:{$and:[{$eq:["$postingDate","$$postingDate"]},{$eq:["$instrumentId","$$instrumentId"]}]}}},{$project:{status:1,_id:0}}],as:"attributes"}},{$unwind:"$attributes"},{$group:{_id:{postingDate:"$postingDate",status:"$attributes.status"},allAmountsInstrumentId:{$first:"$allAmounts"}}}])