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"};
}); 

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: