Why is my Mongodb aggregation pipeline updateOne() working in compass, but not working in my node driver

Wayne_Barker · December 14, 2022, 1:53am

Problem:

I need to only update one document in a nested array of subdocuments. My previous query was updating all matching documents which is no good Example Below. So I decided to use aggregation so that I could add a limit stage so that I could only update one item, but I cannot get the update to happen through node and I am not even getting errors.

Old Query/Update:

This query updates all documents that match the shape of userId: "empty"

           const query = await db.collection('events').updateOne({
                _id: new ObjectId(eventId),
                createdBy: new ObjectId(createdBy),
                "weights.weight": weight
            },
                {
                    $set: {
                        "weights.$.spotsAvailable.$[el2]": {
                            "name": applicantName,
                            "userId": new ObjectId(applicantId)
                        }
                    }
                },
                {
                    arrayFilters: [
                        {
                            "el2.userId": "empty"
                        }
                    ]
                })

I have tested the aggregation in the MongoDB compass aggregation builder and it works fine.
But in the actual node code no luck

I have tried:

Pluging in variables without the new ObjectId syntax
Plugin in variables with the new ObjectId syntax
Using the exact same hardcoded values that I got from copying the aggregation code from compass for the node driver

Code:

const acceptOrRemoveApplicant = async (eventId: ObjectId, createdBy: ObjectId, applicantId: ObjectId, applicantName: string, boolean: boolean, weight: number): Promise<boolean | undefined> => {
    console.log({ eventId, createdBy, applicantId, applicantName, boolean, weight })
    if (boolean == true) {
        try {
            /*
         * Requires the MongoDB Node.js Driver
         * https://mongodb.github.io/node-mongodb-native
         */

            const agg = [
                {
                    '$match': {
                        '_id': new ObjectId('6398c34ca67dbe3286452f23'),
                        'createdBy': new ObjectId('636c1778f1d09191074f9690')
                    }
                }, {
                    '$unwind': {
                        'path': '$weights'
                    }
                }, {
                    '$unwind': {
                        'path': '$weights.spotsAvailable'
                    }
                }, {
                    '$match': {
                        'weights.spotsAvailable.name': 'empty',
                        'weights.weight': 15
                    }
                }, {
                    '$limit': 1
                }, {
                    '$set': {
                        'weights.spotsAvailable.name': 'Wayen',
                        'weights.spotsAvailable.userId': '123456'
                    }
                }
            ]

            const client = await clientPromise;
            const db = client.db();
            const query = db.collection('events').aggregate(agg);


            // const query = await db.collection('events').updateOne({
            //     _id: new ObjectId(eventId),
            //     createdBy: new ObjectId(createdBy),
            //     "weights.weight": weight
            // },
            //     {
            //         $set: {
            //             "weights.$.spotsAvailable.$[el2]": {
            //                 "name": applicantName,
            //                 "userId": new ObjectId(applicantId)
            //             }
            //         }
            //     },
            //     {
            //         arrayFilters: [
            //             {
            //                 "el2.userId": "empty"
            //             }
            //         ]
            //     })

            if (query) {
                console.log("we queried")
                console.log({ query })
                return true
            } else {
                throw new Error("User not added to event")
            }

        } catch (e) {
            console.error(e);
        }

steevej · December 14, 2022, 1:36pm

If the code works in one place it should at the other.

I am suspicion about

"arrayFilters" : [ { "el2.userId": "empty" } ]

It does not seem to match the documented syntax. From what I read it should be

"arrayFilters" : [ { "el2" : { "userId": "empty" } } ]

How is the actual result differs from the expected modification? Share the UpdateResult?

Please share some sample documents.

Wayne_Barker · December 15, 2022, 12:22pm

Example document

{
  "_id": {
    "$oid": "6398c34ca67dbe3286452f23"
  },
  "name": "test",
  "createdBy": {
    "$oid": "636c1778f1d09191074f9690"
  },
  "description": "testing",
  "date": {
    "$date": {
      "$numberLong": "1645488000000"
    }
  },
  "location": {
    "type": "Point",
    "coordinates": [
      0,
      0
    ]
  },
  "weights": [
    {
      "spotsAvailable": [
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "empty",
          "userId": "empty"
        }
      ],
      "weight": 12
    },
    {
      "spotsAvailable": [
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "empty",
          "userId": "empty"
        }
      ],
      "weight": 15
    }
  ],
  "eventApplicants": [
    {
      "userId": {
        "$oid": "636c1778f1d09191074f9690"
      },
      "name": "Wayne Wrestler",
      "weight": 15
    }
  ]
}

Wayne_Barker · December 15, 2022, 12:24pm

