Sampling data from two collections with multiple array matching MongoDB

I'm trying to find all unread messages, by chat ID, and user ID. Unread message is if chats_users.max_read_date is less than message.create_date and message.from_id is not equal to user.

How it works now. I have an array of chat IDs, and I search all chats_users documents by chat_id and user_id and then just loop search for messages.

I'd like to rewrite it all into one query, but I'm running into a few problems. What is my problem, how do I find all chats_users by chat ID and user ID to then find all messages that are written by a non-user, and the creation date message.create_date is greater than chats_users.max_read_date

How to make a condition so that for each chat, find its chats_users and then select only those messages which are written not by the user who makes the request and all other chatters, and the creation date of the message message.create_date is greater than chats_users.max_read_date

Message collection

type ChatsUsers struct {
	ID     string `json:"id" bson:"id"`
	ChatID string `json:"chat_id" bson:"chat_id"`
	// user ID, which corresponds to the user ID in the system.
	UserID string `json:"user_id" bson:"user_id"`
	// MaxReadDate time of the last message read
	MaxReadDate int64 `json:"max_read_date" bson:"max_read_date"`
}

ChatsUsers collection

type Message struct {
	ID         string `json:"id" bson:"id"`
	ChatID     string `json:"chat_id" bson:"chat_id"`
	FromID     string `json:"from_id" bson:"from_id"`
	CreateDate int64  `json:"create_date" bson:"create_date"`
	Body     string `json:"body" bson:"body"`
}

Rewrite the query to get a single aggregation, the purpose of the query is to find all messages, by all chats that are unread and unscripted by a non-user, the uid variable is the user looking for such messages

fun (r *Mongo) UnreadMessageCount(ctx context.Context, chats []*Chat, uid string) (map[string]int64, error) {

	match := bson.A{}
	for _, chat := range chats {
		match = append(match, chat.ID)
	}

	chatsUsersList := make([]*domain.ChatsUsers, 0)
	for _, ch := range chats {
		chu, err := r.FindChatUser(ctx, ch.ID, uid)
		if err != nil {
			l.Error().Err(err).Msg("failed to find chat user")
			return nil, err
		}
		chatsUsersList = append(chatsUsersList, chu)
	}

	list := make([]*domain.Message, 0)

	for _, ch := range chats {
		for _, chu := range chatsUsersList {
			if chu.ChatID == ch.ID {
				filter := bson.D{
					// search for messages by active chat IDs
					{ Key: "chat_id", Value: ch.ID},
					// add filtering: that the message has not yet been read,
					// and that the messages we select are not written by the current user.
					{Key: "$and",
						Value: bson.A{
							bson.D{
								{
									Key: "create_date", Value: bson.D{ bson.D{
									// $gt Matches values that are greater than the specified value.
									{ Key: "$gt", Value: chu.MaxReadDate}}
								},
								// $ne Matches all values that are not equal to the specified value.
								// https://www.mongodb.com/docs/manual/reference/operator/query-comparison/
								{Key: "from_id", Value: bson.M{"$ne": uid}}
							},
						},
					},
				}

				cursor, err := r.colMessage.Find(ctx, filter)
				....

				var res []*domain.Message
				....
			}
		}
	}

	// it doesn't make sense to use an array of messages, we need to create a map,
	// which will have the chat ID and the number of unread messages in it.
	messages := make(map[string]int64)
	for _, msg := range list {
		messages[msg.ChatID]++
	}

	return messages, nil
}

My try (sorry, but this is a very crooked query, but I'm still trying to write the right query)

db.message.aggregate([
{
$match: {
$expr: {
$and: [
{
chat_id: {
$in: ["ad0a3405-1a16-48f9-93e6-51b17a7283e2"]
}
},
{
from_id: {
$ne: "63f5002735bb916dab3f2b1d"
}
},
{
$gt: ["$create_date", { $max: "$chats_users.max_read_date" }]
}
]
}
}
},
{
$lookup: {
from: "chatsusers",
localField: "chat_id",
foreignField: "chat_id",
as: "chats_users"
}
},
{
$unwind: "$chats_users"
},
{
$group: {
_id: "$chat_id",
messages: {
$push: {
id: "$id",
chat_id: "$chat_id",
from_id: "$from_id",
create_date: "$create_date",
type: "$type",
media: "$media",
body: "$body",
update_at: "$update_at",
modifications: "$modifications",
viewed: "$viewed"
}
},
max_read_date: { $max: "$chats_users.max_read_date" }
}
}
]);

In the response I expect to get only four objects from the test data

{
"_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": "63f5002735bb916dab3f2bb2"},
"body": "after you",
"chat_id": "ad0a3405-1a16-48f9-93e6-51b17a7283e2-y7y3r2",
"create_date": 1677001427398,
"from_id": "63f5002735bb916dab3f2b23",
"id": "e5189d64-0c43-41dd-befa-f554d09475cf",
"media": "",
"modifications": null,
"type": "text",
"update_at": 0,
"viewed": true
},
{
"_id": {"$oid": "63f5002735bb916dab3f2bb1"},
"body": "I'm not religious",
"chat_id": "ad0a3405-1a16-48f9-93e6-51b17a7283e2-y7y3r2",
"create_date": 1677001427327,
"from_id": "63f5002735bb916dab3f2b23",
"id": "a1fbc6b0-5f8d-4fa2-aef7-807314dfba0d",
"media": "",
"modifications": null,
"type": "text",
"update_at": 0,
"viewed": true
}

Test data

And my second try

db.chats_users.aggregate([
{
$match: {
user_id: "63f5002735bb916dab3f2b1d",
chat_id: { $in: ["ad0a3405-1a16-48f9-93e6-51b17a7283e2"] },
}
},
{
$lookup: {
from: "message",
let: { chat_id: "$chat_id", max_read_date: "$max_read_date" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$chat_id", "$$chat_id"] },
{ $ne: ["$from_id", "63f5002735bb916dab3f2b1d"] },
{ $gt: ["$create_date", "$$max_read_date"] },
]
}
}
}
],
as: "messages"
}
},
{
$project: {
chat_id: 1,
message_count: { $size: "$messages" }
}
}
])

It turned out to be simpler, I made a query using the chatsusers collection - which simplified the query.

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,
},
},
])

Playground