title: Pymongo: Subqueries with $lookup for the same collection and $in for certain ids. Is this the right way?
body: <p>I''m new to MongoDB and I need to do kind of a subquery, in  which I want:</p>
  <ul>
  <li>Return the count of records for top3 cariers (the array in $in) for each week.</li>
  </ul>
  <p>My collection is as follows:</p>
  <pre><code>&quot;MONTH&quot;,&quot;DAY_OF_MONTH&quot;,&quot;DAY_OF_WEEK&quot;,&quot;FL_DATE&quot;,&quot;OP_UNIQUE_CARRIER&quot;,&quot;ORIGIN_CITY_NAME&quot;,&quot;DEST_CITY_NAME&quot;,&quot;ARR_DELAY&quot;,&quot;DISTANCE&quot;,
  3,28,3,2018-03-28,&quot;B6&quot;,&quot;Newark, NJ&quot;,&quot;Fort Myers, FL&quot;,4.00,1068.00,
  3,29,4,2018-03-29,&quot;B6&quot;,&quot;Newark, NJ&quot;,&quot;Fort Myers, FL&quot;,6.00,1068.00,
  3,30,5,2018-03-30,&quot;B6&quot;,&quot;Newark, NJ&quot;,&quot;Fort Myers, FL&quot;,-4.00,1068.00,
  3,31,6,2018-03-31,&quot;B6&quot;,&quot;Newark, NJ&quot;,&quot;Fort Myers, FL&quot;,-3.00,1068.00,
  3,1,4,2018-03-01,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,39.00,2465.00,
  3,2,5,2018-03-02,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,0.00,2465.00,
  3,3,6,2018-03-03,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,2.00,2465.00,
  3,4,7,2018-03-04,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,-2.00,2465.00,
  3,5,1,2018-03-05,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,25.00,2465.00,
  3,6,2,2018-03-06,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,-21.00,2465.00,
  3,7,3,2018-03-07,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,20.00,2465.00,
  3,8,4,2018-03-08,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,48.00,2465.00,
  3,9,5,2018-03-09,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,16.00,2465.00,
  3,10,6,2018-03-10,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,6.00,2465.00,
  3,11,7,2018-03-11,&quot;B6&quot;,&quot;New York, NY&quot;,&quot;Long Beach, CA&quot;,-13.00,2465.00,
  </code></pre>
  <p>and my current query only returns the #count for each of the top3 but with harcoded values inside $in stage:</p>
  <pre><code>total_per_week = [
      {&quot;$match&quot; :{
          &quot;OP_UNIQUE_CARRIER&quot;: { &quot;$in&quot;: [&quot;DL&quot;,&quot;9E&quot;,&quot;B6&quot;]}
          ,&quot;ORIGIN_CITY_NAME&quot;: {&quot;$regex&quot;: &quot;^New York&quot;}
          }
      },    
      {&quot;$group&quot; : {
          &quot;_id&quot; : {                   
              &quot;week&quot;: { &quot;$week&quot;: &quot;$FL_DATE&quot; },
              &quot;carrier&quot;: &quot;$OP_UNIQUE_CARRIER&quot;
          },                
          &quot;total&quot; : { &quot;$sum&quot;: 1 } 
          }
      },
      #// Maybe project a prettier &quot;flatter&quot; output
      {&quot;$project&quot;: {
          &quot;_id&quot;: 0,
          &quot;carrier&quot;: &quot;$_id.carrier&quot;,
          &quot;week&quot;: &quot;$_id.week&quot;,
          &quot;total&quot;: &quot;$total&quot;
      }}
      ,
      {&quot;$sort&quot;: SON([(&quot;carrier&quot;, 1), (&quot;week&quot;, 1)])}
  </code></pre>
  <p>]</p>
  <p>This is the output:</p>
  <pre><code>    carrier  week  total
  0       9E     0    597
  1       9E     1    964
  2       9E     2    917
  3       9E     3    968
  4       9E     4    975
  5       9E     5    927
  6       9E     6    933
  7       9E     7    917
  8       9E     8    978
  9       9E     9   1025
  10      9E    10   1036
  11      9E    11   1036
  12      9E    12   1036
  13      B6     0    797
  14      B6     1    880
  </code></pre>
  <p>(...)</p>
  <p>So, I've tried to mimic this SQL approach:</p>
  <p><strong>SELECT COUNT(*) , week, carrier
  where carrier in ()
  group by carrier,week</strong></p>
  <p>by caming up wit this query:</p>
  <pre><code>    lookQuery = [
      # Returns df with carrier column
      {&quot;$match&quot; : {&quot;ORIGIN_CITY_NAME&quot;: {&quot;$regex&quot;: &quot;^New York&quot;}} },
      {&quot;$sortByCount&quot;:  &quot;$OP_UNIQUE_CARRIER&quot; },
      {&quot;$limit&quot;: 3},
      #adds a new array (inner),top3, field whose elements are the matching documents from the “joined” collection
      { &quot;$lookup&quot;: 
          {
          &quot;from&quot;: &quot;Flights&quot;,
          &quot;let&quot;: {&quot;the_carrier&quot;: &quot;$_id&quot; },
          &quot;pipeline&quot;: [
          {&quot;$match&quot; :{
              &quot;$expr&quot;: {&quot;$eq&quot;: [&quot;$OP_UNIQUE_CARRIER&quot;,&quot;$$the_carrier&quot;]}
               ,&quot;ORIGIN_CITY_NAME&quot;: {&quot;$regex&quot;: &quot;^New York&quot;}
              }
          }, 
          {&quot;$group&quot; : {
              &quot;_id&quot; : {                   
              &quot;week&quot;: { &quot;$week&quot;: &quot;$FL_DATE&quot; },
              &quot;carrier&quot;: &quot;$OP_UNIQUE_CARRIER&quot;
              },                
              &quot;total_sem&quot; : { &quot;$sum&quot;: 1 } 
              }
          }         
        ],
        &quot;as&quot;: &quot;top3&quot; #output of inner-array field
        }
      } #closes lookup
      ,{&quot;$unwind&quot;: &quot;$top3&quot;},
          {&quot;$project&quot;: {
              &quot;_id&quot;: 0,
              &quot;Carrier&quot;: &quot;$top3._id.carrier&quot;,
              &quot;Week&quot;: &quot;$top3._id.week&quot;,
              &quot;Total&quot;: &quot;$top3.total_sem&quot;
              }
          }
          #adds a new outter array to math the carrier code in Carriers ref Table, providing the Description
          ,{&quot;$lookup&quot;:
            {
            &quot;from&quot;: &quot;Carriers&quot;,
            &quot;localField&quot;: &quot;Carrier&quot;,
            &quot;foreignField&quot;: &quot;Code&quot;,
            &quot;as&quot;: &quot;carriers&quot;
            }  
          },
          {&quot;$project&quot;: {
              &quot;_id&quot;: 0,
              &quot;Carrier&quot;: &quot;$Carrier&quot;,
              &quot;Week&quot;: &quot;$Week&quot;,
              &quot;Total&quot;: &quot;$Total&quot;,
              &quot;CarrierName&quot;: &quot;$carriers.Description&quot;
              }
          }
          ,{&quot;$sort&quot;: SON([(&quot;Carrier&quot;, 1), (&quot;Week&quot;, 1)])}      
  </code></pre>
  <p>]</p>
  <p>which produces the <strong>desired</strong> output:</p>
  <pre><code>    Carrier  Week  Total             CarrierName
  0       9E     0    597     [Endeavor Air Inc.]
  1       9E     1    964     [Endeavor Air Inc.]
  2       9E     2    917     [Endeavor Air Inc.]
  3       9E     3    968     [Endeavor Air Inc.]
  4       9E     4    975     [Endeavor Air Inc.]
  5       9E     5    927     [Endeavor Air Inc.]
  6       9E     6    933     [Endeavor Air Inc.]
  7       9E     7    917     [Endeavor Air Inc.]
  8       9E     8    978     [Endeavor Air Inc.]
  9       9E     9   1025     [Endeavor Air Inc.]
  10      9E    10   1036     [Endeavor Air Inc.]
  11      9E    11   1036     [Endeavor Air Inc.]
  12      9E    12   1036     [Endeavor Air Inc.]
  13      B6     0    797       [JetBlue Airways]
  14      B6     1    880       [JetBlue Airways]
  15      B6     2    873       [JetBlue Airways]
  </code></pre>
  <p><strong>My question is</strong>: Is this &quot;query&quot; <strong>efficient</strong> or is it overcomplicated?
  Is there a simpler way to achieve this with similar or better performance?</p>