I have the following MongoDB structure:

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",
  ...
},
...

How to get a result, which is below, using MongoDB aggregation ($lookup, $pipeline, etc):

{
  "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",
      ...
    },
    ...
  ]
}

I manage to make a first-level merge:

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

and then I ran into difficulties, so I would be very grateful if someone could help me with this issue.

CodePudding user response：

You need multi-level nested $lookup with pipeline.

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

Sample Mongo Playground

CodePudding user response：

Maybe someone will be useful. Data also can be merged using the populate method:

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