在记录中使用$ unwind后，只匹配一条记录(Match with only one record after using $unwind on the record)

我有如下记录

[ { _id : 1, nm : 1, usr:[ {id : 1, ty : 'team'}, {id : 2, em : 'a'}, {id : 3, em : 'b'} ] }, { _id : 2, nm : 2, usr:[ {id : 2, em : 'a'}, {id : 3, em : 'b'} ] }, { _id : 3, nm : 3, usr:[ {id : 1, ty : 'team'}, {id : 3, em : 'b'} ] }, { _id : 4, nm : 4, usr:[ {id : 3, em : 'b'} ] } ] 当用userAndTeam = [1, 2]查询时，我希望计数为3 ，也就是说，如果记录具有usr.id as 1 or 2会获取这些记录。当用userAndTeam4 = [1, 4]查询时，我希望计数为2 ，也就是说，如果记录具有usr.id as 1 or 4会得到这些记录。

我尝试过使用$unwind ，因为第一种情况计数为4，因为$unwind为第一条记录创建了3条记录，下面的查询与其中2条记录匹配。

我试过的查询：

var userAndTeam = [1, 2] //User id and team id. Record should match one of the ids. var userAndTeam4 = [1, 4] [{ $unwind: '$usr' }, { $project:{ 'count-2' : {$cond: { if: {$in : ['$usr.id',userAndTeam]}, then: 1, else: 0 }}, 'count-4' : {$cond: { if: {$in : ['$usr.id',userAndTeam4]}, then: 1, else: 0 }} } }

]

输出：

预期用户ID为2 - 'count-2'：3

获得用户ID为2 - 'count-2'：4

预期用户ID为4 - 'count-4'：2

获得用户ID为4 - 'count-4'：2

有人能指导我解决这个问题并获得预期的计数吗？

I have records as below

[ { _id : 1, nm : 1, usr:[ {id : 1, ty : 'team'}, {id : 2, em : 'a'}, {id : 3, em : 'b'} ] }, { _id : 2, nm : 2, usr:[ {id : 2, em : 'a'}, {id : 3, em : 'b'} ] }, { _id : 3, nm : 3, usr:[ {id : 1, ty : 'team'}, {id : 3, em : 'b'} ] }, { _id : 4, nm : 4, usr:[ {id : 3, em : 'b'} ] } ] I want the count to be 3 when querying with userAndTeam = [1, 2], i.e. if a record has usr.id as 1 or 2 get those records. I want the count to be 2 when querying with userAndTeam4 = [1, 4], i.e. if a record has usr.id as 1 or 4 get those records.

I have tried using $unwind, which made the count to 4 for the first case, as $unwind created 3 records for the first record and below query matches with 2 of those records.

The query I have tried:

var userAndTeam = [1, 2] //User id and team id. Record should match one of the ids. var userAndTeam4 = [1, 4] [{ $unwind: '$usr' }, { $project:{ 'count-2' : {$cond: { if: {$in : ['$usr.id',userAndTeam]}, then: 1, else: 0 }}, 'count-4' : {$cond: { if: {$in : ['$usr.id',userAndTeam4]}, then: 1, else: 0 }} } }

]

Output:

Expected for user with id 2 - 'count-2' : 3

Got for user with id 2 - 'count-2' : 4

Expected for user with id 4 - 'count-4' : 2

Got for user with id 4 - 'count-4' : 2

Can someone guide me to solve this problem and to get the expected count?

最满意答案

你可以尝试下面的聚合：

db.col.aggregate([ { $unwind: "$usr" }, { $project:{ "userId": "$usr.id", "count-2": { $cond: { if: { $in: [ "$usr.id", userAndTeam ] }, then: 1, else: 0 } }, "count-4": { $cond: { if: { $in: [ "$usr.id", userAndTeam4] }, then: 1, else: 0 } } } }, { $group: { _id: "$_id", "count-2": { $max: "$count-2" }, "count-4": { $max: "$count-4" } } }, { $group: { _id: null, "count-2": { $sum: "$count-2" }, "count-4": { $sum: "$count-4" } } } ])

你在代码中缺少的是你想每个_id都有0或1 ，所以你需要通过_id进行分组，每个组的$max为0 ，如果任何元素与你的数组匹配，则没有匹配1 。

You can try below aggregation:

db.col.aggregate([ { $unwind: "$usr" }, { $project:{ "userId": "$usr.id", "count-2": { $cond: { if: { $in: [ "$usr.id", userAndTeam ] }, then: 1, else: 0 } }, "count-4": { $cond: { if: { $in: [ "$usr.id", userAndTeam4] }, then: 1, else: 0 } } } }, { $group: { _id: "$_id", "count-2": { $max: "$count-2" }, "count-4": { $max: "$count-4" } } }, { $group: { _id: null, "count-2": { $sum: "$count-2" }, "count-4": { $sum: "$count-4" } } } ])

What you're missing in your code is that you want to either have 0 or 1 per each _id so you need to group by _id taking $max value of each group which is 0 if none match of 1 if any element matches your array.

更多推荐