title: Set local field to number of matching documents in foreign table
body: <p>Say there is one table which contains documents like this:</p>
  
  <pre><code>{ _id: "aaaa", name: "One" }
  { _id: "bbbb", name: "Two" }
  </code></pre>
  
  <p>And a foreign table which contains documents like this:</p>
  
  <pre><code>{ _id: "cccc", state: "pending",  source_id: "aaaa" }
  { _id: "dddd", state: "finished", source_id: "aaaa" }
  { _id: "eeee", state: "finished", source_id: "aaaa" }
  { _id: "ffff", state: "pending",  source_id: "aaaa" }
  </code></pre>
  
  <hr>
  
  <p>Now, I want to get a single array like this:</p>
  
  <pre><code>[
      { _id: "aaaa", name: "One", pending: 2 },
      { _id: "bbbb", name: "Two", pending: 0 }
  ]
  </code></pre>
  
  <p>The resulting documents are from the first table. Aggregation fills in the <em>"pending"</em> field with the number of matching (source_id = _id) <strong>foreign documents that have a status of "pending"</strong>.</p>
  
  <p>How would one do this? What would be the correct query syntax? </p>
  
  <p>One solution I've tried: using $lookup, $match, and $sort by themselves to retrieve a result like this:</p>
  
  <pre><code>{
    _id: "aaaa",
    name: "One",
    foreign: [
      { _id: "cccc", state: "pending",  source_id: "aaaa" },
      { _id: "ffff", state: "pending",  source_id: "aaaa" }
    ]
  },
  { 
    _id: "bbbb",
    name: "Two"
    foreign: []
  }
  </code></pre>
  
  <p>I'm able to just get the length of the child array in my code, but when the foreign table has too many documents, <em>I end up getting "aggregation exceeds maximum document size." because the result is more than 16MB.</em></p>
  
  <hr>
  
  <h3>Making Progress</h3>
  
  <p>The previous has helped. However I'm trying to figure out how to filter pending states, as the original question states:</p>
  
  <pre><code>db.apps.aggregate([
    {
      "$lookup": {
        "from": "requests",
        "localField": "_id",
        "foreignField": "app_id",
        "as": "foreign"
      }
    },
    {
      "$unwind": "$foreign"
    },
    {
      $match: {
        "foreign.state": 0
      }
    },
    {
      "$group": {
        "_id": "$_id",
        "name": {
          "$first": "$name"
        },
        "pending": {
          "$sum": 1
        }
      }
    }
  ])
  </code></pre>
  
  <p>I get some results. However, <strong>documents in the local/first table that have no associated foreign documents (no foreign documents with state = 0) are not returned at all.</strong> I want rows that have 0 pending foreign documents to still be included in the results. </p>
  
  <hr>
  
  <h3>The Solution</h3>
  
  <pre><code>db.apps.aggregate([{
      "$lookup": {
        "from": "requests",
        "as": "pending",
        "let": {
          "id": "$_id"
        },
        "pipeline": [{
            "$match": {
              "$expr": {
                "$eq": ["$$id", "$app_id"]
              }
            }
          },
          {
            "$match": {
              "$expr": {
                "$eq": [0, "$state"]
              }
            }
          },
          {
            "$count": "count"
          }
        ]
      }
    },
    {
      "$addFields": {
        "pending": {
          "$arrayElemAt": ["$pending.count", 0]
        }
      }
    }
  ])
  </code></pre>