I have a document in following format:

// Document
{
   _id: ObjectId("67dc90594947be000838f7a7"),
   persons: [
        {
           personId: "61cd90594947be000838f7c1",
           name: "John Doe"
           employment: [
               {
                  employmentId: "61cd9059494abe000838f7c8",
                  type: "Full time",
                  salary: 1010101
               }
           ]
        },
        {
           personId: "61cd90594947be000838f7c2",
           name: "Jane Austin"
           employment: [
               {
                  employmentId: "61cd9059494abe000838f7c8",
                  type: "Part time",
                  salary: 11011111
               }
           ]
        },
   ]
}

I need a single query which either updates existing employment inside persons if the employmentId of employment matches or push new element to the array.

e.g.

Case I: Update employment

// update payload
// This employment id is the first employment of John Doe
// So the first employment will be updated
{
    personId: "61cd90594947be000838f7c1",
    employmentId: "61cd9059494abe000838f7c8",
    frequency: "weekly"
}

// The updated document
{
   _id: ObjectId("67dc90594947be000838f7a7"),
   persons: [
        {
           personId: "61cd90594947be000838f7c1",
           name: "John Doe"
           employment: [
               // This is updated employment
               {
                  employmentId: "61cd9059494abe000838f7c8",
                  type: "Full time",
                  salary: 1010101,
                  frequency: "weekly"
               }
           ]
        },
        {
           personId: "61cd90594947be000838f7c2",
           name: "Jane Austin"
           employment: [
               {
                  employmentId: "61cd9059494abe000838f7c8",
                  type: "Part time",
                  salary: 11011111
               }
           ]
        },
   ]
}

Case II: Push new employment to array

// update payload
// This employment id is not in John Doe
// So the data will be pushed to array
{
    personId: "61cd90594947be000838f7c1",
    employmentId: "61cd9059494abe000738f7c1",
    frequency: "weekly"
}

// The updated document
{
   _id: ObjectId("67dc90594947be000838f7a7"),
   persons: [
        {
           personId: "61cd90594947be000838f7c1",
           name: "John Doe"
           employment: [
               {
                  employmentId: "61cd9059494abe000838f7c8",
                  type: "Full time",
                  salary: 1010101
               },
               /// This is newly added employment
               {
                  employmentId: "61cd9059494abe000738f7c1",
                  frequency: "weekly"
               }
           ]
        },
        {
           personId: "61cd90594947be000838f7c2",
           name: "Jane Austin"
           employment: [
               {
                  employmentId: "61cd9059494abe000838f7c8",
                  type: "Part time",
                  salary: 11011111
               }
           ]
        },
   ]
}

For a non-nested array I was given this answer.

CodePudding user response：

Again, the approach remains similar to the update from the previous question, we have to iterate on each array and determine wether or not the element is matching.

Note that this answer does not handle case #3 that was not required in the question, the case where a person does not exist. based on the input I gather the assumption the person exists is sound.

const input = {
    personId: "61cd90594947be000838f7c1",
    employmentId: "61cd9059494abe000738f7c1",
    frequency: "weekly"
};

const employmentInput = {
    employmentId: "61cd9059494abe000738f7c1",
    frequency: "weekly"
};

db.collection.update({},
[
  {
    $set: {
      persons: {
        $map: {
          input: "$persons",
          as: "person",
          in: {
            $cond: [
              {
                $eq: [
                  input.personId,
                  "$$person.personId"
                ]
              },
              {
                $mergeObjects: [
                  "$$person",
                  {
                    employment: {
                      $cond: [
                        {
                          $in: [
                            input.employmentId,
                            "$$person.employment.employmentId"
                          ]
                        },
                        {
                          $map: {
                            input: "$$person.employment",
                            as: "employment",
                            in: {
                              $cond: [
                                {
                                  $eq: [
                                    input.employmentId,
                                    "$$employment.employmentId"
                                  ]
                                },
                                {
                                  $mergeObjects: [
                                    "$$employment",
                                    employmentInput
                                  ]
                                },
                                "$$employment"
                              ]
                            }
                          }
                        },
                        {
                          $concatArrays: [
                            "$$person.employment",
                            [
                              employmentInput
                            ]
                          ]
                        }
                      ]
                    }
                  }
                ]
              },
              "$$person"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground