title: MongoDb query to exclude omission of rows based on criteria
body: <p>In below example, looking for new partner suggestions for user abc. <code>abc</code> has already sent a request to <code>123</code> so that can be ignored. <code>rrr</code> has sent request to <code>abc</code> but <code>rrr</code> is in the <code>fromUser</code> field so <code>rrr</code> is still a valid row to be shown as suggestion to <code>abc</code></p>
  <p>I have two collections:</p>
  <ol>
  <li>User collection</li>
  </ol>
  <pre><code>[
      {
        _id: &quot;abc&quot;,
        name: &quot;abc&quot;,
        group: 1
      },
      {
        _id: &quot;xyz&quot;,
        name: &quot;xyyy&quot;,
        group: 1
      },
      {
        _id: &quot;123&quot;,
        name: &quot;yyy&quot;,
        group: 1
      },
      {
        _id: &quot;rrr&quot;,
        name: &quot;tttt&quot;,
        group: 1
      },
      {
        _id: &quot;eee&quot;,
        name: &quot;uuu&quot;,
        group: 1
      }   
  ]
  </code></pre>
  <ol start="2">
  <li>Partnership collection (if users have already partnered)</li>
  </ol>
  <pre><code>[
      {
        _id: &quot;abc_123&quot;,
        fromUser: &quot;abc&quot;,
        toUser: &quot;123&quot;
      },
      {
        _id: &quot;rrr_abc&quot;,
        fromUser: &quot;rrr&quot;,
        toUser: &quot;abc&quot;
      },
      {
        _id: &quot;xyz_rrr&quot;,
        fromUser: &quot;xyz&quot;,
        toUser: &quot;rrr&quot;
      }   
  ] 
  </code></pre>
  <p>My query below excludes the user <code>rrr</code> but it should not because its not listed in <code>toUser</code> field in the partnership collection corresponding to the user <code>abc</code>.</p>
  <p>How to modify this query to include user <code>rrr</code> in this case?</p>
  <pre><code>db.users.aggregate([
    {
      $match: {
        group: 1,
        _id: {
          $ne: &quot;abc&quot;
        }
      }
    },
    {
      $lookup: {
        from: &quot;partnership&quot;,
        let: {
          userId: &quot;$_id&quot;
        },
        as: &quot;prob&quot;,
        pipeline: [
          {
            $set: {
              users: [
                &quot;$fromUser&quot;,
                &quot;$toUser&quot;
              ],
              u: &quot;$$userId&quot;
            }
          },
          {
            $match: {
              $expr: {
                $and: [
                  {
                    $in: [
                      &quot;$$userId&quot;,
                      &quot;$users&quot;
                    ]
                  },
                  {
                    $in: [
                      &quot;abc&quot;,
                      &quot;$users&quot;
                    ]
                  }
                ]
              }
            }
          }
        ]
      }
    },
    {
      $match: {
        &quot;prob.0&quot;: {
          $exists: false
        }
      }
    },
    {
      $sample: {
        size: 1
      }
    },
    {
      $unset: &quot;prob&quot;
    }
  ])
  </code></pre>
  <p><a href="https://mongoplayground.net/p/utGMeHFRGmt" rel="nofollow noreferrer">https://mongoplayground.net/p/utGMeHFRGmt</a></p>