Qfyilyi

While creating a query, I realized that the app I have inherited have a collection with a timestamp field containing either a string or an ISODate value.

So, this aggregation stage :

{

  "$addFields": {

    "timestamp": {

      "$dateFromString": {

        "dateString": "$activity.timestamp"

      }

    },

    "minTimestamp": {

      "$dateFromString": {

        "dateString": "2016-01-01"

      }

    },

    "maxTimestamp": {

      "$dateFromString": {

        "dateString": "2017-01-01"

      }

    }

  }

}

produces the error : $dateFromString requires that 'dateString' be a string, found: date on certain documents.

Obviously, the logical answer would be to convert all field values into ISODate, however this behemoth of a system seem to save inconsistent values being set for that field, and it is not possible for me to guarantee the type in advance.

Is there a way to conditionally convert the field to ISODate?

The app is running on MongoDB 3.6.4.

If some of the fields are actually BSON Dates, then you probably want to leave them alone and output them as is. For this you can use $type along with a $cond expression:

{ "$addFields": {

  "timestamp": {

    "$cond": {

      "if": { "$eq": [{ "$type": "$activity.timestamp" }, "string" ] },

      "then": {

        "$dateFromString": {

          "dateString": "$activity.timestamp"

        }

      },

      "else": "$activity.timestamp"

    }

  }

}}

That's okay from MongoDB 3.4 and upwards which is when $type was added.

Noting for users of MongoDB 4.0 and above, the $convert operator actually has built in error handling branching:

{ "$addFields": {

  "timestamp": {

    "$convert": {

      "input": "$activity.timestamp",

      "to": "date",

      "onError": "Neither date or string"

    }

  }

}}

The onError can be any expression and is returned in the instances where the conversion was invalid. Finding a BSON Date is not actually an error and errors would only occur for an invalid numeric or string value or different type which did not support conversion.

If you know for certain that the data is always either a BSON Date or a valid string for conversion then there is the $toDate helper which is basically a wrapper for $convert without the onError handling:

{ "$addFields": {

  "timestamp": { "$toDate": "$activity.timestamp" }

}}

So some data scrubbing and/or query conditions can often be combined with that for a more streamlined coding experience.

A note on $dateToString usage

Within the question the $dateToString is also being used to convert "static values" from strings into BSON Date. This is not a good idea.

Running functional code within a server expression which is more cleanly expressed in natural language code is not and never really was a good practice. As part of the general MongoDB philosophy, the parts that really should and can be expressed in that language should be done so that way.

For JavaScript simple Date objects are serialized as BSON Date on submission to the server anyway:

"minTimestamp": new Date("2016-01-01")

Since the value of the "string" is external then it does not need server side expression to manipulate it. Just like issuing a query you cast such types before you submit to the server and not afterwards.

The same concept is true of all language implementations, as all languages have a "Date" type which the implemented driver understands and correctly serializes as a BSON Date anyway.

Conversion

With all of that said, the general "best practice" here is of course to actually convert the data. "Behemoth" or not, it's really only making matters worse to rely on conversion of the data at run time. This is even more so if your actual intention in such run-time conversion is to use the BSON Dates in further output processing, rather than just a pretty output.

Mileage varies on which approach works the best, but the basics are to either iterate the collection and update values in place, and the $type "query" operator can help with selection here:

// Presuming date strings in"yyyy-mm-dd" format

var batch = ;



db.collection.find({ "activity.timestamp": { "$type": "string" } }).forEach(d => {

  batch.push({

    "updateOne": {

      "filter": { "_id": d._id },

      "update": { "$set": { "activity.timestamp": new Date(d.activity.timestamp) } }

    }

  });



  if (batch.length >= 1000) {

    db.collection.bulkWrite(batch);

    batch = ;

  }

})



if (batch.length > 0) {

  db.collection.bulkWrite(batch);

  batch = ;

}

Or run the aggregation with $out to a new collection if constraints allow this:

{ "$addFields": {

  "activity": {

    "timestamp": {

      "$cond": {

        "if": { "$eq": [{ "$type": "$activity.timestamp" }, "string" ] },

        "then": {

          "$dateFromString": {

            "dateString": "$activity.timestamp"

          }

        },

        "else": "$activity.timestamp"

      }

    }

  }

}},

{ "$out": "newcollection" }

Any of the above aggregation methods shown can be used here where they are supported, but just showing this in example. Note also the $addFields does allow the nested object syntax since output is "merged" into the existing document structure.

Even on a production system you could always output to a new collection and then drop and rename with minimal downtime. The main constraint here would actually be index rebuilding, which would take significantly longer than a collection rename.