Update property of a nested object in array with its lowercased value

Working with Data
1

Hi, this is the first time I have written here for help, I hope to be the more precise I can.

Having the following documents

[{
    "_id" : 206,    
    "Events" : [
        {
            "_t" : "PageReviewed",            
            "UpdateDate" : null,
        },
        {
            "_t" : "PageMetadataSet",            
            "UpdateDate" : null,
            "Metadata" : {
                "Category" : "Any caSE cateGory",
                "Name" : "Any CaSe name",
                "Culture" : "*",
                "Environment" : "*",
                "Platform" : "*",
                "Value" : true,
                "ValueTypeName" : "System.Boolean"
            }
        }
    ]
},
{
    "_id" : 207,    
    "Events" : [
        {
            "_t" : "PageReviewed",
            "UpdateDate" : null,
        },
        {
            "_t" : "PageMetadataSet",            
            "UpdateDate" : null,
            "Metadata" : {
                "Category" : "Any casing Category",
                "Name" : "Any casing Name",
                "Culture" : "*",
                "Environment" : "*",
                "Platform" : "*",
                "Value" : false,
                "ValueTypeName" : "System.Boolean"
            }
        }
    ]
}]

I would need a way to update the nested property Category and Name inside the Metadata object contained in the Events array.
I’ve tried the updateMany approach with the array element, but I cannot access the original value of the property to be lowercased:

const lowercase = (value) => {
    return value.toLowerCase();
}

db.getCollection("vsm.commits.bck").updateMany(
    {
        "Events._t": /MetadataSet/
    },
    { 
        $set : {
            "Events.$[m].Metadata.Name": lower("Events.$[m].Metadata.Name")
        }
    },    
    {
        arrayFilters: [
            {"m.Metadata" : {$exists: true}}
        ]
    }
)

Unfortunately, I cannot achieve the desired update since the reference to the item inside the $set to get the original value is not resolved. ($set : { “Events.$[m].Metadata.Name”: lower(“Events.$[m].Metadata.Name”) })

The Name field has been updated with the string specified :frowning:

"Events.1.Metadata.Name" : "events.$.metadata.name"
2

I had a play with this as it was something I’ve not had to do before…

One issue here is that you’re creating the lower function and this is running on the string “Events.$[m].Metadata.Name” before this is being sent to the server so the query you’re sending to the server is:

            "Events.$[m].Metadata.Name": "events.$[m].metadata.name"

Which is not what you want, you want to get the server to perform a lower function on the value that’s evaluated.

The next thing I though was to use the aggregate operator $toLower so I put that in, this meant swapping to an aggregation based update (note the wrapping square brackets):

db.test.updateMany(
  {
  },
  [
    {
      $set:{
         'A':{$toLower:'$myField'}
      }
    }
  ]
)

Unfortunately, when swapping to this, you cannot use arrayFilters…

So, checking some SO and other forum replies of similar questions, you can instead use a $map to manipulate the data we have and replace the current array entries with the new value.
So the plan was to use a $map and then we can merge the current object with another where we’ve swapped the case of the field to lower.
Unfortunately, a $merge will not merge child elements, only the top level it seems to merging with a basic object with just the Name field set, removed all other data.
Another issue was that not all documents have this field, we want to only set it on array elements that have the field. We can get round this with a $cond.

So in summary (and that was a lot of rambling, apologies) this is what I came up with:

db.getCollection("Test").updateMany(
{
    "Events._t": /MetadataSet/
},
[
    {
        $set:{
            Events:{
                $map:{
                    input:'$Events',
                    as: 'thisEvent',
                    in:{
                        $cond:[
                            {$gt:['$$thisEvent.Metadata.Name', null]},
                            {
                                $mergeObjects:[
                                    '$$thisEvent',
                                    {
                                        'Metadata':{
                                            $mergeObjects:[
                                                '$$thisEvent.Metadata',
                                                {'Name':{$toLower:'$$thisEvent.Metadata.Name'}}
                                            ]
                                        }
                                    }
                                ]
                            },
                            '$$thisEvent'
                        ]
                    },
                }
            }
        }
    }
]
)

We’re running an update, replacing the Events field with a map of the Events field, that just returns the same array element if it does not have the Metadata.Name field set, of it if does, then it changes the element to the current value, merged with a merge of an object with the lowercase name set into the current Metadata object.

Here it is in mongo playground, but as an aggregate as that does not support updates with aggregate syntax, plus it’s how I stumbled to a solution…

I’ll wait while someone else comes up with a one line equivalent…

/Edit - refs I took a look at

1 Like
3

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.