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.chatsusers.aggregate([{$match:{chat_id:{$in:["ad0a3405-1a16-48f9-93e6-51b17a7283e2","22e1e6f1-459a-4ed1-bc8f-c9947492972d"],},}},{$lookup:{from:"message",localField:"chat_id",foreignField:"chat_id",let:{chat_id:"$chat_id",max_read_date:"$max_read_date"},pipeline:[{$match:{$expr:{$and:[{$eq:["$chat_id","$$chat_id"]},{$ne:["$from_id","63f5002735bb916dab3f2b1e"]},{$gt:["$create_date","$$max_read_date"]},]}},}],as:"messages"}},{$project:{chat_id:1,message_count:{$size:"$messages"},}},{$group:{_id:"$chat_id",message_count:{$sum:"$message_count"},},},{$sort:{"message_count":-1},},{$project:{_id:0,chat_id:"$_id",message_count:1,},},])