Mongo $lookup with more collections and empty field

Question

I have 4 collections: people, company, city and nation.

People collection:

[
  {_id: 1, name: "Mario", surname: "Rossi", company: 2, city: 3, nation: 4},
  {_id: 2, name: "Steve", surname: "Red", company: 2},
  {_id: 3, name: "Alan", surname: "Joe", city: 3},
  {_id: 4, name: "Mark", surname: "Bill", nation: 2},
  {_id: 5, name: "John", surname: "Cena", company: 1, city: 3},
  {_id: 6, name: "Frank", surname: "Avrett", company: 2, nation: 5},
  {_id: 7, name: "Anne", surname: "Swander", nation: 3, city: 8}
]

The company, city and nation collections have only _id and name column.
I want a list of all people with the company, city and nation name.
If the record hasn't got the id company or city or nation I want an empty field.

var aggregate = this.aggregate([
{
    $lookup: {
        from: "company",
        localField: "company",
        foreignField: "_id",
        as: "x"
    }
  },
  {
     $lookup: {
        from: "city",
        localField: "city",
        foreignField: "_id",
        as: "y"
    }
},
  {
     $lookup: {
        from: "nation",
        localField: "nation",
        foreignField: "_id",
        as: "z"
    }
}
,{ $unwind: "$x" }
,{ $unwind: "$y" }
,{ $unwind: "$z" }
,{ $project : { _id:1, name:1, surname:1, nation:1, company:1, city:1, companyName: "$x.name", cityName: "$y.name", nationName: "$z.name" } }
,{ $sort: {name:1} }
]);

This query returns:

[{_id: 1, name: "Mario", surname: "Rossi", company: 2, city: 3, nation: 4, companyName: "Google", cityName: "New York", nationName: "United States" }]

Only the record with all the 3 references with other collections.
How can I have all the people?

Answer 1

Empty and null Data in the aggregation pipeline is lost when you are using $unwind. Add this "preserveNullAndEmptyArrays": true to your $unwind part.

Modified query

var aggregate=this.aggregate([
  {
    $lookup: {
      from: "company",
      localField: "company",
      foreignField: "_id",
      as: "x"
    }
  },
  {
    $lookup: {
      from: "city",
      localField: "city",
      foreignField: "_id",
      as: "y"
    }
  },
  {
    $lookup: {
      from: "nation",
      localField: "nation",
      foreignField: "_id",
      as: "z"
    }
  },
  {
    $unwind: {
      path: "$x",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $unwind: {
      path: "$y",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $unwind: {
      path: "$z",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $project: {
      _id: 1,
      name: 1,
      surname: 1,
      nation: 1,
      company: 1,
      city: 1,
      companyName: "$x.name",
      cityName: "$y.name",
      nationName: "$z.name"
    }
  },
  {
    $sort: {
      name: 1
    }
  }
]);

DB schema needs to be modified as it is resembling exact normalized tables in RDBMS world. MongoDB is a NoSQL DB, In this given scenario of company, city and nation collections are just having _id, I would go for a schema having all these collections merged into a single collection.