mongodb candlestick query

Question

I need to query financial data from mongo and generate a daily Candle Stick graph.

collection schema is something like:

{
  symbol: 'GOOG',
  amount: 1000,
  rate: 123,
  created_at: ISODate('some point in time')
}

Each entry in the graph (i.e per a given day) should consist of 4 values:

1. high (max(rate))
2. low (min(rate))
3. open (first rate in a given day)
4. close (last rate in a given day)

Any idea how to build the query?

Answer 1

OK, got this figured after @David Peleg sent me this repo:

db.collection.aggregate([
      {$match:{
        symbol:'GOOG'
      }},
      {$project:{
        "day":{
          "y": {"$year":"$created_at"},
          "m": {"$month":"$created_at"},
          "d": {"$dayOfMonth": "$created_at"}
        },
        created_at: 1,
        rate: 1
      }},
      {"$sort":{"created_at":1}},
      {"$group":{
        "_id": "$day",
        "created_at": {"$first":"$created_at"},
        "open": {"$first":"$rate"},
        "close": {"$last":"$rate"},
        "high": {"$max":"$rate"},
        "low": {"$min":"$rate"},
      }},
      {$project:{
        "_id": "$_id",
        "rates":{
          "open": "$open",
          "close": "$close",
          "high": "$high",
          "low": "$low"
        }
      }},
      {"$sort":{"_id":1}}
    ])