db={users:[{id:1,name:"Aravinth",prices:[{beginDate:"2022-08-24T07:29:01.639Z",price:45},{beginDate:"2022-08-31T07:29:01.639Z",endDate:"2022-09-31T07:29:01.639Z",price:51}]},{id:2,name:"Raja",prices:[{beginDate:"2022-08-26T07:29:01.639Z",endDate:"2022-08-30T07:29:01.639Z",price:55}]}],datasets:[{color:"braun, rose gold",firstBillable:"2022-08-24T07:29:01.639Z",assignedTo:1},{color:"beige, silber",firstBillable:"2022-09-27T07:29:01.639Z",assignedTo:2},{color:"asdasd1",firstBillable:"2022-07-25T07:29:01.639Z",},{color:"asdasd2",assignedTo:2},{color:"asdasd3",}]}

db.datasets.aggregate([{"$lookup":{"from":"users","localField":"assignedTo","foreignField":"id","as":"matchedUsers"}},{"$addFields":{"cgData":{"$first":"$matchedUsers"}}},{"$addFields":{"cgData":{"$first":{"$filter":{"input":{"$ifNull":["$cgData.prices",[]]},"as":"currentPrice","cond":{"$and":[{"$gte":["$firstBillable","$$currentPrice.beginDate"]},{$or:[{$eq:[{$type:"$$currentPrice.endDate"},"missing"]},{"$lt":["$firstBillable","$$currentPrice.endDate"]}]}]}}}}}},{"$addFields":{cgPrice:"$cgData.price"}},{"$project":{cgData:0,"matchedUsers":0}}])