title: Mongo DB aggregation self join with average
body: <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>