Move data from one array to another array in the same document and delete element from original array

Working with Data
, ,
1

I need to move an array element from one array to another array within the same document and delete the element from the original array it was moved from.

// Document
{
   persons: [
        {
           personId: "61cd90594947be000838f7c1",
           name: "John Doe"
           employment: [
               {
                  type: "Full time",
                  salary: 1010101
               }
           ]
        },
        {
           personId: "61cd90594947be000838f7c2",
           name: "Jane Austin"
           employment: [
               {
                  type: "Part time",
                  salary: 11011111
               }
           ]
        },
   ]
}

I need to move one of the employment element from John Doe to Jane Austin and both of those persons are in the same document. And I need to delete that moved element from John Doe.

I tried using update pipeline but to no avail.


db.personCollection.updateOne(
    { _id: ObjectId("61cd90594947be000838f7c8") },
    [
        { 
            "$project": {
                "dataToMove": {
                    "$ifNull": [
                        { 
                            "$arrayElemAt": [
                                { 
                             
                                    "$slice": [
                                        { 
                                            "$map": {
                                                "input": { 
                                                    "$slice": [ "$persons", 0, 1 ] 
                                                },
                                                "as": "el",
                                                "in": "$$el.employment"
                                            }
                                        },
                                        0, 1
                                    ]
                                    
                                },
                                0
                            ]
                        },
                        "*"
                    ]
                }
            }
        },
        {
            $set: {
                [`persons.1.employment`] : {
                    $concatArrays: [ 
                        "$persons.1.employment", 
                        "$dataToMove"
                    ]
                }
            }
        }, 
        {
            $pull: {
                [`persons.1.employment`]: {
                    $elemMatch: "$dataToMove"
                }
            }
        }
    ]
);
2

I am not sure I understand what is the goal of your update from your description and the aggregation is quite complex. Please post the result document you want. What is the criteria to determine which element of the employment array you need to move.

3

The original doc is:

// Document
{
   persons: [
        {
           personId: "61cd90594947be000838f7c1",
           name: "John Doe"
           employment: [
               {
                  type: "Full time",
                  salary: 1010101
               }
           ]
        },
        {
           personId: "61cd90594947be000838f7c2",
           name: "Jane Austin"
           employment: [
               {
                  type: "Part time",
                  salary: 11011111
               }
           ]
        },
   ]
}

The result i want is:

// Document
{
   persons: [
        {
           personId: "61cd90594947be000838f7c1",
           name: "John Doe"
           employment: [ ]
        },
        {
           personId: "61cd90594947be000838f7c2",
           name: "Jane Austin"
           employment: [
               {
                  type: "Part time",
                  salary: 11011111
               },
               {
                  type: "Full time",
                  salary: 1010101
               }
           ]
        },
   ]
}
5

Hey! I have included the result wanted!

6

Still need

How do we know that we need to move employment.0 of John Doe to Jane Austin?

Is it always employment.0?

7

Welcome to the MongoDB Community Forums @Ashish_Kafle_N_A !

As @steevej noted, the aggregation update does seem overly complex. If this isn’t a common operation for your use case, I would be inclined to just fetch the document, perform manipulations client side, and save the new document.

If this is a common operation, there is probably a more efficient way to model your data :wink: .

Regards,
Stennie

8

Yes definitely the update pipeline will be very complex as noted by @steevej.
But as this can be recurring operation handling this from client side was not an option.
I came to a solution using update pipeline. Though we needed to add a unique identifier to employment as well.
So the slightly modified data structure became:

{
  "persons": [
    {
      "personId": "61cd90594947be000838f7c1",
      "name": "John Doe",
      "employment": [
        {
          "employmentId": "61cd90594947be000838f7b2",
          "type": "Full time",
          "salary": 1010101
        }
      ]
    },
    {
      "personId": "61cd90594947be000838f7c2",
      "name": "Jane Austin",
      "employment": [
        {
          "employmentId": "61cd90594947be000838f7b3",
          "type": "Part time",
          "salary": 11011111
        }
      ]
    },
  ]
}

Below is the query used:

db.collection.update({},
[
  {
    $set: {
      "persons": {
        $map: {
          input: "$persons",
          as: "person",
          in: {
            $switch: {
              branches: [
                {
                  case: {
                    $eq: [
                      // ID of the person to move data to
                      "61cd90594947be000838f7c2",
                      "$$person.personId"
                    ]
                  },
                  then: {
                    $mergeObjects: [
                      "$$person",
                      {
                        employment: {
                          $concatArrays: [
                            "$$person.employment",
                            [
                              {
                                $function: {
                                  body: "function(employment) {if(!employment.employmentId) throw Error(\"[employmentId] is not valid!\");return employment;}",
                                  args: [
                                    {
                                      $reduce: {
                                        input: {
                                          $filter: {
                                            input: "$persons",
                                            as: "fromPerson",
                                            cond: {
                                              $eq: [
                                                "$$fromPerson.personId",
                                                // ID of the data from which data is moved
                                                "61cd90594947be000838f7c1",
                                                
                                              ]
                                            }
                                          }
                                        },
                                        initialValue: {},
                                        in: {
                                          $mergeObjects: [
                                            "$$value",
                                            {
                                              $arrayElemAt: [
                                                {
                                                  $filter: {
                                                    input: "$$this.employment",
                                                    as: "employment",
                                                    cond: {
                                                      $eq: [
                                                        "$$employment.employmentId",
                                                        // ID of the element to move
                                                        "61cd90594947be000838f7b2",
                                                      ]
                                                    }
                                                  }
                                                },
                                                0
                                              ]
                                            }
                                          ]
                                        }
                                      }
                                    }
                                  ],
                                  lang: "js",
                                  
                                }
                              }
                            ]
                          ],
                          
                        }
                      }
                    ]
                  },
                  
                },
                {
                  case: {
                    $eq: [
                      // ID of the person from which to move data
                      "61cd90594947be000838f7c1",
                      "$$person.personId"
                    ]
                  },
                  then: {
                    $mergeObjects: [
                      "$$person",
                      {
                        employment: {
                          $filter: {
                            input: "$$person.employment",
                            as: "employment",
                            cond: {
                              $ne: [
                                "$$employment.employmentId",
                                // ID of the data to move
                                "61cd90594947be000838f7b2",
                              ]
                            }
                          }
                        }
                      }
                    ]
                  },
                  
                },
                
              ],
              default: "$$person"
            }
          }
        }
      }, 
    }
  },
])

This query produces the result I want with some error handling baked into the query itself!

Here $reduce is used. $reduce can be replaced by $getField in MongoDB 5.0. But as we were in Mongodb 4.4 we had to use $reduce as a replacement.

Playground