Mongodb update deeply nested subdocument

mongodb

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.

Best Answer

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 }}
)

Related Solutions

Sql – How to query MongoDB with “like”

That would have to be:

db.users.find({"name": /.*m.*/})

Or, similar:

db.users.find({"name": /m/})

You're looking for something that contains "m" somewhere (SQL's '%' operator is equivalent to regular expressions' '.*'), not something that has "m" anchored to the beginning of the string.

Note: MongoDB uses regular expressions which are more powerful than "LIKE" in SQL. With regular expressions you can create any pattern that you imagine.

For more information on regular expressions, refer to Regular expressions (MDN).

Mongodb – Updating nested arrays in mongodb

Here's the big question, do you need to leverage Mongo's "addToSet" and "push" operations? If you really plan to modify just individual items in the array, then you should probably build these arrays as objects.

Here's how I would structure this:

{
    id: 1,
    items: 
        { 
          "2" : { "blocks" : { "3" : { txt : 'hello' } } },
          "5" : { "blocks" : { "1" : { txt : 'foo'}, "2" : { txt : 'bar'} } }
        }
}

This basically transforms everything in to JSON objects instead of arrays. You lose the ability to use $push and $addToSet but I think this makes everything easier. For example, your query would look like this:

db.objects.update({'items.2': {$exists:true} }, {'$set': {'items.2.blocks.0.txt': 'hi'}})

You'll also notice that I've dumped the "IDs". When you're nesting things like this you can generally replace "ID" with simply using that number as an index. The "ID" concept is now implied.

This feature has been added in 3.6 with expressive updates.

db.objects.update( {id: 1 }, { $set: { 'items.$[itm].blocks.$[blk].txt': "hi", } }, { multi: false, arrayFilters: [ { 'itm.id': 2 }, { 'blk.id': 3} ] } )

Best Answer

Related Solutions

Sql – How to query MongoDB with “like”

Mongodb – Updating nested arrays in mongodb

Related Topic