CODEWITHSUNDEEP
mongodbaggregation-framework
abdulbari
abdulbari

Reputation: 6232

How to use $lookup as INNER JOIN in MongoDB Aggregation?

I have used $lookup in my aggregate query. But as I am seeing it works as LEFT OUTER JOIN.

I want to fetch exact matches document(INNER JOIN) with $lookup.

Is there any way to get it done?

This is my inventory collection:

/* 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"
}

This is my orders collection

/* 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
}

And this is query

db.getCollection('inventory').aggregate([
   {
     $lookup:
       {
         from: "orders",
         localField: "sku",
         foreignField: "item",
         as: "inventory_docs"
       }
  }
])

In this query I am getting all the inventory's document matches with orders documents

Expected Result

/* 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
        }
    ]
}

Upvotes: 13

Views: 13223

Answers (1)

Neodan
Neodan

Reputation: 5252

Just add the $match pipeline stage which skips documents with empty inventory_docs field. There no other way to achieve that.

Query:

db.getCollection('inventory').aggregate([
    {
        $lookup: {
            from: "orders",
            localField: "sku",
            foreignField: "item",
            as: "inventory_docs"
        }
    },
    {
        $match: {
            "inventory_docs": {$ne: []}
        }
    }
])

Result:

{
    "_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
        }
    ]
}

Upvotes: 21

Related Questions