Stupid Tricks with MongoDB

How to Convert Epoch to ISODate

10/31/2019

The great thing about recent versions of MongoDB is that they added a lot of new expressions for handling different types in aggregation. The not so great thing is sometimes you still have to get things done in an older version that doesn't have the same capabilities.

A colleague asked me today how you can convert an epoch (number of milliseconds since 1970/1/1) to a proper ISODate format. The answer comes from date math:

db.coll.aggregate({$addFields:{
  date:{$add:[
  ISODate("1970-01-01T00:00:00Z"),
  "$epoch"
  ]}
}})

Because $add supports adding numbers to date (treating the number as milliseconds) we get back a proper ISODate that the number in "epoch" field represents!

0 Comments

More Truncation of Dates in Aggregation

1/23/2018

1 Comment

A long time ago, I wrote about how to convert an ISODate() that's got time into just a date (meaning zeroing out the hours/minutes/seconds/milliseconds). We did it by subtracting from the date the number of milliseconds since midnight which we calculated using some date math and $hour, $minute, $second and $millisecond expressions.

As of 3.6 there's a slightly simpler way to achieve the same thing using the "$dateFromParts" expression.

Here's an example:

> db.dates.find({},{_id:0})
{ "sys_created_on" : ISODate("2012-02-18T03:04:49Z") }
{ "sys_created_on" : ISODate("2012-02-18T03:04:49Z") }
{ "sys_created_on" : ISODate("2012-02-18T03:04:49Z") }
{ "sys_created_on" : ISODate("2012-02-10T03:04:49Z") }
{ "sys_created_on" : ISODate("2012-02-28T03:04:49Z") }
{ "sys_created_on" : ISODate("2012-03-18T03:04:49Z") }
> db.dates.aggregate({$project:{_id:0, roundDate:{$dateFromParts:{
     year:{$year:"$sys_created_on"},
     month:{$month:"$sys_created_on"},
     day:{$dayOfMonth:"$sys_created_on"}
}}}})
{ "roundDate" : ISODate("2012-02-18T00:00:00Z") }
{ "roundDate" : ISODate("2012-02-18T00:00:00Z") }
{ "roundDate" : ISODate("2012-02-18T00:00:00Z") }
{ "roundDate" : ISODate("2012-02-10T00:00:00Z") }
{ "roundDate" : ISODate("2012-02-28T00:00:00Z") }
{ "roundDate" : ISODate("2012-03-18T00:00:00Z") }

1 Comment

Aggregation Helper Functions: lpad

12/7/2017

6 Comments

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.

6 Comments

Converting ObjectId Values to year-month labels v2

6/5/2017

3 Comments

A long time ago I wrote a blog post showing how to convert ObjectId value field to corresponding "YYYY-MM" string for reporting type applications. Since I wrote that, aggregation pipeline gained the "$switch" expression which makes the syntax a lot shorter and easier to express (and read).

For variety, this version converts *string* type that represents ObjectId value to corresponding year-month:

d=[];
o=[];
for (yr=2011;  yr < 2018; yr++ ) {
       for (m=1; m<13; m++) {
           if (m<10) mo="0"+m; else mo=""+m;
           var dt=new ISODate(""+yr+"-"+mo+"-01T00:00:00Z");
           d.push(""+yr+"-"+mo);
           /* wrap string in 'new ObjectId()' to convert OID rather than string type */
           o.push(""+(dt.getTime()/1000).toString(16)+pad);
      }  
}
makeLabeledSwitch = function(field, keys, values) {
      var sw = {$switch:{
           "branches":[ ],
           default:"other"}
      };
      var br=[];
      var maxPos=keys.length;
      var first="<" + keys[0];
      br.push({case:{$lt:[field,values[0]]}, then:first})
      for (pos = 0; pos < maxPos-1; pos++) {
           br.push({case:{$lt:[field,values[pos+1]]}, then: keys[pos] });
      }
      var last=">" + keys[maxPos-1];
      sw["$switch"]["default"] = last;
      sw["$switch"]["branches"] = br;
      return sw;
}

