Mongo DB updateMany

Hello, I start in mongo Db

Here’s what I’m trying to achieve

I have a voteTwentyHourCalcul object which contains the current date (Day) , a time (Hour) and a value (Value)

and voteTwentyHour which is equal to the sum of all the values

"voteTwentyHourCalcul": {
        "twentyHourCalcul": [
            {
                "day": "2022-05-18",
                "hour": 3,
                "value": 1
            },
            {
                "day": "2022-05-19",
                "hour": 3,
                "value": 2
            },
            {
                "day": "2022-05-28",
                "hour": 10,
                "value": 2
            },
            {
               "day": "2022-05-28",
                "hour": 23,
                "value": 5
            },
            {
                "day": "2022-05-29",
                "hour": 4,
                "value": 2
            },
            {
             "day": "2022-05-29",
                "hour": 12,
                "value": 8
            },
            {
                "day": "2022-05-29",
                "hour": 22,
                "value": 5
            },
            {
                "day": "2022-05-29",
                "hour": 23,
                "value": 10
            },
            {
                "day": "2022-05-30",
                "hour": 9,
                "value": 5
            },
            {
                "day": "2022-06-1",
                "hour": 14,
                "value": 2
            },
            {
                "day": "2022-06-2",
                "hour": 5,
                "value": 8
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            }
        ]
    },
    "voteTwentyHour": 50,

I am looking to transform the value to 0 for all those who have a date 24 less than the current date

I think I have already succeeded in this step with the following code

exports.global = (req, res) => {
 
    let yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);
 
    //Yesterday
    let mondayYesterdayUtc = (yesterday.getUTCMonth() + 1)
    mondayYesterdayUtc = parseInt(mondayYesterdayUtc);
    let dayYesterdayUtc = yesterday.getUTCDate()
    dayYesterdayUtc = parseInt(dayYesterdayUtc);
    if (mondayYesterdayUtc < 10) {
        mondayYesterdayUtc = '0' + mondayYesterdayUtc.toString()
    }
 
    if (dayYesterdayUtc < 10) {
        dayYesterdayUtc = '0' + dayYesterdayUtc.toString()
    }
 
 
    let dateYesterdayUtc = yesterday.getFullYear() + '-' + mondayYesterdayUtc + '-' + dayYesterdayUtc;
 
 
    Project.updateMany(
        {},
        {
            $set: {
                "voteTwentyHourCalcul.twentyHourCalcul.$[elem].value": 0
            },
        },
        {
 
            arrayFilters: [
 
                {
                    $or: [
                        { "elem.day": { $lt: dateYesterdayUtc } },
                        { $and: [{ "elem.day": dateYesterdayUtc }, { "elem.hour": { $gte: yesterday.getUTCHours() } }] }
                    ]
                }]
        },
 
        (err, response) => {
            if (err) return res.status(500).json({ msg: 'update failed', error: err });
            res.status(200).json({ msg: `document updated`, response: response });
        });
 
};

I get the following result

"voteTwentyHourCalcul": {
        "twentyHourCalcul": [
            {
                "day": "2022-05-18",
                "hour": 3,
                "value": 0
            },
            {
                "day": "2022-05-19",
                "hour": 3,
                "value": 0
            },
            {
                "day": "2022-05-28",
                "hour": 10,
                "value": 0
            },
            {
               "day": "2022-05-28",
                "hour": 23,
                "value": 0
            },
            {
                "day": "2022-05-29",
                "hour": 4,
                "value": 0
            },
            {
             "day": "2022-05-29",
                "hour": 12,
                "value": 0
            },
            {
                "day": "2022-05-29",
                "hour": 22,
                "value": 0
            },
            {
                "day": "2022-05-29",
                "hour": 23,
                "value": 0
            },
            {
                "day": "2022-05-30",
                "hour": 9,
                "value": 0
            },
            {
                "day": "2022-06-1",
                "hour": 14,
                "value": 0
            },
            {
                "day": "2022-06-2",
                "hour": 5,
                "value": 8
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            },
            {
                "day": 0,
                "hour": 0,
                "value": 0
            }
        ]
    },
    "voteTwentyHour": 50,

Request made on time at 11:34 UTC 2022-06-2

We obtain the value 0 for all the dates older than 24 hours compared to the current date

Now what I would like to do is recalculate “voteTwentyHour” which is equal to the sum of all the values
After the query it should be equal to 8

How should I go about this?

Thanks to everyone who tries to help me

Hi @Mielpops,

I inserted this in my collection called coll:

{
  _id: ObjectId("629919a4c1da581a878e5db3"),
  voteTwentyHourCalcul: {
    twentyHourCalcul: [
      { day: '2022-05-18', hour: 3, value: 0 },
      { day: '2022-05-19', hour: 3, value: 0 },
      { day: '2022-05-28', hour: 10, value: 0 },
      { day: '2022-05-28', hour: 23, value: 0 },
      { day: '2022-05-29', hour: 4, value: 0 },
      { day: '2022-05-29', hour: 12, value: 0 },
      { day: '2022-05-29', hour: 22, value: 0 },
      { day: '2022-05-29', hour: 23, value: 0 },
      { day: '2022-05-30', hour: 9, value: 0 },
      { day: '2022-06-1', hour: 14, value: 0 },
      { day: '2022-06-2', hour: 5, value: 8 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 }
    ]
  },
  voteTwentyHour: 50
}

Then I execute this aggregation pipeline on the entire collection:

