Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Search 2 text fields as 1 field

I'm to search users by name and I've the properties: firstName and lastName. If I had a property fullName this would be trivial but I don't have it.

I want to search for "Peter Robert" and I need to combine theses 2 fields into 1 before searching.

How do I do it?

like image 690
Mário Avatar asked Mar 18 '23 22:03

Mário


1 Answers

Sounds basically like you want a "text search". Text indexes can span over multiple fields so terms entered would be searched from all indexed fields:

db.collection.ensureIndex({ "firstName": "text", "lastName": "text" })

db.collection.find({ "$text": { "$search": "Peter Robert" } })

That is one way to handle this. Will return other matches as well but exact matches have the highest score so you can rank them.

Alternately, if you know you are always getting a string like "Peter Robert" in that order then you can always "split" and tokenize the input:

var input = "Peter Robert";
var parts = input.split(/ /); // splits on space

db.collection.find({
    "firstName": parts[0],
    "lastName" parts[1]
])

Which is all pretty basic. Or even apply the $regex and $or operators in the mix:

var input = "Peter Robert";
var regexStr = input.split(/ /).join("|");   // make it like "Peter|Robert"

db.collection.find({
    "$or": [
        { "firstName": { "$regex": regexStr } },
        { "lastName": { "$regex": regexStr }}
    ]
})

Honestly, you can even do this with the $where operator and JavaScript. Not the best though since the condition would evaluate for every document:

db.collection.find(function(){
    return ( this.firstName + " " + this.lastName ) == "Peter Robert";
})

Probably plays bit better with the aggregation framework though:

db.collection.aggregate([
    // Borrow something from above to get "might" match documents
    { "$match": {
        "$or": [
            { "firstName": { "$regex": regexStr } },
            { "lastName": { "$regex": regexStr }}
        ]
    }},

    // Then project a "fullName"
    { "$project": {
        "firstName": 1,
        "lastName": 1,
        "fullName": { "$concat": [ "$firstName", " ", "$lastName" ] }
    }},

    // Match on the fullName value
    { "$match": { "fullName": input } }

])

Many ways to do this. You are not restricted by frameworks ( such as those that "emulate" Mongo functions on a client ) since there are both multiple ways to process this and server side operations like "text search" and aggregation and "JavaScript queries" can all be done in server logic code.

like image 151
Neil Lunn Avatar answered Mar 31 '23 14:03

Neil Lunn