MongoDB - 如何聚合深度嵌套数组

Question

我有以下 MongoDB 结构：

Division Collection:

{
  "_id": ObjectId("5b28cab902f28e18b863bd36"),
  "name": "Premier League",
  ...
  "teams": [
    ObjectId("5b28cab902f28e18b863bd01"),
    ObjectId("5b28cab902f28e18b863bd02"),
    ObjectId("5b28cab902f28e18b863bd03"),
    ...
  ]
  ...
},
...

Teams Collection:

{
  "_id": ObjectId("5b28cab902f28e18b863bd01"),
  "name": "Liverpool",
  ...
  "players": [
    ObjectId('5b23tmb902f28e18b863bd01'),
    ObjectId('5b23tmb902f28e18b863bd02'),
    ObjectId('5b23tmb902f28e18b863bd03'),
    ...
  ]
  ...
},
...

Players Collection:

{
  "_id": ObjectId("5b2b9a8bbda339352cc39ec1"),
  "name": "Mohamed Salah",
  "nationality": [
    ObjectId("5b23cn1902f28e18b863bd01"),
    ObjectId("5b23cn2902f28e18b863bd02"),
  ],
  ...
},
...

Countries Collection:

{
  "_id": ObjectId("5b23cn1902f28e18b863bd01"),
  "name": "England",
  ...
},
{
  "_id": ObjectId("5b23cn2902f28e18b863bd02"),
  "name": "Egypt",
  ...
},
...

如何使用 MongoDB 聚合（$lookup、$pipeline 等）获得如下结果：

{
  "divisions": [
    {
      "_id": ObjectId("5b28cab902f28e18b863bd36"),
      "name": "Premier League",
      ...
      "teams": [
        {
          "_id": ObjectId("5b28cab902f28e18b863bd01"),
          "name": "Liverpool",
          ...
          "players": [
            {
              "_id": ObjectId("5b23tmb902f28e18b863bd01"),
              "name": "Mohamed Salah",
              "nationality": [
                {
                  "_id": ObjectId("5b23cn2902f28e18b863bd02"),
                  "name": "Egypt",
                  ...
                },
                {
                  "_id": ObjectId("5b23cn1902f28e18b863bd01"),
                  "name": "England",
                  ...
                }
              ]
              ...
            },
            ...
          ]
        },
        ...
      ]
    },
    {
      "_id": ObjectId("5b28cab902f28e18b863bd37"),
      "name": "Championship",
      ...
    },
    ...
  ]
}

我设法进行一级合并:

db.divisions.aggregate([
  {
    $lookup: {
      from: 'teams',
      localField: 'teams',
      foreignField: '_id',
      as: 'teams'
    }
  },
])

然后我遇到了困难，所以我会很如果有人能帮助我解决这个问题，我将不胜感激。

Answer 1

您需要使用pipeline进行多级嵌套$lookup。

db.division.aggregate([
  {
    $lookup: {
      from: "teams",
      let: {
        teams: "$teams"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$teams"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "players",
            let: {
              players: "$players"
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $in: [
                      "$_id",
                      "$players"
                    ]
                  }
                }
              },
              {
                $lookup: {
                  from: "countries",
                  localField: "nationality",
                  foreignField: "_id",
                  as: "nationality"
                }
              }
            ],
            as: "players"
          }
        }
      ],
      as: "teams"
    }
  }
])

示例 Mongo Playground

Answer 2

也许有人会有用。还可以使用 populate 方法合并数据：

db.divisions.find(_id: division_id).populate(
  {
    path: 'teams',
    populate: {
      path: 'players',
      populate: {
        path: 'nationality'
      }
    }
  }
)