How to convert timestamp to date in an array with Mongodb?-CodePudding

I need to convert the timestamp to date and update it in the same field, in Mongosh. Here is the data:

{   _id: 1, 
    name: 'Annelise',
    movies: [ { movieid: 12, rating: 3, timestamp: 97830291 } ] },
{   _id: 2,
    name: 'Maria',
    movies: [ { movieid: 3, rating: 3, timestamp: 978301398 } ]
  },
  {
    _id: 3,
    name: 'Paul',
    movies: [ { movieid: 23, rating: 4, timestamp: 978302174 } ]
  },
  {
    _id: 4,
    name: 'Fred',
    movies: [
      { movieid: 23, rating: 4, timestamp: 978302174 },
      { moviedid: 45, rating: 2, timestamp: 978302149 }
    ]
  },
  { _id: 5, name: 'Annelise', timestamp: 97830291 },
  { _id: 6, name: 'Maria', timestamp: 978301398 },
  { _id: 7, name: 'Paul', timestamp: 978302174 },
  { _id: 8, name: 'Fred', timestamp: 978302149 }

I tried using an aggregation pipeline, but I have 2 problems:

This command works to update the timestamps for _id 5 to 8, but how can I save it to the same field?

db.usertest.aggregate(
        [ {"$project": 
                {"timestamp": { $dateToString: { format: "%Y-%m-%d", date: { "$toDate": {$toLong:"$timestamp"}}}}
        } } ])

How can I update the timestamp inside the movies array for _id 1 to 4? I tried the following but this does not work:

db.usertest.aggregate( [ {"$project": {"timestamp": { $dateToString: { format: "%Y-%m-%d", date: { "$toDate": {$toLong:"$timestamp"}}}}}} ])

Thanks for your help!

CodePudding user response：

You can use an aggregation pipeline in update. In MongoDB, the date in unix timestamp is in milliseconds(ms). So, multiply your raw timestamp with 1000 before converting them to date.

For _id 1-4, use $map to perform element-wise transformation.

db.collection.update({
  _id: {
    $in: [
      1,
      2,
      3,
      4
    ]
  }
},
[
  {
    "$addFields": {
      "movies": {
        "$map": {
          "input": "$movies",
          "as": "m",
          "in": {
            movieid: "$$m.movieid",
            rating: "$$m.rating",
            timestamp: {
              "$toDate": {
                "$multiply": [
                  {
                    $toLong: "$$m.timestamp"
                  },
                  1000
                ]
              }
            }
          }
        }
      }
    }
  }
],
{
  multi: true
})

Mongo playground

For _id: 5-8, simply update the field.

db.collection.update({
  _id: {
    $in: [
      5,
      6,
      7,
      8
    ]
  }
},
[
  {
    "$addFields": {
      "timestamp": {
        "$toDate": {
          "$multiply": [
            {
              $toLong: "$timestamp"
            },
            1000
          ]
        }
      }
    }
  }
],
{
  multi: true
})

Mongo playground