Push an item to a deeply nested array in MongoDb

Question

Is it possible (preferably using the C# Builders) to add a new item to a deeply nested array I.e. an array within an array within an array.

My data model looks something like :

public class Company 
{
    public string Id { get; set; }
    public string Name { get; set; }
    public IEnumerable<Department> Departments { get; set; }
}

public class Department
{
    public string Id { get; set; }
    public string Name { get; set; }
    public IEnumerable<Managers> Managers { get; set; }
}

public class Manager
{
    public string Id { get; set; }
    public string Name { get; set; }
    public IEnumerable<Employee> Employees { get; set; }
}

public class Employee 
{
    public string Id { get; set; }
    public string Name { get; set; }
}

Which translates to:

{
    "Id": 12345,
    "Name": "Company Ltd",
    "Departments": [
        {
            "Id": 1,
            "Name": "Development",
            "Managers" : [
                {
                    "Id" : 5555,
                    "Name" : "The Boss",
                    "Employees": [
                        {
                            "Id" : 123,
                            "Name" : "Developer 1"
                        },
                        {
                            "Id" : 124,
                            "Name" : "Developer 2"
                        }
                    ]
                }
            ]
        }
    ]
}

If I wanted to add another employee under a specific manager how would I go about doing that?

B. Fleming · Accepted Answer · 2019-05-31 18:36:11Z

In order to push to a nested array, you must make use of the positional operator $ in order to specify a matching outer array element to apply the operation to. For example:

db.collection.update(
    {"my_array._id": myTargetId},
    {$push: {"my_array.$.my_inner_array": myArrayElem}}
);

This breaks down, however, for traversing nested arrays--that is, you can only use the positional operator on the single array, not any nested ones. This is a well-defined problem as noted in the MongoDB documentation.

If you absolutely need to perform these kinds of nested array operations, then you have a couple of options available to you:

The first, and preferred, is to update your document structure and avoid nesting arrays more than one level deep. This will avoid the issue altogether, but will require any existing data to be migrated to the new structure and additional efforts to be made to structure the data in the way you need on the fly on retrieval. Separate client and server representations of your data will end up being required.

The second is to perform a series of less-reliable steps:
1. Retrieve the original document.
2. Locate the indexes for each array where your target element is located manually.
3. Attempt an update on the specific index chain and attempt to match that index chain as well.
4. Check the result of the update attempt--if it fails, then it's possible that the document was changed while the indexes were being calculated.

For example, if you wanted to update manager with ID 5555 to have the additional employee, you'd perform the following query after retrieving the indexes:

// Index chain found to be Departments.0 and Managers.0
db.collection.update(
    {
        "Id": 12345,
        "Departments.0.Managers.0.Id": 5555 // Specify index chain 0,0 and ensure that our target still has Id 5555.
    },
    { $push: {
        "Departments.0.Managers.0.Employees": myNewEmployee // Push to index chain 0,0
    }}

);

teej · Accepted Answer · 2020-12-03 10:01:18Z

Use positional operator for each of the arrays except the one you want to push to.

Use array filters in update options to specify the department and manager ids. The letter used in the array filters should match the letter used as the positional operator in the update definition. So "d.Id" -> "Departments.$[d]"

If you want to match on more than one property you can use a dictionary in the array filter.

    private IMongoCollection<Company> _collection;

    public async Task AddEmployee()
    {
        var filter = Builders<Company>.Filter.Where(d => d.Id == "companyId");
        var update = Builders<Company>.Update
            .Push("Departments.$[d].Managers.$[m].Employees", new Employee { Id = "employeeId", Name = "employeeName" });

        var updateOptions = new UpdateOptions
        {
            ArrayFilters = new List<ArrayFilterDefinition>
                {
                    new BsonDocumentArrayFilterDefinition<BsonDocument>(new BsonDocument("d.Id", "departmentId")),
                    new BsonDocumentArrayFilterDefinition<BsonDocument>(new BsonDocument("m.Id", "managerId")),
                }
        };

        await _collection.UpdateOneAsync(filter, update, updateOptions);
    }

The problem here is that you need to use strings in the update definition and filters but not sure how to manage it without strings.

To remove an employee from the array is similar but you will have to specify an extra filter for the employee that you want to remove.

    public async Task FireEmployee()
    {
        var filter = Builders<Company>.Filter.Where(d => d.Id == "companyId");
        var employeeFilter = Builders<Employee>.Filter.Where(e => e.Id == "employeeId");
        var update = Builders<Company>.Update
            .PullFilter("Departments.$[d].Managers.$[m].Employees", employeeFilter);

        var updateOptions = new UpdateOptions
        {
            ArrayFilters = new List<ArrayFilterDefinition>
                {
                    new BsonDocumentArrayFilterDefinition<BsonDocument>(new BsonDocument("d.Id", "departmentId")),
                    new BsonDocumentArrayFilterDefinition<BsonDocument>(new BsonDocument("m.Id", "managerId")),
                }
        };

        await _collection.UpdateOneAsync(filter, update, updateOptions);
    }

Collectives™ on Stack Overflow

Push an item to a deeply nested array in MongoDb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related