MongoDB - Update all entries in nested array only if they exist

Question

I have a multilevel nested document (its dynamic and some levels can be missing but maximum 3 levels). I want to update all the children and subchildren routes if any. The scenario is same as in any Windows explorer, where all subfolders' route need to change when a parent folder route is changed. For eg. In the below example, If I am at route=="l1/l2a" and it's name needs to be edited to "l2c", then I will update it's route as route="l1/l2c and I will update all childrens' route to say "l1/l2c/l3a".

     {
    "name":"l1",
    "route": "l1",
    "children":
        [
            {
            "name": "l2a",
            "route": "l1/l2a",
            "children": 
                [
                    {
                    "name": "l3a",
                    "route": "l1/l2a/l3a"
                 }]
            },
            {
            "name": "l2b",
            "route": "l1/l2b",
            "children": 
                [
                    {
                    "name": "l3b",
                    "route": "l1/l2b/l3b"
                 }]
            }
      ]
     }

Currently I am able to go to a point and I am able to change its name and ONLY its route in the following manner:

router.put('/navlist',(req,res,next)=>{
newname=req.body.newName //suppose l2c
oldname=req.body.name //suppose l2a
route=req.body.route // existing route is l1/l2a
id=req.body._id


newroute=route.replace(oldname,newname); // l1/l2a has to be changed to l1/l2c
let segments = route.split('/');  
let query = { route: segments[0]};
let update, options = {};

let updatePath = "";
options.arrayFilters = [];
for(let i = 0; i < segments.length  -1; i++){
    updatePath += `children.$[child${i}].`;
    options.arrayFilters.push({ [`child${i}.route`]: segments.slice(0, i + 2).join('/') });
} //this is basically for the nested children

updateName=updatePath+'name'
updateRoute=updatePath+'route';

update = { $setOnInsert: { [updateName]:newDisplayName,[updateRoute]:newroute } };      
NavItems.updateOne(query,update, options)
 })

The problem is I am not able to edit the routes of it's children if any i.e it's subfolder route as l1/l2c/l3a. Although I tried using the $[] operator as follows.

updateChild = updatePath+'.children.$[].route'
updateChild2 = updatePath+'.children.$[].children.$[].route'
//update = { $set: { [updateChild]:'abc',[updateChild2]:'abc' } };

Its important that levels are customizable and thus I don't know whether there is "l3A" or not. Like there can be "l3A" but there may not be "l3B". But my code simply requires every correct path else it gives an error

code 500 MongoError: The path 'children.1.children' must exist in the document in order to apply array updates.

So the question is how can I apply changes using $set to a path that actually exists and how can I edit the existing route part. If the path exists, it's well and good and if the path does not exist, I am getting the ERROR.

Yes l1 is unique. Actually names and routes need not to be same, but for simplicity I have kept them same. — Swapnil Sourabh
– Swapnil Sourabh, Commented Nov 12, 2020 at 4:49

s7vr · Accepted Answer · 2020-11-16 16:05:49Z

4

Update

You could simplify updates when you use references.Updates/Inserts are straightforward as you can only the update target level or insert new level without worrying about updating all levels. Let the aggregation takes care of populating all levels and generating route field.

Working example - https://mongoplayground.net/p/TKMsvpkbBMn

Structure

[
  {
    "_id": 1,
    "name": "l1",
    "children": [
      2,
      3
    ]
  },
  {
    "_id": 2,
    "name": "l2a",
    "children": [
      4
    ]
  },
  {
    "_id": 3,
    "name": "l2b",
    "children": [
      5
    ]
  },
  {
    "_id": 4,
    "name": "l3a",
    "children": []
  },
  {
    "_id": 5,
    "name": "l3b",
    "children": []
  }

]

Insert query

db.collection.insert({"_id": 4, "name": "l3a", "children": []}); // Inserting empty array simplifies aggregation query

Update query

db.collection.update({"_id": 4}, {"$set": "name": "l3c"});

Aggregation

