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': { ... },
},
]);
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",
","
]
}
]
}
}
}
])
@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))
or
{ $map: { input: { "$split": ["136618,136620,136622", ","] }, in: { $toInt: "$$this" } } }