MongoDB Complex aggregate query to sum elements in embedded arrays, with limit

Question

I am relatively new to Mongo, and trying to figure out how to complete this query. I'm using mongoose, in node.js. I want to get the sums of sell.amount, and buy.amount, from this document with a nested array of objects. The trades array is large, and I want to limit it to the first (from 0 ) n number of objects. I want to be able to get returned something like:

{buy.totalAmount, buy.tradesCount},{sell.totalAmount, sell.tradesCount},

as a sum of the number of elements selected by $limit

I figure I must be close, but so far I cannot figure out how to make this work.

My Query:

tradePair.aggregate(
        {$match: {pair:"STOCK"}},
        {$unwind:"$trades"},
        {$limit: 20},
        {$group : { _id : '$trades.buy', 
            count : {$sum : 1},
            totalAmount: { $sum: '$trades.buy.amount' }
        }},
        function(err,result){
          return result
        }
)

My Database Document, showing only 2 trade array elements..of many...

{
    "_id" : ObjectId("58fa86c81cdd7b2375cdd4cc"),
    "pair" : "STOCK",
    "trades" : [ 
        {
            "sell" : {
                "trades" : 1,
                "total" : 0.13309789,
                "amount" : 24.80139,
                "rate" : 0.00536655
            },
            "buy" : {
                "trades" : 0,
                "total" : 0,
                "amount" : 0,
                "rate" : 0
            },
            "_id" : ObjectId("58fa87290567372b4035d16f"),
            "endTradeId" : 41306,
            "startTradeId" : 41306,
            "dateEnd" : ISODate("2017-04-21T21:37:39.000Z"),
            "dateStart" : ISODate("2017-04-21T21:37:39.000Z")
        }, 
        {
            "sell" : {
                "trades" : 2,
                "total" : 1.23879614,
                "amount" : 230.83659924,
                "rate" : 0.00536655
            },
            "buy" : {
                "trades" : 0,
                "total" : 0,
                "amount" : 0,
                "rate" : 0
            },
            "_id" : ObjectId("58fa87290567372b4035d16e"),
            "endTradeId" : 41305,
            "startTradeId" : 41304,
            "dateEnd" : ISODate("2017-04-21T21:35:28.000Z"),
            "dateStart" : ISODate("2017-04-21T21:35:27.000Z")
        }, 
        ..., 
        ..., 

    ],
    "lastTradeId" : 41306,
    "dateStart" : ISODate("2017-04-21T21:37:39.000Z"),
    "dateEnd" : ISODate("2017-04-21T21:37:39.000Z"),
    "__v" : 0
}

s7vr · Accepted Answer · 2017-04-23 23:46:01Z

You can use below aggregation pipeline with Mongo 3.4.

The below code will use $match stage to keep matching documents.

$project uses $slice on trades array to return 20 elements followed by $reduce which takes the array values and sum the value for each document.

db.tradePair.aggregate([{
        $match: {
            pair: "STOCK"
        }
    },
    {
        $project: {
            trades: {
                $reduce: {
                    "input": {
                        $slice: ["$trades", 20]
                    },
                    initialValue: {
                        buyTotalAmount: 0,
                        buyTradesCount: 0,
                        sellTotalAmount: 0,
                        sellTradesCount: 0
                    },
                    in: {
                        buyTotalAmount: {
                            $add: ["$$value.buyTotalAmount", "$$this.buy.amount"]
                        },
                        buyTradesCount: {
                            $add: ["$$value.buyTradesCount", "$$this.buy.trades"]
                        },
                        sellTotalAmount: {
                            $add: ["$$value.sellTotalAmount", "$$this.sell.amount"]
                        },
                        sellTradesCount: {
                            $add: ["$$value.sellTradesCount", "$$this.sell.trades"]
                        }
                    }
                }
            },
            _id: 0
        }
    }
])

Update:

Add avg, min and max in the same pipeline for sell.trades field.

The below query will set the initialValue to the first element of $trades.sell.tradesand followed by $lt comparison on the sell.trades and if true set $$this to $$value, if false keep the previous value and $reduce all the values to find the minimum element and $gt to find the max element with the similar logic. Also, added the count field to keep track of number of trade entry. The outermost $let which reads the result of inner $reduce along with dividing sumsellTradesCount with count to calculate avg.

