I have documents like this:

[
  // 1 
  {
    "_id": ObjectId("573f3944a75c951d4d6aa65e"),
    "Source": "IGN",
    "Family": [
      {
        "Countries": [
          {
            "uid": 17,
            "name": "Japan",
            
          }
        ]
      }
    ]
  },
  // 2 
  {
    "_id": ObjectId("573f3d41a75c951d4d6aa65f"),
    "Source": "VG",
    "Family": [
      {
        "Countries": [
          {
            "uid": 17,
            "name": "USA"
          }
        ]
      }
    ]
  },
  // 3 
  {
    "_id": ObjectId("573f4367a75c951d4d6aa660"),
    "Source": "NRK",
    "Family": [
      {
        "Countries": [
          {
            "uid": 17,
            "name": "Germany"
          }
        ]
      }
    ]
  },
  // 4 
  {
    "_id": ObjectId("573f4571a75c951d4d6aa661"),
    "Source": "VG",
    "Family": [
      {
        "Countries": [
          {
            "uid": 10,
            "name": "France"
          }
        ]
      }
    ]
  },
  // 5 
  {
    "_id": ObjectId("573f468da75c951d4d6aa662"),
    "Source": "IGN",
    "Family": [
      {
        "Countries": [
          {
            "uid": 14,
            "name": "England"
          }
        ]
      }
    ]
  }
]

I want to return only the documents with source equals 'Countries.uid' equal 17

so I have in the end :

[
  {
    "_id": ObjectId("573f3944a75c951d4d6aa65e"),
    "Source": "IGN",
    "Family": [
      {
        "Countries": [
          {
            "uid": 17,
            "name": "Japan",
            
          }
        ]
      }
    ]
  },
  {
    "_id": ObjectId("573f3d41a75c951d4d6aa65f"),
    "Source": "VG",
    "Family": [
      {
        "Countries": [
          {
            "uid": 17,
            "name": "USA"
          }
        ]
      }
    ]
  },
  {
    "_id": ObjectId("573f4367a75c951d4d6aa660"),
    "Source": "NRK",
    "Family": [
      {
        "Countries": [
          {
            "uid": 17,
            "name": "Germany"
          }
        ]
      }
    ]
  }
]

How can I do this with the official C# MongoDB driver?

Tried this :

public List<Example> getLinkedCountry(string porduitId)
{
    var filter = Builders<Example>.Filter.AnyIn("Family.Countries.uid", porduitId);
    var cursor = await _certificats.FindAsync(filter);
    var docs = cursor.ToList();
    return docs;
}

Unfortunately, I think my filter is wrong.

Is there a way to find all the documents by accessing the nested list by id and retrieving it?

CodePudding user response：

Solution 1

Use ElemMatch instead of AnyIn.

var filter = Builders<Example>.Filter.ElemMatch(
    x => x.Family,
    y => y.Countries.Any(z => z.uid == porduitId));

Output

Solution 2

If you are unconfident with MongoDB .Net Driver syntax, you can convert the query as BsonDocument via MongoDB Compass (Export to language feature).

var filter = new BsonDocument("Family.Countries.uid", porduitId);

CodePudding user response：

Just to expand on @Yong Shun 's answer,

if you just want to return the list of nested documents and not all of it, you have a few options.

1. Using project

   var filter = Builders<Example>.Filter.ElemMatch(
       x => x.Family,
       y => y.Countries.Any(z => z.uid == porduitId));
   
   var project = Builders<Example>.Project.ElemMatch(
       x => x.Family,
       y => y.Countries.Any(z => z.uid == porduitId)
   );
   
   var examples = await collection.filter(filter).Project<Example>(project).toListAsync();
   

2. Using the aggregate pipeline

    var filter = Builders<Example>.Filter.ElemMatch(
        x => x.Family,
        y => y.Countries.Any(z => z.uid == porduitId));
   
    var project = Builders<ServiceProvider>.Projection.Expression(
                        x => x.Faimily.Where(y => y.uid == porduitId)
                        );
   
    var result = await collection
    .Aggregate()
    .Match(filter)
    .Project(project)
    .ToListAsync(); //Here result is a list of Iterable<Countries>