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.

score 1 · Answer 1 · answered Sep 17 '20 at 12:34

Indeed you are really close.

You have to use an aggregate. It's a sequence of "stages" where in each stage you can transform the data and pass the result to the next stage.

Here is a solution; Try it Here

with a $project i create a new field full_name by using your $concat

Then with a $match, I use your condition { firstname: { $not: { $in: ["Alice", "Bob"] } } } but I instead apply it to the newly created full_name

You can remove the $match in the mongoplayground and see what it does.

PS : there is a mongo operator $nin that does the combination of $not and $in

db.collection.aggregate([
  {
    "$project": {
      "full_name": {
        $concat: [
          "$firstname",
          " ",
          "$lastname"
        ]
      }
    }
  },
  {
    $match: {
      full_name: {
        $nin: [
          "Alice In wonderland",
          "Bob Marley"
        ]
      }
    }
  }
])

Do I really _have_ to use an aggregate? I'm using aggregations regularly and would've been able to construct such a simple pipeline myself, but in this case I want to avoid it. Sorry for not specifying this initially; I updated my question accordingly. — Christallkeks, Sep 18 '20 at 12:22

score 1 · Accepted Answer · answered Sep 17 '20 at 12:42

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

answered Sep 17 '20 at 12:42

turivishal

34,368
7
36
59

Thank you for the suggestion (and the much nicer last names :D), but is this also possible without an aggregation? I just realised I didn't specify this, so I updated my question. – Christallkeks Sep 18 '20 at 12:19
I am not sure but you can try this main part in your query filter, like find() look at this [playground](https://mongoplayground.net/p/3v2acoyEPdm) – turivishal Sep 18 '20 at 12:23
1

That works like a charm! Reading [the documentation of `$expr`](https://docs.mongodb.com/v4.2/reference/operator/query/expr/index.html) that makes perfect sense too, as the text "Allows the use of aggregation expressions within the query language" defines that operator's purpose as precisely what I needed. Thanks for pointing me in the right direction! – Christallkeks Sep 18 '20 at 14:46

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

2 Answers2