CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Lee
Lee

Reputation: 3032

Data type conversion in MongoDB

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:

  1. 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.

  2. 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.

Upvotes: 26

Views: 14247

Answers (5)

user3616725
user3616725

Reputation: 3655

FOR MONGO >= 3.0 (after ~2015)

In case some poor souls stumble across this question in 2017, like me: As of Mongo 3.0 there is now a dateToString operator available

which means if you have proper Date()'s you should be able to do simply:

db.Document.aggregate( 
    , {$project: {_id:1, CreationDate:1}
    , {$group: {
         _id : { $dateToString: { format: "%Y-%m-$d", date: "$CreationDate" } },
         cnt:{$sum:1}}}
    , {$sort:{'cnt':-1}}
);

For those of us who happen to have dates stored in non-date field (what joy!) you can create a new Date() object in the project step. In my case, the date was stored as a number of milliseconds (integer) and I add the number of milliseconds to the 0-Date.

db.Document.aggregate( 
    , {$project: {
         _id:1, 
         CreationDate: {"$add": [ new Date(0), "$CreatedOn" ]}
}
    , {$group: {
         _id : { $dateToString: { format: "%Y-%m-$d", date: "$CreationDate" } },
         cnt:{$sum:1}}}
    , {$sort:{'cnt':-1}}
);

Upvotes: 4

Neil Lunn
Neil Lunn

Reputation: 151220

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 } }
])

Upvotes: 31

fabien0102
fabien0102

Reputation: 21

To convert date format to "xxxx-xx-xx"

db.getCollection('analytics').aggregate([
 {$project: {
     day: {$dayOfMonth: "$createdAt"},
     month: {$month: "$createdAt"},
     year: {$year: "$createdAt"},
     }
  },
  {$project: {
      day: {$concat:["0", {$substr:["$day",0, 2]}]},
     month: {$concat:["0", {$substr:["$month",0, 2]}]},
     year: {$substr:["$year",0, 4]},
      }
  },
  {$project: {date: {$concat: [{$substr:["$year",0, 4]},"-", {$substr:["$month",0, 2]},"-", {$substr:["$day",0, 2]}]}}},
]);

Upvotes: 2

Joe
Joe

Reputation: 1432

Another easier way you can convert ISODate to various date formats is to use the $dateToString aggregation operator.

db.collection.aggregate([
    { $group: {
        _id: { $dateToString: { format: "%Y-%m-%d %H:%M", date: "$CreationDate" } },
        count: { $sum: 1 }
    }},
    { $sort : { count: -1 }}
])

Upvotes: 15

joaquim.adraz
joaquim.adraz

Reputation: 91

Neil's answer its right to the point but has a little bug. In the conditionals (to check if the number of the month and day needs to be prepended with a 0) is not $gt but $lte.

With $lte, only months and days with one digit will be prepended with 0.

e.g: 2014-10-03, 2014-08-03.

Upvotes: 4

Related Questions