Search code examples
databasemongodbnosqllookupmongodb-lookup

How to apply $lookup in MongoDB, If the record on the second collection is deleted still give the response all the record of the first collection


How to apply $lookup in MongoDB, If the record on the second collection is deleted still give the response all the record of the first collection as shown in the given example

first collection

    A: [
         { _id : "a" ,  P : "dljslfsdjf" },
         { _id : "b" ,  P : "dljslfsdjf" },
         { _id : "c" ,  P : "dljslfsdjf" },
         { _id : "d" ,  P : "dljslfsdjf" }

]

Second collection

    B: [
     {A _id : "a" ,  Q : "dljslfsdjf" },
     {A_id : "b" ,   Q : "dljslfsdjf" },
     { A_id : "c" ,  Q : "dljslfsdjf" }

]

RESPONSE WANTED --

C: [
 { _id : "a" ,  P : "dljslfsdjf", BB:  {A _id : "a" ,  Q : "dljslfsdjf" } },
 { _id : "b" ,  P : "dljslfsdjf", BB : {A_id : "b" ,   Q : "dljslfsdjf" } },
 { _id : "c" ,  P : "dljslfsdjf", BB :  { A_id : "c" ,  Q : "dljslfsdjf" } },
 { _id : "d" ,  P : "dljslfsdjf", BB: {**anything that indicates record not found**} }

]

I Write a query --

 db.A.aggregate([
 {
     $match: {
         P : "dljslfsdjf"
     }
 },
 {
     $lookup : {
            from : "B",
            localField : "_id",
            foreignField : "A_id",
            as : "BB"
        }
 }
 {
        $unwind : "$BB"
 }

])

It returns only tree record, doesn't return the--

_id : "d" , P : "dljslfsdjf", BB: {**anything that indicate record not found**}


Solution

  • $unwind default behaviour is to remove null or empty array when deconstructing. If you need to keep empty array or null value {$unwind:{ path: "$BB",preserveNullAndEmptyArrays: true}}. Refer $unwind