Aggregation Helper Functions: lpad

MongoDB aggregation provides quite a few string manipulation functions, but there are many that it doesn't provide (yet), but we can express them ourselves using existing string expressions.

Today's example is 'lpad' - given a string, desired length and a character to pad with, return a string that is at least that length and if it was shorter then pad it on the front (i.e. left side) with provided pad character (by default we will use space to pad with).

lpad = function (str, len, padstr=" ") {
      var redExpr={$reduce:{
        input:{$range:[0,{$subtract:[len, {$strLenCP:str}]}]},
        initialValue:"",
        in:{$concat:["$$value",padstr]}}};
      return {$cond:{
        if:{$gte:[{$strLenCP:str},len]},
        then:str,
        else:{$concat:[ redExpr, str]}
      }};
}

To test the function, let's look at converting one date format to another:

db.d2s.aggregate({$project:{_id:0}})
{ "d" : "1/4/2017" }
{ "d" : "1/14/2017" }
{ "d" : "11/8/2017" }
{ "d" : "09/6/2017" }

To convert to "YYYY-MM-DD" we can use $split and $arrayElemAt expressions:

db.d2s.aggregate({$project:{_id:0, dt:{$let:{
    vars:{parts:{$split:["$d","/"]}},
    in:{$concat:[
        {$arrayElemAt:["$$parts",2]},'-',
        {$arrayElemAt:["$$parts",0]} ,'-',
        {$arrayElemAt:["$$parts",1]}
    ]}
}}}})
{ "dt" : "2017-1-4" }
{ "dt" : "2017-1-14" }
{ "dt" : "2017-11-8" }
{ "dt" : "2017-09-6" }

But to get it to look right, we want to pad single digit days and months with '0' and we can use our function for this:

db.d2s.aggregate({$project:{_id:0, dt:{$let:{
    vars:{parts:{$split:["$d","/"]}},
    in:{$concat:[
        {$arrayElemAt:["$$parts",2]},'-',
        lpad({$arrayElemAt:["$$parts",0]},2,"0") ,'-',
        lpad({$arrayElemAt:["$$parts",1]},2,"0")
    ]}
}}}})
{ "dt" : "2017-01-04" }
{ "dt" : "2017-01-14" }
{ "dt" : "2017-11-08" }
{ "dt" : "2017-09-06" }

Great news is that in 3.6 (out earlier this week!) you can take advantage of some great new date expressions to avoid all this extra work like this:

db.d2s.aggregate({$project:{_id:0, dt:{
    $dateToString:{
        format:'%Y-%m-%d',
        date:{$dateFromString:{dateString:"$d"}}
    }
}}})
{ "dt" : "2017-01-04" }
{ "dt" : "2017-01-14" }
{ "dt" : "2017-11-08" }
{ "dt" : "2017-09-06" }

$dateFromString is new and while it does not take a format specifier, it can handle just about every format of date string I tried to throw at it!

Luckily we still can use lpad helper when we want to line up string columns.