Don't you hate it when you load in some data into MongoDB from some external source (like Twitter API) and you realize that the dates were sent as strings?  Usually they are something of this sort: 

"Thu Apr 18 18:43:24 +0000 2013" 

and this means that filtering or grouping by month, week, day, hour, etc. is going to be a great big pain!  Now, there is not yet a way to convert this into real ISODate() type using the aggregation framework but it is possible to compare them meaningfully as dates regardless.   Hopefully, soon MongoDB will implement  SERVER-4434 and this won't be necessary, but it can still be an educational example of how to parse strings.

Converting or computing a value from any other value is the prevue of the `$project` phase - that's the step in aggregation framework pipeline where we take an incoming document and transform some of its fields into some other fields.  We can compute new fields, and that's what we will be doing here.

Original document field:        "created_at" : "Thu Apr 18 18:43:24 +0000 2013".

What we would like instead:   "createdDate" : "2013-04-18T18:43:24"   

Note that we can choose to also create fields which have month, day or hour granularity,  as we are not limited to a single new field, we can create multiple new fields.

Our first stage will then be something like:   { "$project" : { "realDate" : { ??? }, <otherFields>:1 } }

We need to split the string we're getting as "created_at" into parts we will use to construct a real date.  That means separating out the year, month, day and time.  We'll ignore time zone for the moment and assume everything is in friendly UTC.

{ "$project" : { "yearStr" : { "$substr" : [ "$created_at", 26, 4 ] } } }  

is a way to extract the four digit year from the created_at string.

To get all the interesting fields we would use this projection:

We have some sample data from Twitter we can use (imported using twitter API straight to `mongoimport`) that looks like this:

Now let's try this project expression and see what we get:

All the fields are nicely comparable except for "monStr" - that's because the three letter abbreviation for month isn't well ordered, at least not in the "normal" sense that we think about when we talk about comparing dates and numbers!  We need to convert the month from "ABC" to "01" through "12".  We could convert them to a number, but then we would have to convert all of the fields of the date to numbers - and that's not easy because I'm still waiting for SERVER-11400 to be done.

So the conversion looks something like this:

Until we get a "case" or "switch" statement SERVER-10689 , the above is the only way to map an arbitrary set of values to another arbitrary set of values:

Now to combine all the substrings into different types of date string which can be compared to each other, or used to group and sort meaningfully:

Running this now gets you this sort of output:

It's not quite ready for real date math, but it will be sortable and group-able in a meaningful way now.

Someone on stackoverflow recently asked about projecting a substring out of a string field using a regular expression.  This capability does not currently exist in the aggregation framework, all we have is the "$substr" operator and several related enhancement requests in MongoDB Jira.  But the specific request was somewhat simple, and I wondered if I could figure out a way to do it with existing available functionality.

The existing "$substr" function takes a string field and two numbers, position and length and it will give you the substring of that field starting at position and of specified length.

What if I could use "$project" phase to figure out the first location of the separator?   I could then use "$substr" and I'd be all set.  Sure, it's only going to handle finding first occurrence of such character, but it's something.

This particular question was about ignoring the first part of the machine name - i.e. everything up to the first '.' character.

Imagine we have a collection which has these documents, including the name of some machine hostname and we want to report on the domain only, that is, remove the part up to the first '.' character. 

We construct the documents we will need in the `$project` stage programmatically, so we need to set some variables, starting position in the string for the search, the maximum position where this character is expected to appear, and the character itself.

Basically, we've constructed a giant "if-then-else" statement by looping and checking every character against the one we are interested in.  This is why there has to be a limit set on the length of the string we want to search on.

We now use the first array element to get the appropriate substring, and here are our results:

If you think it would be useful to have a more straight forward way to manipulate strings, please comment on and vote for SERVER-8951 in Jira.

There had been a lot of questions about when db.fsyncLock() is necessary before creating a backup of your MongoDB files† and when it's not.  And there are tricky things around using it, because if you get yourself into a state where you can't fsyncUnlock then you will have to bring down your `mongod` process by force. 

I had been thinking about this problem and realized that one of the tricks to getting the problem minimized would be to run the backup from the same mongo shell sessions/script as the lock/unlock pair, so I wrote a little script to do that.   The script defines a function called 'runBackup' which takes a string representing your backup command (so it could be a shell script, batch file, ec2-snapshot command, tar czf command, anything at all).  The script checks that the server is not already locked, and if it's not, it acquires the lock, runs the command given, and upon its completion runs the unlock command.

