MongoDB aggregation to update more than one collection

user_8223031 0
Viewed 129

was wondering if it's possible to create such aggregation that could modify three collection in one go?

For example imagine that we have these collections:

MasterCollection:

{
  _id: ObjectId;
}

CollectionOne:

{
  _id: ObjectId;
  _masterCollectionId: ObjectId;
  _parentId: ObjectId;
  _title: String;
  _displayTitle: String;
}

CollectionTwo:

{
  _id: ObjectId;
  _masterCollectionId: ObjectId;
  _parentId: ObjectId;
  _displayTitle: String;
}

CollectionThree:

{
  _id: ObjectId;
  _masterCollectionId: ObjectId
  _parentId: ObjectId;
  _displayTitle: String;
}

Aggregation to lookup:

[
  {
    $match: {
        _id: new ObjectId("618552b66f82e69572e9bf10")
     }
  }, 
  {
    $lookup: {
        from: "CollectionOne",
        let: {
            "collectionOneId": "$_id"
        },
        pipeline: [{
            "$match": {
                "$expr": {
                    "$eq": ["$_parentId", "$$collectionOneId"]
                }
            }
        }, {
            "$lookup": {
                "from": "CollectionTwo",
                "let": {
                    "collectionTwoId": "$_id"
                },
                "pipeline": [{
                    "$match": {
                        "$expr": {
                            "$eq": ["$_parentId", "$$collectionTwoId"]
                        }
                    }
                }, {
                    "$lookup": {
                        "from": "CollectionThree",
                        "let": {
                            "collectionThreeId": "$_id"
                        },
                        "pipeline": [{
                            "$match": {
                                "$expr": {
                                    "$eq": ["$_parentId", "$$collectionThreeId"]
                                }
                            }
                        }],
                        "as": "CollectionThree"
                    }
                }],
                "as": "CollectionTwo"
            }
        }],
        as: "CollectionOne"

    } 
}]

With aggregation above I can get all children of MasterCollection record. In similar way I could get CollectionOne record children.

But I am thinking if there could be a way to apply changes within pipeline for each record.

For example imagine situation that there is a need to change _masterCollectionId for CollectionOne record.

If we change that property we need to update all children records (CollectionTwo / CollectionThree).

  1. Would need to update targeted CollectionOne record.
  2. Would need to update CollectionTwo records that points to updated CollectionOne record.
  3. Would need to update CollectionThree records that points to updated records of CollectionTwo.

Currently, programmatically I can do a loop and update everything, it kinda works. But I think there should be better solution, probably at database layer, using MongoDB aggregations.

Can MongoDB Aggregation achieve desired result ?

Any help would be appreciated.

mongoose mongodb
1 Answers

Unfortunately I think what you're after is impossible using aggregation alone like you've explained.

The MongoDB documentation states that you can pass an aggregation pipeline into an updateMany() call where you can perform operations on the collection within the aggregation pipeline in MongoDB 4.2 or newer.

I haven't actually tried this technique personally, but it sounds like it might do the job. Based on the documentation, you'd need to run the updateMany() on each collection, which is still a huge improvement on what you've got already. Check it out here.

Something like this:

await CollectionOne.updateMany(
    { _masterClientId: <oldID> },
    [
        { $set: { "_masterCollectionId": <newID> } }
    ]
);

// And repeat for CollectionTwo and CollectionThree
user_169885400
Related