Thank you so much for taking the time to help me with my question I really appreciate it. How do you know when you should use the el2.userId: "empty" or the el2: {userId:"empty"} syntax. Also, do you have any idea why my aggregation isn’t working? I copied the exact code from the mongo compass aggregation builder that worked as I wanted, but my code isn’t working in node driver.

Wayne_Barker · December 15, 2022, 12:52pm

Also for whatever reason Your array filter returns the following query object without updating the document:

 query: {
    acknowledged: true,
    modifiedCount: 0,
    upsertedId: null,
    upsertedCount: 0,
    matchedCount: 1
  }

steevej · December 15, 2022, 3:03pm

It is always, as documented:

<identifier> : <expression>

The identifier being the name you use withing the square brakets, el2 in your case. And the expression, the query to perform on the array element, userId:empty in your case.

As for

it means that 1 document matches the query part, that is:

and

means that no array element matched the array filter, so there was nothing to update.

If you think it should, please share the exact input document and the exact updateOne you used.

Wayne_Barker · December 15, 2022, 9:53pm

Please note some of the weights are coming up as strings, but I have confirmed that they are all numbers(int32) using form validation

Event:

{
  "_id": {
    "$oid": "6398c34ca67dbe3286452f23"
  },
  "name": "test",
  "createdBy": {
    "$oid": "636c1778f1d09191074f9690"
  },
  "description": "testing",
  "date": {
    "$date": {
      "$numberLong": "1645488000000"
    }
  },
  "location": {
    "type": "Point",
    "coordinates": [
      0,
      0
    ]
  },
  "weights": [
    {
      "spotsAvailable": [
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "empty",
          "userId": "empty"
        }
      ],
      "weight": 12
    },
    {
      "spotsAvailable": [
        {
          "name": "Wayne Wrestler",
          "userId": {
            "$oid": "636c1778f1d09191074f9690"
          }
        },
        {
          "name": "Wayne Wrestler",
          "userId": {
            "$oid": "636c1778f1d09191074f9690"
          }
        }
      ],
      "weight": 15
    }
  ],
  "eventApplicants": [
    {
      "userId": {
        "$oid": "636c1778f1d09191074f9690"
      },
      "name": "Wayne Wrestler",
      "weight": 12
    }
  ]
}

User:

