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

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) {
    ...
});

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

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"
    }
  }
})

Solution 5

This code is tested and it works fine:

imagine the collection is like this {name :{first:'','last:''}}

the query should be like this: db.collectionName.find({ $or : [
{ 'name.first':{ $regex: 'search', $options: '-i' } },
{ 'name.last': { $regex: 'search', $options: '-i' } },
]})
;

Solution 6

You could use $or (see the doc)

User.find({ $or: [
  {$match: {"firstName": {$regex: "/bob j/i"}}},
  {$match: {"lastName": {$regex: "/bob j/i"}}}
] });

Not tested

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply