I have two collections: collA and collB and I'm doing the following lookup:

db.collA.aggregate([
    {
        $lookup: {from: 'collB', localField: '_id', foreignField: 'key', as: 'bs'}
    }])

If we stop here then bs is an array of objects

Every "b" object has a field name.

I'd like to filter only records where one of the bs answers the condition: b.name == query_name.

1. I think $elemMatch isn't possible
2. I also think I can $unwind and $group but it feels like an overhead.

Is there a simple way to filter by this condition?

Sample Data:

collA:

[
    {"_id": "1", "a": 1, "b": 1},
    {"_id": "2", "a": 2, "b": 2}
]

collB:

[
    {"key": "1", "name": "Ron"},
    {"key": "1", "name": "Bob"},
    {"key": "1", "name": "Dana"},
    {"key": "2", "name": "John"},
    {"key": "2", "name": "Ron"}
]

So if the query is Ron I'm expecting to get both documents from collA. If the query is Bob I'm expecting to get only the document with _id == 1

CodePudding user response：

Solution 1

Use $filter in $project stage to filter the document(s) from the array (bs).

db.collA.aggregate([
  {
    $lookup: {
      from: "collB",
      localField: "_id",
      foreignField: "refId",
      as: "bs"
    }
  },
  {
    $project: {
      _id: 1,
      bs: {
        "$filter": {
          "input": "$bs",
          "cond": {
            $eq: [
              "$$this.name",
              /* Filter value */
            ]
          }
        }
      }
    }
  }
])

Sample Solution 1 on Mongo Playground

Solution 2

Use $lookup with pipeline.

db.collA.aggregate([
  {
    $lookup: {
      from: "collB",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$id",
                    "$refId"
                  ]
                },
                {
                  $eq: [
                    "$name",
                    /* Filter value */
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "bs"
    }
  }
])

Sample Solution 2 on Mongo Playground