Q_Lion
(Q Lion)
December 14, 2020, 9:28am
1
Hi
I’m trying to do to aggregate, sum up values in a month, but i only want to sum 1 entry per day, i tried to use $limit before the group but only get 1 entry return.
Thanks.
[
{
$match:
{
"fPort": 60,
"rxInfo.time":
{
"$gte": "2020-12-01T00:00:00.000000Z",
"$lte": "2020-12-31T59:59:59.935Z"
}
}
},
{ $limit: '1' }, ///// This only returns 1 record and not 1 per day.
{
$group:
{
_id: "9cd9cb00000124c1",
"Total":
{
"$sum": "$object.TotalDaily"
}
}
}
]
1 Like
turivishal
(Vishal Turi)
December 14, 2020, 10:14am
2
Hello @Q_Lion , Welcome to MongoDB Developer Community,
As i can understand your question, you need to get count of documents yearly or monthly or daily,
You can use $year , $month , $dayOfMinth Date Expression Operators.
Example for Daily document count,
[{
$group: {
_id: {
year: { $year: "$yourDateFieldName" },
month: { $month: "$yourDateFieldName" },
day: { $dayOfMonth: "$yourDateFieldName" }
},
Total: { $sum: 1 }
}
}]
Example for Monthly document count,
[{
$group: {
_id: {
year: { $year: "$yourDateFieldName" },
month: { $month: "$yourDateFieldName" }
},
Total: { $sum: 1 }
}
}]
You can replace yourDateFieldName
to your collection field, and put your $match
stage before this $group
stage.
1 Like
Q_Lion
(Q Lion)
December 14, 2020, 5:56pm
3
I think i have found the query.
i managed to get 1 record per day and sun the values for 1 month, Thanks
[
{
$match:
{
"fPort": 60,
"rxInfo.time":
{
"$gte": "2020-12-01T00:00:00.000000Z",
"$lte": "2020-12-31T59:59:59.99"
}
}
},
{
$project:
{
_id:1,
year:
{
$year:
{
$dateFromString:
{
dateString:
{"$arrayElemAt": [ "$rxInfo.time", 0 ] }
}
}
},
month:
{
$month:
{
$dateFromString:
{
dateString:
{"$arrayElemAt": [ "$rxInfo.time", 0 ] }
}
}
},
day:
{
$dayOfMonth:
{
$dateFromString:
{
dateString:
{"$arrayElemAt": [ "$rxInfo.time", 0 ] }
}
}
},
sum : "$object.TotalDaily"
}
},
{
$group:
{
_id:
{
year:"$year",
month:"$month",
day:"$day"
},
TotalPerDay:
{
$sum:"$sum"
}
}
},
{
$group:
{
_id: "9cd9cb00000124c1",
"MonthlyTotal":
{
"$sum": "$TotalPerDay"
},
}
}
]
Q_Lion
(Q Lion)
December 14, 2020, 7:48pm
4
Q_Lion:
$object.TotalDaily
Hi @turivishal Thanks for the response,
no i don’t want count it, i want to add value of $object.TotalDaily together, but u only want to add 1 value per day, per month.
I tried to use $group this way but i get a error.
{
_id: {
year:{$year:"$rxInfo.time"},
month:{$month:"$rxInfo.time"},
day:{$dayOfMonth:"$rxInfo.time"}
},
"Total":
{
"$sum": "$object.TotalDaily"
}
}
but keeps telling me “can’t convert from BSON type array to Date”.
Thanks
1 Like
turivishal
(Vishal Turi)
December 15, 2020, 4:39am
5
The field "$rxInfo.time"
having string type date and also inside an array, it should be date type and outside the array, you need to deconstruct the rxInfo
array using $unwind , and than use $group
stage.
You have already did in your second reply, you can convert it using $dateFromString or $toDate Date Expression Operators, Before the $group
stage inside $addFields
, or inside the $group
stage that you have already did.
Hi, I need to order all the data by months from an aggregate in a list record
something like this
January
01-01-2020
filed1,field2,field3
02-01-2020
fieild1,field2,field3
February
01-02-2020
field1,field2,field3
02-02-2020
field1,field2,field3
April
01-04-2020
field1,field2,field3
02-04-2020
field1,field2,field3
And I have no clue how to do it.
Hello @jack_austin , Welcome to the MongoDB community forum,
This topic is not related to the current topic, Can you please ask your question in a new post?
@turivishal ok no problem. I will create a new topic.