C# mongodb delete document from doubly nested array-CodePudding

First of all, I found so many solutions for python and mongo shell, but nothing for C#, this is why I create this thread.

The database has a list of projects, the project has a list of files and the list of files has a list of translations. The task is to delete a translation document with a given ID from the translation array. We know every information to find the document like _id of the project, _id of the file and the ID of the translation.

Here is how it looks:

[{
  "_id": "62e237a1d866d82c881629a1",
  "Name": "test92",
  "PrimarySourceLanguage": "eng",
  "PivotSourceLanguage": "hun",
  "TargetLanguages": [
    "prs",
    "fre-BE",
    "fre-CH"
  ],
  "Files": [
    {
      "segments": [
        
      ],
      "Name": "smallBatch_test.json",
      "_id": "f7b02afb-ec62-439b-be6d-403b8f1434ff",
      "ImportDate": {
        "$date": {
          "$numberLong": "1658992556090"
        }
      },
      "TargetLanguages": null,
      "Translation": [
        {
          "ID": "e5e09f84-64d7-40a4-87f7-a90f5a956c32",
          "SourceLanguage": "eng",
          "TargetLanguage": "prs",
          "TranslatedString": null,
          "ProjectGuid": "62e237a1d866d82c881629a1",
          "FileGuid": "f7b02afb-ec62-439b-be6d-403b8f1434ff",
          "Status": "Translation deleted"
        },
        {
          "ID": "8d4ca52c-0063-474a-94db-fd2d8b1f2cee",
          "SourceLanguage": "eng",
          "TargetLanguage": "prs",
          "TranslatedString": "Boci múú",
          "ProjectGuid": "62e237a1d866d82c881629a1",
          "FileGuid": "f7b02afb-ec62-439b-be6d-403b8f1434ff",
          "Status": null
        },
        {
          "ID": "2929dc9e-5254-4093-93bc-fbcdcb265ea7",
          "SourceLanguage": "eng",
          "TargetLanguage": "prs",
          "TranslatedString": "Bocikaaa",
          "ProjectGuid": "62e237a1d866d82c881629a1",
          "FileGuid": "f7b02afb-ec62-439b-be6d-403b8f1434ff",
          "Status": "Translated"
        }
      ]
    }
  ],
  "TemplateGuid": {
    "$binary": {
      "base64": "AAAAAAAAAAAAAAAAAAAAAA==",
      "subType": "03"
    }
  },
  "Deadline": {
    "$date": {
      "$numberLong": "1659165300000"
    }
  },
  "TMs": [
    null
  ],
  "TBs": [
    null
  ],
  "Domain": null,
  "Client": null
}]

I tried to use pullfilter or using update with Pull command, but none of them worked :(

CodePudding user response：

The following implementation assumes that the translation ids are globally unique, so that no two files have translations with the same id. It uses an update with an aggregation pipeline that maps the files so that only translations are contained with an ID other than the one to delete:

db.collection.update({
  _id: "62e237a1d866d82c881629a1"
},
[
  {
    "$set": {
      "Files": {
        "$map": {
          "input": "$Files",
          "as": "file",
          "in": {
            "$mergeObjects": [
              "$$file",
              {
                Translation: {
                  "$filter": {
                    "input": "$$file.Translation",
                    "as": "translation",
                    "cond": {
                      "$ne": [
                        "$$translation.ID",
                        "e5e09f84-64d7-40a4-87f7-a90f5a956c32"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

This playground contains a simplified version of the sample data and contains the plain update.

The downside is that the support for updates with aggregation pipelines in C# does not cover all options that MongoDB offers. But you can use a BsonDocument to define the pipeline (I assume the model class is called Project):

var filter = Builders<Project>.Filter
  .Eq(x => x.Id, "62e237a1d866d82c881629a1");
var pipeline = new EmptyPipelineDefinition<Project>()
  .AppendStage(new BsonDocument("$set", 
    new BsonDocument("Files", 
    new BsonDocument("$map", 
    new BsonDocument
                {
                    { "input", "$Files" }, 
                    { "as", "file" }, 
                    { "in", 
                        new BsonDocument("$mergeObjects", 
                        new BsonArray
                        {
                            "$$file",
                            new BsonDocument("Translation", 
                            new BsonDocument("$filter", 
                            new BsonDocument
                                    {
                                        { "input", "$$file.Translation" }, 
                                        { "as", "translation" }, 
                                        { "cond", 
                                          new BsonDocument("$ne", 
                                          new BsonArray
                                            {
                                                "$$translation.ID",
                                                "e5e09f84-64d7-40a4-87f7-a90f5a956c32"
                                            }) }
                                    }))
                        }) }
                }))),
                BsonDocumentSerializer.Instance)
  .As<Project, BsonDocument, Project>();
var update = Builders<Project>.Update.Pipeline(pipeline);
var result = await coll.UpdateOneAsync(filter, update);