MongoDB c# retrieve all the matching elements in an array within a document using Definition builder

Question

I have a document that looks like this in structure with nested sub document

{  
   "_id":ObjectId("50419077c2e6a1e18a489a0f"),
   "user":"Jone Doe",
   "fooArray":[  
      {  
         "plot":"circle",
         "color":"yellow",
      },
      {  
         "plot":"circle",
         "color":"red",
      },
      {  
         "plot":"square",
         "color":"green",
      }
   ]
}

And I want to retrieve all the matching elements in fooArray in this document that has circular plot.

This is what I tried

var filter = FilterBuilder.filter.Eq(doc => doc.User, User);
var projection = ProjectionBuilder
                .Exclude(doc => doc.Id)
                .Exclude(doc => doc.User)
                .Include(doc => doc.FooArray)
                .ElemMatch(x => x.FooArray, y => y.Plot == "circle");

var definition = new OperationDefinitions<ShapeDocument> { Filter = filter };
            return await Performer.Perform(definition, async (def, collection) =>
            {
                var findResult = collection.Find(def.Filter).Project(projection);

                var result = await findResult.SingleOrDefaultAsync();
            });

This is what I get

{  
   "fooArray":[  
      {  
         "plot":"circle",
         "color":"yellow",
      }
   ]
}

But it only gives me the first matching element instead of all the elements that have the plot equals to circle

{  
   "fooArray":[  
      {  
         "plot":"circle",
         "color":"yellow",
      },
      {  
         "plot":"circle",
         "color":"red",
      }
   ]
}

I did read the mongodb documentation which mentions

" The $elemMatch operator limits the contents of an field from the query results to contain only the first element matching the $elemMatch condition."

Not quite sure how to achieve this!

Answer 1

The question doesn't fully describe the use case so I've come up with a few potential options for you to explore based on a few assumptions, in particular they depend on LINQ being available and to be targetting a single document at a time (and that you probably don't want more code than you really need):

1) A variation on what you have. Use a standard find with a projection and LINQ expression.

var projection = Builders<ShapeDocument>.Projection
    .Expression(x => x.fooArray.Where(y => y.plot == "circle"));

var items1 = collection
    .Find(x => x.user == "Jone Doe")
    .Project(projection)
    .ToList();

2) Use the aggregation pipeline (you could use the same projection as above)

var pipeline = collection
    .Aggregate()
    .Match(x => x.user == "Jone Doe")
    .Project(i => new
            {
                x = i.fooArray.Where(x => x.plot == "circle")
            });

var items2 = pipeline.SingleOrDefault();

3) Pull the document back with all array elements then filter locally using LINQ. On the plus side this is a small amount of readable code, however, it does bring the entire document back before filtering. Depending on your exact use this may well be acceptable.

var items3 = collection.AsQueryable()
    .SingleOrDefault(x => x.user == "Jone Doe")
    .fooArray.Where(x => x.plot == "circle");

If LINQ really isn't an option then there's an example here that shows how you might convert the projection to not us LINQ. Totally untested but would be something along the lines of:

var filter = new BsonDocument {
 {"input", "$items"},
 {"as", "item" },
 {"cond", new BsonDocument {
     // Fill in the condition values
     { "", new BsonArray { "", xxx } } }
   }
 };

var project = new BsonDocument {
 { "items", new BsonDocument { { "$filter", filter} } }
};

var pipeline = collection.Aggregate().Project(project);