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:

1. make sure the _id = 636c2c6dcb82e7ae6aac0960
2. Make sure weights.weight = 0
3. 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"}] })

CodePudding user response：

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