This syntax is more straight forward, and what it does is quite similar, which is for every "YYYY-MM" string in the range you're interested in, it maps the range of ObjectId string values (or technically, its first 4-bytes) to the year-month range. If you want to make this work with actual ObjectId type rather than string type, change the loop to populate "o" array with ObjectId() of corresponding string.

3 Comments

Converting ObjectId to dates in aggregation

2/17/2016

3 Comments

Someone just asked me how they can do reporting grouping on "year-month" when they only have the ObjectId generated by MongoDB to represent creation date.

While ObjectId is very useful - its first four bytes are the timestamp when it was generated - there's a simple way to convert it to a full date in Javascript (like mongo shell) but there is no way to convert it to a timestamp in aggregation pipeline (although there is a request for such a feature).

Since we can't do it in aggregation natively, we can use a stupid trick to generate "YEAR-MONTH" from ObjectId during $project stage so that we can group by it. Here is how I did it.

Working in the shell, first I generated an array of objects which represent all the months I want to report for (so I only generated a few years worth of months):

var d = []; 
var o = [];
var pad="f000000000000000";
for (yr=2014;  yr < 2017; yr++ ) {
      for (m=1; m<13; m++) {
         if (m<10) mo="0"+m; else mo=""+m;  
         var dt=new ISODate(""+yr+"-"+mo+"-01T00:00:00Z");
         d.push(""+yr+"-"+mo);
         o.push(new ObjectId( (dt.getTime()/1000).toString(16)+pad));
      }
  }

This generated two arrays of "YYYY-MM" strings and their corresponding ObjectId() values.

Now I can create a shell function which takes a field and two arrays, and creates an expression we can use in $project stage to map ranges in the second array to labels in the first array:

makeLabeledBuckets=function( field, keys, values) {
    var con=[];
    var maxPos=keys.length;
    con[maxPos]=">" + keys[maxPos-1];
    for (pos = maxPos-1; pos > 0; pos--) {
        con[pos] = {"$cond":{
                  if: {$lt:[field, values[pos]]},
                  then:  keys[pos-1],
                  else:  con[pos+1]
        }};
     }
     var first = "< " + keys[0];
     con[0]={"$cond":{if: {$lt:[field,values[0]]}, then: first, else: con[1] }};
     return con[0];
}

Now we can run our aggregation in the shell like this:

> db.collection.aggregate( [ 
  { $project: { yearMonthStr: makeLabeledBuckets("$_id", d, o) } } ] )
{ "_id" : ObjectId("55af2194cd214aaa0a5e3545"), "yearMonth" : "2015-08" }
{ "_id" : ObjectId("55af21b5cd214aaa0a5e3548"), "yearMonth" : "2015-08" }
{ "_id" : ObjectId("55aff3f78909abe4721284bc"), "yearMonth" : "2015-08" }
{ "_id" : ObjectId("55aff4bd8909abe4721284c0"), "yearMonth" : "2015-08" }
{ "_id" : ObjectId("56900c440172f6f5768fb249"), "yearMonth" : "2016-02" }
{ "_id" : ObjectId("56900d780172f6f5768fb24c"), "yearMonth" : "2016-02" }
{ "_id" : ObjectId("56900dc80172f6f5768fb24e"), "yearMonth" : "2016-02" }
{ "_id" : ObjectId("569014240172f6f5768fb251"), "yearMonth" : "2016-02" }

As you can see, each ObjectId in "_id" field got converted to corresponding "year-month" string, which we can now use to aggregate other metrics by.

3 Comments

Yet Another Way to Group by Dates in MongoDB

11/13/2013

4 Comments

I recently had to do some of my own aggregations on the Oplog collection to analyze the root cause of a problem. Oplog is the logical replication collection that MongoDB replica sets use, and all the change operations get written into it. I wanted to examine a pattern of writes and how it changed over several days, but the timestamp stored in the Oplog was stored as BSON Timestamp() type. While Aggregation Framework recognizes it as a date type, you cannot do date math on it, and therefore there is no way to drop the hours/minutes/seconds to get to day granularity. You can extract the "year", "month" and "day" but how would you turn them into an ISODate() type since there is no operator to do that (yet)?

