fetching embedded documents on search criteria of another embedded document

Question

I have a collection HostelTbl which has many documents one of the document is like

    {
    "_id": ObjectId("5ae69fb4d2ccda0e70005551"),
    "Name": "Hostel-MGL-02",
     ...
    "RoomsDetails": [
     {
       "RoomId": "9a21e427-16fd-7a9e-bc16-537dc55093bb",
       "FloorNumber": "1",
       "RoomNumber": "101",
        ...
     },
     {
       "RoomId": "050c65ab-355e-9bec-00a0-2999e75bcdc4",
       "FloorNumber": "1",
       "RoomNumber": "104",
       ...
     },
     {
       "RoomId": "f197c635-a6fb-b4d6-9cf5-ed53177edd47",
       "FloorNumber": "2",
       "RoomNumber": "109",
       ...
     },
     ...
     {
       ...
     }
    ],
   "AllotmentsDetails": [
   {
      "AllotmentId": "5eb05e1d-2690-afad-747a-ef29450ae3f3",
      "StudentId": ObjectId("5ab8d5d4ff24ae120400085e"),
      "RoomId": "9a21e427-16fd-7a9e-bc16-537dc55093bb",
      "FromDate": ISODate("2018-06-12T22:00:00.0Z"),
      "ToDate": ISODate("2018-07-04T22:00:00.0Z"),
      ... 
     },
     {
      "AllotmentId": "40d73d84-0f94-48d8-823c-2255fd2ae0ed",
      "StudentId": ObjectId("5ab8d5bcff24ae120400085d"),
      "RoomId": "050c65ab-355e-9bec-00a0-2999e75bcdc4",
      "FromDate": ISODate("2018-01-21T22:00:00.0Z"),
      "ToDate": ISODate("2018-02-06T22:00:00.0Z"),
      ...
       },
       {
      "AllotmentId": "40d73564-0f94-48d8-823c-2255fd2440428",
      "StudentId": ObjectId("5ab8d5bc4624ae1204000855),
      "RoomId": "f197c635-a6fb-b4d6-9cf5-ed53177edd47",
      "FromDate": ISODate("2018-03-21T22:00:00.0Z"),
      "ToDate": ISODate("2018-05-04T22:00:00.0Z"),
      ...
       } 
     ] 
  }

I have two embedded documents inside one documents. One is storing rooms information and another storing rooms allotments to a student information. Now I want to fetch those available rooms from RoomsDetails if their allotments detail's "ToDate" field contains the dates in past from today inside AllotmentsDetails Embedded document. Like in the above case the rooms with ids "050c65ab-355e-9bec-00a0-2999e75bcdc4", f197c635-a6fb-b4d6-9cf5-ed53177edd47 contains ToDate dates in the past inside AllotmentsDetails embedded documents. It should fetch those two rooms only.

I am not able to find such queries. Please help!!!

I am trying to tweak the below lines of code

public function fetchAllAvailableRooms() 
{
   $cursor = $this->collection->aggregate(array(
             array(
                   '$match' => array(
                                "_id" => new MongoDB\BSON\ObjectID($this->id)
                                )
                  ),
             array(
                  '$project' => array(
                  'RoomsDetails' => array(
                  '$filter' => array(
                  'input' => '$AllotmentsDetails',
                  'as' => 'allot',
                  'cond' => array(
                  '$lt' => array('$$allot.FromDate', $this->Today)
                   )
                  )
                 ),
               'Name' => 1        
                )
              )
            ))->toArray();

    return $cursor;
   } 

Answer 1

You can use the $map and $filter aggregation operators for that.

• $map - for AllotmentsDetails transformation into final list of the room ids

• $filter - for RoomsDetails filtering by the final list of the room ids

Example:

public function fetchAllAvailableRooms() 
{
    $cursor = $this->collection->aggregate([
        ['$match' => ["_id" => new MongoDB\BSON\ObjectID($this->id)]],
        [
            '$addFields' => [
                'filteredIds' => [
                    '$map' => [
                        'input' => '$AllotmentsDetails',
                        'as' => 'item',
                        'in' => [
                            '$cond' => [
                                ['$lt' => ['$$item.ToDate', $this->Today]],
                                '$$item.RoomId',
                                false
                            ]
                        ]
                    ]
                ]
            ]
        ],
        [
            '$project' => [
                'RoomsDetails' => [
                    '$filter' => [
                        'input' => '$RoomsDetails',
                        'as' => 'item',
                        'cond' => ['$in' => ['$$item.RoomId', '$filteredIds']]
                    ]
                ]
            ]
        ]
    ])->toArray();

    return $cursor;
} 

/* Result example
{
    "_id" : ObjectId("5ae69fb4d2ccda0e70005551"),
    "RoomsDetails" : [ 
        {
            "RoomId" : "050c65ab-355e-9bec-00a0-2999e75bcdc4",
            "FloorNumber" : "1",
            "RoomNumber" : "104"
        }, 
        {
            "RoomId" : "f197c635-a6fb-b4d6-9cf5-ed53177edd47",
            "FloorNumber" : "2",
            "RoomNumber" : "109"
        }
    ]
}
*/

P.S. PHP code is not tested, so i'm not 100% sure if it is working.

P.P.S. The final solution is strongly depends on the details of your project.