The last line shows how to call this function - you can use this as a starting point to create your own.
The code is in my Github repo, along with many other little scripts that might be useful to people who are playing with mongo shell much of the time.

† the general answer is that you only need fsyncLock/Unlock during backup if you are taking a file system snapshot and your journal is on different volume than your data files, or if you are using another way of copying files that can't guarantee getting the data files and journal files as of the same point in time.  No other method requires fsyncLock, not mongodump, nor snapshots that can get data and journal files together.

If you read my posts, you know that I'm a big fan of MongoDB Aggregation Framework.  It was brand new in 2.2 over a year ago and last year in 2.4 release it got faster and picked up some cool new features.   The next production release is 2.6 and aggregation framework continues growing up as a major feature of MongoDB.  Since MongoDB 2.6.0 is now available, I thought I would point out some of the coolest improvements that will make aggregation even simpler and more powerful.

If you want to see the complete list of SERVER tickets fixed for 2.6, it's HERE.  A more readable summary is in the release notes for 2.6.

The biggest and baddest is SERVER-5932, support for aggregations returning a cursor.   Some think that SERVER-3253 is the biggie, that's the one that allows you to redirect the output of of aggregation to another collection (and its cousin SERVER-10097 that allows you the same capability on a sharded cluster).  But if you ask me, getting back a cursor pointing to the results, so I can iterate over it the same way we already do over results from find(), that's the more important one and if I want to, I can then send the results to another target collection (or some other place), so I've been most excited about that.  Needless to say, this makes the old limitation of aggregation returning only 16MB history.

Of course there are many other new features, from major ones or minor ones.  I'm sure you'll be seeing them show up in my future posts as I think of new and novel ways of torturing documents into new shapes, forms and uses!

One of the most disappointing things about Aggregation Framework in MongoDB is that it doesn't give you many ways to figure out dynamically what schema you are working with.  There's the expected '$ifNull' operator, since many fields might show up only in some documents, but I wish there was a simple way to tell if something is or isn't an array.

It turns out there is a "stupid trick" for that.  You can use "$project" stage to output a new field which tells you whether or not another field is an array like this:

This isn't strictly "kosher" but it works in 2.4 and in upcoming 2.6 so I'm going to keep using it - hopefully by the time it stops working, a real operator to project type may be available.

What's the point of doing this, other than just "for fun"?  Well, it turns out there are some operators which only work on arrays and if you try to use them on non-arrays you will get an error.  One of them in "$unwind" stage, another is the new in 2.6 "$size" operator which returns the size of an array field, but gives an error if the field is not an array. 

We took an assortment of documents which had tags field as an array sometimes, as non-array of different types sometimes, and absent some of the times.  The projection we created outputs the size of the field tags in every case, where if the field is null, absent or a non-array we output size 'null' and in all other cases we output the actual size of the array.

Of course the '$size' operator is quite handy in much simpler scenarios where you might be creating an array of unique values during a '$group' stage using'$addToSet' and then you can use '$size' to find out how many unique values ended up being accumulated in that array.

For those looking forward to other new features of aggregation in 2.6 I figured I'd show an alternate way to do this with only a single "$project" stage, instead of three in sequence above:

