I have two collections, 1. temporaryCollection, 2. permanentCollection, I would like to take data from temporaryCollection and update in permanentCollection. To see the expected result see updatedPermanentCollection below.

Fields that are taken from Temporary collection and updated in Permanent collection are:

1. emailAddresses
2. phoneNumbers
3. ContactName
4. ContactNumber

For your info, the fields that are changed in Temporary collection

1. contacts[0]['emailAddresses']
2. contacts[0]['ContactName']
3. contacts[0]["phoneNumbers"]
4. contacts[0]["ContactNumber"]

Field that are that should not be changed after updation in UpdatedPermanentCollection is

contacts._id

Note: contacts is an Array of objects, for simplicity I have shown just one object.

I am currently using the below query which updates the permanentCollection but also overrides the contacts._id field. I don't want the contacts._id field to be overridden.

Here is my MongoDB Query

db.temporaryCollection.aggregate([
  {
    $match: {
      userID: ObjectId("61d1efea2c0fab00340f47c8"),
    },
  },
  {
    $merge: {
      into: "permanentCollection",
      on: "userID",
      whenMatched: "merge",
      whenNotMatched: "insert",
    },
  },
]);

1. temporaryCollection

{
  "_id": { "$oid": "61d1f04266289f003452d705" },
  "userID": { "$oid": "61d1efea2c0fab00340f47c8" },
  "contacts": [
    {
      "emailAddresses": [
        { "id": "6884", "label": "email1", "email": "[email protected]" }
      ],
      "phoneNumbers": [
        {
          "label": "other",
          "id": "4594",
          "number": " 918984292930"
        },
        {
          "label": "other",
          "id": "4595",
          "number": " 911234567890"
        }
      ],
      "_id": { "$oid": "61d1f04266289f003452d744" },
      "ContactName": "Sample User 1 Name Changed",
      "ContactNumber": " 918984292930",
      "recordID": "833"
    }
  ],
  "userNumber": " 911234567890",
  "__v": 7
}

2. permanentCollection

    {
  "_id": { "$oid": "61d1f04266289f003452d701" },
  "userID": { "$oid": "61d1efea2c0fab00340f47c8" },
  "contacts": [
    {
      "emailAddresses": [],
      "phoneNumbers": [
        {
          "label": "other",
          "id": "4594",
          "number": " 918984292929"
        },
        {
          "label": "other",
          "id": "4595",
          "number": " 911234567890"
        }
      ],
      "_id": { "$oid": "61d1f04266289f003452d722" },
      "ContactName": "Sample User 1",
      "ContactNumber": " 918984292929",
      "recordID": "833"
    }
  ],
  "userNumber": " 911234567890",
  "__v": 7
}

3. updatedPermanentCollection (Expected result)

    {
  "_id": { "$oid": "61d1f04266289f003452d701" },
  "userID": { "$oid": "61d1efea2c0fab00340f47c8" },
  "contacts": [
    {
      "emailAddresses": [
        { "id": "6884", "label": "email1", "email": "[email protected]" }
      ],
      "phoneNumbers": [
        {
          "label": "other",
          "id": "4594",
          "number": " 918984292930"
        },
        {
          "label": "other",
          "id": "4595",
          "number": " 911234567890"
        }
      ],
      "_id": { "$oid": "61d1f04266289f003452d722" },
      "ContactName": "Sample User 1 Name Changed",
      "ContactNumber": " 918984292930",
      "recordID": "833"
    }
  ],
  "userNumber": " 911234567890",
  "__v": 7
}

CodePudding user response：

Try to add $unset to db query.

db.temporaryCollection.aggregate([
  {
    $unset: "_id"
  },
  {
    $match: {
      userID: ObjectId("61d1efea2c0fab00340f47c8"),
    },
  },
  {
    $merge: {
      into: "permanentCollection",
      on: "userID",
      whenMatched: "merge",
      whenNotMatched: "insert",
    },
  },
]);

CodePudding user response：

Try with this aggregation query.

db.temporarCollection.aggreagate(
[
  {
    "$lookup": {
      "from": "permanantCollection", 
      "let": {
        "user_id": "$userID"
      }, 
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$eq": [
                "$$user_id", "$userID"
              ]
            }
          }
        }
      ], 
      "as": "pcontacts"
    }
  }, {
    "$unwind": {
      "path": "$pcontacts", 
      "preserveNullAndEmptyArrays": true
    }
  }, {
    "$project": {
      "contacts": {
        "$map": {
          "input": "$contacts", 
          "as": "contact", 
          "in": {
            "tcontact": "$$contact", 
            "pcontact": {
              "$first": {
                "$filter": {
                  "input": "$pcontacts.contacts", 
                  "as": "pcontact", 
                  "cond": {
                    "$eq": [
                      "$$pcontact.recordID", "$$contact.recordID"
                    ]
                  }
                }
              }
            }
          }
        }
      }, 
      "userNumber": 1, 
      "userID": 1, 
      "_id": 0
    }
  }, {
    "$project": {
      "contacts": {
        "$map": {
          "input": "$contacts", 
          "as": "contact", 
          "in": {
            "emailAddresses": "$$contact.tcontact.emailAddresses", 
            "phoneNumbers": "$$contact.tcontact.phoneNumbers", 
            "ContactName": "$$contact.tcontact.ContactName", 
            "ContactNumber": "$$contact.tcontact.ContactNumber", 
            "recordID": {
              "$let": {
                "vars": {}, 
                "in": {
                  "$cond": {
                    "if": "$$contact.pcontact.recordID", 
                    "then": "$$contact.pcontact.recordID", 
                    "else": "$$contact.tcontact.recordID"
                  }
                }
              }
            }, 
            "_id": {
              "$let": {
                "vars": {}, 
                "in": {
                  "$cond": {
                    "if": "$$contact.pcontact._id", 
                    "then": "$$contact.pcontact._id", 
                    "else": "$$contact.tcontact._id"
                  }
                }
              }
            }
          }
        }
      }, 
      "userNumber": 1, 
      "userID": 1
    }
  }, {
    "$merge": {
      "into": "pc", 
      "on": "userID", 
      "whenMatched": "replace", 
      "whenNotMatched": "insert"
    }
  }
])

It is not a fully optimized query but it works.