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"};
});
var middleOne="$"+actions[1]+"all"
var unwind={ $unwind : middleOne };
var groupBack = { "$group" : { } };
groupBack["$group"]["_id"] = "$_id";
groupBack["$group"][actions[0]+"first"] = {"$first":"$"+actions[0]+"first"};
groupBack["$group"][actions[1]+"first"] = {"$min": {$cond:[ {$gt:["$"+actions[1]+"all", "$"+actions[0]+"first"]}, "$"+actions[1]+"all", ever]}};
groupBack["$group"][actions[2]+"last"] = {"$first": {$cond:[ {$lt:["$"+actions[2]+"last", ever]}, "$"+actions[1]+"all", never]}};
var didA = "did" + actions[0];
var andClause = { "$and" : [ ] };
var didFirst = { "$lt" : [ "$" + actions[0] + "first", ever ] };
andClause["$and"].push(didFirst);
var projectBool = { "$project" : { "_id" : 0, "p" : "$_id" } };
projectBool["$project"][didA] = didFirst;
var groupAll = { "$group" : { "_id" : null } };
groupAll["$group"][didA] = { "$sum" : { "$cond" : [ "$" + didA, 1, 0] } };
var projectNeat = { "$project" : { "_id" : 0 } };
projectNeat["$project"][didA] = 1;
// middle action didA = didA + "then" + actions[1];
andClause["$and"].push( { "$lt" : [ "$" + actions[1] + "first", ever ] } );
andClause["$and"].push( { "$gt" : [ "$" + actions[1] + "first", "$" + actions[0] + "first" ] } );
projectBool["$project"][didA] = { "$and" : [ ] };
andClause["$and"].forEach(function(a) { projectBool["$project"][didA]["$and"].push(a); });
groupAll["$group"][didA] = { "$sum" : { "$cond" : [ "$" + didA, 1, 0] } };
projectNeat["$project"][didA] = 1;
// for last action
didA = didA + "then" + actions[2];
andClause["$and"].push( { "$lt" : [ "$" + actions[2] + "last", ever ] } );
andClause["$and"].push( { "$gt" : [ "$" + actions[2] + "last", "$" + actions[1] + "first" ] } );
projectBool["$project"][didA] = andClause;
groupAll["$group"][didA] = { "$sum" : { "$cond" : [ "$" + didA, 1, 0] } };
projectNeat["$project"][didA] = 1;
> db.pats.aggregate([ match, projectActions, groupByPerson, unwind,
groupBack, projectBool, groupAll, projectNeat ]).next()
{ "dida1" : 2770, "dida1thena2" : 974, "dida1thena2thena3" : 569 }