Here, I'm using the new in 2.6 "$let" expression, which allows me to create a named variable "isArray" which I later reference using "$$isArray" syntax.  Note that I'm also using the new in 2.6 "$cond" syntax which lets me use a document with "if", "then", and "else" field names, hopefully making it more readable than just using an array of three expressions (which is still supported so that you don't have to rewrite your existing aggregations).

I hope you enjoy some of the new 2.6 aggregation enhancements - I will probably continue writing about them, as they make some of our old tricks unnecessary, but open the door to a whole new set of tricks to come!

There is no `$round` operator in MongoDB Aggregation Framework at the moment.  It would probably be useful, but in the meanwhile, I thought I could show a trick that allows you to get the same result using just the supported math operators.

Starting with very simple example - we enter floating point numbers and we want to truncate them to two decimal places:

Note we're not actually rounding here, we are truncating to the desired decimal place.

But we are having trouble with floating point arithmetic so we have to be more careful like this:

Here is a more complex example to get the ratio of two numbers as percentage to two decimal places:

And last, the example that got me to write this up - I was doing some aggregations of orders and line items and wanted to show the total price in thousands of dollars - but I wanted to round rather than truncating the amount:

Over the last few weeks, users of MongoDB all over the world have managed to employ what one of my co-workers calls a "Foot-Gun" - that's when you shoot yourself in the foot, needlessly and usually carelessly causing yourself very much pain.

I've worked with databases for more years than I care to think about - open source, closed source, big and well known, small and obscure, OLTP and OLAP.  I've been on the vendor side, and I've been on the customer side, as a developer and as a DBA.

In each of them there were ways to employ the foot-gun and depending on when, how and how many times you shot yourself you might recover completely, or you might limp along for a long time painfully trying to recreate the data from backups, replay logs and anything else you could get your hands on.

In a perfect world, a database would have a solid trigger lock on the foot-gun that requires bio-recognition, two-factor authentication and some other magical incantation before allowing you to mess with its innards, but we live in the real world where 'sudo' allows an OS user run 'rm' on arbitrary set of files, and where rushed and overworked DBAs and developers sometimes try to fix things "ad hoc" in the heat of a fire drill (which is when the worst self-mutilations usually occur, IME).

The most I realistically hope for is that we (as database makers/vendors/champions) don't load the gun, take the safety off, and place it within easy reach of every database and OS user.  When we do that, it's our fault even when the user picked up the gun and shot themselves in the foot. Twice.

I've debated whether or not it's a good idea to document exact failings that led to such painful outcomes.  On one hand, schadenfreude may be an enjoyable pastime; on the other hand, I don't think it ever helped anyone in a constructive manner, except when it included the detailed explanation of either how to avoid such circumstances, or how to recover with least amount of long-term damage.

How about it readers?  Have you shot yourself with the foot-gun?  Do you think you would have been able to avoid it had you read about someone else doing the exact same thing you wished you hadn't done?  Or was in caused in part by reading on the internet "how to fix" something and getting it wrong?

P.S. One of the best footgun metaphors I've ever seen is by Tom \"spot\" Calloway on Fedora developers mailing list where he described always logging in as root like this:    

         "What you're doing is analogous to using a loaded shotgun as a golf club, and what you're 
           suggesting is that we take the safety off, because it interferes with your golf game."

In an early post I mention that the reports of demise of MongoDB DBA have been greatly exaggerated, in my opinion.  NoSQL does not mean "no DBA".  If anyone tries to convince you otherwise, they probably have something to sell you.

This does not mean that you have a team or even a person who has the title "DBA" - however, if you have a database, whether it's relational, or non-relational, then someone has the role of "DBA" - and if they don't know that they do, then a whole bunch of things aren't being done or thought about before problems happen.

Over the last couple of weeks, I've worked with users who ran into major problems, some of which could have been prevented if they had better clarity around "DBA" role.  In all but one of those cases, they were able to recover successfully partly because they had recent backups.  Some of the tasks owned by the DBA role were happening, even if others were not.

In the remaining case, it turned out the most recent backup was more than three months old.  There is no excuse for that, unless you can easily regenerate all of your data!

Luckily, we were able to recover the lost data for this user (straight from the free list in the database files, but you really cannot count of that being possible - they got very lucky in that regard) but I'm willing to bet that they will start keeping track of whose job it is to make sure that the database is backed up.

Sometimes people forget that to measure performance you need to measure throughput (how many requests are you servicing in parallel) and latency (how long is it taking to service each request).  

It boggles my mind when someone runs a single threaded test and sends work to the database serially and then complains about bad throughput.  In order to measure throughput you have to throw as much work at the database server as you can get to it, and usually it means running many clients or multi-threaded clients.

Recently on the MongoDB Google Group someone asked if adding more shards to their cluster would make a particular MapReduce job faster.   Here is how I answered them:

Make sense?

MongoDB Replica Sets are an awesome included feature that gives you High Availability with automatic failover via a consensus election.  Having been a pager-carrying DBA in my previous life, I can tell you that if failover involves a human (usually me) being woken up in the middle of the night to promote a secondary to be the new master, I do not consider that "high" availability at all!   Working in the middle of the night is when I make all the errors that catch up with me weeks or months later, so I'd rather the system "do the right thing", and replica sets do just that.

Of course you also get extra copies of your data (for Disaster Recovery or just for compliance reasons) and other benefits, like ability to do maintenance without scheduling downtime for the database, but what if you don't need any of that?

What if you just want to have an extra copy of your data, but you don't want to have high availability?

I occasionally want to have that myself, when I need to test replication of data but don't want to deal with elections, arbiters or for whatever other reason want to pretend that my "primary" is basically a stand-alone node, it just happens to be replicating to another node (that might even be running on the same laptop).

In MongoDB the replica set architecture is set up to prevent two primaries from existing at the same time (this allows the data to stay consistent and avoids needing to conflict-resolve in cases of network partition) - consensus elections where a node must receive >50% of the votes to assume a PRIMARY role guarantees that two nodes cannot be elected at the same time.  But that also means that if you have an even number of nodes (like two) you must get both votes to become or stay a primary.

In order to avoid losing the primary whenever I happen to take down my secondary node, I adjust the replica set configuration to make sure that only one node gets to participate in elections.  Having only one vote means that majority is one, and my intended primary can "unanimously" elect itself every time - as long as it's up!

Here is the configuration:

The "priority" field says whether a node can be elected as a primary - zero means not eligible.  Other numbers would influence the nodes relative standing against other equally eligible nodes during an election;  "hidden" means the node's presence won't be revealed to clients/drivers that are connecting to the replica set as a whole; "votes" expresses whether the node gets a vote in the election - note that whether a node can vote and whether it can become a primary are two separate settings!

This means that if I lose the primary node, the secondary will remain as a secondary since it cannot elect itself - and I won't have high availability, and that's exactly what I was trying to achieve here.

You wouldn't think this happens very often, but then you would be surprised...

This week someone's sharded MongoDB cluster was running with one config server instead of three (you are "required" to have three but for testing purposes the system "allows" you to run with one with a warning).   Wouldn't you know it, somehow the database directory got deleted.  Oops.

Last week, someone was asking how they can recover from accidentally deleted files on a node which was running stand-alone (not replica set!)

The week before, someone accidentally let loose a script which deleted a bunch of database files on nodes (primaries and secondaries were affected, making it impossible to "just sync from unaffected node").

Here's a little trick you might keep in the back of your mind, in case something like this is needed to save your bacon some day, when someone deletes some database files that you were in the process of actually using!

What I say applies to Linux because Windows is a little more pedantic about letting users mess around with files that other users or processes happen to have open.  But in Linux, I can have a file open, and you can delete it.  If I'm in the editor it might give me a message like "Hey, this file seems to have changed on disk, what do you want to do - load this new thing?  Save your work on top of it?  Something else?"

If you know a little about MongoDB internals, you know that each database has its own set of files inside the "dbpath" - by default "/data/db" directory.  If your database is called "mydb" then the files will look something like:

As you can see, there is a "namespace" file, and two "data" files, each double the size of the previous one (up to 2GB) and there is always one more preallocated than the one you are currently writing to.  I just created this database, so I've got a little bit of data in mydb.0 and just lots of zeros in mydb.1.  Let's change that.

Now that I've got some useful data (or at least *some* data) I'm going to simulate some idiot "cleaning up" and let's say he deletes all my files that are bigger than 100MB.  Hey, we know that sort of thing happens...

What a jerk!!!  But notice that we can still see the files "out there" even though we can't see them in the directory listing...  And when we get back to the database, we see the strangest thing - our fileSize dropped from 2239MB to 64MB, but our data and index counts haven't changed.   In fact, if we play around, we will find that it's not noticeable in a running 'mongod' that the physical files are not on disk!

So let's take advantage of that.  There are many ways to rebuild the database files if they are still memory mapped, the simplest one would be the database command "repairDatabase()"

We did it!   We took advantage of the fact that 'mongod' was still running, we connected to it and asked it to run a command which rewrites all of the records in all collections of a db anew (and then drops the old database, and its old files).

Please promise me, that if you are about to run out and try this out yourself that you won't try it on a live production database.  Sure, you can restore the files with db.repairDatabase() if mongod is still running, but do you really want to run that experiment and find out that in the middle of it the power went out and you lost your files anyway?  (Of course you didn't, because you run a replica set, right?  Right?)

