Back

Technologies:

javascriptjavascript
avatar
Tolerim
a month ago

How can I utilize $regex to match in both the document and $lookup result within mongodb?

As part of my work, I am developing a search function that uses regular expressions to match the search input to the name of the company, owner's name, and owner's email. The company and user collections are structured as follows: "Companys" collection: 
{
    _id: ObjectId('6421204b937b21001d44cadf'),
    name: 'test',
    is_delete: false,
    active: true,
    owners_id: ObjectId('641c021cda143c01b05c370a'),
}
"Users" collection (owner): 
{
    _id: ObjectId('641c021cda143c01b05c370a'),
    name: 'test_user',
}
Currently, the query I'm using (with the input 'test') is as follows: 
companys.aggregate(
    [
        { '$match': {
            '$and': [
                { is_delete: false, active: true }
            ],
            '$or': [
                { name: { '$regex': '.*test.*', '$options': 'i' } }
            ]
        } },
        { '$lookup': {
            from: 'users',
            as: 'owner_user',
            let: { usersid: '$ownersid' },
            pipeline: [
                { '$match': {
                    '$expr': {
                        '$and': [
                            { '$eq': [ '$id', '$$usersid' ] }
                        ]
                    },
                    '$or': [
                        { name: { '$regex': '.*test.*', '$options': 'i' } },
                        { email: { '$regex': '.*test.*', '$options': 'i' } }
                    ]
                } }
            ]
        } },
        { '$unwind': '$owner_user' },
        { '$sort': { _id: -1 } },
        { '$facet': {
            metadata: [
                { '$count': 'total' },
                { '$addFields': { page: 1 } }
            ],
            data: [
                { '$skip': 0 },
                { '$limit': 10 }
            ]
        } }
    ], {}
)
The problem with this approach is that the results only consider companies and owners that contain the word 'test' in their name. If the search input is 'us', which matches the regular expression in the owner's name but not the company name, the result will be an empty array. Is there any way to modify the query to show results if the regular expression matches either the company's name, owner's name, or owner's email?
Answers(2)
avatar
Vogi
a month ago
Verified Answer
Yes, you can modify the query to show results if the regular expression matches either the company's name, owner's name, or owner's email. You can use the $lookup stage to join the users collection with the companys collection and then use $or operator to match either the company's name, owner's name or owner's email. Here is an example of how you can modify your query: 
companys.aggregate(
    [
        { '$match': {
            '$and': [
                { is_delete: false, active: true }
            ],
            '$or': [
                { name: { '$regex': '.*test.*', '$options': 'i' } },
                { owners_id: { '$in': [ ObjectId('641c021cda143c01b05c370a') ] } }
            ]
        } },
        { '$lookup': {
            from: 'users',
            as: 'owner_user',
            let: { users_id: '$owners_id' },
            pipeline: [
                { '$match': {
                    '$expr': {
                        '$and': [
                            { '$eq': [ '$_id', '$$users_id' ] }
                        ]
                    },
                    '$or': [
                        { name: { '$regex': '.*test.*', '$options': 'i' } },
                        { email: { '$regex': '.*test.*', '$options': 'i' } }
                    ]
                } }
            ]
        } },
        { '$unwind': '$owner_user' },
        { '$sort': { _id: -1 } },
        { '$facet': {
            metadata: [
                { '$count': 'total' },
                { '$addFields': { page: 1 } }
            ],
            data: [
                { '$skip': 0 },
                { '$limit': 10 }
            ]
        } }
    ], {}
)
In this modified query, we have added another condition in the $or operator that matches the owners_id field in the companys collection with the _id field in the users collection. This will ensure that if the regular expression matches either the company's name, owner's name or owner's email, it will return a result. I hope this helps! Let me know if you have any other questions.
avatar
Tolerim
a month ago
Yes, we can modify the existing query to show results if the regex matches either company's name, owner's name or owner's email. We can use the $lookup stage to lookup users collection based on the owners_id field in companys collection and use $or operator to match any of the three fields (name, email, owners_name) in the users collection. Here's the modified query: 
companys.aggregate([
  {
    $match: {
      is_delete: false,
      active: true,
      $or: [
        { name: { $regex: '.*test.*', $options: 'i' } },
        {
          owners_id: {
            $in: [
              {
                $lookup: {
                  from: 'users',
                  let: { users_id: '$owners_id' },
                  pipeline: [
                    {
                      $match: {
                        $expr: {
                          $or: [
                            { $regexMatch: { input: '$name', regex: '.*test.*', options: 'i' } },
                            { $regexMatch: { input: '$email', regex: '.*test.*', options: 'i' } }
                          ]
                        }
                      }
                    }
                  ],
                  as: 'owner_user'
                }
              }
            ]
          }
        }
      ]
    }
  },
  { $sort: { _id: -1 } },
  {
    $facet: {
      metadata: [{ $count: 'total' }, { $addFields: { page: 1 } }],
      data: [{ $skip: 0 }, { $limit: 10 }]
    }
  }
]);
In this modified query, we have added a nested $lookup stage inside the $or operator. This $lookup stage does a lookup on the users collection based on the owners_id field in the companys collection and matches any of the three fields (name, email, owners_name) in the users collection using the $or operator with $regexMatch operator. This will return the documents in companys collection where either the company's name or the owner's name or the owner's email matches the search input.
;