db={message:[{"_id":{"$oid":"63f502d3c1be2b78aaa6152e"},"body":"полковник конь тобі копитом в грудь! 3","chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","create_date":1677001427977,"from_id":"63f5002735bb916dab3f2b1d5g","id":"G30uZ1HvlYQCZVwRRTBi","media":"","modifications":null,"type":"text","update_at":0,"viewed":false},{"_id":{"$oid":"63f502d2c1be2b78aaa6152d"},"body":"полковник конь тобі копитом в грудь! 2","chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","create_date":1677001428101,"from_id":"63f5002735bb916dab3f2b1d5g","id":"RzrfvZuLWyhrKOHEx5gK","media":"","modifications":null,"type":"text","update_at":0,"viewed":false},{"_id":{"$oid":"63f502c9c1be2b78aaa6152c"},"body":"полковник конь тобі копитом в грудь! 1","chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","create_date":1677001417531,"from_id":"63f5002735bb916dab3f2b1d","id":"LfaWykt0m5PYEItDGtYi","media":"","modifications":null,"type":"text","update_at":0,"viewed":false},{"_id":{"$oid":"63f5002735bb916dab3f2bb7"},"body":"methinks the lady doth protest too much","chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","create_date":1677000743590,"from_id":"63f5002735bb916dab3f2b23","id":"9dcde504-d4ba-4b5d-9903-9ca61c1a7aa8","media":"","modifications":null,"type":"text","update_at":0,"viewed":true},{"_id":{"$oid":"63f5002735bb916dab3f2bb6"},"body":"bless someone's cotton socks","chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","create_date":1677000743589,"from_id":"63f5002735bb916dab3f2b23","id":"00ce5bc1-6036-46cd-86df-8b055aa7df55","media":"","modifications":null,"type":"text","update_at":0,"viewed":true},],chatsusers:[{"_id":{"$oid":"63f5002735bb916dab3f2b25"},"added_at":1677000743362,"chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","end_message_id":4832674343362,"id":"ywPLT-r3AiLy6tj0Mxcp","max_read_date":1677001427277,"start_message_id":1677000743362,"user_id":"63f5002735bb916dab3f2b1d"},{"_id":{"$oid":"63f5002735bb916dab3f2b26"},"added_at":1677000743362,"chat_id":"ad0a3405-1a16-48f9-93e6-51b17a7283e2","end_message_id":4832674343362,"id":"fcgWo38Rls-PhGk6hfJb","max_read_date":1677001427127,"start_message_id":1677000743362,"user_id":"63f5002735bb916dab3f2b1e"},],}

db.message.aggregate([/** Match messages based on chat ID and non-specified user*/{$match:{chat_id:{$in:["ad0a3405-1a16-48f9-93e6-51b17a7283e2"]},from_id:{$ne:"63f5002735bb916dab3f2b1d"}}},/** Look up the corresponding ChatsUsers document based on chat ID and user ID*/{$lookup:{from:"chatsusers",let:{chatId:"$chat_id",userId:"$from_id"},pipeline:[{$match:{$expr:{$and:[{$eq:["$chat_id","$$chatId"]},{$eq:["$user_id","$$userId"]}]}}},/** Get the MaxReadDate for the user*/{$project:{max_read_date:"$MaxReadDate"}}],as:"userMaxReadDate"}},/** Filter out messages that have MaxReadDate >= CreateDate*/{$match:{$expr:{$and:[{$gt:["$create_date",{$arrayElemAt:["$userMaxReadDate.MaxReadDate",0]}]},]}}}])