Date math to the rescue. Here's a cute way to convert a Timestamp to an ISODate() of day granularity:

 db.foos.find({},{_id:0,TimeStamp:1}) 
{ "TimeStamp" : Timestamp(1384370105,6) } 
{ "TimeStamp" : Timestamp(1391248800,0) } 

project1={$project:{_id:0,
                     y:{$subtract:[{$year:"$TimeStamp"}, 2013]},
                   d:{$subtract:[{$dayOfYear:"$TimeStamp"},1]}, 
                   jan1:{$literal:new ISODate("2013-01-01T00:00:00")}
         } };
project2={$project:{tsDate:{$add:[
                       "$jan1",
                       {$multiply:["$y", 365*24*60*60*1000]},
                       {$multiply:["$d", 24*60*60*1000]}
         ] } } };

> db.foos.aggregate(project1, project2)
{ "tsDate" : ISODate("2013-11-13T00:00:00Z") }
{ "tsDate" : ISODate("2014-02-01T00:00:00Z") }

What we did is create a new field of type ISODate which is at midnight of January 1st of this year and add to it number of days of the year from TimeStamp (times 24, times 60, times 60, times 1000) bringing the value of it to midnight of TimeStamp but in ISODate() format. Now we can use it as '_id' field of '$group' stage!

4 Comments

More on Aggregations Over Time

10/28/2013

4 Comments

Yesterday I wrote about aggregating events over time in MongoDB, and it was pointed out to me that the sequence of events that my example code finds are ones where the order is strictly linear - in other words the actor cannot skip from action1 to action3 so I didn't have to worry about detecting sequences of events that went something like 'a1', 'a3', 'a2', 'a3' since first 'a3' would come before 'a2'. There were other sequences that my aggregation framework code wouldn't catch - and mostly because initially it was code that specifically was tailored for certain types of events that could only progress in specific order.

But what if we want to see if there is an arbitrary sequence anywhere in a chain of actions that goes 'a1', 'a2', 'a3' regardless of what is between them? Here's an updated pipeline that finds exactly that.

We'll assume the same collection we had yesterday, and the same action array ["a1", "a2", "a3"] and we will generate our pipeline programmatically, though simplifying it slightly and not worrying for the moment about counting number of occurrences of each action or time taken between actions, since we saw how to do that yesterday. Instead we'll make sure our 1then2 and 1then2then3 counts are correct regardless of possible sequence of events.

 var never=ISODate("1970-01-01")
var ever=ISODate("2035-01-01")
var match={ "$match" : { "action_id" : { "$in" : actions } } };
var projectActions= { "$project" : {  "p" : "$p_id" } };
var groupByPerson={ "$group" : { "_id" : "$p" } };
actions.forEach( function(act) {  
    projectActions["$project"][act] = { };
    projectActions["$project"][act]["ts"] = { "$cond" : [ { "$eq" : [ "$action_id", act ] }, "$ts", ever ] };
    var first = act + "first";
    var last = act + "last";
    var all = act + "all";
    groupByPerson["$group"][first] = { "$min" : "$" + act + ".ts" };
    groupByPerson["$group"][last] = { "$max" : "$" + act + ".ts" };
    groupByPerson["$group"][all] = { "$push" : "$" + act + ".ts"};
});

4 Comments

Aggregating Over Time

10/27/2013

4 Comments

It's commonly thought that you can't use aggregation framework to do cumulative over time sort of analysis. While there are many problems which are hard or impossible to do in MongoDB aggregation framework, there are some that are more or less straight forward, even though at first it may seem that they are beyond the scope of simple aggregation.

One of those problems stems from the need to analyze large collections where actions were written in append only fashion with simple key information, containing only actor id, action id and time of action. This can be the case when pre-aggregation by action by person isn't feasible because rather than answering questions like "how many people did this during this hour" we will instead want to ask questions about actions relative to each other for the same person. An example would be "How many people first performed action1, then performed action2 and then performed action3?" We might also want to know how long on average it took from first to last action or from first to second. This is the sort of problem that the following aggregation framework example addresses.

