set all fields in subdocument to false, then set the second one to true in a single query

user_8613630 0
Viewed 41

Suppose I have the following the document structure.

[
  {
    "_id": 1,
    "depots": [
      {
        "_id": 1,
        "isFavourite": true
      },
      {
        "_id": 2,
        "isFavourite": false
      },
      {
        "_id": 3,
        "isFavourite": true
      },
      {
        "_id": 4,
        "isFavourite": false
      }
    ]
  }
]

I want to write a single update query which filters for the document with _id: 1 and first sets every isFavourite value to false and then sets the second isFavourite value (or any specified index) to true.

The resulting document should look like this.

[
  {
    "_id": 1,
    "depots": [
      {
        "_id": 1,
        "isFavourite": false
      },
      {
        "_id": 2,
        "isFavourite": true
      },
      {
        "_id": 3,
        "isFavourite": false
      },
      {
        "_id": 4,
        "isFavourite": false
      }
    ]
  }
]

What I tried:

db.collection.update({
  _id: 1
},
[
  {
    "$set": {
      "depots.isFavourite": false
    }
  },
  {
    "$set": {
      "depots.2.isFavourite": true
    }
  }
])

Yet strangely this does not work. See the linked playground for the result of this query.

Mongo Playground

aggregation-framework mongodb-query mongodb
2 Answers

Using the index as a dot notation only works when the update is not a pipeline.

One option is to "rebuild" the array using $reduce, which allow us to use the size of the currently built array to find the item with the requested index, and then $mergeObjects it with the updated field and value:

db.collection.update(
{_id: 1},
[
  {$set: {"depots.isFavourite": false}},
  {$set: {
      depots: {
        $reduce: {
          input: "$depots",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                {$cond: [
                    {$eq: [{$size: "$$value"}, requestedIndex]},
                    {$mergeObjects: ["$$this", {isFavourite: true}]},
                    "$$this"
                  ]
                }
              ]
            ]
          }
        }
      }
    }
  }
])

See how it works on the playground example

user_184823100

What do you think about this:

db.collection.update({
  _id: 1
},
{
  "$set": {
    "depots.$[y].isFavourite": false,
    "depots.$[x].isFavourite": true
  }
},
{
  arrayFilters: [
    {
      "x._id": 2
    },
    {
      "y._id": {
        $ne: 2
      }
    }
  ],
  "multi": true
})

Explained:

Set two arrayFilters x & y that match the two conditions ...

Playground

user_104150470
Related