Building a pipeline and aggregate in Mongo

Question

How do I aggregate the below collection of document type to sum the quantity of all product_id sold based on each district_id and city_id within a period of time

I tried using the aggregate functions of $match, $group but haven't been successful.

{
    "_id" : ObjectId("5b115e00a186ae19062b0714"),
    "id" : 86164014,
    "cost" : 3,
    "created_date" : "2017-04-04 21:44:14",
    "quantity" : 12,
    "bill_id" : 46736603,
    "product_id" : 24,
    "bill_date" : "2017-04-04",
    "district_id" : 75
    "city_id": 21
}

[SQL to Aggregation Mapping Chart](https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/). But right now your biggest problem is the `created_date` is a "string" and not something you really should be using for a "period of time". Your first priority should be [converting this](https://stackoverflow.com/questions/10942931/converting-string-to-date-in-mongodb), and the rest is really not that difficult when you try. I suggest you first convert your dates and then show an attempt at least. — Neil Lunn, Jun 02 '18 at 08:19
Hi Neil, i have converted created_date from string to date and now am able to aggregate . find the result in the answer below — Vigneshwar A, Jun 04 '18 at 06:31
db.getCollection("collectionname").aggregate([ {$match: {bill_date: { $gte: ISODate("2017-04-01T00:00:00.0Z"), $lte: ISODate("2017-04-30T00:00:00.0Z") }}}, { $group: { _id: { product_id: "$product_id", district_id: "$district_id" }, quantities: { $sum: "$quantity" } } } ]) — Vigneshwar A, Jun 04 '18 at 06:48

score 0 · Answer 1 · answered Jun 02 '18 at 08:32

You should be more specific about the "within a period of time" and which field we should consider, but the query for the first part could be this one:

db.getCollection("your collection").aggregate([
    {
        $group: {
            _id: {
                city_id: "$city_id", 
                district_id: "$district_id"
            },
            quantities: { $sum: "$quantity" }
        }
    }
])

Building a pipeline and aggregate in Mongo

1 Answers1