Querying, filtering and updating multiple level nested arrays in MongoDB using C#

Question

I have this MongoDB document. I am developing an MVC application and trying to update the comment array (commented description to "comment after update") using C#. I'm using the new mongodb version.

{
   "ProjectID":1,
   "ProjectName":"Project test",
   "ProjectStatus":"Active",
   "ProjectTasks":[
      {
         "ProjectTaskID":1,
         "TaskShortDescription":"short task description",
         "TaskLongDescription":"long task description",
         "Comments":[
            {
               "CommentID":1,
               "CommentDescription":"comment before update",
               "CreatedBy":"Mike",
               "UploadDocuments":{
                  "TaskID":null,
                  "CommentID":null,
                  "UploadDocumentID":1,
                  "UploadDocumentName":"first document upload"
               }
            }
         ]
      }
   ]
}

I tried using this:

var filter = Builders<Project>.Filter.And(Builders<Project>.Filter.Eq(x => x.ProjectID, projectID), Builders<Project>.Filter.ElemMatch(x => x.ProjectTasks, x => x.ProjectTaskID == projectTaskID), Builders<Project>.Filter.ElemMatch(x => x.ProjectTasks.ElementAt(-1).Comments, x => x.CommentID == comment.CommentID));

var update = Builders<Project>.Update.Set(x => x.ProjectTasks.ElementAt(-1).Comments.ElementAt(-1).CommentDescription, comment.CommentDescription );

Collection.UpdateOneAsync(filter, update, new UpdateOptions() { IsUpsert = true });

I also tried to change the filter to

var filter = Builders<Project>.Filter.And(Builders<Project>.Filter.Where(p => p.ProjectID == projectID), Builders<Project>.Filter.Eq("ProjectTasks.ProjectTaskID", projectTaskID), Builders<Project>.Filter.Eq("ProjectTasks.$.Comments.$.CommentID", comment.CommentID));

For both cases, I'm not able to query, filter and update the comments.

Can you please tell me how to find and update the comments in this document? Any suggestion is much appreciated!

Answer 1

You should use $[] multiple positional operator, I'll try to write what you should use for the code you've pasted:

var baseFilter = Builders<Project>.Filter.Eq("ProjectID": 1);
var update = Builders<Project>.Update.Set("ProjectTasks.$[i].Comments.$[j].CommentDescription", comment.CommentDescription);

var arrayFilters = new List<ArrayFilterDefinition>
{
    /* change the type names here if they have different names, I just guessed */
    new BsonDocumentArrayFilterDefinition<ProjectTask>(new BsonDocument("i.ProjectTaskID", projectTaskID)),
    new BsonDocumentArrayFilterDefinition<Comment>(new BsonDocument("j.CommentId", commentID))
};

var updateOptions = new UpdateOptions { ArrayFilters = arrayFilters };

await Collection.UpdateOneAsync(baseFilter, update, updateOptions);

Answer 2

I faced the similar problem - I needed to insert a new item in a nested collection. Assuming that we cannot change the data structure the idea is to find the index of a nested entity to update. Then the entity becomes accessible in the update definition.

In the given context what I did is as follows:

var projectTaskIndex = await _mongoContext.Projects
    .Find(p => p.ProjectID == projectID)
    .Project(p => p.ProjectTasks.FindIndex(t => t.ProjectTaskID == projectTaskID))
    .SingleOrDefaultAsync();

var updateDefinition = new UpdateDefinitionBuilder<Project>()
    .AddToSet(p => p.ProjectTasks[projectTaskIndex].Comments, comment);

await _mongoContext.Projects
    .UpdateOneAsync(p=> p.ProjectID == projectID, updateDefinition);

In the same way one can find the index of a comment and access the comment's properties. Update definition will look like follows:

var updateDefinition = new UpdateDefinitionBuilder<Project>()
    .Set(p => p.ProjectTasks[projectTaskIndex]
                 .Comments[commentIndex].CommentDescription,
            comment.CommentDescription);

Very important here is to ensure thread safety - the collections of ProjectTasks and Comments must not be changes between getting the indexes and the actual update.

@Cisco, have you found a solution? If so, could you please share it?