The following example was loosely inspired by some consulting work I did last year with a customer who is collecting a large number of "check-in" events from many devices and then needs to answer questions after the fact about devices which failed to check-in on schedule and which ones of them performed certain events immediately prior the failure (like upgraded software, or sent a "battery low" alert). Recently a "Stack Overflow" question on a similar topic reminded me of this problem and I decided to write it up in detail.

In the example, I assume that each document in my collection (I'll call it "pats") has three fields in addition to the required _id: "p_id" number representing a person identifier, "action_id" string representing an action identifier and "ts" representing the time of action as ISODate(). It should be easy to modify my code to use exact field names and types of your collection.

First, we need to have a list of actions that we are reporting on so that we can generate the report automatically. I'm going to assume that my input will come as an array of action strings which represents actions in the order I expected them to be performed. Let's assume that "a1", "a2" and "a3" represent actions which are expected to be performed in order (though each may be performed multiple times) and that I want to know how many people performed just "a1", how many performed "a1" followed by "a2", how many performed all three actions in order and how long on average it took from "a1" to "a2" and "a1" to "a3".

So I have a giant collection, looking something like:

4 Comments

More fun with dates and MongoDB aggregations

3/12/2013

3 Comments

I mentioned a few days ago that sometimes we might want to aggregate data on day of the week. It's relatively simple to do that with MongoDB Aggregation Framework, but that does not mean that it's easy.

Imagine that you have this document:

 {
    "time" : ISODate("2013-03-03T14:43:51.468Z"),
    "quantity" : 5,
    "totalPaid" : 5423
}

There may be other fields, but we mainly want to see how many items we sold by day of the week and for what average price. Imagine that totalPaid is expressed in cents (so the above document represents $54.23 paid for five items).

First we want to project to a document that has an additional field we need: day of the week.

 {
        "$project" : {
                "quantity" : 1,
                "totalPaid" : 1,
                "_id" : 0,
                "dow" : {
                        "$dayOfWeek" : "$time"
                }
        }
}

This will turn our ISODate into an integer that represents day of the week - from 1 to 7. Next we want to do the aggregation grouping by this new field and computing the totals paid and total quantity.

3 Comments

Stupid date tricks with Aggregation Framework

3/10/2013

15 Comments

Ever since I discovered Aggregation Framework in MongoDB version 2.1 I thought it was the "coolest thing evah"(tm) and I've been playing with it ever since. Going through my notes recently I realized that I had a lot of examples some of which did some pretty clever things (if I do say so myself).

So I thought I should share them with the world! I said: "World - wouldn't you like to know how to do all kinds of cool stuff with MongoDB aggregation framework?" and world said ... well, pretty much nothing, so I took that as a "yes".

I'm not going to explain what MongoDB is or what Aggregation Framework is (since you're here, maybe you already know, if not just go and read the linked pages) and instead I'll dive right into some examples.

Imagine you have a lot of records like these:

 {
    "time" : ISODate("2013-03-08T22:25:24.973Z"), 
    "type": "annoying",
    "PINGS" : 143 
 }

You might want to group by type the number of records you have. You might want to add up how many ping records you got each month, week, each day. Maybe you even want to find out how many you get by day of the week (Do you get pinged more often on Sundays? Or does it just seem that way?). You want to count the number of records during each time period and you also want to sum up the total pings in each record.

The basic aggregation query by type goes like this:

15 Comments

How to Convert Epoch to ISODate

More Truncation of Dates in Aggregation

Aggregation Helper Functions: lpad

Converting ObjectId Values to year-month labels v2

Converting ObjectId to dates in aggregation

Yet Another Way to Group by Dates in MongoDB

More on Aggregations Over Time

Aggregating Over Time

More fun with dates and MongoDB aggregations

Stupid date tricks with Aggregation Framework

Asya Kamsky

Archives

Categories