Mongodb update deeply nested subdocument

Question

I have a document structure that is deeply nested, like this:

{id: 1, 
 forecasts: [ { 
             forecast_id: 123, 
             name: "Forecast 1", 
             levels: [ 
                { level: "proven", 
                  configs: [
                            { 
                              config: "Custom 1",
                              variables: [{ x: 1, y:2, z:3}]
                            }, 
                            { 
                              config: "Custom 2",
                              variables: [{ x: 10, y:20, z:30}]
                            }, 
                    ]
                }, 
                { level: "likely", 
                  configs: [
                            { 
                              config: "Custom 1",
                              variables: [{ x: 1, y:2, z:3}]
                            }, 
                            { 
                              config: "Custom 2",
                              variables: [{ x: 10, y:20, z:30}]
                            }, 
                    ]
                }
            ]
        }, 
    ]

}

I'm trying to update the collection to insert a new config, that looks like this:

newdata =  {
  config: "Custom 1", 
  variables: [{ x: 111, y:2222, z:3333}]
}

I'm trying something like this in mongo (in Python):

db.myCollection.update({"id": 1, 
                        "forecasts.forecast-id": 123, 
                        "forecasts.levels.level": "proven", 
                        "forecasts.levels.configs.config": "Custom 1"
                         },
                         {"$set": {"forecasts.$.levels.$.configs.$": newData}}
                      )

I'm getting "Cannot apply the positional operator without a corresponding query field containing an array" error though. What is the proper way to do this in mongo? This is mongo v2.4.1.

Answer 1

Unfortunately, you can't use the $ operator more than once per key, so you have to use numeric values for the rest. As in:

db.myCollection.update({
    "id": 1, 
    "forecasts.forecast-id": 123, 
    "forecasts.levels.level": "proven", 
    "forecasts.levels.configs.config": "Custom 1"
  },
  {"$set": {"forecasts.$.levels.0.configs.0": newData}}
)

MongoDB's support for updating nested arrays is poor. So you're best off avoiding their use if you need to update the data frequently, and consider using multiple collections instead.

One possibility: make forecasts its own collection, and assuming you have a fixed set of level values, make level an object instead of an array:

{
  _id: 123,
  parentId: 1,
  name: "Forecast 1", 
  levels: {
    proven: { 
      configs: [
        { 
          config: "Custom 1",
          variables: [{ x: 1, y:2, z:3}]
        }, 
        { 
          config: "Custom 2",
          variables: [{ x: 10, y:20, z:30}]
        }, 
      ]
    },
    likely: {
      configs: [
        { 
          config: "Custom 1",
          variables: [{ x: 1, y:2, z:3}]
        }, 
        { 
          config: "Custom 2",
          variables: [{ x: 10, y:20, z:30}]
        }, 
      ]
    }
  }
}

Then you can update it using:

db.myCollection.update({
    _id: 123,
    'levels.proven.configs.config': 'Custom 1'
  },
  { $set: { 'levels.proven.configs.$': newData }}
)

Answer 2

Managed to solve it with using mongoose:

All you need to know is the '_id's of all of the sub-document in the chain (mongoose automatically create '_id' for each sub-document).

for example -

  SchemaName.findById(_id, function (e, data) {
      if (e) console.log(e);
      data.sub1.id(_id1).sub2.id(_id2).field = req.body.something;

      // or if you want to change more then one field -
      //=> var t = data.sub1.id(_id1).sub2.id(_id2);
      //=> t.field = req.body.something;

      data.save();
  });

More about the sub-document _id method in mongoose documentation.

explanation:_id is for the SchemaName, _id1 for sub1 and _id2 for sub2 - you can keep chaining like that.

*You don't have to use findById method, but it's seem to me the most convenient as you need to know the rest of the '_id's anyway.

Answer 3

MongoDB has introduced ArrayFilters to tackle this issue in Version 3.5.2 and later.

New in version 3.6.

Starting in MongoDB 3.6, when updating an array field, you can specify arrayFilters that determine which array elements to update.

[https://docs.mongodb.com/manual/reference/method/db.collection.update/#specify-arrayfilters-for-an-array-update-operations][1]

Let's say the Schema design as follows :

var ProfileSchema = new Schema({
    name: String,
    albums: [{
        tour_name: String,
        images: [{
            title: String,
            image: String
        }]
    }]
});

And Document created looks like this :

{
   "_id": "1",
   "albums": [{
            "images": [
               {
                  "title": "t1",
                  "url": "url1"
               },
               {
                  "title": "t2",
                  "url": "url2"
               }
            ],
            "tour_name": "london-trip"
         },
         {
            "images": [.........]: 
         }]
}

Say I want to update the "url" of an image. Given - "document id", "tour_name" and "title"

For this the update query :

Profiles.update({_id : req.body.id},
    {
        $set: {

            'albums.$[i].images.$[j].title': req.body.new_name
        }
    },
    {
        arrayFilters: [
            {
                "i.tour_name": req.body.tour_name, "j.image": req.body.new_name   // tour_name -  current tour name,  new_name - new tour name 
            }]
    })
    .then(function (resp) {
        console.log(resp)
        res.json({status: 'success', resp});
    }).catch(function (err) {
    console.log(err);
    res.status(500).json('Failed');
})

Answer 4

This is a very OLD bug in MongoDB

https://jira.mongodb.org/browse/SERVER-831

Answer 5

I was facing same kind of problem today, and after lot of exploring on google/stackoverflow/github, I figured arrayFilters are the best solution to this problem. Which would work with mongo 3.6 and above. This link finally saved my day: https://thecodebarbarian.com/a-nodejs-perspective-on-mongodb-36-array-filters.html

const OrganizationInformationSchema = mongoose.Schema({
user: {
    _id: String,
    name: String
},
organizations: [{
    name: {
        type: String,
        unique: true,
        sparse: true
    },
    rosters: [{
        name: {
            type: String
        },
        designation: {
            type: String
        }
    }]
}]
}, {
    timestamps: true
});

And using mongoose in express, updating the name of roster of given id.

const mongoose = require('mongoose');
const ControllerModel = require('../models/organizations.model.js');
module.exports = {
// Find one record from database and update.
findOneRosterAndUpdate: (req, res, next) => {
    ControllerModel.updateOne({}, {
        $set: {
            "organizations.$[].rosters.$[i].name": req.body.name
        }
    }, {
        arrayFilters: [
            { "i._id": mongoose.Types.ObjectId(req.params.id) }
        ]
    }).then(response => {
        res.send(response);
    }).catch(err => {
        res.status(500).send({
            message: "Failed! record cannot be updated.",
            err
        });
    });
}
}