mongo aggregation framework get transactions in sequence

Question

How do i do this in mongo aggregation framework? given these records

record 1. {id:1,action:'clicked',user:'id', time:'1'}
record 2. {id:2,action:'video play',user:'id',time:'2'}
record 3. {id:3,action:'page load',user:'id',time:'3'}
record 4. {id:4,action:'video play',user:'id',time:'4'}
record 5. {id:1,action:'clicked',user:1id', time:'5'}
record 6. {id:2,action:'video play',user:'id',time:'6'}

now, how do i get the all "video play" that are after clicked action? anybody come across with this kind of aggregation?

Answer 1

You will need to redesign your schema. I can think of two approaches. In your application you can track the click path of a session. When you insert an action to your collection, you will need to also track the previous interaction. Once you have this, then you just need to do something like db.actions.find({prevAction:"clicked",action:"video play"}).count(). This will be very fast.

Alternatively, if you decide you like to track session click path information, you may have a document like:

{_id:sessionid
user:usderid
actions:[
{...login}
{...click link}
{...play video}
]}

You can create this collection by doing upserts. Make sure you keep the action subdocuments small so you don't exceed the 16MB limit for standard documents. Also set the collection's padding factor to "powersof2."

Once you have this collection, you can pull out these documents to get some interesting info. The specific aggregation that you want to do would be more complex on this collection than the suggestion that I previously made though. You will need to create a MR process that may run periodically behind the scenes to calculate what you want (emit key value only if the area contains the expected sequence of actions).