Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...