How to do a $lookup with pipeline doing matches on an array of objects evaluating a boolean and OID

Question

I'm working with mongo and I'm currently facing an issue that I quite can't figure out.

We have 2 collections, and we are doing an aggregate on the first one with a lookup on the second one

this is and example of the shape of the collections

// document item in collection Items
let item = {
   ...
   category: ObjectId('A')
}

// document partner in collection Partners
let partner = {
   ...
   supportedCategories: [{ category: ObjectId('A'), active: true }, ...]
}

The intention behind this is to get all ITEMS, adding a field on them called 'partners' with the list of partners that within the supportedCategories array, have a matching category and the active flag is true

Right now we have this $lookup without pipeline, which is retrieving all partners matching the category ID but not taking into consideration the other boolean

{
  $lookup: {
    from: 'partners',
    localField: 'category',
    foreignField: 'supportedCategories.category',
    as: 'joinedPartners',
  },
},

I'm now having troubles adding this other match to the 'active'. I've tried a lot of different things non with success.. the latest lookup one I was trying is

{
  $lookup: {
    from: "partners",
    let: {
      mycat: "$category"
    },
    pipeline: [
      {
        $match: {
          supportedCategories: {
            $elemMatch: {
              category: '$$mycat',
              active: true
            }
          }
        },
      },
    ],
    as: "joinedPartners"
  }
}

So in this last try, what is failing is actually the match of category: '$$mycat'.. the active:true works fine, and everything works totally right if I replace '$$mycat' for a hardcoded value, like category: ObjectId('65282c77bcad9b5380acfa28'). So I think what I can't figure out is how to used the value stored on the 'let' map.

Any clues on what I'm doing wrong or what am I missing here?

Tried several different alternatives with $projection, $expr, $elemMatch. can't get it to match both fields

Answer 1

In your $lookup subpipeline, you can use $map to map the array entries to an array of boolean with your condition check. Then, use $anyElementTrue to filter on the result array for filtering.

db.items.aggregate([
  {
    "$match": {
      "category": "A"
    }
  },
  {
    "$lookup": {
      "from": "partners",
      "let": {
        "mycat": "$category"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$anyElementTrue": {
                "$map": {
                  "input": "$supportedCategories",
                  "as": "cat",
                  "in": {
                    $and: [
                      {
                        $eq: [
                          "$$cat.category",
                          "$$mycat"
                        ]
                      },
                      {
                        $eq: [
                          true,
                          "$$cat.active"
                        ]
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      ],
      as: "joinedPartners"
    }
  }
])

Mongo Playground