在MongoDB中的数组中查找重复的值，但它可能存在于对象外部

Question

{
"_id" : ObjectId("15672"),
"userName" : "4567",
"library" : [ 
{
"serialNumber" : "Book_1"
}, 
{
"serialNumber" : "Book_2"
}, 
{
"serialNumber" : "Book_4"
}
]
},
{
"_id" : ObjectId("123456"),
"userName" : "123",
"library" : [ 
{
"serialNumber" : "Book_2"
}
]
},
{
"_id" : ObjectId("1835242"),
"userName" : "13526",
"library" : [ 
{
"serialNumber" : "Book_7"
}, 
{
"serialNumber" : "Book_6"
}, 
{
"serialNumber" : "Book_5"
}, 
{
"serialNumber" : "Book_4"
}, 
{
"serialNumber" : "Book_3"
}, 
{
"serialNumber" : "Book_5"
}
]
}

我想要一个查询，它将为我提供serialNumber值重复的用户名。一个库中的序列号值可以存在于其他用户名库中，但不应该存在于一个特定的用户名库中

Answer 1

尝试此查询：

db.collection.aggregate([
/** First match stage is optional if all of your docs are of type array & not empty */
{ $match: { $expr: { $and: [{ $eq: [{ $type: "$library" }, "array"] }, { $ne: ["$library", []] }] } } },
/** Add a new field allUnique to each doc, will be false where if elements in library have duplicates */
{
$addFields: {
allUnique: {
$eq: [
{
$size:
{
$reduce: {
input: "$library.serialNumber",
initialValue: [], // start with empty array
/** iterate over serialNumber's array from library & push current value if it's not there in array, at the end reduce would produce an array with uniques */
in: { $cond: [{ $in: ["$$this", "$$value"] }, [], { $concatArrays: [["$$this"], "$$value"] }] }
}
}
},
{
$size: "$library"
}
]
}
}
},
/** get docs where allUnique: false */
{
$match: {
allUnique: false
}
},
/** Project only needed fields & remove _id which is bydefault projected */
{
$project: {
userName: 1,
_id: 0
}
}
])

另一种选择可以通过$unwind来实现，但这在庞大的数据集上并不可取，因为它会爆炸您的集合。

测试：MongoDB Playground

或者根据@Dennis在这个链接中的回答，从数组中复制条目，你可以尝试如下：

db.collection.aggregate([
{
$match: {
$expr: {
$and: [
{
$eq: [
{
$type: "$library"
},
"array"
]
},
{
$ne: [
"$library",
[]
]
}
]
}
}
},
{
$addFields: {
allUnique: {
$eq: [
{
$size: {
"$setUnion": [
"$library.serialNumber",
[]
]
}
},
{
$size: "$library"
}
]
}
}
},
{
$match: {
allUnique: false
}
},
{
$project: {
userName: 1,
_id: 0
}
}
])

测试：MongoDB Playground