Mongodb search multiple collections

Question

Basically I am searching messages. I have 2 collections:

• Users
• Messages

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?

Answer 1

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