Mongo DB aggregation self join with average
<p>I need to make enough complicated trick with aggregation framework.</p>

<p>I am aggregating logs of the operation state change. (pretty common I guess) </p>

<p>After a couple of pipeline steps I get something like:</p>

<pre><code>    {
    "operaiton_id" : "56cf29e90c15c45630347320",
    "timestamp" : 1456417257087,
    "old_state" : "OPEN",
    "new_state" : "IN_EXAM"
    },

    { 
    "operaiton_id" : "56cf29e90c15c45630347320",
    "timestamp" : 1456481305971,
    "old_state" : "IN_EXAM",
    "new_state" : "IN_ALERT"
    },

    ...
    { 
    "operaiton_id" : "33ab23f92999c40638553320",
    "timestamp" : 1458147304895,
    "old_state" : "OPEN",
    "new_state" : "IN_EXAM"
    },

    { 
    "operaiton_id" : "33ab23f92999c40638553320",
    "timestamp" : 1458147310251,
    "old_state" : "IN_EXAM",
    "new_state" : "IN_ALERT"
    }
</code></pre>

<p>What I am looking for here is an average transition time from each state to each state. Having an SQL I make a self join, kind of: </p>

<pre><code>SELECT AVG(a.timestamp - b.timestamp)
FROM a JOIN a AS b ON a.old_state=b.new_state
</code></pre>

<p>Any ideas how could I do it in Mongo Aggregation? Feeling desperate.</p>