Search Algorithm Implementation using NodeJS + MongoDB(or SQL)

Question

There is an application with search input that gives an opportunity to search for contacts by their information stored in database.

For example, I can type 0972133122 Alan and my search engine must return all contacts whose firstname is Alan & whose numbers match 0972133122 string.

Of course, I can just type Alan 0972, for instance, and there must be returned all possible contacts matching this pattern. The query order may be different, so that I can type 0972 Alan Smith, and if there are 2 contacts with Alan names and whose phone numbers start with 0972, then additional Smith clarification should return the only 1 contact.

I suggest built in phone applications for Android make use of this search algorithm:
So that my goal is to achieve similar result, but I do know how to do this. Here my code:
GraphQL query

query contacts {
  contacts(input: {
    contactQuery: "Alan Smith"
  }) {
    name {
      firstName
      lastName
    }
  }
}

NodeJS query to MongoDB

const conditions = {};
const expr = contactQuery
  .split(' ')
  .map((contact) => new RegExp(`${contact}`, 'i'))

conditions.$or = [
  { 'firstName': { $in: expr } },
  { 'lastName': { $in: expr } },
  { 'university': { $in: expr } },
  { emails: { $elemMatch: { email: { $in: expr } } } },
  { phones: { $elemMatch: { phone: { $in: expr } } } },
  { socials: { $elemMatch: { id: { $in: expr } } } },
]

const contacts = await this.contacts
  .find(conditions, undefined)
  .exec()

This works partly, but I receive unwanted documents from MongoDB:

{
  contacts: [
    {
      firstName: "Alan",
      lastName: "Smith",
      university: "KNTU",
      ...
    },
    {
      firstName: "Alan",
      lastName: "Alderson", // should not be returned
      university: "ZNU",
      ...
    },
    ...
  ]
}

But I need to get one contact that has strictly Alan firstname and Smith lastname. If it's impossible to do with MongoDB, -- please, provide me an example of SQL query. Any suggestions & solutions will be accepted!

Please, let me know if my question still is not clear.

Answer 1

Firstly, you need to separate out the numbers and words from the search text and then you can create a possible combination of it for an example:

1. FirstName: Alan, LastName: Smith
2. FirstName: Smith, LastName: Alan

Using regex you can do this easily and then you can use logical operators of mongodb to create your query like this

Approach 1

db.collection.find({
  $or: [
    {
      $and: [
        {
          firstName: {
            $regex: "Alan",
            $options: "i"
          }
        },
        {
          lastName: {
            $regex: "Smith",
            $options: "i"
          }
        }
      ]
    },
    {
      $and: [
        {
          firstName: {
            $regex: "Smith",
            $options: "i"
          }
        },
        {
          lastName: {
            $regex: "Alan",
            $options: "i"
          }
        }
      ]
    }
  ]
})

Here is the link to the playground for you to look at it in action Mongo Playground

Approach 2

Another way is where you concat all the searchable keys into one field and then use regex to filter it out like this

db.collection.aggregate([
  {
    $addFields: {
      text: {
        $concat: [
          "$firstName",
          " ",
          "$lastName",
          " ",
          "$university",
          " ",
          "$phones"
        ]
      }
    }
  },
  {
    $match: {
      text: {
        $regex: "(?=.*?(0972))(?=.*?(Alan))(?=.*?(Smith))",
        $options: "i"
      }
    }
  },
  {
    $project: {
      text: 0
    }
  }
])

Code to build the query:

let text = "0972 Alan Smith";
let parts = text.split(" ");
let query = parts.map(part => "(?=.*?("+part+"))").join("");

console.log(query);

But you need to check the performance implication of this approach or you can create a view and then query to view to make your query more cleaner

Here is the link to the playground for you to look at it in action Mongo Playground