mongodb - Mongo $lookup 具有更多集合和空字段

Question

我有 4 个收藏:人物、公司、城市和国家。

人物集合:

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

公司、城市和国家集合只有 _id 和 name 列。
我想要一份包含公司、城市和国家名称的所有人的名单。
如果记录没有公司、城市或国家的 id，我想要一个空字段。

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} }
]);

此查询返回:

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

仅包含所有 3 个引用的记录与其他集合。
我怎样才能拥有所有人？

Answer 1

使用 $unwind 时，聚合管道中的空数据会丢失。将此 "preserveNullAndEmptyArrays": true 添加到您的 $unwind 部分。

修改后的查询

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 模式，因为它类似于 RDBMS 世界中的精确规范化表。 MongoDB 是一个 NoSQL 数据库，在这个给定的公司、城市和国家集合只有 _id 的场景中，我会选择将所有这些集合合并到一个集合中的模式。