Mongodb - How to find string in multiple fields?

Question

Using Pymongo for this scenario.

I have User that has email, first_name, last_name.

I am using this Pymongo snippet:

user_found = users.find({'$or':[
            {'email':{'$regex':searchString, '$options':'i'}},
            {'first_name':{'$regex':searchString, '$options':'i'}},
            {'last_name':{'$regex':searchString, '$options':'i'}}]})

this example works, if I want to find searchString in:

• email, or
• first_name, or
• last_name

now I need to also find searchString in first_name + last_name combined.

how can I do that? Is there a way in mongo, through the query, to combine the two into a "fullname" then search the fullname?

Answer 1

Easiest way is to add an array field and populate it with all of the variants that you want to search on. Index that array field.

That way you only need one index and your search across all fields is simple and doesn't change when you want to search on some new search variant. You can also normalize the text you put into the search array, for example, lower casing it, removing punctuation etc.

See https://stackoverflow.com/q/8206188/224370

Edit: MongoDB's documentation now covers keyword search and the new full-text search feature.

Answer 2

I had the same problem. I already used regex string search, so my solution was:

generate a helper collection. Here I combine all relevant string, like:

{
    search_field: email + " " + first_name + " " + last_name,
    ref_id: (id to real object)
}

I then use a regexp to creat what i allow to be looked for:

// logic found here: http://stackoverflow.com/questions/10870372/regex-match-if-string-contain-all-the-words-or-a-condition
var words = query.split(/[ ,]+/);
var regstr = "";
for (var i = 0; i < words.length; ++i) {
    var word = words[i];
    regstr += "(?=.*?\\b" + word + ")";
}
regstr += "^.*$";
regex = new RegExp(regstr, "i");

This then also gives some flexibility about the order.

Searching is not the fastest, since it still uses regex on all elements, but it is ok for me. (I also index the collection on search_field.

Getting results also becomes a nested call, since first you need to get the _ids you really want, and then you can query for them like so:

connection.find({ "search_field" : regex }, { _id: 0, ref_id: 1 }, { limit: limit, skip: start }).toArray(function (err, docs) {
    if (err) throw err;
    // map array of documents into simple array of ids
    var ids = [];
    for (var i = 0; i < docs.length; ++i)
    {
        var doc = docs[i];
        ids.push(doc.ref_id);
    }
    if (ids.length > 0)
        MongooseEmails.find({ "_id": { $in: ids } }, function (err, docres) {
            if (err) throw err;
            res.send(JSON.stringify(docsres));
        });
    else
        res.send("");
});

This is edited code.. perhaps there is a syntax error, generally, it is working for me.