CODEWITHSUNDEEP
sqlmongodb
Krishna
Krishna

Reputation: 549

How to make a LEFT JOIN using $lookup MongoDB

I created the below view. I want to get all customers on orderheader with addresstype billing if not I still want the orderheader document. I think match stage is eliminating few documents as there is no address type billing for few documents.

I mean its acting like INNER JOIN, instead I want LEFT JOIN. trying hard but all I'm getting is INNER JOIN.

    db.createView(
       "VWOrderHeader",
       "OrderHeader",
       [
        { 
          $lookup:{  
             from:"AddressDoc",
             as: "Address",
             localField: "CustomerNo",
             foreignField: "CustomerNo"
          }
        },
        {
            $match:{ "Address.AddressType":/Billing/i }
        },
        {
            $project:{
                _id:0,
                CustomerNo :"$CustomerNo",
                ContactName:{ $ifNull:[{$arrayElemAt: [ "$Address.ContactName",0 ]},""]}
            }
        }
       ]
)}

Upvotes: 5

Views: 3684

Answers (1)

Krishna
Krishna

Reputation: 549

Re wrote the query to be 

 db.createView(
       "VWOrderHeader",
       "OrderHeader",
       [
        { 
          $lookup:{  
             from:"AddressDoc",
             as: "Address",
             localField: "CustomerNo",
             foreignField: "CustomerNo"
          }
        },
    { $unwind: { path: "$Address", preserveNullAndEmptyArrays: true } },
    {
        $match:{ $or:[ {"Address.AddressType":/billing/i},{"Address.AddressType":""},{"Address.AddressType":null}] }
    },
        {
            $project:{
                _id:0,
                CustomerNo :"$CustomerNo",
                ContactName:{ $ifNull:[ "$Address.ContactName",""]}
            }
        }
       ]
)}

Upvotes: 6

Related Questions