Mongo updateMany statement with an inner array of objects to manipulate-CodePudding

I'm struggling to write a Mongo UpdateMany statement that can reference and update an object within an array.

Here I create 3 documents. Each document has an array called innerArray always containing a single object, with a single date field.

use test;
db.innerArrayExample.insertOne({ _id: 1, "innerArray": [ { "originalDateTime" : ISODate("2022-01-01T01:01:01Z") } ]});

db.innerArrayExample.insertOne({ _id: 2, "innerArray": [ { "originalDateTime" : ISODate("2022-01-02T01:01:01Z") } ]});

db.innerArrayExample.insertOne({ _id: 3, "innerArray": [ { "originalDateTime" : ISODate("2022-01-03T01:01:01Z") } ]});

I want to add a new date field, based on the original date field, to end up with this:

{ _id: 1, "innerArray": [ { "originalDateTime" : ISODate("2022-01-01T01:01:01Z"), "copiedDateTime" : ISODate("2022-01-01T12:01:01Z") } ]}

{ _id: 2, "innerArray": [ { "originalDateTime" : ISODate("2022-01-02T01:01:01Z"), "copiedDateTime" : ISODate("2022-01-02T12:01:01Z") } ]}

{ _id: 3, "innerArray": [ { "originalDateTime" : ISODate("2022-01-03T01:01:01Z"), "copiedDateTime" : ISODate("2022-01-03T12:01:01Z") } ]}

In pseudo code I am saying take the originalDateTime, run it through a function and add a related copiedDateTime value.

For my specific use-case, the function I want to run strips the timezone from originalDateTime, then overwrites it with a new one, equivalent to the Java ZonedDateTime function withZoneSameLocal. Aka 9pm UTC becomes 9pm Brussels (therefore effectively 7pm UTC). The technical justification and methodology were answered in another Stack Overflow question here.

The part of the query I'm struggling with, is the part that updates/selects data from an element inside an array. In my simplistic example, for example I have crafted this query, but unfortunately it doesn't work:

This function puts copiedDateTime in the correct place... but doesn't evaluate the commands to manipulate the date:

db.innerArrayExample.updateMany({ "innerArray.0.originalDateTime" : { $exists : true }}, { $set: { "innerArray.0.copiedDateTime" : { $dateFromString: { dateString: { $dateToString: { "date" : "$innerArray.0.originalDateTime", format: "%Y-%m-%dT%H:%M:%S.%L" }}, format: "%Y-%m-%dT%H:%M:%S.%L", timezone: "Europe/Paris" }}});

// output
  {
    _id: 1,
    innerArray: [
      {
        originalDateTime: ISODate("2022-01-01T01:01:01.000Z"),
        copiedDateTime: {
          '$dateFromString': {
            dateString: { '$dateToString': [Object] },
            format: '%Y-%m-%dT%H:%M:%S.%L',
            timezone: 'Europe/Paris'
          }
        }
      }
    ]
  }

This simplified query, also has the same issue:

b.innerArrayExample.updateMany({ "innerArray.0.originalDateTime" : { $exists : true }}, { $set: { "innerArray.0.copiedDateTime" : "$innerArray.0.originalDateTime" }});

//output
  {
    _id: 1,
    innerArray: [
      {
        originalDateTime: ISODate("2022-01-01T01:01:01.000Z"),
        copiedDateTime: '$innerArray.0.originalDateTime'
      }
    ]
  }

As you can see this issue looks to be separate from the other stack overflow question. Instead of being able changing timezones, it's about getting things inside arrays to update.

I plan to take this query, create 70,000 variations of it with different location/timezone combinations and run it against a database with millions of records, so I would prefer something that uses updateMany instead of using Javascript to iterate over each row in the database... unless that's the only viable solution.

I have tried putting $set in square brackets. This changes the way it interprets everything, evaluating the right side, but causing other problems:

test> db.innerArrayExample.updateMany({ "_id" : 1 }, [{ $set: { "innerArray.0.copiedDateTime" : "$innerArray.0.originalDateTime" }}]);

//output
  {
    _id: 1,
    innerArray: [
      {
        '0': { copiedDateTime: [] },
        originalDateTime: ISODate("2022-01-01T01:01:01.000Z")
      }
    ]
  }

Above it seems to interpret .0. as a literal rather than an array element. (For my needs I know the array only has 1 item at all times). I'm at a loss finding an example that meets my needs.

I have also tried experimenting with the arrayFilters, documented on my mongo updateMany documentation but I cannot fathom how it works with objects:

test> db.innerArrayExample.updateMany(
...    { },
...    { $set: { "innerArray.$[element].copiedDateTime" : "$innerArray.$[element].originalDateTime" } },
...    { arrayFilters: [ { "originalDateTime": { $exists: true } } ] }
... );
MongoServerError: No array filter found for identifier 'element' in path 'innerArray.$[element].copiedDateTime'
test> db.innerArrayExample.updateMany(
...    { },
...    { $set: { "innerArray.$[0].copiedDateTime" : "$innerArray.$[element].originalDateTime" } },
...    { arrayFilters: [ { "0.originalDateTime": { $exists: true } } ] }
... );
MongoServerError: Error parsing array filter :: caused by :: The top-level field name must be an alphanumeric string beginning with a lowercase letter, found '0'

If someone can help me understand the subtleties of the Mongo syntax and help me back on to the right path I'd be very grateful.

CodePudding user response：

You want to be using pipelined updates, the issue you're having with the syntax you're using is that it does not allow the usage of aggregation operators and document field values.

Here is a quick example on how to do it:

db.collection.updateMany({},
[
  {
    "$set": {
      "innerArray": {
        $map: {
          input: "$innerArray",
          in: {
            $mergeObjects: [
              "$$this",
              {
                copiedDateTime: "$$this.originalDateTime"
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground