Filter array using the $in operator in the $projec

Question

Right now, it's not possible to use the $in operator in the $filter array aggregation operator.

Let's say this is the document schema:

{
    _id: 1,
    users: [
        {
            _id: 'a',
            accounts: ['x', 'y', 'z']
        },
        {
            _id: 'b',
            accounts: ['j','k','l']
        }
    ]
}

I want, using aggregate, to get the documents with filtered array of users based on the contents of the accounts array.

IF the $in would work with the $filter operator, I would expect it to look like this:

db.test.aggregate([
    {
        $project: {
            'filtered_users': {
                $filter: {
                    input: '$users',
                    as: 'user',
                    cond: {
                        $in: ['$$user.accounts', ['x']]
                    }
                }
            }
        }
    }
])

and return in the filtered_users only the first user since x is in his account.

But, as I said, this doesn't work and I get the error:

"invalid operator '$in'"

because it isn't supported in the $filter operator.

Now I know I can do it with $unwind and using regular $match operators, but then it will be much longer (and uglier) aggregation with the need of using $group to set the results back as an array - I don't want this

My question is, if there is some other way to manipulate the $filter operator to get my desired results.

Answer 1

Since $in is not supported in aggregate operation for array, the alternative would be for you to use $setIsSubset. For more information on this you can refer this link. The aggregate query would now look like

db.test.aggregate([
{
    $project: {
        'filtered_users': {
            $filter: {
                input: '$users',
                as: 'user',
                cond: {
                   $setIsSubset: [['x'], '$$user.accounts']           
                }
            }
        }
    }
}])

This query will return only elements which have [x] as a subset of the array in user.accounts.

Answer 2

Starting From MongoDB 3.4, you can use the $in aggregation operator in the $project stage

db.collection.aggregate([     
    {         
        "$project": {             
            "filtered_users": {                 
                "$filter": {                     
                    "input": "$users",                     
                    "as": "user",                     
                    "cond": { "$in": [ "x", "$$user.accounts" ] }                     
                }  
            }         
        }     
    } 
])