MongoDB .NET Driver - How to search the document(s) with fulfilling in the nested documents of an ar-CodePudding

{
  "isbn": "123-456-222",
  "author": {
    "_id": 1,
    "lastname": "Doe",
    "firstname": "Jane"
  },
  "editor": [
    {
      "_id": 1,
      "lastname": "Smith",
      "firstname": "Jane"
    },
    {
      "_id": 2,
      "lastname": "Lopez",
      "firstname": "Jennifer"
    }
  ],
  "title": "The Ultimate Database Study Guide",
  "category": [
    "Non-Fiction",
    "Technology"
  ]
}

I am using the MongoDB database and MongoDB .NET driver library in .NET Core. I can search by isbn, but I want to find all books with editor firstname: Jane, lastname: Smith and firstname: Jennifer, lastname: Lopez. How can I do that?

CodePudding user response：

@rickhg12hs provided the correct filter in the MongoDB query.

To convert it into MongoDB .NET Driver syntax:

Assume that you have these model classes for your collection:

internal class Book
{
    [BsonId]
    public ObjectId Id { get; set; }

    [BsonElement("isbn")]
    public string Isbn { get; set; }

    [BsonElement("author")]
    public Author Author { get; set; }

    [BsonElement("editor")]
    public Editor[] Editor { get; set; }

    [BsonElement("title")]
    public string Title { get; set; }

    [BsonElement("category")]
    public string[] Category { get; set; }
}

internal class Author: People
{

}

internal class Editor : People
{

}

internal class People
{
    [BsonElement("_id")]
    public int Id { get; set; }

    [BsonElement("lastname")]
    public string LastName { get; set; }

    [BsonElement("firstname")]
    public string FirstName { get; set; }
}

FilterDefinition<Book> filter = Builders<Book>.Filter.Empty;

filter &= Builders<Book>.Filter.ElemMatch(x => x.Editor,
    Builders<Editor>.Filter.And(
        Builders<Editor>.Filter.Eq(y => y.FirstName, "Jane"),
        Builders<Editor>.Filter.Eq(y => y.LastName, "Smith")
    )
);

filter &= Builders<Book>.Filter.ElemMatch(x => x.Editor,
    Builders<Editor>.Filter.And(
        Builders<Editor>.Filter.Eq(y => y.FirstName, "Jennifer"),
        Builders<Editor>.Filter.Eq(y => y.LastName, "Lopez")
    )
);

List<Book> books = (await _mongoCollection.FindAsync(filter))
    .ToList();

CodePudding user response：

I don't know C#/.NET, but here's a query that you may be able to translate to retrieve the documents you want.

db.collection.find({
  "$and": [
    {
      "editor": {
        "$elemMatch": {
          "lastname": "Smith",
          "firstname": "Jane"
        }
      }
    },
    {
      "editor": {
        "$elemMatch": {
          "lastname": "Lopez",
          "firstname": "Jennifer"
        }
      }
    }
  ]
})

Try it on mongoplayground.net.