Why won't this query work?

Hello,

I have documents with the array emails of subdocuments:

    "name": "John Doe",
    "emails" : [
        {
            "email" : "test@test-email.com"
        }
    ],

I am trying to locate and replace all the subdocuments that contain an address domain @test-email.com to @test-email2.com .

However, when I try to run this query, it literally inserts the expression itself into the documents instead of running it. What is wrong with this query?

db.getCollection("clients").updateMany(
    { "emails.email": {$regex: "@test-email\\.com$"}},
    { $set: {"emails.$[v].email": {$replaceOne: { input: "$emails.email", find: "@test-email.com", replacement: "@test-email2.com"}}}},
    {arrayFilters: [{ "v.email": {$regex: "@test-email\\.com$"}}]}
);

Try wrapping the update statement in [ ] brackets, i.e.:

db.myCol.updateMany(
    {myField:'myValue'},
    [
        {$set:{'updateFieldName':'updateFieldValue'}}
    ]
)

I don’t think that works when arrayFilters are being used. I have tried it and I get the query error:

MongoServerError: Unrecognized pipeline stage name: ‘arrayFilters’

Good point, quickly digging about i saw this:

aggregation framework - MongoDB - arrayFilters and updateMany from same document - Stack Overflow.

I’m away from a pc at the moment so cannot have a play with the query.

That does aeem rather convulated to run an update like this!

Hey,

Thank you for your reply. Following the link you have provided I have tried to apply the proposed solution for my use case as follows:

db.getCollection("clients").updateMany(
    { "emails.email": { $regex: "@test-email\\.com$" } },
    [{
        $set: {
            "emails": {
                $map: {
                    input: "$emails",
                    in: {
                        $mergeObjects: [
                            "$$this",
                            {
                                email: {
                                    $replaceOne: { input: "$$this.email", find: "@test-email.com", replacement: "@test-email2.com" }
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
    ]
);

It appears to have worked. Not sure what $mergeObjects exactly does here?

Checking the documentation there seems to be not a lot I could see that specifies the limits imposed when using an arrayFilter.

I also tried using a bulk operator but that seemed to have the same issue, as soon as you have the arrayFilter then you can no longer use the pipeline operations.

So you’re limited to non-pipeline style actions apart from a handful when using an arrayfilter. I’ve only used arrayFilters in a few projects, typically when basically unwinding old relational data and merging in one child table into it’s parent, where a relationship already exists, so imagine a nested table within a nested table. But that’s not important here!

In the above case, $mergeObject and $map seem to be allowed.

In the above case, it seems that the option given in the SO post was using $map to loop over every element of the array and then set the emails element equal to the output of that map.

In the case here, each element that we’re looping over for the $map is an object and not a value. Imagine if we had this data:

    "name": "John Doe",
    "emails" : [
            "test@test-email.com",
            "john@test.com"
    ],

In this case we could just do the following:

db.getCollection("Test").updateMany(
    {},
    [{
        $set: {
            "emails": {
                $map: {
                    input: "$emails",
                    in: {
                        $replaceOne: { input: "$$this", find: "@test-email.com", replacement: "@test-email2.com" }
                    }
                }
            }
        }
    }
    ]
);

We’re just applying an operator to a string, which is fine.

In the case of an object, I believe the $replaceOne operator is running on the object:

$replaceOne: { input: "$$this.email", find: "@test-email.com", replacement: "@test-email2.com" }

The return of this is a string, just the string, so the output of this when running the update is:

    "emails" : [
        "test@test-email2.com",
        "john@thing.com"
    ]

In order to get around this we use a $mergeObject which will retain the object structure and more importantly, the other elements of the object (if there were any!)

$mergeObjects will keep all items of two objects when merging and replace the overlapping properties with the last item.

So the return value of the $map of an element will be all current properties AND the email property with the new updated value.

I had to look in detail at this myself a bit to reply as it’s not something I do that much, I’m sure someone else can chime in with either a better solution…or tell me what I’ve managed to mangle in my logic above :slight_smile:

John

2 Likes

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