There are three collections.

Leave

• _id
• user (ref to user model)
• status

LeaveApproval

• _id
• leave (ref to leave model)
• auditUser (ref to user model)
• role

User

• _id
• name

After performing lookup stage on Leave collection:

Leave.aggregate([
    $lookup: {
        from: 'leaveapprovals',
        localField: '_id',
        foreignField: 'leave',
        as: 'approverRecommender'
    }
])

{
    _id: ObjectId('6262851ab059b81fd9076532'),
    user: ObjectId('21ca9490192add08fa385aec'),
    status: "pending",
    approverRecommender: [
        {
            _id: ObjectId('6262851ab059b81fd907653d'),
            leave: ObjectId('6262851ab059b81fd9076532'),
            auditUser: ObjectId('61ca9490192add08fa385aeb'),
            role: "recommender"    
        },
        {
            _id: ObjectId('6262851ab059b81fd907653d'),
            leave: ObjectId('6262851ab059b81fd9076532'),
            auditUser: ObjectId('71ca9490192add08fa385aec'),
            role: "approver"    
        }
    ]
}

The result that I want to get is:

{
    _id: ObjectId('6262851ab059b81fd9076532'),
    user: ObjectId('21ca9490192add08fa385aec'),
    status: "pending",
    recommender: {
        _id: ObjectId('61ca9490192add08fa385aeb'),
        name: "Andrew"
    },
    approver: {
        _id: ObjectId('71ca9490192add08fa385aec'),
        name: "James"
    }
}

How can I transform it?

CodePudding user response：

1. $lookup - Need a nested $lookup. First $lookup with pipeline to join leave and leaveapprovals collection. While inner $lookup to join user collection and return auditUser and role fields.

2. $set -

   2.1. For approver field, filter approverRecommender array field for the approver role and take the first document via $first.

   2.2. For recommender field, filter approverRecommender array field for the recommender role and take the first document via $first.

3. $project - Decorate the output document. Use $first to return the first document for approver and recommender as from Result 2 both fields return as array.

db.leave.aggregate([
  {
    $lookup: {
      from: "leaveapprovals",
      let: {
        leaveId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$leaveId",
                "$leave"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "user",
            localField: "auditUser",
            foreignField: "_id",
            as: "auditUser"
          }
        },
        {
          $project: {
            auditUser: 1,
            role: 1
          }
        }
      ],
      as: "approverRecommender"
    }
  },
  {
    "$set": {
      approver: {
        "$first": {
          "$filter": {
            "input": "$approverRecommender",
            "cond": {
              $eq: [
                "$$this.role",
                "approver"
              ]
            }
          }
        }
      },
      recommender: {
        "$first": {
          "$filter": {
            "input": "$approverRecommender",
            "cond": {
              $eq: [
                "$$this.role",
                "recommender"
              ]
            }
          }
        }
      }
    }
  },
  {
    "$set": {
      approver: {
        "$first": {
          "$filter": {
            "input": "$approverRecommender",
            "cond": {
              $eq: [
                "$$this.role",
                "approver"
              ]
            }
          }
        }
      },
      recommender: {
        "$first": {
          "$filter": {
            "input": "$approverRecommender",
            "cond": {
              $eq: [
                "$$this.role",
                "recommender"
              ]
            }
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      user: 1,
      status: 1,
      "approver": {
        $first: "$approver.auditUser"
      },
      "recommender": {
        $first: "$recommender.auditUser"
      }
    }
  }
])

Sample Mongo Playground