[
  {
    '$unwind': {
      'path': '$voteTwentyHourCalcul.twentyHourCalcul'
    }
  }, {
    '$group': {
      '_id': '$_id', 
      'voteTwentyHour': {
        '$sum': '$voteTwentyHourCalcul.twentyHourCalcul.value'
      }
    }
  }, {
    '$merge': {
      'into': 'coll', 
      'on': '_id', 
      'whenMatched': 'merge', 
      'whenNotMatched': 'fail'
    }
  }
]

Result in my collection:

{
  _id: ObjectId("629919a4c1da581a878e5db3"),
  voteTwentyHourCalcul: {
    twentyHourCalcul: [
      { day: '2022-05-18', hour: 3, value: 0 },
      { day: '2022-05-19', hour: 3, value: 0 },
      { day: '2022-05-28', hour: 10, value: 0 },
      { day: '2022-05-28', hour: 23, value: 0 },
      { day: '2022-05-29', hour: 4, value: 0 },
      { day: '2022-05-29', hour: 12, value: 0 },
      { day: '2022-05-29', hour: 22, value: 0 },
      { day: '2022-05-29', hour: 23, value: 0 },
      { day: '2022-05-30', hour: 9, value: 0 },
      { day: '2022-06-1', hour: 14, value: 0 },
      { day: '2022-06-2', hour: 5, value: 8 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 },
      { day: 0, hour: 0, value: 0 }
    ]
  },
  voteTwentyHour: 8
}

As you can see, my voteTwentyHour has been updated correctly.

I only have a single doc here but because I’m grouping on _id, this would update the entire collection correctly. But feel free to add a $match at the beginning of the pipeline to only update a subset of docs.

Cheers,
Maxime.

Hello, thank you for your answer

How to do if I have several collections, I would like to use your solution for all of my collections with update Many

You will have to do a FOR loop over your list of collections. You can’t $merge into several collections.
The good news is that these can run in parallel if they are running in a multi-threaded program (one thread per collection).

Here’s what I tried, but it doesn’t seem to work
I must be doing it wrong

[image]

[image]

db.coll.insertMany
([

{
_id: “1”,
voteTwentyHourCalcul: {
twentyHourCalcul: [
{ day: ‘2022-05-18’, hour: 3, value: 0 },
{ day: ‘2022-05-19’, hour: 3, value: 0 },
{ day: ‘2022-05-28’, hour: 10, value: 0 },
{ day: ‘2022-05-28’, hour: 23, value: 0 },
{ day: ‘2022-05-29’, hour: 4, value: 0 },
{ day: ‘2022-05-29’, hour: 12, value: 0 },
{ day: ‘2022-05-29’, hour: 22, value: 0 },
{ day: ‘2022-05-29’, hour: 23, value: 0 },
{ day: ‘2022-05-30’, hour: 9, value: 0 },
{ day: ‘2022-06-1’, hour: 14, value: 0 },
{ day: ‘2022-06-2’, hour: 5, value: 8 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 }
]
},
voteTwentyHour: 50
},
{
_id: “2”,
voteTwentyHourCalcul: {
twentyHourCalcul: [
{ day: ‘2022-05-18’, hour: 3, value: 0 },
{ day: ‘2022-05-19’, hour: 3, value: 0 },
{ day: ‘2022-05-28’, hour: 10, value: 0 },
{ day: ‘2022-05-28’, hour: 23, value: 0 },
{ day: ‘2022-05-29’, hour: 4, value: 0 },
{ day: ‘2022-05-29’, hour: 12, value: 0 },
{ day: ‘2022-05-29’, hour: 22, value: 0 },
{ day: ‘2022-05-29’, hour: 23, value: 0 },
{ day: ‘2022-05-30’, hour: 9, value: 0 },
{ day: ‘2022-06-1’, hour: 14, value: 0 },
{ day: ‘2022-06-2’, hour: 5, value: 8 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 }
]
},
voteTwentyHour: 50
},
{
_id: “3”,
voteTwentyHourCalcul: {
twentyHourCalcul: [
{ day: ‘2022-05-18’, hour: 3, value: 0 },
{ day: ‘2022-05-19’, hour: 3, value: 0 },
{ day: ‘2022-05-28’, hour: 10, value: 0 },
{ day: ‘2022-05-28’, hour: 23, value: 0 },
{ day: ‘2022-05-29’, hour: 4, value: 0 },
{ day: ‘2022-05-29’, hour: 12, value: 0 },
{ day: ‘2022-05-29’, hour: 22, value: 0 },
{ day: ‘2022-05-29’, hour: 23, value: 0 },
{ day: ‘2022-05-30’, hour: 9, value: 0 },
{ day: ‘2022-06-1’, hour: 14, value: 0 },
{ day: ‘2022-06-2’, hour: 5, value: 8 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 },
{ day: 0, hour: 0, value: 0 }
]
},
voteTwentyHour: 50
},
]);





for (var i = 0; i <= 2; i=i++){
    db.coll.aggregate([
  {
    '$unwind': {
      'path': '$voteTwentyHourCalcul.twentyHourCalcul'
    }
  }, {
    '$group': {
      '_id': '$_id', 
      'voteTwentyHour': {
        '$sum': '$voteTwentyHourCalcul.twentyHourCalcul.value'
      }
    }
  }, {
    '$merge': {
      'into': 'coll', 
      'on': '_id', 
      'whenMatched': 'merge', 
      'whenNotMatched': 'fail'
    }
  }
]
    )
}

hum error

i++ and not i=i++

You mentioned having

but your code access only the collection coll.

You have the variable i in your for-loop but you do not use it in your code.

I feel like you misunderstand the fundamental difference between the terms collection and documents.

1 Like