I have objects in my bookings collection. Structure of the object is as follows:
{
"_id": "asdkbasdkjcbhsajcbh",
"created_at": 1662712231,
"total_teus": 4,
...
}
I want to write a query which gives me Month-on-Month or Year-on-Year sum of total_teus sold. I tried my hands but failed.
I wrote the following query:
db.bookings.aggregate([{
"$match": {
"created_at": {
"$gte": 1648771200
},
"company_id": {
"$nin": ["ln_exports1582702220", "marico_limited1588656327", "star_fisheries_pvt_ltd1576862831", "td_infotech1569314187", "Xportify1561638979", "msuyash1572972166", "demo_account1587548110", "production_testing1586152669", "abc_tech_pvt._ltd.1571657756"]
}
}
},
{
"$addFields": {
"created_date_long": {
"$toLong": "$created_at"
}
}
},
{
"$addFields": {
"created_date": {
"$toDate": "$created_date_long"
}
}
},
{
"$group": {
"_id": {
"$dateToString": {
"date": "$created_date",
"format": "%Y-%m"
}
},
"teus_exported": {
"$sum": "$total_teus"
}
}
}
])
When I ran this query, I got the following result:
{
"_id": "1970-01",
"teus_exported": 234
}
I want result to look like this:
[{
"_id": "1970-01",
"teus_exported": 234
},
{
"_id": "1970-02",
"teus_exported": 654
},
{
"_id": "1970-03",
"teus_exported": 623
}]
Can someone throw some light as to where my query is going wrong?
CodePudding user response:
You need to multiply your created_at
by 1000, as it is in seconds, and $dateToString expect milliseconds:
db.collection.aggregate([
{
"$match": {
"created_at": {
"$gte": 1648771200
},
"company_id": {
"$nin": [
"ln_exports1582702220",
"marico_limited1588656327",
"star_fisheries_pvt_ltd1576862831",
"td_infotech1569314187",
"Xportify1561638979",
"msuyash1572972166",
"demo_account1587548110",
"production_testing1586152669",
"abc_tech_pvt._ltd.1571657756"
]
}
}
},
{$addFields: {
created_date: {
$dateToString: {
date: {$toDate: {$multiply: ["$created_at", 1000]}},
format: "%Y-%m"
}
}
}},
{$group: {_id: "$created_date", teus_exported: {$sum: "$total_teus"}}}
])
See how it works on the playground example