Search string contained between two fields in MongoDB

Question

I'm looking forward to a solution very similar to the one proposed in this question: Select records matching concat value of two fields in mongodb

I have two fields: firstName and lastName, and I want to search a string that can be contained in this two fields. In that question I linked before I found this:

db.coll.find({$expr:{$eq:["MY QUERY STRING", {$concat:["$firstName", "$lastName"]}]}})

But this expression check for the exact match. I would like something like Sql: %like% instead of $eq`

So if I have a record like:

{
    "firstName": "Mario",
    "lastName": "Roberto Rossi"
}

I want to find that with a query string: Mario Roberto. How can I change my query to solve this problem?

Answer 1

db.collection.aggregate([
  {
    $project: {
      newField: {
        $concat: [
          "$firstName",
          " ",
          "$lastName"
        ]
      }
    }
  },
  {
    $addFields: {
      m: {
        "$regexMatch": {
          "input": "$newField",
          "regex": "mario robert",
          "options": "i"
        }
      }
    }
  },
  {
    "$match": {
      m: true
    }
  }
])

Playground

You can simplify this. You can change regex as per your use-case.

You can check for atlas search if you are looking for search functionality in mongo.