CODEWITHSUNDEEP
c#mongodb
Coffka
Coffka

Reputation: 759

Mongo C# driver How to exclude elements from projection when using elemMatch?

I have a collection named "Trades". This is the stucture of the collection: 

{
 "TradeId": 1234,
 "Products": [
  {
     "Name": "Test product",
     "Offers": [
        {
           "SupplierName": "John Smith",
           "OfferPrice": 12345.6
        }],
     "Requests": [
        {
           "CustomerName": "Anna Doe",
           "RequestPrice": 28574.5
        }]
   }]
}

I need to fetch only "Offers"array for the specified trade while filtering by product's name. Here is what I've done so far:

            var filteredTrade = await _tradesCollection.Find(
                        x => x.TradeId == 1234)
                    .Project<Trade>(Builders<Trade>.Projection.ElemMatch(
                        x => x.Products,
                        i => i.Name == "Test product"))
                    .SingleOrDefaultAsync();

This filters by product's name just as expected. However the query also fetches the entire requests collection. Is there any way I could get only offers collection or exclude the requests collection somehow?

Upvotes: 3

Views: 1434

Answers (1)

mickl
mickl

Reputation: 49985

You need Aggregation Framework to project a document that has multiple levels of nested arrays. In mongo shell you can try following code:

db.col.aggregate([
    {
        $match: { TradeId: 1234 }
    },
    {
        $unwind: "$Products"
    },
    {
        $match: { "Products.Name": "Test product" }
    },
    {
        $project: {
            _id: 0,
            Offers: "$Products.Offers"
        }
    }
])

prints: { "Offers" : [ { "SupplierName" : "John Smith", "OfferPrice" : 12345.6 } ] }

which can be translated to following C# code (returns a list of type Product):

var project = new BsonDocumentProjectionDefinition<BsonDocument>(
    BsonDocument.Parse("{ Offers: \"$Products.Offers\", _id: 0 }"));

var q = Col.Aggregate()
            .Match(x => x.TradeId == 1234)
            .Unwind<Trade>(x => x.Products)
            .Match(x => x["Products.Name"] == "Test product")
            .Project(project)
            .As<Product>()
            .ToList();

Upvotes: 1

Related Questions