C# MongoDb Conditionally Update Existing Array Elements In Document And Add New Element

Question

With the following document structure

public class Disclaimer
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public ObjectId Id { get; set; }

    public List<Override> Overrides { get; set; }
}

public class Override
{
    public int CategoryId { get; set; }
    public DateTime EffectiveDate { get; set; }
    public DateTime? ExpiryDate { get; set; }
    public string Message { get; set; }
}

And the query

var idFilter = Builders<Models.Database.Disclaimer>.Filter.Where(x => x.Id == ObjectId.Parse(id) && x.Overrides.Any(y => y.CategoryId == createOverrideDto.CategoryId)));

var overrideCancellations = Builders<Models.Database.Disclaimer>.Update.Set(x => x.Overrides[-1].ExpiryDate, DateTime.Now);

var update = overrideCancellations.Push(x => x.Overrides, new Models.Database.Override()
  {
    CategoryId = createOverrideDto.CategoryId,
    EffectiveDate = DateTime.Now,
    Message = createOverrideDto.Message
  });

result = _repository.Get().UpdateOne(filter, update);

I would like to update existing Override elements with an expiry date when a new one is added in the same category, and add the new element. If there are existing elements not in the same category, these should be left alone, just the new element should be appended.

My current code works fine if there is one or more existing Override elements with the chosen category. However, when there are not, i.e if the array is empty or all existing are not in the category, the filter clause does not match, and no update runs.

I have tried to amend my filter to match when there are no overrides in this category. However, then any existing elements not in the selected category are also updated i.e. have an expiry date added.

I could change my document structure so that Overrides is an object with category id field and then separate arrays, but I may still have the upsert problem?

Answer 1

what you want to do requires the use of array filters and a bulk update with 2 steps. here's an example of how it's done using MongoDB.Entities for brevity. but it's directly translatable to the official driver as it's just a wrapper around the driver.

using MongoDB.Entities;
using System;

namespace StackOverflow
{
    public class Disclaimer : Entity
    {
        public Override[] Overrides { get; set; }
    }

    public class Override
    {
        public int CategoryId { get; set; }
        public DateTime EffectiveDate { get; set; }
        public DateTime? ExpiryDate { get; set; }
        public string Message { get; set; }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            new DB("test", "localhost");

            // create seed data
            var seed = new Disclaimer
            {
                Overrides = new[]
                {
                    new Override
                    {
                        CategoryId = 666, EffectiveDate = DateTime.Now.AddDays(-1), Message = "disclaimer 1"
                    },
                    new Override
                    {
                        CategoryId = 666, EffectiveDate = DateTime.Now.AddDays(-1), Message = "disclaimer 2"
                    },
                    new Override
                    {
                        CategoryId = 777, EffectiveDate = DateTime.Now.AddDays(-1), Message = "disclaimer 3"
                    }
                }
            }; seed.Save();

            // start bulk update command
            DB.Update<Disclaimer>()

              // step1: set expiry date on existing 666s
              .Match(d => d.ID == seed.ID)
              .WithArrayFilter("{ 'x.CategoryId' : 666 }")
              .Modify(b => b.Set("Overrides.$[x].ExpiryDate", DateTime.Now))
              .AddToQueue()

              // step2: add a new 666
              .Match(d => d.ID == seed.ID)
              .Modify(b => b.Push(d => d.Overrides,
                                         new Override
                                         {
                                             CategoryId = 666,
                                             EffectiveDate = DateTime.Now,
                                             Message = "disclaimer 4"
                                         }))
              .AddToQueue()

              // run two step bulk update command
              .Execute();
        }
    }
}