How to compare two fields of one subdocument in array?

Question

Given some documents in MongoDB

[
  {
    "id": 1,
    "items": [
      {
        "id": "T0001",
        "x": 10,
        "y": 10
      },
      {
        "id": "T0002",
        "x": 10,
        "y": 5
      },
      {
        "id": "T0003",
        "x": 10,
        "y": 10
      },
      {
        "id": "T0004",
        "x": 10,
        "y": 20
      }
    ]
  },
  {
    "id": 2,
    "items": [
      {
        "id": "T0001",
        "x": 10,
        "y": 5
      },
      {
        "id": "T0002",
        "x": 10,
        "y": 15
      }
    ]
  }
]

I would like to remove the subdocuments with items.id="T0001" and items.id="T0002" in document id=1. This could be done with the following command.

db.collection.update({
  id: 1
},
{
  $pull: {
    items: {
      id: {
        $in: [
          "T0001",
          "T0002"
        ]
      }
    }
  }
})

However, if I would like to add one more condition "items.x === items.y" into the $pull operation. (i.e. only the subdocument whose id="T0001" will be removed because its x=10 and y=10)

The sample code can be found here https://mongoplayground.net/p/rYJp8wQPpcS.

Could anyone show me some tips to solve this problem? Thank you!

score 2 · Answer 1 · answered May 23 '22 at 08:40

Working on the update with the aggregation pipeline will be easier.

$set - Set the items field.

1.1. $filter - Filter the items array by condition:

1.1.1. $not - Negate the result of 1.1.1.1.

1.1.1.1 $and - Fulfill both conditions of id in ["T0001", "T0002"] and x and y are equal.

db.collection.update({
  id: 1
},
[
  {
    $set: {
      items: {
        $filter: {
          input: "$items",
          cond: {
            $not: {
              $and: [
                {
                  $in: [
                    "$$this.id",
                    [
                      "T0001",
                      "T0002"
                    ]
                  ]
                },
                {
                  $eq: [
                    "$$this.x",
                    "$$this.y"
                  ]
                }
              ]
            }
          }
        }
      }
    }
  }
])

Sample Mongo Playground

How to compare two fields of one subdocument in array?

1 Answers1