Update document using the result of concatenated f

问题:

Consider a collection with the following documents

{
        "_id" : "aaaaaaaaaaaa",
        "title" : "Hello, World!",
        "date" : "Thursday, November 12, 2015",
        "time" : "9:30 AM",
        "endtime" : "11:30 AM"
},
{
        "_id" : "bbbbbbbbbbbb",
        "title" : "To B or not to B",
        "date" : "Thursday, November 12, 2015",
        "time" : "10:30 AM",
        "endtime" : "11:00 AM"
},
{
        "_id" : "cccccccccccc",
        "title" : "Family Time",
        "date" : "Thursday, November 12, 2015",
        "time" : "10:30 AM",
        "endtime" : "12:00 PM"
}

In this simplified output, I have events that have had their start times, ending times, and dates all entered in as strings. How can I use fields in an update() that use the existing data to calculate new properly formed Date()-type data that I can actually query.

The following works to create a new "iso_start" field

db.events.update({},{$set: {iso_start:Date()}},{multi: true})

I imagined I would be able to build a sort of update-select like so

db.events.update({},{$set: {iso_start:Date(date + " " + time)}},{multi: true}) and db.events.update({},{$set: {iso_end:Date(date + " " + time)}},{multi: true})

but I get the error "date is not defined."

Update: this.date and this.time made the not defined error go away, however the dates inserted were for the present datetime. I tried writing new Date() but then the date inserted was ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")

回答1:

You need use the .aggregate() method which provides access to the to the aggregation pipelines.

In your $project stage you need to use the $concat operator to concatenate your field.

You can then use your aggregation result to update your collection using "bulk" operations for efficiency

var bulk = db.events.initializeOrderedBulkOp(); 
var count = 0;
db.events.aggregate([
    { "$project": {
        "iso_start": { "$concat": [ "$date", " ", "$time" ] }, 
        "iso_end": { "$concat": [ "$date", " ", "$endtime" ] }    
    }}
]).forEach(function(doc) { 
    bulk.find({'_id': doc._id}).updateOne({
        "$set": {
            "iso_start": new Date(doc.iso_start),
            "iso_end": new Date(doc.iso_end)
        }
    }); 
    count++; 
    if(count % 200 === 0) { 
        // update per 200 operations and re-init
        bulk.execute();     
        bulk = db.events.initializeOrderedBulkOp(); 
    } 
})
// Clean up queues
if(count > 0) bulk.execute();

After this operation your documents look like this:

{
        "_id" : "aaaaaaaaaaaa",
        "title" : "Hello, World!",
        "date" : "Thursday, November 12, 2015",
        "time" : "9:30 AM",
        "endtime" : "11:30 AM",
        "iso_start" : ISODate("2015-11-12T06:30:00Z"),
        "iso_end" : ISODate("2015-11-12T08:30:00Z")
}
{
        "_id" : "bbbbbbbbbbbb",
        "title" : "To B or not to B",
        "date" : "Thursday, November 12, 2015",
        "time" : "10:30 AM",
        "endtime" : "11:00 AM",
        "iso_start" : ISODate("2015-11-12T07:30:00Z"),
        "iso_end" : ISODate("2015-11-12T08:00:00Z")
}

---

That is not the end of the story because the "Bulk" API and his associated methods are deprecated in the forthcoming release (version 3.2 ) thus from that version we will need to use the db.collection.bulkWrite() method.

var operations = [];
db.events.aggregate([
    { "$project": {
        "iso_start": { "$concat": [ "$date", " ", "$time" ] }, 
        "iso_end": { "$concat": [ "$date", " ", "$endtime" ] }    
    }}
]).forEach(function(doc) {
    var operation = {
        updateOne: { 
            filter: { "_id": doc._id }, 
            update: { 
                "$set":  { 
                   "iso_start": new Date(doc.iso_start),
                   "iso_end": new Date(doc.iso_end)
                }
            }
        }
    }; 
    operations.push(operation); 
})
operations.push({ ordered: true, writeConcern: { w: "majority", wtimeout: 5000 } });
db.events.bulkWrite(operations)