Everyone knows that MongoDB is incredibly easy to get started with.

Most people also agree that MongoDB is very easy to prototype with.  I'm talking especially about the kind of prototyping where you don't really know what the stress points of your application are going to be, what data will be most critical to access fast, what sort of questions you will need to ask of the data after the fact...  In other words, exactly the situation you're in when you first start developing any new application.

MongoDB is easy to install, easy to use with your choice of development languages and it has some wonderful operational features.  Okay, we already know all this, so what is this trap that I am talking about?

I've seen time and time again what happens to a super-fast little MongoDB backed app as the demand on it suddenly skyrockets: if there was no performance/load testing before launch, it's likely that the bottlenecks are a surprise and it's not always clear what they are, where they are, and how to "fix" them.

Here is where people can fall into one of two traps:
  
1. assume that their application load is not a good fit for MongoDB at scale.  
2. assume that MongoDB can handle this if they can figure out how to "tune" it properly.

"Wait a minute," I hear you say.  "Isn't it going to be the case that one of those things is true?"

Absolutely true.  It's possible that the requirements of the application at scale are not a good fit for a document database.  It's also possible that it is the use of MongoDB (schema design, indexing, hardware configuration) that was not done with high scale in mind, and MongoDB is perfectly suited to the workload being thrown at it.

