英文:
Mongo lookup condition: not exist
问题
We have two collections.
One is the books collection with loads of data. Like:
{
'_id': {"$oid": "64ba6a416504f1f01d773faa"},
'isbn': 8765434567890,
'booktitle': 'some text',
}
{
'_id': {"$oid": "64ba6a416504f1f01d773faa"},
'isbn': 8765434566666,
'booktitle': 'other book',
}
The notes collection has documents with extra info about some of the documents in the base collection. Like:
{
'_id': {"$oid": "64ba6a416504f1f01d773faa"},
'isbn': 8765434567890,
'note': 'blabla',
}
So not all documents in books have a counterpart in notes. If there is, it's one-to-one.
What I try to find out is an aggregation that shows all of the books documents (with possibly additional {match} stuff), that specifically do not have a counterpart in the notes collection.
[
{'$match': {}},
{'$lookup':
{
'from': 'notes',
'localField': 'isbn',
'foreignField': 'isbn',
'as': 'related_note',
}
},
{'$project':
{
'isbn': '$isbn',
'title': '$title',
'note': '-- no notes about this title',
}
}
]
So not a left-join, not a right-join but a: NOT(left-join)
Anybody? Thanks.
英文:
We have two collections.
One is the books collection with loads of data. Like:
{
'_id' {"$oid": "64ba6a416504f1f01d773faa"},
'isbn': 8765434567890,
'booktitle': 'some text',
}
{
'_id' {"$oid": "64ba6a416504f1f01d773faa"},
'isbn': 8765434566666,
'booktitle': 'other book',
}
The notes collection has documents with extra info about some of the documents in the base collection. Like:
{
'_id' {"$oid": "64ba6a416504f1f01d773faa"},
'isbn': 8765434567890,
'note': 'blabla',
}
So not all documents in books have a counterpart in notes. If there is, it's one-to-one.
What I try to find out is an aggregation that shows all of the books documents (with possibly additinal {match} stuff), that specifically do not have a counterpart in the notes collection.
[
{'$match': {}},
{'$lookup':
{
'from': 'notes',
'localField': 'isbn',
'foreignField': 'isbn',
'as': 'related_note',
}
},
{'$project':
{
'isbn': '$isbn',
'title': '$title',
'note': '-- no notes about this title',
}
}
]
So not a left-join, not a right-join but a: NOT(left-join)
Anybody? Thanks.
答案1
得分: 3
以下是翻译好的部分：
"What I try to find out is an aggregation that shows all of the books documents (with possibly additinal {match} stuff), that specifically do not have a counterpart in the notes collection."
我试图查找的是一个聚合操作，显示所有的书籍文档（可能包括其他{match}的内容），这些文档在笔记集合中没有对应项。
"Would say that your result should be LEFT JOIN but excluding intersection."
可以说你的结果应该是LEFT JOIN，但要排除交集。
"By default, MongoDB $lookup performs as the LEFT JOIN. To exclude the documents which are under the intersection, you can filter with related_note: [] via the $match stage."
默认情况下，MongoDB的$lookup执行左连接操作。要排除在交集下的文档，可以通过$match阶段使用related_note: []进行过滤。
英文:
> What I try to find out is an aggregation that shows all of the books documents (with possibly additinal {match} stuff), that specifically do not have a counterpart in the notes collection.*
Would say that your result should be LEFT JOIN but excluding intersection.
Reference: SQL joins as Venn diagram
By default, MongoDB $lookup performs as the LEFT JOIN. To exclude the documents which are under the intersection, you can filter with related_note: [] via the $match stage.
db.books.aggregate([
//{
// "$match": {}
//},
{
"$lookup": {
"from": "notes",
"localField": "isbn",
"foreignField": "isbn",
"as": "related_note"
}
},
{
$match: {
related_note: []
}
},
{
"$project": {
"isbn": "$isbn",
"title": "$title",
"note": "-- no notes about this title",
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论