MongoDB - $filter 2 array contains object

Question

Let's say I have a document like bellow

{
  fieldA : [
    {
      _id : 1,
      value : 1,
    },
    {
      _id : 2
      value : 2,
    },
    {
      _id : 3,
      value : 3,
    },
  ],
  fieldB : [
    {
      _id : 2
    },
    {
      _id : 3
    },
    {
      _id : 4
    },
  ],
  
}

I want to filter which _id in fieldB has in fieldA and take value in fieldA, add to new field name fieldC

Expected output

  fieldC : [
    {
      _id : 2,
      value : 2,
    },
    {
      _id : 3,
      value : 3,
    },
  ]

I tried using $filter in $addFields but it returned an empty array

{
  $addFields : {
    fieldC : {
      $filter : {
        input : "$fieldB",
        cond : {
          $in : ["$$this._id", "$fieldA._id"]
        }
      }
    }
  }
}
fieldC = []

Is `fieldC` just an output or do you seek an *update* back into the collection? — Buzz Moschetti, Jun 01 '22 at 15:09

score 1 · Answer 1 · answered May 31 '22 at 23:50

From my understanding, your expected behaviour is actually a set intersection behaviour. You may simply "swap" your $filter to do a._id in b._id.

db.collection.aggregate([
  {
    "$addFields": {
      "fieldC": {
        "$filter": {
          "input": "$fieldA",
          "as": "a",
          "cond": {
            "$in": [
              "$$a._id",
              "$fieldB._id"
            ]
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.

Minor note: to vend *only* `fieldC` change `$addFields` above to `$project`. — Buzz Moschetti, Jun 01 '22 at 15:10

MongoDB - $filter 2 array contains object

1 Answers1