Mongodb Aggregation and Group By query

Question

I have set of baskets stored in mongodb like:

/* 47 */
{
    "_id" : ObjectId("535ff14c2e441acf44708ec7"),
    "tip" : "0",
    "basketTransactionCash" : "2204",
    "basketTransactionCard" : "0",
    "completed" : ISODate("2014-04-07T14:35:17.000Z"),
    "consumerId" : null,
    "storeId" : 1,
    "basketId" : 210048,
    "basketProduct" : [ 
        {
            "_id" : ObjectId("535feffa2e441acf446facb7"),
            "name" : "Vanilla Spice Hot Chocolate",
            "productId" : 23,
            "basketProductInstanceId" : 838392,
            "quantity" : 1,
            "productModifiedPrice" : null,
            "productInstancePrice" : "633",
            "vatPercentage" : "0.2"
        }
    ],
    "created" : ISODate("2014-04-07T14:35:42.000Z"),
    "__v" : 0
}

/* 48 */
{
    "_id" : ObjectId("535ff14c2e441acf44708ede"),
    "tip" : "0",
    "basketTransactionCash" : "230",
    "basketTransactionCard" : "0",
    "completed" : ISODate("2014-04-07T14:41:51.000Z"),
    "consumerId" : 1,
    "storeId" : 1,
    "basketId" : 210072,
    "basketProduct" : [ 
        {
            "_id" : ObjectId("535feffa2e441acf446facf3"),
            "name" : "Melon",
            "productId" : 4544,
            "basketProductInstanceId" : 838430,
            "quantity" : 1,
            "productModifiedPrice" : null,
            "productInstancePrice" : "200",
            "vatPercentage" : "0"
        }, 
        {
            "_id" : ObjectId("535feffa2e441acf446facf4"),
            "name" : "30p",
            "productId" : 8496,
            "basketProductInstanceId" : 838431,
            "quantity" : 1,
            "productModifiedPrice" : null,
            "productInstancePrice" : "30",
            "vatPercentage" : "0"
        }
    ],
    "created" : ISODate("2014-04-07T14:42:16.000Z"),
    "__v" : 0
}

/* 49 */
{
    "_id" : ObjectId("535ff14c2e441acf44708ee2"),
    "tip" : "0",
    "basketTransactionCash" : "2204",
    "basketTransactionCard" : "0",
    "completed" : ISODate("2014-04-07T14:41:54.000Z"),
    "consumerId" : 2,
    "storeId" : 1,
    "basketId" : 210076,
    "basketProduct" : [ 

        {
            "_id" : ObjectId("535feffb2e441acf446fad02"),
            "name" : "Creamy Natural Yoghurt",
            "productId" : 69,
            "basketProductInstanceId" : 839800,
            "quantity" : 1,
            "productModifiedPrice" : null,
            "productInstancePrice" : "911.7",
            "vatPercentage" : "0.2"
        }, 
        {
            "_id" : ObjectId("535feffb2e441acf446fad03"),
            "name" : "Melon",
            "productId" : 4544,
            "basketProductInstanceId" : 839801,
            "quantity" : 1,
            "productModifiedPrice" : null,
            "productInstancePrice" : "200",
            "vatPercentage" : "0"
        }, 
        {
            "_id" : ObjectId("535feffb2e441acf446fad04"),
            "name" : "30p",
            "productId" : 8496,
            "basketProductInstanceId" : 839802,
            "quantity" : 1,
            "productModifiedPrice" : null,
            "productInstancePrice" : "30",
            "vatPercentage" : "0"
        }
    ],
    "created" : ISODate("2014-04-07T14:43:00.000Z"),
    "__v" : 0
}

Could I have this grouped by completed by hourly? So the end result should be something similar to the below

