Filter on projection not working in mongodb

Question

I have done aggregation query on student table with activity table which gives the desired fields except one field i.e "ActivityForMediumSize". This field comes empty.

The code is

  $query = ["is_temp_deleted"=>0, "funRun" => "Yes"];

        $pipeline = array(
            array(
                '$match' => $query
            ),
            array(
                '$lookup' => array(
                    'from' => 'studentTbl',
                    'localField' => '_id',
                    'foreignField' => 'activity_details.activityId',
                    'as' => 'studentsOfActivities'
                )
            ),
           ['$lookup'=> [
               'from'=> 'studentTbl',
               'localField' => 'academic_year_Id',
               'foreignField' => 'studentsOfActivities.academic_year_Id',
               'as'=> 'Students'
            ]],
            ['$project' => [
                '_id' => 1.0,
                'activityName' => 1.0,
                'activityTime' => 1.0,
                'activityDate' => 1.0,
                'funRun' => 1.0,
                'Students' => 1.0,
                'studentsOfActivities' => 1.0,
                'studentsOfWeekgroup' => [
                    '$filter' => [
                        'input' => '$Students',
                        'as' => 'item',
                        'cond' => [
                            '$eq' => ['$$item.academic_year_Id', new MongoDB\BSON\ObjectID($this->id)],

                        ]
                    ]
                ],
                'ActivityForMediumSize' => [
                    '$filter' => [
                        'input' => '$studentsOfActivities',
                        'as' => 'item',
                        'cond' => [
                            '$eq' =>['$$item.activity_details.-1.shirtSize', 'M'],
                        ]
                    ]
                ],

                'StudentCountPerActivity'=>['$size'=>'$studentsOfActivities'],
               // 'MediumCount'=>['$size'=>'$countForMedium']
              ]], 
            );

    $cursor =  $this->db->activitiesTbl->aggregate($pipeline)->toArray();

I have checked in console that data in "studentsOfActivities" appears like

   0 =>
    "_id": ObjectId("5bf518378d03ec0b2400536f"),
    "ssn_Last_four": "0001",
    "first_name": "Thomas",
    "activity_details": [
   {
      "studentId": ObjectId("5bf518378d03ec0b2400536f"),
      "funrun": "Yes",
      "weekgroupId": ObjectId("5bf50bd48d03ec0b2400536d"),
      "activityId": ObjectId("5c10a0e08d03ec1834001c6d"),
      "attending": "Yes",
       "id": ObjectId("5c10e5ae3250041188005dde") 
   },
   {
   "studentId": ObjectId("5bf518378d03ec0b2400536f"),
   "funrun": "Yes",
   "weekgroupId": ObjectId("5bf50bd48d03ec0b2400536d"),
   "activityId": ObjectId("5c10a0e08d03ec1834001c6d"),
   "attending": "Yes",
   "shirtSize": "L",
   "id": ObjectId("5c13338032500409f4007cf3") 
    }
   ] 
   1=>
     "_id": ObjectId("5bf518378d03ec0b2400536e"),
     ....
     .... 

The embedded document "activity_details" contains student's all the activities and the last element of it is considered active one i.e why I have used -1 in the above code.

Now in projection "ActivityForMediumSize" I am trying find those students who have selected shirtSize: "M". The filter on projection "ActivityForMediumSize" is not working. Please help !!!

Note that there are some students where the last activity inside "activity_details" which does not contain "shirtSize" field.

Answer 1

I guess "is not working" means you have an empty array. It's because you cannot refer to the last element of an array just by "-1". Instead you need to use array operators to get the last element. For example it can be a combination of arrayElemAt and slice so the filter condition can look like :

$eq: [
    { $let: {
        vars: {
            last: { $arrayElemAt: [ 
                { $slice: [ '$$item. activity_details', -1 ] },
                0   
            ] }
        },
        in: '$$last.shirtSize'
    } },
    'M'
]

It's in js syntax but it should be trivial to translate to php.