mongodb - MongoDB查找查询:返回重复记录但具有唯一的现有ID
问题描述
我有一个收集文件说测试:
{id: 123,
lId: abc,
cnum: [{num: 112, type:R}]
},
{id: 234,
lId: abc,
cnum:[{ num: 112, type: R}]
},
{id: 345,
lId: cbd,
cnum: [{num: 112, type: R}]
},
{id: 456,
lId: efg,
cnum: [{num: 121, type:R}]
}
我希望查询返回具有重复 cnum 的 num 值但唯一 lId 的值。那就是它应该返回
id: 123,lId: abc, cnum.num: 112, id: 345,lId: cbd, cnum.num: 112
但目前它正在返回
id: 123,lId: abc,cnum.num: 112, id: 234, lId: abc, cnum.num: 112, id: 345,lId: cbd, cnum.num: 112
我当前的脚本也返回了重复的 lId。这是我的脚本:
var groupCnum = db.getCollection('test').aggregate([
{ $match: {"cnum.0": {$exists: true}}},
{ $unwind: "$cnum" },
{ $match: { "cnum.type": "R" } },
{ $group: { "_id": "$cnum.num", "count": { $sum: 1 } } },
{ $match: {"count": {"$gt": 1} } }
], {allowDiskUse: true}).map(record => record._id);
var duplicatedCnum = db.getCollection('test').aggregate([
{ $match: {"lId": {$nin: groupCnum}}},
{ $match: { "cnum.num": {$in: groupCnum} } },
{ $unwind: "$cnum" },
{ $match: { "cnum.type": "R" } },
{ $sort: {cnum: 1} },
{ $limit: 100}
], {allowDiskUse: true});
var fieldNames = ["id", "lId", "cnum.num"];
print(fieldNames.join(","));
谁能建议我错过了什么?
解决方案
如果它对某人有帮助,我可以通过以下查询获得所需的结果:
db.getCollection('test').aggregate([
{$match: {"cnum.0": { $exists: true }} },
{$unwind: "$cnum"},
{$match: { "cnum.type": "R"}},
{$group: {"_id": {"lId": "$lId", "cnum": "$cnum.num" } } },
{$group: {"_id": "$_id.cnum", "count": {$sum: 1}}},
{$match: {"count": {"$gt": 1}}
}])