"The trap" is when the assumption about which scenario you're in and reality don't match:

1. Scenario: Assumption is made that application is not a good fit for MongoDB but the application load is actually a perfect fit for MongoDB but poor schema design or bad indexing strategy or suboptimal topology or hardware choices are the cause of bad performance.  
    Result: the team will spend tremendous effort architecting a new system to store their data when they could have improved their MongoDB performance by several orders of magnitude by fixing their design or hardware or cluster topology.

Here are some examples of this:
- There is a missing index.  Solution: you don't fix the slow query by moving the task into a different system, you simply add the missing index.
- Indexes created are not best for the queries/updates running on the system, they take up RAM without providing much of a benefit.  Solution: review and fix your indexes.
- The schema split a number of things into separate collections that should be stored together and every request for the object takes multiple accesses to the database instead of just one.  Solution: reconsider/redesign parts of your schema.
- You have multiple shards but every request is sent to every shard, increasing the overall number of requests that the system must handle.  Sharding scales best when each shard is only required to do work on its portion of the dataset, not the entire dataset.
- You have system that does heavy writes but you chose extremely slow storage system.  Solution: get faster disks (after making sure that your writes aren't unnecessarily inefficient).
- You've made some inappropriate assumptions about how various parts of the system will benefit your use case, or followed outdated or flat-out wrong advice about how to use MongoDB.  Solution: examine your assumptions that don't seem to be holding up, and remove any inefficiency that was created by following the incorrect assumption.

A variant of these scenarios is when an assumption is made about how MongoDB will work with the application and then time is wasted trying to "tune" the wrong thing or worse, "pre"-optimizations are made to cater to some rumored limitation of MongoDB and it's the "optimization" that ends up killing the performance, not the original thing that it was meant to "correct".

2. Scenario: The application load was a terrible fit for MongoDB but for whatever reason an assumption is made that MongoDB will be able to handle it, if only the right "tuning" is made.  
    Result:   The team will spend tremendous effort trying to improve performance of a square peg in a round hole, instead of finding a round peg.

Examples of this include any scenario where you find yourself implementing more database work in your  application than you are asking the database to do for you.   Some examples out in the real world include situations where after migrating from MongoDB to another datastore, the application ended up having a lot less code - that tends to tell me that MongoDB was a poor choice from the start.   

To be honest, I've seen a lot more examples of 1. than 2.  Because MongoDB is quite flexible, it can be a good fit for an extremely wide range of application needs, but if no thought is given to proper schema, indexes and cluster configuration to serve those needs, there is no end to the number of ways that it can fall short of your expectations.

The worst of them all is the assumption that because MongoDB was so easy to install and so easy to get started with (not to mention so fast when it was being run with just some test data) that it will somehow tune itself at scale, without the developer having to give any thought to it.  It would be wonderful if that were true, but at the end of the day, MongoDB is a database, and there is no magic pixie dust that you can sprinkle on it to say "just go faster" - it is my humble opinion, that reports of death of the role of MongoDB DBA have been greatly exaggerated.

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!

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: