title: Querying MondoDB collection when a field value of an item in a list matches another item's different field value
body: <p>I have a collection in <code>mongoDB</code> where records are like the followings (example data) where latest versions are stored with an incremental value in a field called &quot;version&quot; and copy of an <code>_id</code> as <strong>resourceId</strong> for an item for historical data. Here record 1, 2, 3, is the same record with a different version and status. So I need to find all the records where one item's <code>id</code> matches with other records <code>recourseId</code> and the status is &quot;Published&quot; along with the other records. In this case my output should be only record 4 because although record 3 has highest version but the status is not published. In this case I don't show any of the record 1 or 2 or 3. How can this achievable in <code>MongoDB</code> using <code>c#.net</code> or <code>vb.net</code>?</p>
  <pre><code>1.  id:&quot;11A&quot;
      name: &quot;Blue Block&quot;
      status: &quot;Published&quot;
      version: &quot;0&quot;
      resourceId: &quot;&quot;
  2.  id:&quot;11B&quot;
      name: &quot;Blue Block&quot;
      status: &quot;Published&quot;
      version: &quot;1&quot;
      resourceId: &quot;11A&quot;
  3.  id:&quot;11C&quot;
      name: &quot;Blue Block&quot;
      status: &quot;Draft&quot;
      version: &quot;2&quot;
      resourceId: &quot;11A&quot;
  4.  id:&quot;11D&quot;
      name: &quot;Red Block&quot;
      status: &quot;Published&quot;
      version: &quot;0&quot;
      resourceId: &quot;&quot;
  </code></pre>
  <p>What I tried:</p>
  <pre><code>Public Class Toy
      Public Property ID As String
      Public Property Name As String
      Public Property Status As String
      Public Property Version As String
      Public Property ResourceID As String
  End class
  
  Private Function GetToys() As List(Of Toy)
   
    Dim listOfToys = db.GetCollection(Of BsonDocument)(&quot;Toys&quot;).Aggregate().Match(Function(x) x.GetElement(&quot;id&quot;) = x.GetElement(&quot;resourceId&quot;)).SortByDescending(Function(x) x.GetElement(&quot;version&quot;)).Group(BsonDocument.Parse(&quot;{ 'id':'$group',  'latestvalue':{$first:'$value'} }&quot;)).ToList()
  
    Return listOfToys
  End Function
  </code></pre>
  <p>Which didn't work. Not sure how to add the &quot;Status&quot; check here.</p>