skip to Main Content

I have a collection as

[
  {
    firstName: "John",
    middleName: "F",
    lastName: "Kennedy"
  }, 
  {
    firstName: "Barack",
    lastName: "Obama"
  }
]

I am trying to create a function that searches the collection by name. I need to concat the names before trying to find a match. I tried the following

User.aggregate([
  {
    "$project": {
      fullName: {
        "$concat": [
          "$firstName",
          " ",
          "$lastName"
        ]
      }
    }
  },
  {
    $match: {
      "fullName": {
        $regex: /[a-z\s]*oba[a-z\s]*/i
      }
    }
  }
])

It works for names without middle names. But I need this to work for names with middle names too. When I try to concat middleName, I get an error because the path middleName does not exist on all documents. I could not implement $cond and $exists operators properly make this work. Any kind of help is highly appreciated. Thanks!

2

Answers


  1. One option is using $ifNull:

    db.collection.aggregate([
      {$project: {
          fullName: {$concat: [
              "$firstName",
              " ",
              {$ifNull: [
                  {$concat: ["$middleName", " "]},
                  ""
              ]},
              "$lastName"
          ]}
      }}
    ])
    

    See how it works on the playground example

    Login or Signup to reply.
  2. As usual, @nimrod serok’s good answer directly addresses the question that was asked and should be the accepted one. But there is an additional consideration to keep in mind associated with the following text in the question that won’t fit in a comment:

    I am trying to create a function that searches the collection by name. I need to concat the names before trying to find a match.

    This approach is going to be very inefficient and probably won’t deliver the performance or scalability that you need long term. It requires the database to first generate this fullName field for all candidate documents (which is all documents in the collection if there are no other filter conditions) and then search through them to look for matches. I would strongly recommend avoiding this.

    In your particular situation, the answer is not simply storing a fullName field in the documents though. This is because your query is doing an unanchored and case-insensitive regex search which itself cannot use indexes efficiently. You may want to look into more efficient text searching options (such as those provided by Atlas Search) to support this functionality long term.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search