I have a collection called Document in MongoDB. Documents in this collection have a field called CreationDate stored in ISO date type. My task is to count the number of documents created per day and sort by the number asynchronously. The output format is required to be [{_id:'yyyy-MM-dd', cnt:x}]. I tried to use aggregation framework as below.
db.Document.aggregate( , {$project: {_id:1, Year:{$year:'$CreationDate'}, Month:{$month:'$CreationDate'}, Date:{$dayOfMonth:'$CreationDate'}}} , {$group: {_id:{$concat:['$Year', '-', '$Month', '-', '$Date']}, cnt:{$sum:1}}} , {$sort:{'cnt':-1}} );
The code gives me error as below:
$concat only supports strings, not NumberInt32
I understand this is because $year, $month and $dayOfMonth all return number. It's possible to compose the _id field as an object and re-format it in the desired format in application level.
But from technical perspective, I have two questions:
How to convert a number to string in MongoDB shell? In this case, output of $year can then be converted to string and used in $concat.
Is there a better way to format ISODate output to various date formats? In many cases, we only need certain part of an ISODate, for example: the date component or the time portion. Is there any MongoDb inbuilt operators to achieve this?
Thanks in advance for any advice.
You can change the data type of a field by using the data type selectors on the right of the field in the Atlas Cloud Cluster as well as MongoDB Compass . If you want to update it using Mongo shell or any specific drivers of MongoDB then you can refer to the $convert operator.
NumberInt. The mongo shell treats all numbers as floating-point values by default. The mongo shell provides the NumberInt() constructor to explicitly specify 32-bit integers.
Definition. $project. Passes along the documents with the requested fields to the next stage in the pipeline. The specified fields can be existing fields from the input documents or newly computed fields.
Double: The double data type is used to store the floating-point values. Example: In the following example we are storing the marks of the student in the student collection: 4. Boolean: The boolean data type is used to store either true or false.
You can do this with $concat
but first you need to convert to a string via $substr
, also handling the double digit case:
db.Document.aggregate([ { "$group": { "_id":{ "$concat": [ { "$substr": [ { "$year": "$CreationDate" }, 0, 4 ] }, "-", { "$cond": [ { "$gt": [ { "$month": "$CreationDate" }, 9 ] }, { "$substr": [ { "$month": "$CreationDate" }, 0, 2 ] }, { "$concat": [ "0", { "$substr": [ { "$month": "$CreationDate" }, 0, 1 ] }, ]}, ]}, "-", { "$cond": [ { "$gt": [ { "$dayOfMonth": "$CreationDate" }, 9 ] }, { "$substr": [ { "$dayOfMonth": "$CreationDate" }, 0, 2 ] }, { "$concat": [ "0", { "$substr": [ { "$dayOfMonth": "$CreationDate" }, 0, 1 ] }, ]} ]} ] }, { "cnt": { "$sum": 1 } } }} { "$sort":{ "cnt" :-1 }} ]);
Possibly better is to just use date math instead, this returns an epoch timestamp value, but it is easy to work into a date object in post processing:
db.Document.aggregate([ { "$group": { "_id": { "$subtract": [ { "$subtract": [ "$CreationDate", new Date("1970-01-01") ] }, { "$mod": [ { "$subtract": [ "$CreationDate", new Date("1970-01-01") ] }, 1000 * 60 * 60 * 24 ]} ] }, "cnt": { "$sum": 1 } }}, { "$sort": { "cnt": -1 } } ])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With