How to change date format of this data structure in MongoDB?-CodePudding

I have some users and the structure is something like that:

{
 "_id": {"$oid":"6213baaba013b7c5f1232e25"},
 "birthDate": 239290215000,
 "surname": "Yang",
 "name": "Ruby",
 "joinDate": 1534861815000,
 "children": [
    {
     "birthDate": 876749415000,
     "surname":"Yang",
     "name":"Bob"
    },
    {
     "birthDate":926753415000,
     "surname":"Yang",
     "name":"Mel",
     "joinDate":1579005015000
    }
 ],
}

I will calculate age of user and then how long ago did they join? So I've tried to change date format but I was only able to change the format of the parents' birth dates. I could not change the format of the children's birth dates. How can I change all dates and calculate their ages? My expected output:

{
 "_id": {"$oid":"6213baaba013b7c5f1232e25"},
 "birthDate": ISODate(...),
 "surname": "Yang",
 "name": "Ruby",
 "joinDate": ISODate(...),
 "children": [
    {
     "birthDate": ISODate(...),
     "surname":"Yang",
     "name":"Bob"
    },
    {
     "birthDate": ISODate(...),
     "surname":"Yang",
     "name":"Mel",
     "joinDate": ISODate(...)
    }
 ],
}

CodePudding user response：

If I've understood correctly you can try this:

The trick here is to use $toDate.

So, to output the values as ISODate instead of timestamp you can use this aggregate query with a $map to update every children object:

db.collection.aggregate([
  {
    "$set": {
      "birthDate": {
        "$toDate": "$birthDate"
      },
      "joinDate": {
        "$toDate": "$birthDate"
      },
      "children": {
        "$map": {
          "input": "$children",
          "in": {
            "surname": "$$this.surname",
            "name": "$$this.name",
            "birthDate": {
              "$toDate": "$$this.birthDate"
            }
          }
        }
      }
    }
  }
])

Example here

Also, if you want to update the DB values, (not only output ISODate but also change values in DB) you can try this update query:

db.collection.update({},
[
  {
    "$set": {
      // the same $set as before
    }
  }
])

Example here