mongodbaggregation-framework

Mongo DB Nested Array Updates & create in single mongo command


I have a nested mongo array

{
  "_id": 1,
  "date": "2024-09-30",
  "employees": [
    {
      "_id": 101,
      "name": "John Doe",
      "company": "ABC Corp",
      "department": "HR",
      "salary": 50000,
      "contactNumber": "123-456-7890"
    },
    {
      "_id": 102,
      "name": "A",
      "company": "XYZ Corp",
      "department": "HR",
      "salary": 500100,
      "contactNumber": "1333-456-7890"
    },
    {
      "_id": 103,
      "name": "B",
      "company": "XYZ Corp",
      "department": "HR",
      "salary": 500100,
      "contactNumber": "1333-456-7890"
    }
  ]
}


I need to update the salary of 101 and also add new memeber 104. How can i do in single update?

I am able to do with 2 updates. One to update 101's salary using ArrayFilters and another is to $push 104 to array. Below are the queries,

db.collection.updateOne(
  { _id: 1 },  // Match the document with _id: 1
  {
    $set: { "employees.$[emp].salary": 60000 }  // Set the new salary for employee with _id: 101
  },
  {
    arrayFilters: [ { "emp._id": 101 } ]  // Array filter to target the specific employee with _id: 101
  }
)
db.collection.updateOne(
  { _id: 1 },  // Match the document with _id: 1
  {
    $push: {
      employees: {
        _id: 104,
        name: "New Employee",
        company: "New Corp",
        department: "HR",
        salary: 70000,
        contactNumber: "987-654-3210"
      }
    }
  }
)

Is there any way I can do both adding new employee and updating existing employee in same query.?

I think I can use aggregate to do so. but which is better in case of performance?


Solution

  • You can do the following in an update with aggregation pipeline.

    1. $map to iterate through the array. Conditionally update the array entry with $mergeObjects if the entry's id match your update cases(i.e. id =101 or 102) in a $switch
    2. $concatArrays to append your new employee entry there
    db.collection.update({
      _id: 1
    },
    [
      {
        "$set": {
          "employees": {
            "$map": {
              "input": "$employees",
              "as": "e",
              "in": {
                "$switch": {
                  "branches": [
                    {
                      "case": {
                        "$eq": [
                          101,
                          "$$e._id"
                        ]
                      },
                      "then": {
                        "$mergeObjects": [
                          "$$e",
                          {
                            "salary": 60000
                          }
                        ]
                      }
                    },
                    {
                      "case": {
                        "$eq": [
                          102,
                          "$$e._id"
                        ]
                      },
                      "then": {
                        "$mergeObjects": [
                          "$$e",
                          {
                            "salary": 70000
                          }
                        ]
                      }
                    }
                  ],
                  "default": "$$e"
                }
              }
            }
          }
        }
      },
      {
        "$set": {
          "employees": {
            "$concatArrays": [
              "$employees",
              [
                // new employee to insert here
                {
                  _id: 104,
                  name: "New Employee",
                  company: "New Corp",
                  department: "HR",
                  salary: 70000,
                  contactNumber: "987-654-3210"
                }
              ]
            ]
          }
        }
      }
    ])
    

    Mongo Playground