Combine $in with $split in mongo-CodePudding

I want to combine $in with $split like in the following example, but it fails saying "$in needs an array". I understand the output of $split is an array so I don't know why it fails. Do you know how to solve it or another way to do it? Thanks

db.mydoc.aggregate([
    {
        '$match': {
            'myid': {
                '$in': {
                    '$split': [
                        '136618,136620,136622',
                        ',',
                    ],
                },
            },
        },
    },
    {
        '$project': { ... },
    },
]);

CodePudding user response：

This "fail" is the expected behavior, let's understand why.

We must first take a look at the $match behavior as specified in the docs:

$match takes a document that specifies the query conditions. The query syntax is identical to the read operation query syntax; i.e. $match does not accept raw aggregation expressions. Instead, use a $expr query expression to include aggregation expression in $match.

This means when you use $match it uses the query language by default, now the "issue" comes from the difference between the two $in operators the query $in operator (which is being used) and the aggregation $in operator ( which you assume is being used ).

It is true that $split resolves to an array. but $split is also an aggregation operator, now I think this case should throw an error but for some reason as you mentioned this behavior just resolves with no results. the aggregation $in operator however , does accept raw aggregation expressions.

This means all you have to do is convert your $match query to use $expr so you can use the aggregation version of $in within the match, like so:

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        $in: [
          "$myid",
          {
            "$split": [
              "136618,136620,136622",
              ","
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground

CodePudding user response：

@Tom Slabbaert gave a very comprehensive and good answer. Just for sake of completeness, an alternative solution (if you work with Javascript/Mongo shell) is this one:

db.mydoc.aggregate([
    {
        '$match': {
            'myid': { '$in': '136618,136620,136622'.split(',') }
        }
    },
    {
        '$project': { ... },
    },
]);

Be aware either solutions create an array of strings, i.e. [ "136618", "136620", "136622" ]. It does not match if your collection has numeric values, e.g. { myid: 136618 }

You may use

'136618,136620,136622'.split(',').map(x => NumberInt(x))

{ $map: { input: { "$split": ["136618,136620,136622", ","] }, in: { $toInt: "$$this" } } }