How to access deeply nested array with MongoDB (ASP.NET Core 2.2)

Question

I'm designing an inventory management system with MongoDB. I have the following database structure:

inventory
└─storage_slots
└─storage_locations
...etc...

Every time a new Slot is added, a tree representing the slot's location in the hierarchy is added to the storage_locations collection to represent its location (according to location, room, section, shelf). So far I have managed to successfully add a new item where none of the location fields are already used: (The slot is also added to the storage_slots collection)

{
"_id" : ObjectId("5c57169f0863d665c7f13d27"),
"CreatedUtc" : {
    "$date" : 1549211298017
},
"UpdatedUtc" : {
    "$date" : 1549211298017
},
"Description" : null,
"Address" : null,
"StorageRooms" : [
    {
        "_id" : ObjectId("5c57169f0863d665c7f13d28"),
        "CreatedUtc" : {
            "$date" : 1549211297719
        },
        "UpdatedUtc" : {
            "$date" : 1549211297719
        },
        "Description" : null,
        "StorageSections" : [
            {
                "_id" : ObjectId("5c57169f0863d665c7f13d29"),
                "CreatedUtc" : {
                    "$date" : 1549211297719
                },
                "UpdatedUtc" : {
                    "$date" : 1549211297719
                },
                "Description" : null,
                "StorageShelves" : [
                    {
                        "_id" : ObjectId("5c57169f0863d665c7f13d2a"),
                        "CreatedUtc" : {
                            "$date" : 1549211297719
                        },
                        "UpdatedUtc" : {
                            "$date" : 1549211297719
                        },
                        "Description" : null,
                        "StorageSlotIds" : [
                            ObjectId("5c57169f0863d665c7f13d26")
                        ]
                    }
                ]
            }
        ]
    }
]
}

To be clear, storage_locations is the above hierarchy while storage_slots is just a collection of slots.

However, if the fields are already present in the hierarchy, the following code is run: (I took insipration from this post)

var filter = Builders<StorageLocation>.Filter.And(
            Builders<StorageLocation>.Filter.Where(location => location.Id == id),
            Builders<StorageLocation>.Filter.Eq("StorageRooms.Id", roomId),
            Builders<StorageLocation>.Filter.Eq("StorageRooms.$.StorageSections.Id", sectionId),
            Builders<StorageLocation>.Filter.Eq("StorageRooms.$.StorageSections.$.StorageShelves.Id", shelfId));
        var update =
            Builders<StorageLocation>.Update.Push("StorageRooms.$.StorageSections.$.StorageShelves.$.StorageSlotIds",
                storageSlotIds);
        return await UpdateAsync(filter, update, cancellationToken);

Also, if only some of them are defined then I do a mix of both that I decided not to show here because they are built on the same principles and would not contribute to the question.

The problem

Whenever the code directly above is run. I get the following error:

InvalidCastException: Unable to cast object of type 'MongoDB.Bson.ObjectId[]' to type 'MongoDB.Bson.ObjectId'.

MongoDB.Bson.Serialization.Serializers.SerializerBase<TValue>.MongoDB.Bson.Serialization.IBsonSerializer.Serialize(BsonSerializationContext context, BsonSerializationArgs args, object value)

//annoying scrollbar

The error occurs on this line:

return await UpdateAsync(filter, update, cancellationToken);

The method is:

public Task<UpdateResult> UpdateAsync(FilterDefinition<T> filter, UpdateDefinition<T> updateDefinition,
        string database, string collection, CancellationToken cancellationToken)
    {
        return _mongoContext.MongoClient.GetDatabase(database).GetCollection<T>(collection)
            .UpdateOneAsync(filter, updateDefinition.Set(o => o.UpdatedUtc, DateTime.UtcNow),
                cancellationToken: cancellationToken);
    }

Extra stuff

Here are some more relevant classes for the question:

public class StorageLocation : Dbo
{
    public string Description { get; set; }
    public Address Address { get; set; }
    public IEnumerable<StorageRoom> StorageRooms { get; set; }
}
public class StorageRoom : Dbo
{
    public string Description { get; set; }
    public IEnumerable<StorageSection> StorageSections { get; set; }
}
public class StorageSection : Dbo
{
    public string Description { get; set; }
    public IEnumerable<StorageShelf> StorageShelves { get; set; }
}
public class StorageShelf : Dbo
{
    public string Description { get; set; }
    public IEnumerable<ObjectId> StorageSlotIds { get; set; }
}
public class StorageSlot : Dbo
{
    public string Description { get; set; }

    public ObjectId LocationId { get; set; }
    public ObjectId RoomId { get; set; }
    public ObjectId SectionId { get; set; }
    public ObjectId ShelfId { get; set; }

    ...etc...
}

Answer 1

You're getting this error because $ positional operator can be used only once while in your case there are multiple levels of nested arrays. From the docs:

The positional $ operator cannot be used for queries which traverse more than one array, such as queries that traverse arrays nested within other arrays, because the replacement for the $ placeholder is a single value

To fix that you can use filtered positional operator which was introduced in MongoDB 3.6. It allows you to specify multiple placeholders in your update path and then you can use arrayFilters to define conditions for those placeholders.

var filter = Builders<StorageLocation>.Filter.And(
   Builders<StorageLocation>.Filter.Where(location => location.Id == id),
   Builders<StorageLocation>.Filter.Eq("StorageRooms._id", roomId));

var arrayFilters = new List<ArrayFilterDefinition>();
ArrayFilterDefinition<BsonDocument> sectionFilter = new BsonDocument("section._id", new BsonDocument("$eq", sectionId));
ArrayFilterDefinition<BsonDocument> shelfFilter = new BsonDocument("shelf._id", new BsonDocument("$eq", shelfId));
arrayFilters.Add(sectionFilter);
arrayFilters.Add(shelfFilter);

var updateOptions = new UpdateOptions { ArrayFilters = arrayFilters };


var update =
    Builders<StorageLocation>.Update.Push("StorageRooms.$.StorageSections.$[section].StorageShelves.$[shelf].StorageSlotIds",
        storageSlotIds);

await Col.UpdateOneAsync(filter, update, updateOptions);