db.collection.aggregate([
  {"$match":{"_id":1}},
  {"$lookup":{
    "from":"collection",
    "let":{"name":"$name","children":"$children"},
    "pipeline":[
      {"$match":{"$expr":{"$in":["$_id","$$children"]}}},
      {"$addFields":{"route":{"$concat":["$$name","/","$name"]}}},
      {"$lookup":{
        "from":"collection",
        "let":{"route":"$route","children":"$children"},
        "pipeline":[
          {"$match":{"$expr":{"$in":["$_id","$$children"]}}},
          {"$addFields":{"route":{"$concat":["$$route","/","$name"]}}}
        ],
        "as":"children"
      }}
    ],
    "as":"children"
  }}
])

Original

You could make route as array type and format before presenting it to user. It will greatly simplify updates for you. You have to break queries into multiple updates when nested levels don’t exist ( ex level 2 update ). May be use transactions to perform multiple updates in atomic way.

Something like

[
  {
    "_id": 1,
    "name": "l1",
    "route": "l1",
    "children": [
      {
        "name": "l2a",
        "route": [
          "l1",
          "l2a"
        ],
        "children": [
          {
            "name": "l3a",
            "route": [
              "l1",
              "l2a",
              "l3a"
            ]
          }
        ]
      }
    ]
  }
]

level 1 update

db.collection.update({
  "_id": 1
},
{
  "$set": {
    "name": "m1",
    "route": "m1"
  },
  "$set": {
    "children.$[].route.0": "m1",
    "children.$[].children.$[].route.0": "m1"
  }
})

level 2 update

db.collection.update({
  "_id": 1
},
{
  "$set": {
    "children.$[child].route.1": "m2a",
    "children.$[child].name": "m2a"
  }
},
{
  "arrayFilters":[{"child.name": "l2a" }]
})


db.collection.update({
  "_id": 1
},
{
  "$set": {
    "children.$[child].children.$[].route.1": "m2a"
  }
},
{
  "arrayFilters":[{"child.name": "l2a"}]
})

level 3 update

db.collection.update({
  "_id": 1
},
{
  "$set": {
    "children.$[].children.$[child].name": "m3a"
    "children.$[].children.$[child].route.2": "m3a"
  }
},
{
  "arrayFilters":[{"child.name": "l3a"}]
})

edited Nov 16, 2020 at 16:05

answered Nov 15, 2020 at 0:04

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Swapnil Sourabh Over a year ago

The updates for the childrens' give the same 500 error and may not solve my major issue. Eg. In level1 update, "children.$[].children.$[].route.0": "m1" create the 500 mongo error. Testcase is when "children.$[].children.$[].route.0" dont exist . BUT @s7vr the idea of dividing routes into array is awesome. It no doubt brings more clarity when I edit the pre-existing values of items and remove the need of reading values first. Although it seems overall update execution may take similar efforts(regard to 500 error). I sincerely thank you for your time and an AWESOME approach to redesigning.

s7vr Over a year ago

You're very welcome - I've updated answer to simplify your updates even further with different design. We now can use aggregation framework to populate references. Play with it and see if it fits your use case.

turivishal · Accepted Answer · 2020-11-15 05:51:32Z

I don't think its possible with arrayFilted for first level and second level update, but yes its possible only for third level update,

The possible way is you can use update with aggregation pipeline starting from MongoDB 4.2,

I am just suggesting a method, you can simplify more on this and reduce query as per your understanding!

Use $map to iterate the loop of children array and check condition using $cond, and merge objects using $mergeObjects,

let id = req.body._id;
let oldname = req.body.name;
let route = req.body.route;
let newname = req.body.newName;

let segments = route.split('/');

LEVEL 1 UPDATE: Playground

