How to use a concatenation as the field name for the MongoDB $in operator?

Question

Assume I have a list of names and want to match documents which are not part of it:

{ firstname: { $not: { $in: ["Alice", "Bob"] } } }

But now I have to match against first name + last name (i.e. the given list is ["Alice Smith", "Bob Jones"]).

I know I can concatenate the two fields easily like this:

{ $concat: ["$firstname", " ", "$lastname"] }

But how do I use this new "field" in the initial query like I used firstname there? Obviously, I can't just replace the object key with this expression.

This answer is pretty close, but unfortunately it's missing the last piece of information on how exactly one uses that solution in the $in context. And since I think this is a general usage question but couldn't find anything about it (at least with the search terms I used), I'm opening this separate question.

Edit: If possible, I want to avoid using an aggregation. The query I'm looking for should be used as the filter parameter of the Node driver's deleteMany method.

Answer 1

You can use $expr, and for not equal to use $not outer side of $in,

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $not: {
          $in: [
            { $concat: ["$firstname", " ", "$lastname"] },
            ["Alice In wonderland", "Bob Marley"]
          ]
        }
      }
    }
  }
])

Playground