Mongodb $unwind removes objects. if $lookup doesn't have reference

Question

If country doesn't have reference states and cities. $unwind removes country name from the collections.

Expected Output will be Mongodb should return country name even if the country doesn't any states and cities reference.

Country Collection:

[
  {
    "_id": "5d052c76df076d23a48d4a3b",
    "name": "India"
  },
  {
    "_id": "5d052c76df076d23a48d4b07",
    "name": "Indonesia"
  },
  {
    "_id": "5d052c76df076d23a48d22f4",
    "name": "Iran"
  }
]

State Collection:

   [
      {
        "_id": "5d2236c37ed1112b3cc41397",
        "name": "Andaman and Nicobar Islands",
        "countryId": "5d052c76df076d23a48d4a3b"
      },
      {
        "_id": "5d2236c37ed1112b3cc41398",
        "name": "Andhra Pradesh",
        "countryId": "5d052c76df076d23a48d4a3b"
      }
    ]

City Collection:

[
   {
     "name": "Port Blair",
     "stateId": "5d2236c37ed1112b3cc41397"
   },
   {
     "name": "Adoni",
     "stateId": "5d2236c37ed1112b3cc41398"
   }

]

Query:

Country.aggregate([
    {
        $lookup:{
            from: 'states',
            localField:'_id',
            foreignField:'countryId',
            as:'states'
        }
    },
    {
        $unwind: {
            path: "$states"
        }
    },
    {
        $lookup:{
            from: 'cities',
            localField:'states._id',
            foreignField:'stateId',
            as:'states.cities'
        }
    },
    {
        $group: {
            _id: {
                _id: '$_id',
                name: '$name'
            },
            states: {
                $push: '$states'
            }
        }
    },
    {
        $project: {
            _id: '$_id._id',
            name: '$_id.name',
            states: 1
        }
    }
])

Output:

[
   {
      "_id":"5d052c76df076d23a48d4a3b",
      "name":"India",
      "states":[
         {
            "_id":"5d2236c37ed1112b3cc41397",
            "name":"Andaman and Nicobar Islands",
            "countryId":"5d052c76df076d23a48d4a3b",
            "cities":[
               {
                  "name":"Port Blair",
                  "stateId":"5d2236c37ed1112b3cc41397"
               }
            ]
         },
         {
            "_id":"5d2236c37ed1112b3cc41398",
            "name":"Andhra Pradesh",
            "countryId":"5d052c76df076d23a48d4a3b",
            "cities":[
               {
                  "name":"Adoni",
                  "stateId":"5d2236c37ed1112b3cc41398"
               }
            ]
         }
      ]
   }
]

Expected Output:

[
   {
      "_id":"5d052c76df076d23a48d4a3b",
      "name":"India",
      "states":[
         {
            "_id":"5d2236c37ed1112b3cc41397",
            "name":"Andaman and Nicobar Islands",
            "countryId":"5d052c76df076d23a48d4a3b",
            "cities":[
               {
                  "name":"Port Blair",
                  "stateId":"5d2236c37ed1112b3cc41397"
               }
            ]
         },
         {
            "_id":"5d2236c37ed1112b3cc41398",
            "name":"Andhra Pradesh",
            "countryId":"5d052c76df076d23a48d4a3b",
            "cities":[
               {
                  "name":"Adoni",
                  "stateId":"5d2236c37ed1112b3cc41398"
               }
            ]
         }
      ]
   },
   {
      "_id":"5d052c76df076d23a48d4b07",
      "name":"Indonesia",
      "states":[

      ]
   },
   {
      "_id":"5d052c76df076d23a48d22f4",
      "name":"Iran",
      "states":[

      ]
   }
]

score 2 · Accepted Answer · answered Jul 24 '19 at 21:32

just add " preserveNullAndEmptyArrays: true " to $unwind

Country.aggregate([
    {
        $lookup:{
            from: 'states',
            localField:'_id',
            foreignField:'countryId',
            as:'states'
        }
    },
    {
        $unwind: {
            path: "$states",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $lookup:{
            from: 'cities',
            localField:'states._id',
            foreignField:'stateId',
            as:'states.cities'
        }
    },
    {
        $group: {
            _id: {
                _id: '$_id',
                name: '$name'
            },
            states: {
                $push: '$states'
            }
        }
    },
    {
        $project: {
            _id: '$_id._id',
            name: '$_id.name',
            states: 1
        }
    }
])

output

[
    {
        "states" : [ 
            {
                "cities" : []
            }
        ],
        "_id" : "5d052c76df076d23a48d22f4",
        "name" : "Iran"
    },
    {
        "states" : [ 
            {
                "cities" : []
            }
        ],
        "_id" : "5d052c76df076d23a48d4b07",
        "name" : "Indonesia"
    },
    {
        "states" : [ 
            {
                "_id" : "5d2236c37ed1112b3cc41397",
                "name" : "Andaman and Nicobar Islands",
                "countryId" : "5d052c76df076d23a48d4a3b",
                "cities" : [ 
                    {
                        "_id" : ObjectId("5d38ccb6f9c5fa48bf099027"),
                        "name" : "Port Blair",
                        "stateId" : "5d2236c37ed1112b3cc41397"
                    }
                ]
            }, 
            {
                "_id" : "5d2236c37ed1112b3cc41398",
                "name" : "Andhra Pradesh",
                "countryId" : "5d052c76df076d23a48d4a3b",
                "cities" : [ 
                    {
                        "_id" : ObjectId("5d38ccbcf9c5fa48bf09902a"),
                        "name" : "Adoni",
                        "stateId" : "5d2236c37ed1112b3cc41398"
                    }
                ]
            }
        ],
        "_id" : "5d052c76df076d23a48d4a3b",
        "name" : "India"
    }
]

Mongodb $unwind removes objects. if $lookup doesn't have reference

1 Answers1