Consider these models:

users

{
    _id: 1,
    name: 'Some User 1'
},
{
    _id: 2,
    name: 'Some User 2'
}

teams

{
    _id: 1,
    users: [
        {
             user: 1, // (ref)
             type: "ADMIN"
        },
        {
             user: 2, // (ref)
             type: "MEMBER"
        },
    ]
}

vendors

{
    _id: 1,
    team: 1 // (ref)
}

groups

{
    _id: 1,
    vendor: 1 // (ref)
}

I'm trying to create a view that show all of the above documents without references into one single view. Here is an example:

{
    _id: 1,
    vendor: {
        _id: 1,
        team: {
            _id: 1,
            users: [
                {
                     user: {
                         _id: 1,
                         name: 'Some User 1'
                     },
                     type: "ADMIN"
                },
                {
                     user: {
                         _id: 2,
                         name: 'Some User 2'
                     },
                     type: "MEMBER"
                },
            ]
        }
    }
}

Here is my attempt creating the view starting from group collection:

[{
    $lookup: {
        from: 'vendors',
        localField: 'vendor',
        foreignField: '_id',
        as: 'vendor'
    }
}, 
{
    $unwind: '$vendor',
    preserveNullAndEmptyArrays: true
}, 
{
    $lookup: {
        from: 'teams',
        localField: 'vendor.team',
        foreignField: '_id',
        as: 'vendor.team'
    }
}, 
{
    $unwind: '$vendor.team',
    preserveNullAndEmptyArrays: true
}, 
{
    $lookup: {
        from: 'users',
        localField: 'vendor.team.users.user',
        foreignField: '_id',
        as: 'vendor.team.users'
    }
}, 
{
    $unwind: '$vendor.team.users.user',
    preserveNullAndEmptyArrays: true
}]

But I'm getting:

{
    _id: 1,
    vendor: {
        _id: 1,
        team: {
            _id: 1,
            users: [
                {
                    _id: 1,
                    name: 'Some User 1'
                },
                {
                    _id: 2,
                    name: 'Some User 2'
                },
            ]
        }
    }
}

I'm losing type at the users array. I've tried other ways, like unwinding the users array but got no luck on grouping it back into one document.

CodePudding user response：

Here the solution: Demo@mongoplayground

aggregation pipeline https://mongoplayground.net/p/7cY8zQhSxp-

db.groups.aggregate([
  {
    $lookup: {
      from: "vendors",
      localField: "vendor",
      foreignField: "_id",
      as: "vendor"
    }
  },
  {
    $unwind: "$vendor",
    
  },
  {
    $lookup: {
      from: "teams",
      localField: "vendor.team",
      foreignField: "_id",
      as: "vendor.team"
    }
  },
  {
    $unwind: "$vendor.team",
    
  },
  {
    $lookup: {
      from: "users",
      localField: "vendor.team.users.user",
      foreignField: "_id",
      as: "userlist"
    }
  },
  {
    "$set": {
      "vendor.team.users": {
        $map: {
          input: "$vendor.team.users",
          as: "user",
          in: {
            $mergeObjects: [
              "$$user",
              {
                user: {
                  $first: {
                    $filter: {
                      input: "$userlist",
                      cond: {
                        $eq: [
                          "$$user.user",
                          "$$this._id"
                        ]
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$unset": "userlist"
  }
])

Output :

//output
[
  {
    "_id": 1,
    "vendor": {
      "_id": 1,
      "team": {
        "_id": 1,
        "users": [
          {
            "type": "ADMIN",
            "user": {
              "_id": 1,
              "name": "Some User 1"
            }
          },
          {
            "type": "MEMBER",
            "user": {
              "_id": 2,
              "name": "Some User 2"
            }
          }
        ]
      }
    }
  }
]