Mongodb: Populate based on condition

Question

I have some collections and I am trying to transform a log object into its details (using populate).

Companies (company with its users):

[
    {
        _id: "comp123",
        companyId: "compName123",
        users: [
            { user: "user111", status: "active"},
            { user: "user222", status: "active"},
        ]
    },
    {
        _id: "comp456",
        name: "compName456",
        users: [
            { user: "user333", status: "active"}
        ]
    },
    {
        _id: "comp789",
        name: "compName789",
        users: [
            { user: "user444", status: "inactive"}
        ]
    },
]

Users:

[
    {_id: "user111", firstName: "userName111"},
    {_id: "user222", firstName: "userName222"},
    {_id: "user333", firstName: "userName333"},
    {_id: "user444", firstName: "userName444"},
]

I am trying to transform log collection into data.

examples:

For the first object of the log:

{
   companyId: "comp123",
   actionDetails: [
      entities: [
         { id: "user111", entityType: "User"}
      ]
   ]
},

I want it to return:

{
    companyId: {_id: "comp123", name: "compName123"}, // taken from companies
    userId: { _id: "user111", firstName: "userName111"}, // taken from users
    // Does company=comp123 that has a user with user=user111 and status=active exist?
    isUserActiveInCompany: true
}

Another example of log:

{
    companyId: "comp456",
    actionDetails: [
        entities: [
            { id: "user444", entityType: "User"}
        ]
    ]
}

Output is:

{
    companyId: {_id: "comp456", name: "compName456"}, // taken from companies
    userId: { _id: "user444", firstName: "userName444"}, // taken from users
    isUserActiveInCompany: false // Does company=comp456 that has a user with user=user444 and status=active exist?
}

last important example of log:

{
    companyId: "comp789",
    actionDetails: [
        entities: [
            { id: "attr333", entityType: "Attribute"}
        ]
    ]
}

Output:

{
    companyId: {_id: "comp789", name: "compName789"}, // taken from companies
    userId: {}, // taken from users (entityType is Attribute so we ignore it)
    isUserActiveInCompany: null // entityType is Attribute so we ignore it
}

If there will be a log of comp789 with user444, isUserActiveInCompany should be false (cause the user is inactive in his company).

Currently, I do:

populate([
    {
        path: "actionDetails.entities.id",
        select: "id firstName",
    },
    {
        path: "companyId",
        select: "name",
    },
]

Any help appreciated!

Answer 1

Convert the below Aggregation Pipeline code to Mongoose Equivalent to get the output you desire.

db.log.aggregate([
  {
    '$match': {
      // <-- I highly recommend that you use a `$match` condition since there are 2 lookup operators in the aggregation which will significantly increase execution time.
    }
  },
  {
      '$lookup': {
      'from': 'Companies',
      'let': {'cId': '$companyId'},
      'pipeline': [
        {
          '$match': {
            '$expr': {
              '$eq': ['$_id', '$$cId']
            }
          }
        },
        {
          "$project": {
            'company': {
              "_id": "$_id",
              "companyName": "$companyId"
            },
            'users': {
              "$filter": {
                'input': "$users",
                'as': "usr",
                'cond': {
                  "$eq": ["$$usr.status", "active"],
                },
              },
            },
          }
        },
      ],
      'as': 'companyDetails'
    }
  },
  {
    '$unwind': {
      'path': "$actionDetails",
    }
  }, 
  {
    '$unwind': {
    'path': "$actionDetails.entities",
    }
  }, 
  {
    '$lookup': {
      'from': 'Users',
      'let': {"uId": "$actionDetails.entities.id"},
      'pipeline': [
        {
          "$match": {
            "$expr": {
              "$eq": ["$_id", "$$uId"],
            },
          },
        },
        {
          "$project": {
            "firstName": 1,
          },
        },
      ],
      'as': "userDetails",
    }
  },
  {
    '$project': {
      "companyId": {"$arrayElemAt": ["$companyDetails.company", 0]},
      "userId": {
        "_id": "$actionDetails.entities.id",
        "firstName": {"$arrayElemAt": ["$userDetails.firstName", 0]},
      },
      "isUserActiveInCompany": {
        "$switch": {
          "branches": [
            {
              'case': {
                "$ne": ["$actionDetails.entities.entityType", "User"]
              },
              'then': null,
            },
            {
              'case': {
                "$in": [
                  "$actionDetails.entities.id",
                  {
                    "$map": {
                      'input': {"$arrayElemAt": ["$companyDetails.users", 0]},
                      'as': "elem",
                      'in': "$$elem.user"
                    }
                  }
                ]
              },
              'then': true,
            },
          ],
          'default': false,
        }
      }
    }
  }
], {
  'allowDiskUse': true,
});

Let me know if you want a complete explanation and logic of each stage.