db.tradePair.aggregate([{
        $match: {
            pair: "STOCK"
        }
    },
    {
        $project: {
            trades: {
                $let: {
                    vars: {
                        obj: {
                            $reduce: {
                                "input": {
                                    $slice: ["$trades", 20]
                                },
                                initialValue: {
                                    minsellTradesCount: {
                                        $let: {
                                            vars: {
                                                obj: {
                                                    $arrayElemAt: ["$trades", 0]
                                                }
                                            },
                                            in: "$$obj.sell.trades"
                                        }
                                    },
                                    maxsellTradesCount: {
                                        $let: {
                                            vars: {
                                                obj: {
                                                    $arrayElemAt: ["$trades", 0]
                                                }
                                            },
                                            in: "$$obj.sell.trades"
                                        }
                                    },
                                    sumsellTradesCount: 0,
                                    count: 0
                                },
                                in: {
                                    sumsellTradesCount: {
                                        $add: ["$$value.sumsellTradesCount", "$$this.sell.trades"]
                                    },
                                    minsellTradesCount: {
                                        $cond: [{
                                            $lt: ["$$this.sell.trades", "$$value.minsellTradesCount"]
                                        }, "$$this.sell.trades", "$$value.minsellTradesCount"]
                                    },
                                    maxsellTradesCount: {
                                        $cond: [{
                                            $gt: ["$$this.sell.trades", "$$value.minsellTradesCount"]
                                        }, "$$this.sell.trades", "$$value.minsellTradesCount"]
                                    },
                                    count: {
                                        $add: ["$$value.count", 1]
                                    }
                                }
                            }
                        }
                    },
                    in: {
                        minsellTradesCount: "$$obj.minsellTradesCount",
                        maxsellTradesCount: "$$obj.maxsellTradesCount",
                        sumsellTradesCount: "$$obj.sumsellTradesCount",
                        avgsellTradesCount: {
                            $divide: ["$$obj.sumsellTradesCount", "$$obj.count"]
                        }
                    }
                }
            },
            _id: 0
        }
    }
])

Update 2:

Add avg, min and max in the same pipeline for sell.amount field.

db.tradePair.aggregate([{
        $match: {
            pair: "STOCK"
        }
    },
    {
        $project: {
            trades: {
                $let: {
                    vars: {
                        obj: {
                            $reduce: {
                                "input": {
                                    $slice: ["$trades", 20]
                                },
                                initialValue: {
                                    minsellTotalAmount: {
                                        $let: {
                                            vars: {
                                                obj: {
                                                    $arrayElemAt: ["$trades", 0]
                                                }
                                            },
                                            in: "$$obj.sell.amount"
                                        }
                                    },
                                    maxsellTotalAmount: {
                                        $let: {
                                            vars: {
                                                obj: {
                                                    $arrayElemAt: ["$trades", 0]
                                                }
                                            },
                                            in: "$$obj.sell.amount"
                                        }
                                    },
                                    sumsellTotalAmount: 0,
                                    count: 0
                                },
                                in: {
                                    minsellTotalAmount: {
                                        $cond: [{
                                            $lt: ["$$this.sell.amount", "$$value.minsellTotalAmount"]
                                        }, "$$this.sell.amount", "$$value.minsellTotalAmount"]
                                    },
                                    maxsellTotalAmount: {
                                        $cond: [{
                                            $gt: ["$$this.sell.amount", "$$value.maxsellTotalAmount"]
                                        }, "$$this.sell.amount", "$$value.maxsellTotalAmount"]
                                    },
                                    sumsellTotalAmount: {
                                        $add: ["$$value.sumsellTotalAmount", "$$this.sell.amount"]
                                    },
                                    count: {
                                        $add: ["$$value.count", 1]
                                    }
                                }
                            }
                        }
                    },
                    in: {
                        minsellTotalAmount: "$$obj.minsellTotalAmount",
                        maxsellTotalAmount: "$$obj.maxsellTotalAmount",
                        sumsellTotalAmount: "$$obj.sumsellTotalAmount",
                        avgsellTotalAmount: {
                            $divide: ["$$obj.sumsellTotalAmount", "$$obj.count"]
                        }
                    }
                }
            },
            _id: 0
        }
    }
])

Thanks Veeram, I'll give this a shot tomorrow and see what my result is. The $project thing is a little mysterious to me. A couple of questions.. will $slice grab the top 20 (0 - 19) or the last 20? Also, is it possible to for example grab a slice from index (5 - 25) for example.. skipping the first few documents..
You are welcome. $project to keep/remove fields in the pipeline. Answer to first question: top 20. Use $slice:[array, -20] for last 20.. Answer to second question: Yes, $slice:[array, 5, 25]. More examples and info docs.mongodb.com/manual/reference/operator/aggregation/slice & docs.mongodb.com/manual/reference/operator/aggregation/proje‌ct
Great Veeram... everything works as expected, as well this will be a very good template for me to start making more queries into the data.. thank you so much!
Hi Veeram, you seem very knowledgeable.. I wonder if you know how I can also do things like return an average, minimum, and maximum Amount, where I am summing Amounts. I notice the mongo arithmetic operators don't seem to allow directly doing this.

Collectives™ on Stack Overflow

MongoDB Complex aggregate query to sum elements in embedded arrays, with limit

1 Answer 1

12 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Linked

Related