How to search for users by both first and last name with MongoDB?

Question

I have a basic collection of users that have their firstName, lastName and a few other details.

How would I simply search for users by a combination of both names, or partial search?

For example, for a collection of:

{
   firstName: Bob,
   lastName: Jerry
}, {
   firstName: Clark,
   lastName: Mcbobby
}

If the search term was bob, both users would be returned since the first documents firstName is bob, and the last users lastName contains bob. If bob j was the search term, just the first document would be returned since if both names are combine, it equals Bob Jerry which matches the search term.

I tried creating a basic aggregate to concatenate the names and then make a match, although Mongoose kept throwing me an error of: Arguments must be aggregate pipeline operators.

Here is my current code:

User.aggregate({
    $project: { "name" : { $concat : [ "$firstName", " ", "$lastName" ] } },
    $match: {"name": {$regex: "/bob j/i"}}
}).exec(function(err, results) {
    ...
});

Solution 1

I see couple of mistakes in your code causing undesired result.

1. Aggregation pipeline accepts array of aggregation framework operations. In your case, you are missing [] operator. It should be like

   User.aggregate([{$project...},{$match...}])

2. In $match stage you are using regex, if you are using /../ style of regex, you don't need to wrap it around string quotes. It should be /bob j/i

Here is finished example:

User.aggregate([
  {$project: { "name" : { $concat : [ "$firstName", " ", "$lastName" ] } }},
  {$match: {"name": {$regex: /bob j/i}}}
]).exec(function(err, result){
  console.log(result);
});

You should see [ { _id: 574c3e20be214bd4078a9149, name: 'Bob Jerry' } ] on screen.

Solution 2

The arguments for the aggregate function must be an array containing pipeline stage documents

var query = 'bob j';
Users.aggregate([ //pipeline array
 {$project:{name: { $concat : [ "$firstName", " ", "$lastName" ] }}}, //stage1
 {$match : { name: { $regex: query, $options:'i'}}} //stage2
])

Solution 3

You don't need to use the aggregation framework to select documents where the a give value is in "firstName" or "lastName".

var reg = new RegExp(/bob/, 'i');
User.find({
    '$or': [
        { 'firstName': reg }, 
        { 'lastName': reg }
    ]
}).exec(function(err, results) { // Do something }

If you want based on the concatenated value, then:

User.aggregate([
    { "$project": { "name": { "$concat" : [ "$firstName", " ", "$lastName" ] } } },  
    { "$match" : { "name": /bob j/i } }  
]).exec(function(err, results) { // Do something } 

Solution 4

With the latest mongodb version 4.2 you can use $regexMatch aggregation. Something like this

db.collection.find({
  "$expr": {
    "$regexMatch": {
      "input": { "$concat": ["$firstName", " ", "$lastName"] },
      "regex": "a",  //Your text search here
      "options": "i"
    }
  }
})

View more solutions

Comments

[Fizzix]

Won't that match 'firstName' OR 'lastName'? Therefore having "bob j" not matching either since it's a combination of both?

[Fizzix]

Ahhh okay, so the error was being thrown since my aggregate pipeline was not an array. Your fixes work perfectly, thanks Saleem.