Using Mongoose Aggregation to Group By and Calculate Averages

Question

I have a MongoDB collection with the following structure:

    Prices: [ 
        exchange, symbol, volume, buy, sell, last, low, high
    ]

I am using Mongoose to query the records, and I'd like to get averages of buy, sell, last, low, high over the last 3 hours grouped by the symbol.

Essentially my goal is to display something like the following: (Let A, B, C, D, E, F be placeholders for numbers)

    Latest Prices for Exchange:
        Symbol A    Volume: A   Buy: B  Sell: C Last: D Low: E  High: F Spread: C - B
        Symbol B    Volume: A   Buy: B  Sell: C Last: D Low: E  High: F Spread: C - B

    3 Hour Averages for Exchange:
        Symbol A:   Volume: A   Buy: B  Sell: C Last: D Low: E  High: F Spread: C - B
        Symbol B:   Volume: X   Buy: B  Sell: C Last: D Low: E  High: F Spread: C - B

I've been reading through the MongoDB docs: https://docs.mongodb.org/manual/reference/operator/aggregation/group/

To get the averages I've tried something like this:

    query = Prices.aggregate([
        {
            $group: {
                _id: "$symbol",
                avg_last: { $avg: "$last" },
                avg_buy: { $avg: "$buy" },
                avg_sell: { $avg: "$sell" },
                avg_low: { $avg: "$low" },
                avg_high: { $avg: "$high" },
                averageSpread: { $avg: { $subtract: ["$sell", "$buy"] } },
                count: { $sum: 1 }
            }
        }
    ]);

The problem is that there is no "condition" in this to limit the results to the past 3 hours.

Using the MongoDB Docs again for $cond https://docs.mongodb.org/manual/reference/operator/aggregation/cond/

I've tried to apply this:

    var threeHoursAgo = new Date();
    threeHoursAgo.setHours(threeHoursAgo.getHours() - 3);

    query = Prices.aggregate([
        {
            $group: {
                _id: "$symbol",
                avg_last: { $avg: "$last" },
                avg_buy: { $avg: "$buy" },
                avg_sell: { $avg: "$sell" },
                avg_low: { $avg: "$low" },
                avg_high: { $avg: "$high" },
                averageSpread: { $avg: { $subtract: ["$sell", "$buy"] } },
                count: { $sum: 1 }
            },
            $cond: {
                exchange: strategy.primaryExchanges[i].exchange._id,
                timestamp: {
                    $gte: threeHoursAgo
                }
            }
        }
    ]);

But I'm getting the following error:

"Error: Arguments must be aggregate pipline operators".

EDIT: I've also tried using $match like:

    query = Prices.aggregate([
        {
            $group: {
                _id: "$symbol",
                avg_last: { $avg: "$last" },
                avg_buy: { $avg: "$buy" },
                avg_sell: { $avg: "$sell" },
                avg_low: { $avg: "$low" },
                avg_high: { $avg: "$high" },
                averageSpread: { $avg: { $subtract: ["$sell", "$buy"] } },
                count: { $sum: 1 }
            }
        },
        {
            $match: {
                $and: [
                    {exchange: strategy.primaryExchanges[i].exchange._id},
                    {timestamp: {$gte: threeHoursAgo} }
                ]
            }
        }
    ]);

but I get an empty array back.

EDIT 2 Here is a sample document from my Prices collection:

 {"_id":"ObjectId(56296ac0603c75c80b3d581f)","symbol":"XBT24H","contract":"XBT24H","exchange":"ObjectId(55dc8ae9ecb73538125ddd9a)","timestamp":"ISODate(2015-10-22T23:01:15.000Z)","volume":260475,"buy":275.41,"sell":275.7,"low":274.44,"last":275.7,"high":277.03,"__v":0}

Using Mongoose Aggregation to Group By and Calculate Averages

Answers (1)

Related Questions