英文:
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:
$groupand$addToSet- To get all$some_ids_of_bfrom all the documents inAcollection.$setwith$reduce- To create an array with all unique values of the IDs from theBcollection.$lookup- To fetch the documents from theBcollection, where the_idof the document is not present in the$b_idsarray.$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
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论