db={"wco_email_message":[{"_id":11,"conversation_id":1,"read_at":ISODate("2024-02-27T02:04:06.123Z"),"created_at":ISODate("2024-02-27T01:02:03.456Z")},{"_id":12,"conversation_id":1,"read_at":null,"created_at":ISODate("2024-02-26T01:02:03.456Z")},{"_id":13,"conversation_id":1,"read_at":ISODate("2024-02-26T02:04:06.123Z"),"created_at":ISODate("2024-02-25T01:02:03.456Z")}],"conversation":[{"_id":1,"tag_ids":[ObjectId("658db0d00f5b4f4471bdb3b6")]}]}

db.conversation.aggregate([{"$match":{"tag_ids":{"$in":[ObjectId("658db0d00f5b4f4471bdb3b6")]}}},{"$lookup":{"from":"wco_email_message","localField":"_id","foreignField":"conversation_id","pipeline":[{"$match":{"read_at":null}},{"$sort":{"created_at":1}},{"$project":{"created_at":1,"read_at":1}}],"as":"unread_messages"}},{"$lookup":{"from":"wco_email_message","localField":"_id","foreignField":"conversation_id","pipeline":[{"$sort":{"created_at":-1}},{"$limit":1},{"$project":{"created_at":1,"read_at":1}}],"as":"last_message"}},{"$project":{"subject":1,"all_messages":{"$setUnion":["$unread_messages","$last_message"]}}}])