Q:

Split a string during MongoDB aggregate

Currently, I have just fullname stored in the User collection in MongoDB. I'd like to run a report that splits the first and last name so for now I'm trying to run an aggregate and split the string when a whitespace is found.
Here is what I have now, but I'd like to replace the hard coded end position with a variable based on where whitespace is found. Is this possible in an aggregate pipeline?
db.users.aggregate([{ 
    $project : {
        fullname:{ $toUpper:"$fullname" },
        first: { $substr: [ "$fullname", 0, 2 ]}, _id:0 }
    }, { $sort : { fullname : 1 }
}]);

A:

The aggregation framework does not have any operator to perform a "split" based on a matched character or any such thing. There is only $substr which of course requires an index, and there is no operator to return a "index" of a matched character either.
You could use mapReduce, which can use JavaScript .split(), but of course there is no "sort stage" in mapReduce other than the results in the main key which are always pre-sorted before attempting to apply a reduce ( which would not be applied here with all unique keys ):
db.users.mapReduce(
    function() {
        var lastName = this.fullname.split(/\s/).reverse()[0].toUpperCase();

        emit({ "lastName": lastName, "orig": this._id },this);
    },
    function(){},     // Never called on all unique
    { "out": { "inline": 1 } }
);

And that will basically extract the last name after a whitespace, convert it to uppercase and use it as a composite value in the primary key so results will be sorted by that key ( note you cannot use _id as any part of the key name or it will be sorted by that field instead ).
But if your real case here is "sorting", then you are better off storing the data that way, thus giving you a direct value to sort on without calculation:
var bulk = db.users.initializeOrderedBulkOp(),
    count = 0;

db.users.find().forEach(user) {
    bulk.find({ "_id": user._id }).updateOne({
        "$set": { "lastName": user.fullname.split(/\s/).reverse()[0].toUpperCase() }
    });
    count++;

    if ( count % 1000 == 0 ) {
        bulk.execute();
        bulk = db.users.initializeOrderedBulkOp();
    }
}  

if ( count % 1000 != 0 )
    bulk.execute();

Then with a solid field in place you just run your sort:
db.users.find().sort({ "lastName": 1 });

Which is going to be a lot faster than trying to calculate a value from which to perform a sort.
Of course if sorting is not the purpose and it's just for presentation, then just perform the split in client code where it makes the most sense to do so. The aggregation framework cannot restructure the data like that, and while mapReduce "could", it's output is very opinionated and not really purposed for such an operation.

