$lookup not working when in mongodb aggregrate-CodePudding

I have a query

fromDate = '2021-10-01'
toDate = '2021-10-10'
dataType = 'location'
typeId = '60dd6d303da6c17209d5ef68'
workerType = 'Employee'

start_date = datetime.strptime(str(fromDate) " 00:00:00", '%Y-%m-%d %H:%M:%S')
end_date = datetime.strptime(str(toDate) " 00:00:00", '%Y-%m-%d %H:%M:%S')

if dataType == 'location':
    found_location = prodLocationCollection.find_one({"_id":ObjectId(typeId)})
    match_filter = {'Date':{"$gte":start_date,"$lte":end_date},
                                    'Location':found_location["locationName"],
                                    'locationId':{'$exists':True},
                                'workerType':workerType
                                    }
elif dataType == 'user':
    match_filter = {'Date':{"$gte":start_date,"$lte":end_date},
                                    'employeeId':ObjectId(typeId),
                                    'locationId':{'$exists':True},
                                'workerType':workerType
                                    }
    


output = list(prodAttendanceCollection.aggregate([{'$match': match_filter},
                        {"$group":{'_id':{
                                'employeeId':'$employeeId',
                                'workerId':'$workerId',
                                'workerFullName':'$workerFullName'
                                },
                                'dailyPointsArray':{
                                    '$push':{
                                    'Date':'$Date',
                                    'createdAs':'$createdAs',
                                    'Points':'$shiftPoints'
                                    }
                                },
                                'total_shift_points':{'$sum':'$shiftPoints'},
                                'total_duration':{'$sum':'$duration'},
                                }
                        },
        { '$lookup':
            {
               'from': "users",
               'localField': "employeeId",
               'foreignField': "_id",
               'as': "userInfo"
            }
    }
                                                  
]))

This is giving output

[{'_id': {'employeeId': ObjectId('60dd78184524e6c116e22a44'),
   'workerId': '1008',
   'workerFullName': 'RADHIKA GOTHIVREKAR'},
  'dailyPointsArray': [{'Date': datetime.datetime(2021, 10, 1, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 2, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 3, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 4, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 5, 0, 0),
    'createdAs': 'IN-TIME PUNCHED',
    'Points': 0},
   {'Date': datetime.datetime(2021, 10, 6, 0, 0),
    'createdAs': 'FULL-TIME PUNCHED',
    'Points': 1},
   {'Date': datetime.datetime(2021, 10, 7, 0, 0),
    'createdAs': 'FULL-TIME PUNCHED',
    'Points': 1},
   {'Date': datetime.datetime(2021, 10, 8, 0, 0),
    'createdAs': 'FULL-TIME PUNCHED',
    'Points': 1},
   {'Date': datetime.datetime(2021, 10, 9, 0, 0),
    'createdAs': 'FULL-TIME PUNCHED',
    'Points': 1},
   {'Date': datetime.datetime(2021, 10, 10, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None}],
  'total_shift_points': 4,
  'total_duration': 0,
  'userInfo': []},
 {'_id': {'employeeId': ObjectId('60dd77e34524e6c116e1b27d'),
   'workerId': '1365',
   'workerFullName': 'HARISON NIKALJE'},
  'dailyPointsArray': [{'Date': datetime.datetime(2021, 10, 1, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 2, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 3, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 4, 0, 0),
    'createdAs': 'IN-TIME PUNCHED',
    'Points': 0},
   {'Date': datetime.datetime(2021, 10, 5, 0, 0),
    'createdAs': 'IN-TIME PUNCHED',
    'Points': 0},
   {'Date': datetime.datetime(2021, 10, 6, 0, 0),
    'createdAs': 'IN-TIME PUNCHED',
    'Points': 0},
   {'Date': datetime.datetime(2021, 10, 7, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 8, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 9, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None},
   {'Date': datetime.datetime(2021, 10, 10, 0, 0),
    'createdAs': 'ABSENT',
    'Points': None}],
  'total_shift_points': 0,
  'total_duration': 0,
  'userInfo': []}]

As you can see everything is working but the lookup part is returning [] empty array

This is sample data of attendance & user

ATTENDANCE

{
    "_id": {
        "$oid": "60dd7d723fcb2eb7df248af3"
    },
    "workerId": "2073",
    "workerFullName": "ARUN NAIR",
    "workerType": "Employee",
    "workerAadharCardNumber": "xxxxxxxxxx",
    "Date": {
        "$date": "2021-07-01T00:00:00.000Z"
    },
    "employeeId": {
        "$oid": "60dd77c14524e6c116e1692c"
    },
    "Location": "HEAD OFFICE 2",
    "sourceUnitType": null,
    "duration": null,
    "shiftPoints": null,
    "createdAs": "ABSENT",
    "ruleApplied": null,
    "detections": [],
    "locationId": {
        "$oid": "60dd6d303da6c17209d5ef68"
    },
    "workerFaceRegistered": true
}

USER

    {
    "_id": {
        "$oid": "60dd77c14524e6c116e1692c"
    },
    "workerFirstName": "ARUN",
    "workerSurname": "NAIR",
    "workerPhoneNumber": "xxxxxxxxxx",
    "workerId": "2073",
    "locationType": "HEAD OFFICE",
    "locationName": "HEAD OFFICE 2",
    "workerDesignation": "EXECUTIVE",
    "workerDepartment": "SALES",
    "workerAadharCardNumber": "xxxxxxxxxx",
    "workerType": "Employee",
    "workerEmail": "xxxxxxxxxxxx",
    "workerStatus": "REGULAR",
    "workerUsername": "2073",
    "workerPassword": "$2b$12$wBrDRYgPbRxrLG8N9Wxs2eDxGVFauZMraosrkePnYWWpZmPE1ljY2",
    "totalLoginAttemptLeft": 3,
    "lastLocationId": {
        "$oid": "60dd6d303da6c17209d5ef68"
    },
    "allowFencing": true,
    "workerFaceRegistered": true,
    "isActive": false
}

CodePudding user response：

After grouping the original fields are not longer present, so $employeeId does not exist, which won't match anything.

Since employeeId was moved to _id.employeeId in the group stage, in the lookup, use localField: "_id.employeeId"