Hi, so I’m trying to $group by date using the ‘Entry Date’ Field, which is inside the array “fields” (fields.Entry Date). after that I also want to count the results in a new field, after this I would like to divide this number by the total count of _ids group by date also, in order to get a ratio value ie: (0.03). But I’m having this error “invalid end of input”. Thanks for the help in advanced
[
{
$match: { "fields.Zach Approval": { $exists: true } }
},
{
$group: {_id: {$getField: [
field: "fields",
input: "$Entry Date"
]}: {format: "%Y-%m-%d", date: "$day"},
XTotalByDay:{ $sum: 1}
}
}
]
Looks like your query there is malformed, re-formatting the query slightly:
You can see the : {format: …
Section is not syntactically correct, it’s just hanging on the end of the _id field definition, unless there is a special format for charts I’ve not used.
$getField also takes in an object, not an array, so it should be:
_id: {
$getField: {
field: "fields",
input: "$Entry Date"
}
},
Why are you using $getField here? Is the field actually called “$Entry Date”, i.e. the field name has a $ in it?
What does an actual document look like?
This is how the documents looks like, Entry date is a string, “fields” is an object my bad.
{
"_id": {
"$oid": "655d7d0042b1f4a47c6971d8"
},
"fields": {
"Last Name": "Killgore",
"Entry Date": "2023-11-22T04:01:04.000Z",
"Zach Approval": true
}
}
I’m using $getField because I got this error when grouping
and If I use $toDate: “$fields.Entry Date” I just get a single document with the total count, no grouped by date.
Ok, to start with your dates are strings, this is bad. It makes them a pain to deal with, they take up more storage space and you need to cast them to do anything with them.
You could $substr them to trim down to the day, or if you convert to a date then use something like this to limit to the period you want:
This would work for your data as-is but again, if they are really date/times, then store them as a native type.
db.getCollection("Test").aggregate([
{
$group:{
_id:{
$substr:[
'$fields.Entry Date', 0,10
]
},
total:{$sum:1}
}
}
])
In your above snippit you missed the $ in the $toDate so it was trying to convert the string “fields.Entry Date” to a date as opposed to the value within the field.
Hi @John_Sewell, I found the solution doing some research. Many Thanks
