MongoDB - Update an array of documents with two levels deep which matches conditionals from different levels

Question

What I need to do is only update the availableWeights field if the event id and weight match what I pass in (ObjectId("67823649279") & 0) and the userId field is "empty" exactly. I will replace the empty fields with { userId: ObjectId(), name: "wendy" }.

For example:

I want to be able to:

make sure the _id = 636c2c6dcb82e7ae6aac0960
Make sure weights.weight = 0
Make sure weights.spotsAvailable.userId = "empty"

Here is an example document:

{
  "_id": {
    "$oid": "636c2c6dcb82e7ae6aac0960"
  },
  "name": "onmygowd",
  "createdBy": {
    "$oid": "636c1778f1d09191074f9690"
  },
  "description": "It's gonnam be a good one",
  "date": {
    "$date": {
      "$numberLong": "1667913330000"
    }
  },
  "location": {
    "type": "Point",
    "coordinates": [
      0,
      0
    ]
  },
  "weights": [
    {
      "spotsAvailable": [
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "John",
          "userId": {
            "$oid": "636c1778f1d09191074f9690"
          }
        }
      ],
      "weight": 0
    },
    {
      "spotsAvailable": [
        {
          "name": "empty",
          "userId": "empty"
        },
        {
          "name": "John",
          "userId": {
            "$oid": "636c1778f1d09191074f9690"
          }
        }
      ],
      "weight": 123
    }
  ],
  "eventApplicants": [
    {
      "userId": {
        "$oid": "636c1778f1d09191074f9690"
      },
      "name": "wallace",
      "weight": 123.1
    }
  ]
}

I am pretty sure I need to use array filters, but all 3 of my attempts failed:

1

db.events.updateOne({ _id: ObjectId('636c2c6dcb82e7ae6aac0960'), "weights.weight": 0 },
            { $set: { "weights.spotsAvailable.$[el2]":  { "name": "George", "userId": ObjectId('636c1778f1d09191074f9690') } } },
            { arrayFilters: [ {"el2.userId": "empty"}] })

2

db.events.updateOne(db.events.findOne({_id: ObjectId('636c2c6dcb82e7ae6aac0960'), "weights.weight": 0, "weights.spotsAvailable.userId": "empty"},
{$set: {"weights.$[el].spotsAvailable": {"name": "George", "userId": ObjectId('636c1778f1d09191074f9690')}}},
{arrayFilters:[{"el.spotsAvailable.userId": "empty"}] })

3

db.events.updateOne({ _id: ObjectId('636c2c6dcb82e7ae6aac0960'), "weights.weight": 0 },
            { $set: { "weights.$[el].spotsAvailable.$[el2]":  { "name": "George", "userId": ObjectId('636c1778f1d09191074f9690') } } },
            { arrayFilters: [ {"el.el2.userId": "empty"}] })

Yong Shun · Accepted Answer · 2022-11-11T00:11:00.837

4

Solution 1

You need the $ positional operator for

weights.$.spotsAvailable.$[el2]

Complete query:

db.events.updateOne({
  _id: ObjectId("636c2c6dcb82e7ae6aac0960"),
  "weights.weight": 0
},
{
  $set: {
    "weights.$.spotsAvailable.$[el2]": {
      "name": "George",
      "userId": ObjectId("636c1778f1d09191074f9690")
    }
  }
},
{
  arrayFilters: [
    {
      "el2.userId": "empty"
    }
  ]
})

Demo Solution 1 @ Mongo Playground

Solution 2

Approach 3 is almost right to the answer. You have to add another object for el in arrayFilters.

db.events.updateOne({
  _id: ObjectId("636c2c6dcb82e7ae6aac0960"),
  "weights.weight": 0
},
{
  $set: {
    "weights.$[el].spotsAvailable.$[el2]": {
      "name": "George",
      "userId": ObjectId("636c1778f1d09191074f9690")
    }
  }
},
{
  arrayFilters: [
    {
      "el.weight": 0
    },
    {
      "el2.userId": "empty"
    }
  ]
})

Demo Solution 2 @ Mongo Playground

edited Nov 11 '22 at 00:11

answered Nov 10 '22 at 23:27

Yong Shun

35,286
4
24
46

thank you so much can you explain the difference between using .$ and using .$[] I thought the only reason you used the empty array was so you can use a variable – Wayne Nov 10 '22 at 23:52
`$` is used to update the first matched element with `weight: 0` in the `weights` array. While `$[]` all positional operator will update all the documents in the `weights` array. FYI, I have edit the answer for the Solution 1. – Yong Shun Nov 11 '22 at 00:13
so could we do an identical query where we don't use el2? Since we will only need to match the first element or... Is it that because we are using an array filter we are technically always filtering all elements so we will always want to use the all operator when we are using array filters? THANKS A BUNCH FOR THE HELP BY THE WAY SAVED ME SO MUCH TIME I WAS REALLY STUCK – Wayne Nov 11 '22 at 00:18
Hmmm, I don't think that is possible. The MongoDB query will complain if you use multiple `$` operators. [Demo](https://mongoplayground.net/p/LkpauJLYD_W). From my perspective, `arrayFilters` is still needed. – Yong Shun Nov 11 '22 at 00:24
Hello Yong Shun, I seem to have failed at a similar issue again. If you don't mind taking a quick look: https://stackoverflow.com/questions/74548212/i-am-trying-to-perform-a-pull-on-a-nested-document-for-all-users-in-the-collect – Wayne Nov 23 '22 at 14:15

MongoDB - Update an array of documents with two levels deep which matches conditionals from different levels

1 Answers1

Linked