How to capitalize first letter of each word in MongoDB?

aggregation-framework, mongodb, mongodb-query

Solution

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.

Problem

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

Original source