2

I'm wondering how one can query a MongoDB collection for an embedded document (in an array) via the official C# driver version 1.7? By querying for an embedded document I mean I'd like to retrieve only the embedded document and not the one containing it, AKA projection.

Example of the kind of data model, with embedded documents, I am querying:

// Library, contained in db.Libraries
{
    _id: 1,
    Categories: [{_id: 2, Name: "Classics", Books: [{_id: 3, Name: The Count of Monte Cristo}]}]
}

The problem here would be how to query the Library collection for an object with _id 1 and one of its books with _id 3, and return only the Book. Is it doable at all? As far as I could tell, it would be possible to do this through a projection in the MongoDB shell.

My example query would be to query db.Libraries for a Book with _id 3 contained in a Library with _id 1. The Book returned would be this sub-document:

{_id: 3, Name: The Count of Monte Cristo}]}

I've looked at the question How to retrieve an embedded document using the official C# driver for MongoDB?, but I can't make the accepted answer work for me.

EDIT:

I can see now that the accepted answer of How to retrieve an embedded document using the official C# driver for MongoDB? works, kind of. I failed to see that it iterates over each found document, equivalent to this:

var libraryCollection = new MongoCollection<Library>();
var refBook = new Book { Id = ObjectId.GenerateNewId().ToString(), Name = "The Count of Monte Cristo" };
libraryCollection.Insert(new Library { Id = ObjectId.GenerateNewId().ToString(), Categories = new[] { new Category { Books = new[] { refBook } } } });

MongoCursor<Library> libraries = libraryCollection.Find(new QueryDocument(
                    new Dictionary<string, object>
                        {
                            {"_id", new ObjectId()},
                        }
                    ));
Book book;
foreach (var library in libraries)
{
    book = library.Categories.SelectMany(c => c.Books).FirstOrDefault(b => b.Id == refBook.Id);
}

However, this solution is moot since it retrieves whole Library documents rather than just the embedded Book document. I'm really after having to deserialize only the embedded Book, AKA projection.

Community
  • 1
  • 1
aknuds1
  • 65,625
  • 67
  • 195
  • 317
  • How can you expect someone to help you when the "Accepted Answer" in the link that you provided truly shows a lot more code than you have provided.. please show all relevant code that pertains to your question otherwise you are asking us to be amazing `Mind Readers` – MethodMan Jan 26 '13 at 15:26
  • @DJKRAZE No need to be rude. The problem itself should be clear from the data model I have provided and what I am querying for (which I have also stated). Can you specify what kind of code is missing to explain the problem? – aknuds1 Jan 26 '13 at 15:44
  • I am not being rude I am asking you to show the `code` that you have where you are trying to do the query.. you are only showing what appears to be JSON Script.. – MethodMan Jan 26 '13 at 15:48
  • @DJKRAZE The "JSON Script" as you call it is the MongoDB's representation of the data model. If I knew how to code the desired query in C# I wouldn't have to ask... Data projections in MongoDB are not trivial, AFAICT they were not possible in C# at some point, but I am hoping they are now. – aknuds1 Jan 26 '13 at 15:51
  • are you saying that something like this won't work..? `var query = Query.And(Query.EQ("Books._id", new ObjectId("1"))); MongoCollection collection = _database.GetCollection("Books"); MongoCursor cursor = collection.Find(query);` – MethodMan Jan 26 '13 at 16:03
  • @DJKRAZE No that does not work, since the collection consists of *Library* documents. Books are embedded in Libraries. See also my edit as to why the answer to the linked question is moot. – aknuds1 Jan 26 '13 at 16:29
  • Glad you were able to get that to work.. – MethodMan Jan 26 '13 at 16:30
  • @DJKRAZE It kind of works, but not how I want (as regards the answer to the linked question). – aknuds1 Jan 26 '13 at 16:31
  • I am sure with some messing around with the solution you will be able to get that to work even better.. – MethodMan Jan 26 '13 at 16:34
  • @DJKRAZE No, since it doesn't touch on the fundamental issue which is to project embedded data. – aknuds1 Jan 26 '13 at 16:36

1 Answers1

4

To perform a projection where the resulting document is not just filtered, but reshaped, you need to use Aggregate instead of one of the Find methods like this:

var result = collection.Aggregate(
    // Only include the document where _id = 1
    new BsonDocument {{"$match", new BsonDocument {{"_id", 1}}}},
    // 'Unwind' the Categories array by duplicating the docs, one per element.
    new BsonDocument {{"$unwind", "$Categories"}},
    // Now do the same for the Books array inside each Categories element.
    new BsonDocument {{"$unwind", "$Categories.Books"}},
    // Only include the resulting docs with a Book _id of 3
    new BsonDocument {{"$match", new BsonDocument {{"Categories.Books._id", 3}}}},
    // Reshape the document to bring the book attributes out to the top level 
    new BsonDocument {{"$project", new BsonDocument {
        {"_id", "$Categories.Books._id"},
        {"Name", "$Categories.Books.Name"}
    }}}
);

result.Response.toJson():

{"result": [{ "_id": 3.0, "Name": "The Count of Monte Cristo" }], "ok": 1.0 }
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471