Search Algorithm Implementation using NodeJS + MongoDB(or SQL)

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

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