Querying MondoDB collection when a field value of an item in a list matches another item's different field value

Question

I have a collection in mongoDB where records are like the followings (example data) where latest versions are stored with an incremental value in a field called "version" and copy of an _id as resourceId 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 id matches with other records recourseId and the status is "Published" 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 MongoDB using c#.net or vb.net?

1.  id:"11A"
    name: "Blue Block"
    status: "Published"
    version: "0"
    resourceId: ""
2.  id:"11B"
    name: "Blue Block"
    status: "Published"
    version: "1"
    resourceId: "11A"
3.  id:"11C"
    name: "Blue Block"
    status: "Draft"
    version: "2"
    resourceId: "11A"
4.  id:"11D"
    name: "Red Block"
    status: "Published"
    version: "0"
    resourceId: ""

What I tried:

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)("Toys").Aggregate().Match(Function(x) x.GetElement("id") = x.GetElement("resourceId")).SortByDescending(Function(x) x.GetElement("version")).Group(BsonDocument.Parse("{ 'id':'$group',  'latestvalue':{$first:'$value'} }")).ToList()

  Return listOfToys
End Function

Which didn't work. Not sure how to add the "Status" check here.

Answer 1

The query needed more than the standard $lookup. First I tried to create the pipeline in MongoDB Compass as follows, then I translated it into VB.Net.

The query first checks if three of the property field values exist. Then it sorts the documents by version in descending order. Then it groups the documents by _id where it matches resourceId. Then the group is given a different name latestversion. Then it matches the newly grouped documents by status. This is the expected result.

MongoDBCompass Aggregession Pipeline:

[{$match: {
$and: [
{
  status: {
    $exists: true
  }
},
{
  version: {
    $exists: true
  }
},
{
  resourceId: {
    $exists: true
  }
}
]
}}, {$sort: {
      version: -1
}}, {$group: {
      _id: '$resourceId',
      latestVersion: {
            $first: '$$ROOT'
    }
 }}, {$match: {
     'latestVersion.status': {
     $eq: 'Published'
}
}}, {$replaceRoot: {
     newRoot: '$latestVersion'
}}]