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 1

You could try converting the field 'year-month-day' to mongoDB native ISODate data type by using the datetime 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); 
})