mongodb group and combine
Question: I have a doc that has a schema like `{'year-month-day','others'}` and I want
to convert `'year-month-day` to an ISODate time so I can use
`$match:{'$gte:targetDate'}`

I have several problems:

  1. I am using pymongo, which dones support javaciprt, so I can not use `new Date()` in python and at the same time `datetime` is not working as well because it can not read `'$year'`.

I think one way to achieve the above goal is first get the substrings of
`'year-month-day'` and after aggregation I can use `forEach(function(x){...})`
to create a `ISODate` for each date and compare with `target` but doing means
I will have to scan through every docs in the database which I dont think is a
good choice.

  2. If the first is not doable in pymongo, how can I do it by mongodb query? how can I use project to create a column with new data type?(like what I did in the second project).

  3. Is there any way to do javascrip inside pymongo?

My script is like following:

    
    
    Collection.aggregate([                    
                    {
                        '$project':{
                            'year':{'$substr':['$year-month-day',0,4]},
                            'month':{'$substr':['$year-month-day',5,2]},
                            'day':{'$substr':['$year-month-day',8,2]},
                            'others':'others'
                         }    
                    },
                    {
                        '$project':{
                            'approTime':new Date(Date.UTC('$year','$month','$day')),
                            'others':'others'
                         }    
                    },
                    {
                        '$group':{
                            '_id':{
                                'approTime':'$approTime',
                                'others':'others'
                            },
                            'count':{'$sum':1}
                        }
                    }
    


Answer: You could try converting the field `'year-month-day'` to mongoDB native
ISODate data type by using the
**[datetime](https://docs.python.org/2/library/datetime.html)** module which
is stored under the hood as the native date object in MongoDB:

    
    
    from pymongo import MongoClient
    from datetime import datetime
    
    client = MongoClient('host', port)
    db = client['database']
    col = db['collection']
    attr = 'year-month-day'
    date_format = "%Y-%m-%d %H:%M:%S.%f" #date_format is the format of the string eg : "%Y-%m-%d %H:%M:%S.%f"
    for doc in col.find():
        if doc[attr]:
            if type(doc[attr]) is not datetime:
                isodate = datetime.strptime(doc[attr], date_format)
                col.update({'_id': doc['_id']}, {'$set': {attr: isodate}})
    

This can also be done in Mongo shell as:

    
    
    db.collection.find({
        "$and": [
            { "year-month-day": { "$exists": true } },
            { "year-month-day": { "$not": {"$type": 9} } }
        ]
    }).forEach(function(doc) { 
        doc["year-month-day"] = new Date(doc["year-month-day"]);
        db.collection.save(doc); 
    })
    

