如何在MongoDB聚合中将$ lookup用作INNER JOIN? [英] How to use $lookup as INNER JOIN in MongoDB Aggregation?

问题描述

我在汇总查询中使用了$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"
       }
  }
])

在此查询中，我得到所有与orders文档匹配的inventory's文档

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屋！