How to update sub arrays in MongoDB

I recently needed to update specific values in sub arrays or nested arrays in MongoDB to lower case. At the end of my research and experiments I found a few variants of how to do it. I'll show you all of them using a shell to be language agnostic. Concepts are the same and it will be easy to write such queries in any language.

First of all we need to create a demo collection to play with it and make things clear:

use 4devs
db.users.insert({type: 'developer', nickname: 'victor', skills: ['PHP', 'SYMFONY', 'JAVASCRIPT', 'MONGODB', 'GIT']})

My real collection isn't related to users and is more complicated, has nested documents with arrays, but for explanation this collection is quite good.

Solution with forEach

db.users.find({type: 'developer'}).forEach(function(dev) {
    dev.skills = dev.skills.map(function(skill) {
        return skill.toLowerCase();         
    }); 
    
    db.users.save(dev); 
})
db.users.find().pretty()
{
    "_id" : ObjectId("53edd8ec32d7a10deb5ab65d"),
	"type" : "developer",
	"nickname" : "victor",
	"skills" : [
		"php",
		"symfony",
		"javascript",
		"mongodb",
		"git"
	]
}

Here we're iterating through all the needed documents (type === developer) and changing skills to lower case and then saving changes back to the collection.

Pros: 

+ easy to understand and quick to write

+ flexible. You can do any modifications of document using poor JavaScript

Cons:

- for each document one query will be executed

MongoDB 2.6 $map solution

In MongoDB 2.6. we have a new operator in Aggregation Pipeline Freamework, called $map. We can use $map operator in $project stage, it iterates through all the elements in an array and applies expression to them. Let's see how we can do the same update to the documents, using $map operator:

db.users.aggregate([
    {
        $project: {
            _id: 1,
            nickname: 1,
            type: 1,
            skills: {
                $cond: {
                    if: {
                        $eq: ['$type', 'developer']
                    },
                    then: {
                        $map: {
                            input: '$skills',
                            as: 'skill',
                            in: {
                                $toLower: '$$skill' 
                            }
                        }
                    },
                    else: '$skills'
                }
            }
        }
    },
    {
        $out: 'users'
    }
])

We can apply $map operator in $project stage. Also we need to keep in mind that we need to work with all the documents in the collection, because in the end we will use $out (available only from 2.6 too) stage to replace the old collection with the modified one. 

To achieve the needed result we need to write all the fields in $project stage even if we don't change them:

_id: 1,
nickname: 1,
type: 1

Then we need to apply the changes only to developers, so we need to use $cond operator. It works as a ternary operator and has three main parts: if, then, else. If type === developer (here we use $eq operator to check this), then we need to apply a $toLower operator, else - or leave it as it is.

To apply $toLower operator for each value in the skills array we use $map operator, that has three parts too:

input - use it to say through what field you want to iterate. In our example we want to iterate through skills, so we use: input: '$skills'

as - here you can set an alias for the current element of array. We used skill: as: 'skill'

in - here you can apply expression to each element of the array. To access the element here we need to use its alias and $$: '$$skill'

That's all. Using the aggregation query we updated all the needed fields in our collection.

 

Pros:

+ one query

Cons:

- complicated query

- isn't so flexible. you can only use MongoDB's expressions

- need to work with all documents in a collection, and apply changes after $cond check

- this approach works only for arrays (or null). If at least one document has another type for $map field, then you'll get an error

- you need to write all document fields in $project stage, even if you don't change them (nickname: 1 and so on)

- too much overhead for big collections because of full rewrite

 

MongoDB 2.4

if you use MongoDB 2.4, then you're out of luck and can't use $map operator, also $out operator is unavailable too. The only choice for you is forEach.

 

Conclusion

I solved my problem with the forEach variant written in PHP because my collection was more complex and I couldn't apply the $map variant. Here I wanted to show that almost any problem can be solved in many ways, even if an arsenal of MongoDB seems limited to you.