How to concat multiple fields in mongoose query?

Question

I am trying to find() all docs with LIKE 'query.name' in concatenated fields instead of using $or to search in separated ones.

SQL query would be:

SELECT * FROM table WHERE column.name + ' ' + column.surname LIKE query.name

This is the current code:

Model.find()
.and([
  {
    $or: [
      { name: new RegExp(req.query.name, 'i') },
      { surname: new RegExp(req.query.name, 'i') }
    ]
  },
  {
    $or: [
      { workPlace: new RegExp(req.query.place, 'i') },
      { location: new RegExp(req.query.place, 'i') }
    ]
  }
])

Answer 1

This can be achieved in a two stage aggregation query: The idea is to concatenate $concat the name and location fields as separate filter attributes in the first stage using $addFields and then search using $match and $regex in the next stage.

Example:

db.collection.aggregate([
  {
    $addFields: {
      nameFilter: {
        $concat: ["$name", " ", "$surname"],
      },
      locationFilter: {
        $concat: ["$workplace", " ", "$location"],
      },
    },
  },
  {
    $match: {
      nameFilter: {
        $regex: req.query.name,
        $options: "i",
      },
      locationFilter: {
        $regex: req.query.place,
        $options: "i",
      },
    },
  },
]);

mongo playground example link