如何在 MongoDB 聚合中使用 $lookup 作为 INNER JOIN? [英] How to use $lookup as INNER JOIN in MongoDB Aggregation?
本文介绍了如何在 MongoDB 聚合中使用 $lookup 作为 INNER JOIN?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在聚合查询中使用了 $lookup
.但正如我所见,它可以作为 LEFT OUTER JOIN
.
I have used $lookup
in my aggregate query.
But as I am seeing it works as LEFT OUTER JOIN
.
我想用 $lookup
获取完全匹配的文档(INNER JOIN).
I want to fetch exact matches document(INNER JOIN) with
$lookup
.
有什么办法可以解决吗?
Is there any way to get it done?
这是我的 inventory
收藏:
/* 1 */
{
"_id" : 1,
"sku" : "abc",
"description" : "product 1",
"instock" : 120
}
/* 2 */
{
"_id" : 2,
"sku" : "def",
"description" : "product 2",
"instock" : 80
}
/* 3 */
{
"_id" : 3,
"sku" : "ijk",
"description" : "product 3",
"instock" : 60
}
/* 4 */
{
"_id" : 4,
"sku" : "jkl",
"description" : "product 4",
"instock" : 70
}
/* 5 */
{
"_id" : 5,
"sku" : null,
"description" : "Incomplete"
}
这是我的orders
收藏
/* 1 */
{
"_id" : 1,
"item" : "abc",
"price" : 12,
"quantity" : 2
}
/* 2 */
{
"_id" : 2,
"item" : "jkl",
"price" : 20,
"quantity" : 1
}
/* 3 */
{
"_id" : 10,
"item" : "jklw",
"price" : 20,
"quantity" : 1
}
这是查询
db.getCollection('inventory').aggregate([
{
$lookup:
{
from: "orders",
localField: "sku",
foreignField: "item",
as: "inventory_docs"
}
}
])
在这个查询中,我得到所有 inventory's
文档与 orders
文档匹配
In this query I am getting all the inventory's
document matches with orders
documents
预期结果
/* 1 */
{
"_id" : 1,
"sku" : "abc",
"description" : "product 1",
"instock" : 120,
"inventory_docs" : [
{
"_id" : 1,
"item" : "abc",
"price" : 12,
"quantity" : 2
}
]
}
/* 2 */
{
"_id" : 4,
"sku" : "jkl",
"description" : "product 4",
"instock" : 70,
"inventory_docs" : [
{
"_id" : 2,
"item" : "jkl",
"price" : 20,
"quantity" : 1
}
]
}
推荐答案
只需添加 $match
管道阶段跳过具有空 inventory_docs
字段的文档.没有其他方法可以实现.
Just add the $match
pipeline stage which skips documents with empty inventory_docs
field. There no other way to achieve that.
查询:
db.getCollection('inventory').aggregate([
{
$lookup: {
from: "orders",
localField: "sku",
foreignField: "item",
as: "inventory_docs"
}
},
{
$match: {
"inventory_docs": {$ne: []}
}
}
])
结果:
{
"_id" : 1.0,
"sku" : "abc",
"description" : "product 1",
"instock" : 120.0,
"inventory_docs" : [
{
"_id" : 1.0,
"item" : "abc",
"price" : 12.0,
"quantity" : 2.0
}
]
}
{
"_id" : 4.0,
"sku" : "jkl",
"description" : "product 4",
"instock" : 70.0,
"inventory_docs" : [
{
"_id" : 2.0,
"item" : "jkl",
"price" : 20.0,
"quantity" : 1.0
}
]
}
这篇关于如何在 MongoDB 聚合中使用 $lookup 作为 INNER JOIN?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文