{
        "totalTip" : "0",
        "basketTransactionCashTotal" : "4638",
        "basketTransactionCardTotal" : "0",
        "completed" : "2014-04-07 14",
        "consumerIds" : [null, 1, 2],
        "storeId" : 1,
        "basketIds" : [210048, 210072, 210076]
        "basketProduct" : [ 
            {
                "_id" : ObjectId("535feffa2e441acf446facf3"),
                "name" : "Melon",
                "productId" : 4544,
                "basketProductInstanceId" : 838430,
                "quantity" : 1,
                "productModifiedPrice" : null,
                "productInstancePrice" : "200",
                "vatPercentage" : "0"
            }, 
            {
                "_id" : ObjectId("535feffa2e441acf446facf4"),
                "name" : "30p",
                "productId" : 8496,
                "basketProductInstanceId" : 838431,
                "quantity" : 1,
                "productModifiedPrice" : null,
                "productInstancePrice" : "30",
                "vatPercentage" : "0"
            },
            {
                "_id" : ObjectId("535feffb2e441acf446fad02"),
                "name" : "Creamy Natural Yoghurt",
                "productId" : 69,
                "basketProductInstanceId" : 839800,
                "quantity" : 1,
                "productModifiedPrice" : null,
                "productInstancePrice" : "911.7",
                "vatPercentage" : "0.2"
            }, 
            {
                "_id" : ObjectId("535feffb2e441acf446fad03"),
                "name" : "Melon",
                "productId" : 4544,
                "basketProductInstanceId" : 839801,
                "quantity" : 1,
                "productModifiedPrice" : null,
                "productInstancePrice" : "200",
                "vatPercentage" : "0"
            }, 
            {
                "_id" : ObjectId("535feffb2e441acf446fad04"),
                "name" : "30p",
                "productId" : 8496,
                "basketProductInstanceId" : 839802,
                "quantity" : 1,
                "productModifiedPrice" : null,
                "productInstancePrice" : "30",
                "vatPercentage" : "0"
            },
            {
                "_id" : ObjectId("535feffa2e441acf446facb7"),
                "name" : "Vanilla Spice Hot Chocolate",
                "productId" : 23,
                "basketProductInstanceId" : 838392,
                "quantity" : 1,
                "productModifiedPrice" : null,
                "productInstancePrice" : "633",
                "vatPercentage" : "0.2"
            }
        ]
    }

Could anyone help me understand this for mongoose in nodejs please?

Why do you have the fields `tip`, `basketTransactionCash` and `basketTransactionCard` as Strings? Unless they are numeric, you cannot perform arithmetic operations using aggregation framework. — Anand Jayabalan, Sep 12 '14 at 15:48

score 3 · Accepted Answer · edited May 23 '17 at 12:21

First of all, the fields tip, basketTransactionCash and basketTransactionCard need to be converted to numeric type for you to perform any arithmetic operations. You can look at this question for an approach to update all documents with the right data type.

Once the data type is taken care of, you can use the aggregation framework to get close to what you want. The date aggregation operators provide a way to group by the hour. Sample query is below:

db.collection.aggregate([
{
    "$group": {
        "_id": {
            "year": {"$year": "$completed"},
            "month": {"$month": "$completed"},
            "day": {"$dayOfMonth": "$completed"},
            "hour": {"$hour": "$completed"}
        },
        "totalTip": {
            "$sum": "$tip"
        },
        "basketTransactionCashTotal": {
            "$sum": "$basketTransactionCash"
        },
        "basketTransactionCardTotal": {
            "$sum": "$basketTransactionCard"
        },
        "consumerIds": {
            "$push": "$consumerId"
        },
        "storeId": {
            "$addToSet": "$storeId"
        },
        "basketIds": {
            "$push": "$basketId"
        },
        "basketProducts": {
            "$push": "$basketProduct"
        }
    }
}
])

I would suggest you to read up on aggregation and play around with it a little bit as it's a quite powerful tool.

NOTE: Unless the data types are updated, you'll get 0 for totalTip, basketTransactionCashTotal and basketTransactionCardTotal

Thank you Anand, I will try this now and post the seconds it take to execute the query. — Karthik, Sep 12 '14 at 16:06
Just have a look at my answer too and suggest any improvement. Thank you so much — Karthik, Sep 12 '14 at 16:07
If you can get the results you want using aggregation, it will most certainly perform much better than map-reduce. Take a look at [this question](http://stackoverflow.com/questions/13908438/is-mongodb-aggregation-framework-faster-than-map-reduce) — Anand Jayabalan, Sep 12 '14 at 16:11

Mongodb Aggregation and Group By query

1 Answers1