Basically I am searching messages. I have 2 collections:
Users:
[
{
"_id": "Xuibgsadbgsi35Gsdf",
"fullName": "User A"
},
{
"_id": "Afstg34tg4536gh",
"fullName": "User B"
},
{
"_id": "KHJDFhfs7dfgsvdfwsef",
"fullName": "User C"
}
]
Messages:
[
{
"_id": "YONgsa793423bD",
"groupId": "Phsdfyg92345sgb7651",
"senderId": "Xuibgsadbgsi35Gsdf",
"message": "Hello there!"
},
{
"_id": "sdgDFGbaofh135df",
"groupId": "Phsdfyg92345sgb7651",
"senderId": "KHJDFhfs7dfgsvdfwsef",
"message": "Hello @Xuibgsadbgsi35Gsdf"
},
{
"_id": "sdgDFGbaofh135df",
"groupId": "Phsdfyg92345sgb7651",
"senderId": "KHJDFhfs7dfgsvdfwsef",
"message": "Hello"
}
]
Now here I want to search: User A, so I should get those message in which User A is involved in any way, either he is a sender or he is mentioned in some message text.
How can I query this scenario?
You can do this
db.users.aggregate([
{
$match: {
fullName: "User A"
}
},
{
"$lookup": {
"from": "messages",
let: {
id: "$_id" //This is what you need to match in the messages collection, kind of variable
},
"pipeline": [
{
$match: {
$or: [
{
$expr: { //Matching in sender id
$eq: [
"$senderId",
"$$id"
]
}
},
{
$expr: {
"$regexMatch": { //matching in messages
"input": "$message",
"regex": "$$id",
}
}
}
]
}
}
],
"as": "senders"
}
},
{
$match: {
$expr: {
"$gt": [//Filtering only the matched results
{
"$size": "$senders"
},
0
]
}
}
}
])
To add filtering, you can add a match stage before lookup as below
{
$match: {
fullName: "User A"
}
}
Note, mongo is case sensitive db. Updated sample