title: Mongo $lookup filter using nested query
body: <p>I'm playing with mongoDB's $lookup function, specifically the pipeline syntax, to allow me to perform some more complex queries than the ORM I am using (Sails/Waterline) allows.</p>
  
  <p>A cut down version of my data looks like....</p>
  
  <pre class="lang-js prettyprint-override"><code>    // 'job' collection
      {
        "id"      : j1,
        "mediaID" : "ABC1234"
      },
      {
        "id"      : j2,
        "mediaID" : "DEF1234"
      },
      {
        "id"      : j3,
        "mediaID" : "FGH3456"
      }
  </code></pre>
  
  <p>..and..</p>
  
  <pre class="lang-js prettyprint-override"><code>    // 'task' collection
  
      // j1 tasks
      {
        "id"      : "t1",
        "job"     : "j1",
        "taskName": "MOVE",
        "status"  : "COMPLETE"
      },
      {
        "id"      : "t2",
        "job"     : "j1",
        "taskName": "PUBLISH",
        "status"  : "COMPLETE"
      },
      // j2 tasks
      {
        "id"      : "t3",
        "job"     : "j2",
        "taskName": "MOVE",
        "status"  : "FAILED"
      },
      // j3 tasks
      {
        "id"      : "t4",
        "job"     : "j3",
        "taskName": "MOVE",
        "status"  : "COMPLETE"
      }
  </code></pre>
  
  <p>..where the task collection links to the job collection via <code>job.id -&gt; task.job</code></p>
  
  <p>What I would like to achieve is, to be able to filter jobs by <code>job.mediaID</code> and/or also <code>task.status</code>. The query I currently have almost gets what I want, but it doesn't filter out the <code>jobs</code>, it just doesn't populate the <code>tasks</code> part.</p>
  
  <p>My current query is as follows...</p>
  
  <pre class="lang-js prettyprint-override"><code>    let results = await jobCollection.aggregate([
  
        // First filter jobs with matching criteria
        {
          $match: {
            $text: {$search: "1234"}
          }
        },
  
        // Next, link the task collection, and filter by status
        {
          "$lookup": {
            from    : 'task',
            'let'   : {job_id: '$_id'},
            pipeline: [
              {
                $match: {
                  $expr: {
                    $and: [
                      // This does the linking of job.id -&gt; task.job_id
                      {$eq: ['$job', '$$job_id']},
                      // This filters out unwanted tasks
                      {$eq: ['$status', 'FAILED']}
                    ]
                  }
                }
              }
            ],
            as      : 'tasks'
          }
        }
      ])
      .toArray();
  </code></pre>
  
  <p>In that example, the first stage would match <code>j1</code> and <code>j2</code> as they both contain "1234", then I would like to further filter out the jobs, based on the task status, for example, only <code>j2</code> has a task that has <code>status==FAILED</code>, so my end result would just be the <code>j2</code> document.</p>
  
  <p>I hope this makes. I think I may just need to add some clever projection on the end. Thanks</p>