{
  "_id": {
    "$oid": "636c1778f1d09191074f9690"
  },
  "name": "Wayne Wrestler",
  "email": "wakywayne80@gmail.com",
  "image": "https://lh3.googleusercontent.com/a/ALm5wu32gXjDIRxncjjQA9I4Yl-sjFH5EWsTlmvdM_0kiw=s96-c",
  "emailVerified": {
    "$date": {
      "$numberLong": "1670864727212"
    }
  },
  "createdEvents": [
    {
      "createdEventName": "test",
      "createdEventDate": {
        "$date": {
          "$numberLong": "1645488000000"
        }
      },
      "createdEventDescription": "testing",
      "createdEventWeights": [
        {
          "weight": "12",
          "filled": [
            false,
            false,
            false
          ]
        },
        {
          "weight": "15",
          "filled": [
            false,
            false
          ]
        }
      ],
      "createdEventId": {
        "$oid": "6398c34ca67dbe3286452f23"
      }
    }
  ],
  "userSignedUpEvents": [],
  "availableWeights": [
    1,
    123
  ],
  "signedUpEvents": [
    {
      "eventId": {
        "$oid": "636c722f67642c30dc5ffc30"
      },
      "eventName": "Utah",
      "eventDate": {
        "$date": {
          "$numberLong": "1667913330000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "636c722f67642c30dc5ffc30"
      },
      "eventName": "Utah",
      "eventDate": {
        "$date": {
          "$numberLong": "1667913330000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "637ec484ac2d675b30590b47"
      },
      "eventName": "Maybe?",
      "eventDate": {
        "$date": {
          "$numberLong": "1672272000000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "636c722f67642c30dc5ffc30"
      },
      "eventName": "Utah",
      "eventDate": {
        "$date": {
          "$numberLong": "1667913330000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "638d5274628db2a7bf61df49"
      },
      "eventName": "Eva's",
      "eventDate": {
        "$date": {
          "$numberLong": "1698019200000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "636c722f67642c30dc5ffc30"
      },
      "eventName": "Utah",
      "eventDate": {
        "$date": {
          "$numberLong": "1667913330000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "6398a922abb5c168ede595fb"
      },
      "eventName": "Nikko's event",
      "eventDate": {
        "$date": {
          "$numberLong": "1670976000000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "6398a922abb5c168ede595fb"
      },
      "eventName": "Nikko's event",
      "eventDate": {
        "$date": {
          "$numberLong": "1670976000000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "6398c34ca67dbe3286452f23"
      },
      "eventName": "test",
      "eventDate": {
        "$date": {
          "$numberLong": "1645488000000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "6398c34ca67dbe3286452f23"
      },
      "eventName": "test",
      "eventDate": {
        "$date": {
          "$numberLong": "1645488000000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "6398c34ca67dbe3286452f23"
      },
      "eventName": "test",
      "eventDate": {
        "$date": {
          "$numberLong": "1645488000000"
        }
      },
      "accepted": false
    },
    {
      "eventId": {
        "$oid": "6398c34ca67dbe3286452f23"
      },
      "eventName": "test",
      "eventDate": {
        "$date": {
          "$numberLong": "1645488000000"
        }
      },
      "accepted": false
    }
  ]
}

Query:

        const query = await db.collection('events').updateOne({
                _id: new ObjectId("6398c34ca67dbe3286452f23"),
                createdBy: new ObjectId("636c1778f1d09191074f9690"),
                "weights.weight": 12
            },
                {
                    $set: {
                        "weights.$.spotsAvailable.$[el2]": {
                            "name": "Wayne Wrestler",
                            "userId": new ObjectId("636c1778f1d09191074f9690")
                        }
                    }
                },
                {
                    arrayFilters: [{ "el2": { "userId": "empty" } }]
                })

            if (query) {
                console.log("we queried")
                console.log({ query })
                return true
            } else {
                throw new Error("User not added to event")
            }

Wayne_Barker · December 18, 2022, 3:29am

Don’t feel bad you are taking the time to help me and I really appreciate it. In the above, I meant to say schema validation. Not form I hope that didn’t make me sound like I don’t know anything

steevej · December 18, 2022, 1:57pm

This is still in my bookmarks.

steevej · December 19, 2022, 2:41am

I feel really really bad.

You had the correct the correct syntax with

And I sent you in the wrong direction. You had the correct syntax, I am not sure why you had

It does work in mongosh with your syntax but it does not do what I think you want it to do. I think you want to assign the applicant to an available spot. With arrayFilters like this all elements matching are updated so in your case the applicant will be assign all available spots.

I understand that you pre-fill the spotsAvailable array with spots to fill but with the arrayFilters issue above I do not think that this could work without complex $map and/or $filter. I would consider to

start with an empty spotsAvailable array
keep a count of available spots with the weight object
use $inc with -1 and $push in your update

To resume I would start with a document that looks like:

/* fields not related to the use case have been edited out */
{
  "_id" : 1 ,
  "weights": [
    {
      "spots": [ ] ,
      "weight": 12 ,
      "spotsAvailable" : 3
    },
    {
      "spots": [ ] ,
      "weight": 15 ,
      "spotsAvailable" : 2
    }
  ]
}

And the the update query

query = {
    "_id" : 1 ,
    "weights" : { "$elemMatch" : { "weight" : 12 ,"spotsAvailable" : { "$gt" : 0 } }
}
update = {
    "$inc" : { "weights.$.spotAvailable" : -1} ,
    "$push" : { "weights.$.spots" : { name : "steevej" , "userId" : 369 } }
}
db.events.updateOne( query , update )

Wayne_Barker · December 22, 2022, 4:06pm

But my aggregation pipeline limits to one before it runs the $set. So shouldn’t it only be updating one? And even if my aggregation is updating all spots I can’t even get that behavior to work. When I run my aggregation pipeline nothing happens and I don’t even get an error

steevej · December 24, 2022, 8:18pm

An aggregation does not update the original document and since you are calling updateOne, I assume that you want to update the original document. So whatever what you end up doing in your aggregation with limit:1 or not, your original document will not be updated.

I was able to update all wieghts.$.spotsAvailable by using the arrayFilters:

{ "el2" : { "userId" : "empty" , "name" : "empty" } }

I still do not know how to set up a filter that test only one of the field of a sub-object.

I still do not know how to limit the update to a single element using arrayFilters.

The only way I could make work what I think you want to achieve (assign an available spot to a participant) is by modifying your model to

steevej:

{
  "_id" : 1 ,
  "weights": [
    {
      "spots": [ ] ,
      "weight": 12 ,
      "spotsAvailable" : 3
    },
    {
      "spots": [ ] ,
      "weight": 15 ,
      "spotsAvailable" : 2
    }
  ]
}

and update with

steevej:

query = {
    "_id" : 1 ,
    "weights" : { "$elemMatch" : { "weight" : 12 ,"spotsAvailable" : { "$gt" : 0 } }
}
update = {
    "$inc" : { "weights.$.spotAvailable" : -1} ,
    "$push" : { "weights.$.spots" : { name : "steevej" , "userId" : 369 } }
}
db.events.updateOne( query , update )

Wayne_Barker · December 29, 2022, 10:02pm

Thank you I didn’t realized that the aggregation pipeline only updates a newly created document and not the original. Lesson learned, I will definitely have to update my database structer.

system · January 3, 2023, 10:03pm

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