我有如下记录
[ { _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.
更多推荐
发布评论