且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何在MongoDB聚合中将$ lookup用作INNER JOIN?

更新时间:2023-02-18 13:00:35

只需添加 $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
        }
    ]
}