How to update an array in mongodb by using another array as both identifier and value?

Question

Imagine the is a document like this:

{
  _id: ObjectID('someIdblahbla')
  users: [
    {
      _id: 'id1',
      name: 'name1',
    },
    {
      _id: 'id2',
      name: 'name2',
    },
    {
      _id: 'id3',
      name: 'name3'
    }
  ]
}

I have an array like this:

const newData = [
  {_id: 'id1', name: 'newName1'},
  {_id: 'id2', 'name': 'newName2', family:'newFamily2'}
]

what I want is to update the array in the document using the corresponding _id and using it to add/update each element.
so my end result would be like:

{
  _id: ObjectID('someIdblahbla')
  users: [
    {
      _id: 'id1',
      name: 'newName1',
    },
    {
      _id: 'id2',
      name: 'newName2',
      family:'newFamily2'
    },
    {
      _id: 'id3',
      name: 'name3'
    }
  ]
}

my guess was using The filtered positional operator but I am not sure if it's the correct way to go and how to do it.

thank you for your kind tips beforehand.

turivishal · Accepted Answer · 2021-08-22 04:05:51Z

There is no straight way to add/update in array, you can use update with aggregation pipeline starting from MongoDB 4.2,

First of all, you need to convert _id from string to objectId type, if you are using mongoose npm you can use mongoose.Types.ObjectId method or if you are using mongodb npm you can use ObjectId method,

let newData = [
  { _id: 'id1', name: 'newName1' },
  { _id: 'id2', 'name': 'newName2', family:'newFamily2' }
];
let newIds = [];
newData = newData.map(n => {
  n._id = ObjectId(n._id); // or mongoose.Types.ObjectId(n._id)
  newIds.push(n._id); // for checking conditions
  return n;
});

You can put query condition, and do below operations,

$map to iterate loop of users array, check condition if user._id is in input newIds then do update operation otherwise do insert operation
update operation:
- $filter to iterate loop of input newData and filter already present object from input so we can update it
- $arrayElemAt to get first object from above filtered array
- $mergeObjects to merge current object with above input object
insert operation:
- $filter to iterate loop of newData array and return not present object means new items in array of objects
$concatArrays to concat above new and updated result array

db.collection.updateOne(
  { _id: ObjectId("someIdblahbla") },
  [{
    $set: {
      users: {
        $concatArrays: [
          {
            $map: {
              input: "$users",
              as: "u",
              in: {
                $cond: [
                  { $in: ["$$u._id", newIds] },
                  {
                    $mergeObjects: [
                      "$$u",
                      {
                        $arrayElemAt: [
                          {
                            $filter: {
                              input: newData,
                              cond: { $eq: ["$$this._id", "$$u._id"] }
                            }
                          },
                          0
                        ]
                      }
                    ]
                  },
                  "$$u"
                ]
              }
            }
          },
          {
            $filter: {
              input: newData,
              cond: { $not: { $in: ["$$this._id", "$users._id"] } }
            }
          }
        ]
      }
    }
  }]
)

Playground

Is this still working? I am trying this on a project of mine and I am getting errors... Could you take a look here: stackoverflow.com/questions/77188663/…

Takis · Accepted Answer · 2021-08-22 07:17:55Z

Query1 (update(merge objects) existing members, doesn't add new members)

Test code here

Replace

[{"_id": "id1","name": "newName1"},{"_id": "id2","name": "newName2","family": "newFamily2"}] with you array or the driver variable that hold the array

db.collection.update({
  "_id": {
    "$eq": "1"
  }
},
[
  {
    "$addFields": {
      "users": {
        "$map": {
          "input": "$users",
          "as": "user",
          "in": {
            "$reduce": {
              "input": [
                {
                  "_id": "id1",
                  "name": "newName1"
                },
                {
                  "_id": "id2",
                  "name": "newName2",
                  "family": "newFamily2"
                }
              ],
              "initialValue": "$$user",
              "in": {
                "$let": {
                  "vars": {
                    "old_user": "$$value",
                    "new_user": "$$this"
                  },
                  "in": {
                    "$cond": [
                      {
                        "$eq": [
                          "$$old_user._id",
                          "$$new_user._id"
                        ]
                      },
                      {
                        "$mergeObjects": [
                          "$$old_user",
                          "$$new_user"
                        ]
                      },
                      "$$old_user"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

Query2 (update(merge) if found, else push in the end)
Its like the above but finds the not-existing members,and push them in the end.Its a bit more slower and complicated

Test code here

Replace

[{"_id": "id1","name": "newName1"},{"_id": "id2","name": "newName2","family": "newFamily2"},{"_id": "id4","name": "newName4"}] with your array or the driver variable that hold the array

db.collection.update({
  "_id": {
    "$eq": "1"
  }
},
[
  {
    "$addFields": {
      "yourarray": [
        {
          "_id": "id1",
          "name": "newName1"
        },
        {
          "_id": "id2",
          "name": "newName2",
          "family": "newFamily2"
        },
        {
          "_id": "id4",
          "name": "newName4"
        }
      ]
    }
  },
  {
    "$addFields": {
      "new-ids": {
        "$setDifference": [
          {
            "$map": {
              "input": "$yourarray",
              "as": "u",
              "in": "$$u._id"
            }
          },
          {
            "$map": {
              "input": "$users",
              "as": "u",
              "in": "$$u._id"
            }
          }
        ]
      }
    }
  },
  {
    "$addFields": {
      "users": {
        "$concatArrays": [
          {
            "$map": {
              "input": "$users",
              "as": "user",
              "in": {
                "$reduce": {
                  "input": "$yourarray",
                  "initialValue": "$$user",
                  "in": {
                    "$let": {
                      "vars": {
                        "old_user": "$$value",
                        "new_user": "$$this"
                      },
                      "in": {
                        "$cond": [
                          {
                            "$eq": [
                              "$$old_user._id",
                              "$$new_user._id"
                            ]
                          },
                          {
                            "$mergeObjects": [
                              "$$old_user",
                              "$$new_user"
                            ]
                          },
                          "$$old_user"
                        ]
                      }
                    }
                  }
                }
              }
            }
          },
          {
            "$filter": {
              "input": "$yourarray",
              "as": "u",
              "cond": {
                "$in": [
                  "$$u._id",
                  "$new-ids"
                ]
              }
            }
          }
        ]
      }
    }
  },
  {
    "$unset": [
      "yourarray",
      "new-ids"
    ]
  }
])

Collectives™ on Stack Overflow

How to update an array in mongodb by using another array as both identifier and value?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related