// LEVEL 1: Example Values in variables
// let oldname = "l1";
// let route = "l1";
// let newname = "l4";
if(segments.length === 1) {
  let result = await NavItems.updateOne(
        { _id: id },
        [{
            $set: {
                name: newname,
                route: newname,
                children: {
                    $map: {
                        input: "$children",
                        as: "a2",
                        in: {
                            $mergeObjects: [
                                "$$a2",
                                {
                                    route: { $concat: [newname, "/", "$$a2.name"] },
                                    children: {
                                        $map: {
                                            input: "$$a2.children",
                                            as: "a3",
                                            in: {
                                                $mergeObjects: [
                                                    "$$a3",
                                                    { route: { $concat: [newname, "/", "$$a2.name", "/", "$$a3.name"] } }
                                                ]
                                            }
                                        }
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        }]
    );
}

LEVEL 2 UPDATE: Playground

// LEVEL 2: Example Values in variables
// let oldname = "l2a";
// let route = "l1/l2a";
// let newname = "l2g";
else if (segments.length === 2) {
    let result = await NavItems.updateOne(
        { _id: id },
        [{
            $set: {
                children: {
                    $map: {
                        input: "$children",
                        as: "a2",
                        in: {
                            $mergeObjects: [
                                "$$a2",
                                {
                                    $cond: [
                                        { $eq: ["$$a2.name", oldname] },
                                        {
                                            name: newname,
                                            route: { $concat: ["$name", "/", newname] },
                                            children: {
                                                $map: {
                                                    input: "$$a2.children",
                                                    as: "a3",
                                                    in: {
                                                        $mergeObjects: [
                                                            "$$a3",
                                                            { route: { $concat: ["$name", "/", newname, "/", "$$a3.name"] } }
                                                        ]
                                                    }
                                                }
                                            }
                                        },
                                        {}
                                    ]
                                }
                            ]
                        }
                    }
                }
            }
        }]
    );
}

LEVEL 3 UPDATE: Playground

// LEVEL 3 Example Values in variables
// let oldname = "l3a";
// let route = "l1/l2a/l3a";
// let newname = "l3g";
else if (segments.length === 3) {
    let result = await NavItems.updateOne(
        { _id: id },
        [{
            $set: {
                children: {
                    $map: {
                        input: "$children",
                        as: "a2",
                        in: {
                            $mergeObjects: [
                                "$$a2",
                                {
                                    $cond: [
                                        { $eq: ["$$a2.name", segments[1]] },
                                        {
                                            children: {
                                                $map: {
                                                    input: "$$a2.children",
                                                    as: "a3",
                                                    in: {
                                                        $mergeObjects: [
                                                            "$$a3",
                                                            {
                                                                $cond: [
                                                                    { $eq: ["$$a3.name", oldname] },
                                                                    {
                                                                        name: newname,
                                                                        route: { $concat: ["$name", "/", "$$a2.name", "/", newname] }
                                                                    },
                                                                    {}
                                                                ]
                                                            }
                                                        ]
                                                    }
                                                }
                                            }
                                        },
                                        {}
                                    ]
                                }
                            ]
                        }
                    }
                }
            }
        }]
    );
}

Why separate query for each level?

You could do single query but it will update all level's data whenever you just need to update single level data or particular level's data, I know this is lengthy code and queries but i can say this is optimized version for query operation.

Thank you for providing an apt answer as per the scenario. Took little tweaks here and there :)The way of iterating the document using the maps was something new to me. Happy to award the bounty.

Pavel · Accepted Answer · 2020-11-12 22:14:19Z

2

you can't do as you want. Because mongo does not support it. I can offer you to fetch needed item from mongo. Update him with your custom recursive function help. And do db.collection.updateOne(_id, { $set: data })

function updateRouteRecursive(item) {
  // case when need to stop our recursive function
  if (!item.children) {
    // do update item route and return modified item
    return item;
  }

  // case what happen when we have children on each children array
}

answered Nov 12, 2020 at 22:14

Pavel

1651 silver badge2 bronze badges

Collectives™ on Stack Overflow

MongoDB - Update all entries in nested array only if they exist

3 Answers 3

2 Comments

Why separate query for each level?

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Why separate query for each level?

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related