mongodb find: how to use a variable regexp inside $where?-CodePudding

I have a nodejs express web with a mongo db with

const AuthorSchema = new Schema({
    first_name: { type: String, required: true, maxLength: 100 },
    family_name: { type: String, required: true, maxLength: 100 },
    date_of_birth: { type: Date },
    date_of_death: { type: Date },
});

I want to search for an author using a regexp over the first_name family_name. In other words, given we have an author with: ... first_name: 'Alessandro' family_name: 'Manzoni' ... I want to match it using all of the search strings:

'Alessandro' or 'ale' or 'manzo' or 'alessandro manzoni' or 'Manzoni Alessandro'

so I wrote this:

const searchRegex = new RegExp(req.body.search_text, 'i');  
Author.find({ $where: 
    function() {
        return (searchRegex.test(this.first_name   " "   this.family_name) 
            || searchRegex.test(this.family_name   " "   this.first_name) )
    } 
},"_id").exec(function (err, list_authors) {

and I got:

MongoServerError: Executor error during find command :: caused by :: ReferenceError: searchRegex is not defined :
@:2:33

I tried using a $let clause but all I've achieved is getting all of the authors id in the results list.

CodePudding user response：

I think you are looking for something like this (and avoiding $where which is very inefficient):

This query get the first and family names concatenated and do the regex match on it.

If there is a match in the regex (the result is true) the document will be returned. Otherwise it doesn't.

Author.find({
  "$expr": {
    "$eq": [
      {
        "$regexMatch": {
          "input": {
            "$concat": [
              "$first_name",
              " ",
              "$family_name"
            ]
          },
          "regex": yourStringRegexHere,
          "options": "i"
        }
      },
      true
    ]
  }
})

Example here

CodePudding user response：

Thanks, your answer works perfectly. I added a $or to search also in family_name " " first_name and deleted the options line because they are in the regex. So the final code is:

                Author.find({$or: [
                {
                  "$expr": {
                    "$eq": [
                      {
                        "$regexMatch": {
                          "input": {
                            "$concat": [
                              "$first_name",
                              " ",
                              "$family_name"
                            ]
                          },
                          "regex": searchRegex
                        }
                      },
                      true
                    ]
                  }
                },
                {
                  "$expr": {
                    "$eq": [
                      {
                        "$regexMatch": {
                          "input": {
                            "$concat": [
                              "$family_name",
                              " ",
                              "$first_name"
                            ]
                          },
                          "regex": searchRegex
                        }
                      },
                      true
                    ]
                  }
                },                    
                ]},"_id").exec(callback);

really a lot of code, but it works...