英文:
Find MongoDB documents that are not contained across arrays
问题
MongoDB集合A包含具有数组的文档,其中包含集合B的一些文档ID:
集合A:
{
some_ids_of_b: ["id1", ...]
}
集合B:
{
_id: "id1"
},
{
_id: "id2"
},
...
如何查询集合B中所有不包含在A文档的some_ids_of_b
数组中的_id
的文档?
英文:
MongoDB Collection A contains documents with an array with some document ids of collection B:
Collection A:
{
some_ids_of_b: ["id1", ...]
}
Collection B:
{
_id: "id1"
},
{
_id: "id2"
},
...
How do I query all documents from B whose _ids
are NOT in contained in the some_ids_of_b
arrays of documents of A?
答案1
得分: 1
这是一种选项:
db.collectionB.aggregate([
{$lookup: {
from: "collectionA",
let: {my_id: "$_id"},
pipeline: [
{$match: {$and: [
{_id: collADocId},
{$expr: {$in: "$$my_id", "$some_ids_of_b"}}
]}},
{$project: {_id: 1}}
],
as: "some_ids_of_b"
}},
{$match: {"some_ids_of_b.0": {$exists: false}}},
{$unset: "some_ids_of_b"}
])
在playground示例上查看它的工作方式。
英文:
One option is:
db.collectionB.aggregate([
{$lookup: {
from: "collectionA",
let: {my_id: "$_id"},
pipeline: [
{$match: {$and: [
{_id: collADocId},
{$expr: {$in: ["$$my_id", "$some_ids_of_b"]}}
]}},
{$project: {_id: 1}}
],
as: "some_ids_of_b"
}},
{$match: {"some_ids_of_b.0": {$exists: false}}},
{$unset: "some_ids_of_b"}
])
See how it works on the playground example
答案2
得分: 1
从集合B到集合A的简单查找并筛选,只保留那些在其中没有找到匹配项的文档。
db.collb.aggregate([
{
"$lookup": {
"from": "colla",
"localField": "_id",
"foreignField": "someIdsOfB",
"as": "a"
}
},
{
$match: {
$expr: {
$eq: [{$size: "$a"}, 0]
}
}
}
])
英文:
Simple lookup from collection B to A and filter to keep only those documents where you don't find any matches.
db.collb.aggregate([
{
"$lookup": {
"from": "colla",
"localField": "_id",
"foreignField": "someIdsOfB",
"as": "a"
}
},
{
$match: {
$expr: {
$eq: [{$size: "$a"}, 0]
}
}
}
])
答案3
得分: 1
你可以使用聚合框架来完成:
$group
和$addToSet
- 从A
集合中的所有文档中获取所有$some_ids_of_b
的值。- 使用
$set
和$reduce
- 创建一个包含B
集合中所有唯一ID值的数组。 $lookup
- 从B
集合中获取那些在$b_ids
数组中不存在的文档,其中文档的_id
不在$b_ids
数组中。$project
- 以期望的输出格式投影数据。
db.A.aggregate([
{
"$group": {
"_id": null,
"b_ids": {
"$addToSet": "$some_ids_of_b"
}
}
},
{
"$set": {
b_ids: {
$reduce: {
input: "$b_ids",
initialValue: [],
in: {
$setUnion: [
"$$value",
"$$this"
]
}
}
}
}
},
{
"$lookup": {
from: "B",
let: {
b_ids: "$b_ids"
},
pipeline: [
{
"$match": {
"$expr": {
$ne: [
{
"$in": [
"$_id",
"$$b_ids"
]
},
true
]
}
}
}
],
as: "data"
}
},
{
"$project": {
data: 1,
_id: 0
}
}
])
英文:
You can do it with Aggregation Framework:
$group
and$addToSet
- To get all$some_ids_of_b
from all the documents inA
collection.$set
with$reduce
- To create an array with all unique values of the IDs from theB
collection.$lookup
- To fetch the documents from theB
collection, where the_id
of the document is not present in the$b_ids
array.$project
- To project data as expected output.
db.A.aggregate([
{
"$group": {
"_id": null,
"b_ids": {
"$addToSet": "$some_ids_of_b"
}
}
},
{
"$set": {
b_ids: {
$reduce: {
input: "$b_ids",
initialValue: [],
in: {
$setUnion: [
"$$value",
"$$this"
]
}
}
}
}
},
{
"$lookup": {
from: "B",
let: {
b_ids: "$b_ids"
},
pipeline: [
{
"$match": {
"$expr": {
$ne: [
{
"$in": [
"$_id",
"$$b_ids"
]
},
true
]
}
}
}
],
as: "data"
}
},
{
"$project": {
data: 1,
_id: 0
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论