How to capitalize first letter of each word in MongoDB?

Question

This should be a quick 2 minute find, but i'm searching for an hour and can't find any solution.

How can i capitalize first letter of each word of the username field?

{
    "_id" : ObjectId("5908288bfbce59540a67fa11"),
    "username" : "JOHN DOE"
},
{
    "_id" : ObjectId("5908288bfbce59540a67fa12"),
    "username" : "jane doe"
}

Tried this solution that i don't really understand, but it doesn't work: Fetched 0 record(s) in 0ms

db.getCollection('test').toupper.aggregate(
    [
        {
            $project: {
                username: {
                    $concat: [{
                        $toUpper: { $substr: ["$username", 0, 1] }
                    }, { $substr: ["$username", 1, 3] }]
                }
            }
        }])

Answer 1

TL;DR

var map = {
    "$map": {
        "input": { "$split": [ "$username", " " ] },
        "as": "name",
        "in": {                                                                             
            "$concat": [
                { "$toUpper": { "$substrCP": ["$$name", 0, 1] } }, 
                {                                                           
                    "$substrCP": [
                        "$$name",
                        1,
                        { "$subtract": [{ "$strLenCP": "$$name" }, 1 ]}
                    ] 
                }
            ]
        }
    }
};

db.getCollection('test').aggregate([
    {
        "$addFields": {
            "username": {
                "$concat": [
                    { "$arrayElemAt": [map, 0] },
                    " ",
                    { "$arrayElemAt": [map, 1] }
                ]
            }
        }
    }
]);

---

To capitalize the first letter of each word in a string, you first need to split the string by a given delimiter using the $split operator, in this instance the delimiter would be a space:

Example

{ "$split": [ "jane doe", " " ] }                                           

Results

[ "jane", "doe" ]

---

Next step is to use the $map operator on the result array above and for each element in the array apply the $toUpper transformation on a substring with $substrCP as well as the concat process with $concat to return a transformed array.

Within the transformation, you would need to get the first letter in a given word, transform that letter to uppercase and then concatenate the rest of the string.

For the first part, the transformation follows:

Example

{ "$toUpper": { "$substrCP": ["jane", 0, 1] } }                             

Results

"J"

And for the remainder of the string

Example

{                                                                           
    "$substrCP": [
        "jane",
        1,
        { "$subtract": [{ "$strLenCP": "jane" }, 1 ]}
    ] 
}               

Results

"ane"

---

Concatenation

Example

{                                                                           
    $concat: [
        { "$toUpper": { "$substrCP": ["jane", 0, 1] } }, 
        {                                                           
            "$substrCP": [
                "jane",
                1,
                { "$subtract": [{ "$strLenCP": "jane" }, 1 ]}
            ] 
        }
    ]
}

Results

"Jane"

---

Mapping to elements in an array

Using the above in $map:

Example

{
    "$map": {
        "input": { "$split": [ "jane doe", " " ] },
        "as": "name",
        "in": {                                                                             
            "$concat": [
                { "$toUpper": { "$substrCP": ["$$name", 0, 1] } }, 
                {                                                           
                    "$substrCP": [
                        "jane",
                        1,
                        { "$subtract": [{ "$strLenCP": "$$name" }, 1 ]}
                    ] 
                }
            ]
        }
    }
}

Results

[ "Jane", "Doe" ]

You can then use the above as an expression for the $arrayElemAt operator and use the $concat again as:

{
    "$addFields": {
        "username": {
            "$concat": [
                { "$arrayElemAt": [map, 0] },
                " ",
                { "$arrayElemAt": [map, 1] }
            ]
        }
    }
}

Your final aggregate operation becomes:

var map = {
    "$map": {
        "input": { "$split": [ "$username", " " ] },
        "as": "name",
        "in": {                                                                             
            "$concat": [
                { "$toUpper": { "$substrCP": ["$$name", 0, 1] } }, 
                {                                                           
                    "$substrCP": [
                        "$$name",
                        1,
                        { "$subtract": [{ "$strLenCP": "$$name" }, 1 ]}
                    ] 
                }
            ]
        }
    }
};

db.getCollection('test').aggregate([
    {
        "$addFields": {
            "username": {
                "$concat": [
                    { "$arrayElemAt": [map, 0] },
                    " ",
                    { "$arrayElemAt": [map, 1] }
                ]
            }
        }
    }
]);

NB: Haven't tested the above yet but it should give you at least some direction on how to solve your problem.

Answer 2

Here is another alternative using $substrCP and $concat as expression to $map.

Of course you will need the $toUpper and his brother in crime $toLower to convert the string to uppercase or lowercase

db.collection.aggregate([
       {
          "$addFields": {
             "username": {
                "$reduce": {
                   "input": {
                      "$map": {
                         "input": {
                            "$split": [
                               "$username",
                               " "
                            ]
                         },
                         "in": {
                            "$concat": [
                               {
                                  "$toUpper": {
                                     "$substrCP": [
                                        "$$this",
                                        0,
                                        1
                                     ]
                                  }
                               },
                               {
                                  "$toLower": {
                                     "$substrCP": [
                                        "$$this",
                                        1,
                                        { "$strLenCP": "$$this" }
                                     ]
                                  }
                               }
                            ]
                         }
                      }
                   },
                   "initialValue": "",
                   "in": {
                      "$concat": [
                         "$$value",
                         " ",
                         "$$this"
                      ]
                   }
                }
             }
          }
       }
    ]
)