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?
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