How can I perform a map-reduce on filtered groups in RethinkDB?

Question

I have a RethinkDB table consisting of Robinhood Stock Orders. The relevant fields are:

side (either buy or sell)
state (I'm only looking for filled orders)
price (Average USD price of the order)
quantity (Number of shares sold or purchased)

I'm simply trying to understand my all-time P/L on each stock in my Orders History.

I have the following working query which tells me the price of all SELL orders, grouped by stock symbol:

r.db('robinhood').table('orders').filter({side: 'sell', state: 'filled'})
 .map(function(order) { 
   return {
     cost: (order('price').coerceTo('number')).mul(order('quantity').coerceTo('number')),
     symbol: order('symbol')
   }
}).group('symbol').sum('cost')

This returns something like this:

[{"group":"AA","reduction":491},{"group":"AAPL","reduction":10589},{"group":"ABEO","reduction":7596.16},...]

Obviously, I can do the same query for the BUY side, which would tell me the total cost of all of my purchase orders (and then, theoretically, I could just subtract this from each of the previous SELL values).

In short, how can I subtract the total value of BUY orders for each stock symbol from the total value of SELL orders for each stock symbol, using ONE ReQL query?

Thanks for your time.

score 1 · Accepted Answer · answered Jan 28 '17 at 12:41

You could try adding the inverse value of buy orders to your sum:

r.db('robinhood').table('orders').filter({state: 'filled'})
 .map(function(order) { 
   return {
     cost: order('price').coerceTo('number')
           .mul(order('quantity').coerceTo('number'))
           .mul(r.branch(order('side').eq('sell'), 1, -1)),
     symbol: order('symbol')
   }
}).group('symbol').sum('cost')

How can I perform a map-reduce on filtered groups in RethinkDB?

1 Answers1