英文:
How to pick documents id of which is not contained in array property of all other documents in collection?
问题
以下是您要翻译的内容：
我有一组文档，其形式如下：
<!-- 开始代码片段: js 隐藏: false 控制台: true Babel: false -->
<!-- 语言: lang-js -->
const categorySchema = new Schema<ICategory>({
title: { type: String, required: true },
children: [{ type: Types.ObjectId, ref: categoryModelName }],
});
<!-- 结束代码片段 -->
我想获取所有文档的ID，这些文档的ID不包含在集合中其他文档的children数组属性中。如果可能的话，该如何编写这样的查询？
英文:
I have collection of documents which has the following shape:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->
const categorySchema = new Schema<ICategory>({
title: { type: String, required: true },
children: [{ type: Types.ObjectId, ref: categoryModelName }],
});
<!-- end snippet -->
I want to get all documents id of which is not contained in children array property of all other documents in the collection. How to write such query, if possible?
答案1
得分: 2
你可以编写一个聚合管道：
- 进行自我查找并创建一个字段，该字段包含当前文档是其子文档的文档。
- 如果子文档数组为空，意味着没有文档是当前文档的子文档。因此，使用匹配来过滤掉这些文档。
db.categoryCollection.aggregate([
{ $lookup: { from: "categoryCollection", localField: "_id", foreignField: "children", as: "childOf" } },
{ $match: { childOf: { $size: 0 } } },
{ $project: { _id: 1 } }
])
英文:
you can write an aggregation pipeline
- self lookup and create a field which has documents which the current document is a child of
- If a child of array is empty it means there are no documents where the current document is a child. So filter out those using match
db.categoryCollection.aggregate([
{ $lookup: { from: "categoryCollection", localField: "_id", foreignField: "children", as: "childOf" } },
{ $match: { childOf: { $size: 0 } } },
{ $project: { _id: 1 } }
])
答案2
得分: 1
这是另一种方法来做，尽管如果集合非常大，"$group" 阶段可能会超过最大允许的内存。 评论位于聚合管道中。
db.collection.aggregate([
{
"$group": {
// 遍历每个文档
"_id": null,
// 将所有 _id 放入数组（已经是唯一的）
"ids": {"$push": "$_id"},
// 这将是一个数组的数组，不幸的是
"children": {"$addToSet": "$children"}
}
},
{
"$project": {
// 不需要 _id
"_id": 0,
// 将 theIds 分配为输出
"theIds": {
// 设置差异 = 在 ids 中但不在 children 中的元素
"$setDifference": [
"$ids",
{ // 将 children 折叠为单个平坦数组
"$reduce": {
"input": "$children",
"initialValue": [],
"in": {"$concatArrays": ["$$value", "$$this"]}
}
}
]
}
}
}
])
在 mongoplayground.net 上试试看。
英文:
Here's another way to do it, although if the collection is very large the "$group" stage may exceed the max allowable memory. Comments are in the aggregation pipeline.
db.collection.aggregate([
{
"$group": {
// go through each document
"_id": null,
// push all _id's (already unique)
"ids": {"$push": "$_id"},
// this will be an array of arrays unfortunately
"children": {"$addToSet": "$children"}
}
},
{
"$project": {
// don't need _id
"_id": 0,
// assign theIds as the output
"theIds": {
// set difference = what's in ids but not in children
"$setDifference": [
"$ids",
{ // collapse children to a single flat array
"$reduce": {
"input": "$children",
"initialValue": [],
"in": {"$concatArrays": ["$$value", "$$this"]}
}
}
]
}
}
}
])
Try it on [mongoplayground.net](https://mongoplayground.net/p/sC3FsSGk-UG "Click me!").
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论