0

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?

Steve
  • 406
  • 3
  • 11

1 Answers1

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.

Gibbs
  • 21,904
  • 13
  • 74
  • 138
  • It seems work! But it only gives me the `newField` and `_id` in the results. There's the possibility to have the entire object? – Steve May 10 '22 at 15:59
  • 1
    You can add it to the project stage i.e first stage like `firstName: 1, lastName: 1` or else replace `project` with `addFields` – Gibbs May 10 '22 at 16:08
  • 2
    maybe steve needs all this to a match? like [this](https://cmql.org/playmongo/?q=627a8f39ad87e9cf3a92942c) ? – Takis May 10 '22 at 16:14
  • Yes @Takis That's the simplified version and his expected answer. – Gibbs May 10 '22 at 16:15
  • Thanks a lot both takis and gibbs! Your answers are perfect – Steve May 10 '22 at 17:12