mongoDB multiple-levels nested string conversion to date objects

Question

I have a mongoDB containing entries like this:

{ "_id" : ObjectId("5bdb6a44d9b2d4645509db2e"), 
"crs" : { "type" : "name", 
         "properties" : { "name" : "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"type" : "FeatureCollection", 
"features" : [ { "geometry" : { "type" : "Point", "coordinates" : [ 45,66 ] }, 
"type" : "Feature", 
"id" : 50,  
"properties" : { "fogClass" : 0, "_note" : "movable", "fileLocation" : "blah.jpg", "timeStamp" : "2018-11-01 14:51:00", "predFALSE" : 0.998167, "ipAddr" : "http://abcd.ef", "longitude" : "45", "predTRUE" : 0.001833, "cameraID" : "IDABC", "originalPath" : "originalBlah.jpg", "location" : "location1", "latitude" : "66" } } ] }

I would like to perform timestamp-based queries but I have understood that the timestamp have to be mongoDB timestamp objects and not strings as they are now.

I have found that this function might help in converting to mongoDB date objects https://docs.mongodb.com/manual/reference/operator/aggregation/toDate/ and it works on a toy example with a very simple (not-nested) JSON structure.

When I perform the same operation to convert to date the timeStamp field in my dataset (it is stored in the mongoDB in a collection named "collection") and create a new field for that called "timeMongo" as follows:

db.collection.aggregate({$addFields:{timeMongo:{"$toDate":"$features.properties.timeStamp"}}})

I get the following error:

 [js] Error: command failed: {
    "ok" : 0,
    "errmsg" : "Unsupported conversion from array to date in $convert with no onError value",
    "code" : 241,
    "codeName" : "ConversionFailure"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
@(shell):1:1

Looking forward to any help or tips how to make it work. Thanks in advance!

As asked by Anthony here is a toy example and how it should work: this is an example collection with a timeStamp field as string

db.testColl3.find()
{ "_id" : ObjectId("5c1e7fc6e9739a0c2ef3d7fc"), "item" : "card", "qty" : 15, "timeStamp" : "2018-12-20 08:00:00" }

then once I issue the command:

db.testColl3.aggregate({$addFields:{timeMongo:{$toDate:"$timeStamp"}}})

I get this response:

{ "_id" : ObjectId("5c1e7fc6e9739a0c2ef3d7fc"), "item" : "card", "qty" : 15, "timeStamp" : "2018-12-20 08:00:00", "timeMongo" : ISODate("2018-12-20T08:00:00Z") }

which is what I would like to obtain

Could you show the expected output

Ashh
– Ashh

2018-12-18 11:10:54 +00:00
Commented Dec 18, 2018 at 11:10 — Ashh
– Ashh, Commented Dec 18, 2018 at 11:10
I have edited the question with a little toy example

andrea pagani
– andrea pagani

2018-12-22 18:49:04 +00:00
Commented Dec 22, 2018 at 18:49 — andrea pagani
– andrea pagani, Commented Dec 22, 2018 at 18:49

Ashh · Accepted Answer · 2018-12-23 06:20:28Z

2

Your date is inside the array that's why it is throwing error Unsupported conversion from array to date.

You need to iterate over the features array using $map and then need to add the field timeMongo converted from string to date

db.collection.aggregate([
  { "$addFields": {
    "features": {
      "$map": {
        "input": "$features",
        "in": {
          "$mergeObjects": [
            "$$this",
            {
              "timeMongo": {
                "$toDate": "$$this.properties.timeStamp"
              }
            }
          ]
        }
      }
    }
  }}
])

answered Dec 23, 2018 at 6:20

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andrea pagani Over a year ago

Thank you very much for the explanation and for the solution!! The code you have provided solves the issue perfectly!

Collectives™ on Stack Overflow

mongoDB multiple-levels nested string conversion to date objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related