Filter array using the $in operator in the $project stage

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.

score 16 · Accepted Answer · edited Sep 12 '16 at 07:20

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.

score 12 · Answer 2 · answered Mar 10 '17 at 16:45

12

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" ] }                     
                }  
            }         
        }     
    } 
])

answered Mar 10 '17 at 16:45

styvane

49,879
15
119
132

The right format would be something like this: "cond": { "$in": [ "$$user.accounts", ["x"] ] } – Eric Feb 19 '18 at 11:37
1

@Eric I don't think so. OP's query is wrong. What OPs wants is *return in the filtered_users only the first user since **x** is in his account*. You missed that part in the question. – styvane Feb 19 '18 at 20:14

Filter array using the $in operator in the $project stage

2 Answers2

Linked