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
– Anand Jayabalan, Commented Sep 12, 2014 at 15:48

Community · Accepted Answer · 2017-05-23 12:21:40Z

3

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

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Sep 12, 2014 at 16:05

Anand Jayabalan

13.1k6 gold badges44 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Karthik Over a year ago

Thank you Anand, I will try this now and post the seconds it take to execute the query.

Karthik Over a year ago

Just have a look at my answer too and suggest any improvement. Thank you so much

Anand Jayabalan Over a year ago

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

Karthik Over a year ago

Thank you Anand. I will take yours as answer.

Collectives™ on Stack Overflow

Mongodb Aggregation and Group By query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related