db={"chat":[{"_id":{"$oid":"63d2497f8cf582fc4392d270"},"create_date":1674725758968,"id":"NbM6vsQ_hMQmVywqd2V2","last_message":null,"owner_id":"63ce54460aeee5e72c778d90","participants":["63ce54460aeee5e72c778d90","63ce54460aeee5e72c778d92"],"title":"Chat for test","type":"group","unread":0},],message:[{"_id":{"$oid":"63d24a90a596592f6bc3cbbf"},"body":"aggregation test","chat_id":"NbM6vsQ_hMQmVywqd2V2","create_date":1674726032923,"from_id":"63ce54460aeee5e72c778d90","id":"6z5kp48eYZaRJdoFFoQr","media":"","modifications":null,"type":"text","update_at":0,"viewed":false}],chatsusers:[{"_id":{"$oid":"63d2497e8cf582fc4392d26e"},"added_at":1674725758968,"chat_id":"NbM6vsQ_hMQmVywqd2V2","end_message_id":99999999999,"id":"7LLMuuJ49L5fXMHWwnnM","start_message_id":0,"user_id":"63ce54460aeee5e72c778d90"},{"_id":{"$oid":"63d2497e8cf582fc4392d26f"},"added_at":1674725758968,"chat_id":"NbM6vsQ_hMQmVywqd2V2","end_message_id":99999999999,"id":"AoQwMf8pMkqC-28TkC2I","start_message_id":0,"user_id":"63ce54460aeee5e72c778d92"}]}

db.chat.aggregate([{$match:{participants:"63ce54460aeee5e72c778d90"}},{$lookup:{from:"chatsusers",localField:"id",foreignField:"chat_id",as:"chat_user"}},{$unwind:{path:"$chat_user"}},{$lookup:{from:"message",localField:"id",foreignField:"chat_id",let:{smid:"$chat_user.start_message_id"},pipeline:[{$match:{$expr:{$gte:["$create_date","$$smid"]}}},{$sort:{create_date:-1}},{$limit:1}],as:"last_message",}},{$unwind:{path:"$last_message",preserveNullAndEmptyArrays:true}},{$group:{_id:"$_id","create_date":{$first:"$create_date"},"id":{$first:"$_id"},"last_message":{$max:"$last_message"},"owner_id":{$first:"$owner_id"},"participants":{$first:"$participants"},"title":{$first:"$title"},"type":{$first:"$type"},"unread":{$first:"$unread"}}},{$project:{id:1,title:1,create_date:1,type:1,participants:1,owner_id:1,last_message:"$last_message",unread:1}}])