title: Mongodb pipeline is breaking due to large number of data
body: <p>I have written the below pipeline for fetching data from multiple tables by performing lookup commands. The below query was working fine till yesterday without $step20. As soon as data grew in size, the clients kept adding certificates to the students and employees, the below query started breaking throwing the error message&quot;Total size of documents in studentTbl matching pipeline's $lookup stage exceeds 104857600 bytes&quot;. Then after doing so R &amp; D, I found $step20 which I have added to the below pipeline, but it still throws the same error message.</p>
  <pre><code>    $page = (int) $this-&gt;page;
      $length = (int) $this-&gt;length;
  
      $param = preg_replace('!\s+!', ' ', $this-&gt;search);
      $arg = trim($param);
  
      $dateFormat = &quot;%m/%d/%Y&quot;;
      if ($this-&gt;sessionDateFormat === &quot;m-d-Y&quot;) {
          $dateFormat = &quot;%m/%d/%Y&quot;;
      } elseif ($this-&gt;sessionDateFormat === &quot;d-m-Y&quot;) {
          $dateFormat = &quot;%d/%m/%Y&quot;;
      } elseif ($this-&gt;sessionDateFormat === &quot;Y-m-d&quot;) {
          $dateFormat = &quot;%Y/%m/%d&quot;;
      }
  
      $sortby = $this-&gt;sortby;
    
      switch ($sortby) {
          case 0:
              $sortparam = &quot;UsedFor&quot;;
              break;
          case 1:
              $sortparam = &quot;Type&quot;;
              break;
          case 2:
              $sortparam = &quot;convertedPid&quot;;
              break;
          case 3:
              $sortparam = &quot;certificateNo&quot;;
              break;
          case 4:
              $sortparam = 'insensitiveFirstName';
              break;
          case 5:
              $sortparam = &quot;insensitiveAssignedByCombinedFirstLastName&quot;;
              break;
          case 6:
              $sortparam = &quot;dateAndTime&quot;;
              break;
  
          default:
              $sortparam = &quot;dateAndTime&quot;;
              break;
      }
  
      $skip = ($page);
  
  
      $step1 =  ['$lookup' =&gt; [
                  'from' =&gt; 'EmployeesTbl',
                  'localField' =&gt; '_id',
                  'foreignField' =&gt; 'CertificatesDetails.documentType',
                  'as' =&gt; 'employeeArray'
              ]];
  
      $step2 =  ['$lookup' =&gt; [
                  'from' =&gt; 'studentTbl',
                  'localField' =&gt; '_id',
                  'foreignField' =&gt; 'certificates_details.documentType',
                  'as' =&gt; 'studentArray'
              ]];
  
      $step3 =  [
                  '$project' =&gt; [
                    &quot;_id&quot; =&gt; 1.0,
                    &quot;UsedFor&quot; =&gt; 1.0,
                    &quot;Type&quot; =&gt; 1.0,
                    &quot;Code&quot; =&gt; 1.0,
                    &quot;Description&quot; =&gt; 1.0,
                    &quot;schoolId&quot; =&gt; 1.0,
                    
                    &quot;employeeArray._id&quot; =&gt; 1.0,
                    &quot;employeeArray.FirstName&quot; =&gt; 1.0,
                    &quot;employeeArray.LastName&quot; =&gt; 1.0,
                    &quot;employeeArray.EmployeeNumber&quot; =&gt; 1.0,
                    &quot;employeeArray.CertificatesDetails&quot; =&gt; 1.0,
                    
                    &quot;studentArray._id&quot; =&gt; 1.0,
                    &quot;studentArray.first_name&quot; =&gt; 1.0,
                    &quot;studentArray.last_name&quot; =&gt; 1.0,
                    &quot;studentArray.uploadsFolder&quot; =&gt; 1.0,
                    &quot;studentArray.certificates_details&quot; =&gt; 1.0,
                    &quot;studentArray.registration_temp_perm_no&quot; =&gt; 1.0
                  ]
               ];
  
      $step4 =  array(
          '$match' =&gt; [
              'schoolId' =&gt; new MongoDB\BSON\ObjectID($this-&gt;schoolId),
              '$or' =&gt; [
                  ['employeeArray' =&gt; ['$not' =&gt; ['$size' =&gt; 0]]],
                  ['studentArray' =&gt; ['$not' =&gt; ['$size' =&gt; 0]]]
              ]
          ]
      ); 
  
      $step5 =  ['$unwind' =&gt; [ 
                  'path' =&gt; '$employeeArray',
                  'preserveNullAndEmptyArrays' =&gt; true 
              ]];
              
      $step6 =  ['$unwind' =&gt; [
                  'path' =&gt; '$studentArray',
                  'preserveNullAndEmptyArrays' =&gt; true
              ]];   
  
      $step7 =  [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      &quot;Type&quot; =&gt; 1.0,
                      &quot;Code&quot; =&gt; 1.0,
                      &quot;Description&quot; =&gt; 1.0,
                      &quot;schoolId&quot; =&gt; 1.0,
                      &quot;employeeArray&quot; =&gt; 1.0,
                      &quot;studentArray&quot; =&gt; 1.0,
                      &quot;userId&quot; =&gt;  [
                          '$cond' =&gt; [
                              'if' =&gt; [
                                  '$eq' =&gt; ['$UsedFor', 'Employee'],
                              ],
                              'then' =&gt; '$employeeArray._id',
                              'else' =&gt; '$studentArray._id'
                          ]
                      ],
                      &quot;firstName&quot; =&gt;  [
                          '$cond' =&gt; [
                              'if' =&gt; [
                                  '$eq' =&gt; ['$UsedFor', 'Employee'],
                              ],
                              'then' =&gt; '$employeeArray.FirstName',
                              'else' =&gt; '$studentArray.first_name'
                          ]
                      ],
                      &quot;lastName&quot; =&gt;  [
                          '$cond' =&gt; [
                              'if' =&gt; [
                                  '$eq' =&gt; ['$UsedFor', 'Employee'],
                              ],
                              'then' =&gt; '$employeeArray.LastName',
                              'else' =&gt; '$studentArray.last_name'
                          ]
                      ],
                      &quot;pid&quot; =&gt;  [
                          '$cond' =&gt; [
                              'if' =&gt; [
                                  '$eq' =&gt; ['$UsedFor', 'Employee'],
                              ],
                              'then' =&gt; '$employeeArray.EmployeeNumber',
                              'else' =&gt; '$studentArray.registration_temp_perm_no'
                          ]
                      ],
                      &quot;certificatesDetails&quot; =&gt;  [
                          '$cond' =&gt; [
                              'if' =&gt; [
                                  '$eq' =&gt; ['$UsedFor', 'Employee'],
                              ],
                              'then' =&gt; '$employeeArray.CertificatesDetails',
                              'else' =&gt; '$studentArray.certificates_details'
                          ]
                      ],
                      &quot;uploadsFolder&quot; =&gt;  [
                          '$cond' =&gt; [
                              'if' =&gt; [
                                  '$eq' =&gt; ['$UsedFor', 'Employee'],
                              ],
                              'then' =&gt; &quot;&quot;,
                              'else' =&gt; '$studentArray.uploadsFolder'
                          ]
                      ],
                  ]
              ];  
              
      $step8 =  [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      ....
                          '$filter' =&gt; [
                              'input' =&gt; '$certificatesDetails',
                              'as' =&gt; 'object',
                              'cond' =&gt; [ 
                                    '$eq' =&gt; ['$$object.documentType', '$_id'],
                                  ]
                              ]
                        ],                
                  ]
              ];  
              
      $step9 = [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      &quot;Type&quot; =&gt; 1.0,
                      &quot;Code&quot; =&gt; 1.0,
                      &quot;Description&quot; =&gt; 1.0,
                      &quot;schoolId&quot; =&gt; 1.0,
                      .....
                     // &quot;certificatesDetails&quot; =&gt; 1.0,
                      &quot;currentCertificateDetails&quot; =&gt; 1.0
                  ]
              ];       
              
      $step10 = ['$unwind' =&gt; [
                  'path' =&gt; '$currentCertificateDetails',
                  'preserveNullAndEmptyArrays' =&gt; true
              ]];    
              
      
      $step11 =  [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      &quot;Type&quot; =&gt; 1.0,
                      &quot;Code&quot; =&gt; 1.0,
                       ......
                     .....
                  ]
              ];       
      
  
        $step12 =  ['$lookup' =&gt; [
                  'from' =&gt; 'userTbl',
                  'localField' =&gt; 'createdBy',
                  'foreignField' =&gt; '_id',
                  'as' =&gt; 'createdByArray'
              ]];   
              
      
        $step13 = array(
                  '$match' =&gt; [
                      'isActive' =&gt; true 
                  ]
              );    
           
         $step14 =   [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      //&quot;studentArray&quot; =&gt; 1.0,
                        ....             
                       .....
                      &quot;toShow&quot; =&gt; 1.0,
                      &quot;isActive&quot; =&gt; 1.0,
                      &quot;createdByArray&quot; =&gt;  1.0,
                      &quot;userActualId&quot; =&gt; '$createdByArray.uid',
                      &quot;certificateNo&quot; =&gt; 1
                  ]
              ];      
              
       $step15 = ['$lookup' =&gt; [
                  'from' =&gt; 'EmployeesTbl',
                  'localField' =&gt; 'userActualId',
                  'foreignField' =&gt; '_id',
                  'as' =&gt; 'userActualArray'
              ]];
  
  
       $step16 = ['$unwind' =&gt; [
          'path' =&gt; '$userActualArray',
          'preserveNullAndEmptyArrays' =&gt; true
        ]];       
       
        $step17 =  [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      &quot;Type&quot; =&gt; 1.0,
                      &quot;Code&quot; =&gt; 1.0,
                      &quot;certificateNo&quot; =&gt; 1,
                      &quot;Description&quot; =&gt; 1.0,
                      &quot;schoolId&quot; =&gt; 1.0,
                      //&quot;employeeArray&quot; =&gt; 1.0,
                      //&quot;studentArray&quot; =&gt; 1.0,
                   ...                       
                      &quot;userActualId&quot; =&gt; 1.0,
                      &quot;assignedByFirstName&quot; =&gt; '$userActualArray.FirstName',
                      &quot;assignedByLastName&quot; =&gt; '$userActualArray.LastName',
                      &quot;certificateNo&quot; =&gt; 1
                  ]
              ];  
              
          $step18 = [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      &quot;Type&quot; =&gt; 1.0,
                      &quot;Code&quot; =&gt; 1.0,
                      &quot;Description&quot; =&gt; 1.0,
                      &quot;schoolId&quot; =&gt; 1.0,
                    //  &quot;employeeArray&quot; =&gt; 1.0,
                    //  &quot;studentArray&quot; =&gt; 1.0,
                      &quot;userId&quot; =&gt;  1.0,
                      &quot;certificateNo&quot; =&gt; 1,
                      &quot;firstName&quot; =&gt; 1.0,
                      &quot;lastName&quot; =&gt; 1.0,
                      &quot;firstLastNameCombined&quot; =&gt; 1.0,
                      ...
                      &quot;toShow&quot; =&gt; 1.0,
                      &quot;isActive&quot; =&gt; 1.0,
                      &quot;createdByArray&quot; =&gt;  1.0,
                      &quot;userActualArray&quot; =&gt; 1.0,
                      &quot;userActualId&quot; =&gt; 1.0,
                      &quot;assignedByFirstName&quot; =&gt; 1.0,
                      &quot;assignedByLastName&quot; =&gt; 1.0,
                      &quot;assignedByCombinedFirstLastName&quot; =&gt; array('$concat' =&gt; array('$assignedByFirstName', ' ', '$assignedByLastName')),
                      &quot;certificateNo&quot; =&gt; 1
                  ]
             ];
  
              $step19 = [
                  '$project' =&gt; [
                      &quot;_id&quot; =&gt; 1.0,
                      &quot;UsedFor&quot; =&gt; 1.0,
                      &quot;Type&quot; =&gt; 1.0,
                      ....
                      &quot;certificateNo&quot; =&gt; 1
                  ]
              ];
  
      $querySearch = [
          '$or' =&gt; array(
              array(&quot;UsedFor&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i')),
              array(&quot;Type&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i')),
              array(&quot;pid&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i')),
              array(&quot;certificateNo&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i')),
              array(&quot;dateAndTimeString&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i')),
              array(&quot;insensitiveCombinedFirstLastName&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i')),
              array(&quot;insensitiveAssignedByCombinedFirstLastName&quot; =&gt; new MongoDB\BSON\Regex($arg, 'i'))
          )
      ];
  
  
      $step20 = [
          'allowDiskUse' =&gt; true
      ];
  
      $searchCommand = ['$match' =&gt; $querySearch];
  
      $sortCommand = array('$sort' =&gt; array($sortparam =&gt; ($this-&gt;sortbyparam == 'asc') ? -1 : 1));
  
      $skipCommand =  array('$skip' =&gt; $skip);
      
      $limitCommand = array('$limit' =&gt; $length);
  
      $cursor = iterator_to_array($this-&gt;db-&gt;certificateTypeTbl-&gt;aggregate(array($step1, $step2, $step3,  $step4,  $step5,  $step6, $step7, $step8, $step9, $step10, $step11, $step12, $step13, $step14, $step15, $step16, $step17, $step18, $step19, $searchCommand, $sortCommand, $skipCommand, $limitCommand), $step20));
  </code></pre>
  <p>Please note that there are a lot of students in our database. (above 5000). I am not able to find any better solution. Kindly help!!!</p>