title: MongoDB: Aggregation of stock tick data
body: <p>I am using MongoDB to store my stock tick data. I have one document per stock symbol per minute:</p>
  
  <pre><code>{
  "_id" : ObjectId("535fb330f6a03d59077db43c"),
  "symbol" : "AAPL",
  "ts_minute" : ISODate("2014-04-29T14:12:00Z"),
  "ticks" : [
      {
          "mu" : 115864,
          "ae" : true,
          "t" : 2,
          "v" : 571.93
      },
      {
          "mu" : 803378,
          "ae" : true,
          "t" : 2,
          "v" : 571.91
      },
      {
          "mu" : 903378,
          "ae" : false,
          "t" : null,
          "v" : 9000
      }
  }
  </code></pre>
  
  <p>where <code>mu</code> is the distance in microseconds since <code>ts_minute</code>, <code>t</code> is tick type (bid, ask, open, close, volume, etc.), and <code>v</code> is value.</p>
  
  <p>To aggregate this into minutely bars of OHLC (open, high, low, close) I use the following (with PyMongo):</p>
  
  <pre><code>query = {'$match': {'symbol': 'AAPL'}}
  projection = {
      '$project': {
          'symbol':       1,
          'year':         {'$year':   '$ts_minute'},
          'month':        {'$month':  '$ts_minute'},
          'day':          {'$dayOfMonth': '$ts_minute'},
          'hour':         {'$hour':   '$ts_minute'},
          'minute':       {'$minute': '$ts_minute'},
          'ts_minute':    1,
          'ticks':        1
      }
  }
  unwind = {'$unwind': '$ticks'}
  sort = {'$sort': {'ts_minute': 1}}
  group = {
      '$group': {
          '_id': {
              'symbol':   '$symbol',
              'year':     '$year',
              'month':    '$month',
              'day':      '$day',
              'hour':     '$hour',
              'minute':   '$minute'
          },
          'open':     {'$first':  '$ticks.v'},
          'high':     {'$max':    '$ticks.v'},
          'low':      {'$min':    '$ticks.v'},
          'close':    {'$last':   '$ticks.v'},
      }
  }
  bars = tick_collection.aggregate([query, projection, unwind, sort, group])
  </code></pre>
  
  <p>The problem is that I store volume ticks and price ticks in the same array. Volume ticks are identified by having <code>t</code> equal to <code>null</code>. So you see, when I group, my price ticks and volume ticks get mixed. I would like to aggregate to OHLCV, such that OHLC is based on <code>t</code> not equal to <code>null</code>, and V should be the last element of the array where <code>t</code> equals <code>null</code>.</p>
  
  <p>Does it make sense? Or is it just poor schema design? ;-)</p>